Why do I keep getting a syntax error when using Sql INSERT INTO statement

[James Watson]

'Microsoft VB 6.3, Access 2002
'Syntax error in the INSERT INTO Statement when the query runs
'How can I make this work?

Private Sub Command52_Click ()
On Error GoTo Err_Command52_C lick

Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Dim rsSQL As DAO.Recordset
Dim strSQL As String
Dim rst As Recordset
Dim strDocName As String, strLinkCriteria As String


Set dbs = CurrentDb
strDocName = "InfoTable"

'MyShow is a textbox on a form that has the name of the target table
'in its text property.
strSQL = "INSERT INTO" & Me!MyShow & _
"SELECT *. FROM [InfoTable];"


DoCmd.RunSQL strSQL

Exit_Command52_ Click:
Exit Sub

Err_Command52_C lick:
MsgBox Err.Description
Resume Exit_Command52_ Click

End Sub

[ajalwaysus]

There appears to be a "." dot in your code that shouldn't be there i believe, and there needs to be a space.

strSQL = "INSERT INTO" & Me!MyShow & _
"SELECT *. FROM [InfoTable];"

Should be:

strSQL = "INSERT INTO" & Me!MyShow & _
" SELECT * FROM [InfoTable];"

Maybe that is it.
-AJ

[missinglinq]

Also, I don't believe you can use Me in a SQL statement, I think you have to use a full reference to the form instead of Me!MyShow.

[nico5038]

The stringing of the SQL looks OK, but there's one possible problem when the form holds a tablename with an embedded "-" or space. To be 100% sure add a "[" and "]" surrounding the tablename like:

strSQL = "INSERT INTO [" & Me!MyShow & _
"] SELECT * FROM [InfoTable];"

Another problem might be the "missing space after the INTO, the concatenation will give e.g."
INSERT INTOtblX...

When running into trouble with this type of statements I normally place a break point and after the strSQL has been build I use the immediate window to print the contents. Next I copy/paste the text in a query to get the precise error message :-)

Nic;o)