By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

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

P: 1
'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_Click

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 Sub

MsgBox Err.Description
Resume Exit_Command52_Click

End Sub
Jan 19 '10 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 266
There appears to be a "." dot in your code that shouldn't be there i believe, and there needs to be a space.

Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO" & Me!MyShow & _
  2. "SELECT *. FROM [InfoTable];"
Should be:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO" & Me!MyShow & _
  2. " SELECT * FROM [InfoTable];"
Maybe that is it.
Jan 19 '10 #2

Expert 2.5K+
P: 3,532
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.
Jan 19 '10 #3

Expert 2.5K+
P: 3,072
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:
Expand|Select|Wrap|Line Numbers
  1. strSQL = "INSERT INTO [" & Me!MyShow & _
  2. "] SELECT * FROM [InfoTable];"
Another problem might be the "missing space after the INTO, the concatenation will give e.g."

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 :-)

Jan 19 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.