Moving data from one table to another in access stopped working

Hi All,

I have the following code. I posted about it yesterday and then found a way to get it working. It worked for a few hours and now doesn't want to play ball, hoping one of you can help me 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 = "INSERT INTO" & " " & ArchiveTable & _
      "Select * FROM" & TableName



Debug.Print SQL

cnn.Execute SQL

SQL = "DELETE FROM" & ArchiveTable
Debug.Print SQL

cnn.Execute SQL

Set rs = Nothing
Set cnn = Nothing

Call PushTableToAccess

'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 cnn = Nothing


End Sub

The bit of code that doesn't seem to work is the movement from table to table below:

SQL = "INSERT INTO" & " " & ArchiveTable & _
      "Select * FROM" & TableName



Debug.Print SQL

cnn.Execute SQL

It show the below in the immediate box

INSERT INTO [LHR Archive]Select * FROM[LHR Current]

Any help is greatly appreciated.

Thanks.

Thanks. Will try it when i get to work tomorrow. Such a stuoid mistake :/

This still isn't working, I have tried adding the spaces. Are there any other suggestions please?

I worked it out, I had changed some of the field names in the current table but not in the Archive table so it couldn't be moved across. Feel rather stupid for that one!