Hi All,

I have an excel spreadsheet that imports data from an access table. That works fine but when I send the data back to access I'd like to archive the old data first. Can someone help me with the blow code pleasE?




Sub ExportData()

'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim SQL As String
Dim var As Range
Dim ws As Worksheet
Dim TableName As String
Dim ArchiveTable As String


'add error handling
On Error GoTo errHandler:
'Disable screen flickering.
Application.ScreenUpdating = False
'clear the values from the worksheet
'get the path to the database
dbPath = Sheet5.Range("I3").Value
'set the search variable
Set ws = Worksheets("Audit")
Set var1 = ws.Range("C2")
TableName = "[" & var1 & " current" & "]"

'set the search variable

Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a -method- named Open
'--4 aguments-- ConnectionString, UserID, Password, Options
'ConnectionString formula--Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

ArchiveTable = "[" & var1 & " Archive" & "]"
SQL = "DELETE FROM" & " " & TableName & _
" OUTPUT [deleted]" & _
" INTO" & " " & ArchiveTable

Debug.Print SQL

cnn.Execute SQL

'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.

'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing

End Sub

Thanks in advance.