Runsql and archive problem

[boettger]

Hi:
This site has helped me so much with Access problems I'm hoping someone can help again. This is the code that I'm trying to run. It's easy. If the Year table exists
I insert data from the archive table. If not, copy archive to the new table name.
The problem is that I'm getting a runtime error 3134 saying that there is an error in my insert statement.
Can anyone see what the problem is or give me ideas on how to debug it or
use another command?
Here's the code
Private Sub CheckTables(strdest As String, strsource As String)
Dim tbl As Object, dbs As DAO.Database
Dim foundtable As String
Dim str1 As String
Dim str2 As String
Set dbs = CurrentDb
foundtable = "N"
str1 = "INSERT INTO "
str2 = " (SELECT * FROM ARCHIVE);"
For Each tbl In dbs.TableDefs
' -- Check name of Table
If tbl.name = strdest Then
foundtable = "Y"
End If
Next tbl
If foundtable = "N" Then
DoCmd.CopyObject , strdest, acTable, strsource
Else
DoCmd.RunSQL str1 & strdest & str2
End If
End Sub

Thanks Pat

 

[PaulF]

Too many spaces in the code... leave one space after INTO and one space before SELECT, you can remove the parenthesis.

str1 = "INSERT INTO "
str2 = " SELECT * FROM ARCHIVE;"


PaulF