Clever Geek Handbook

Simple SQL query in Access fails with semicolon

So, I am learning Access 2007, Visual Basic and SQL at the same time. Not ideal.

I have this code attached to a button in the wizard-generated standard interface. I am trying to copy a line from tblA to tblB. Every time the code is executed, I get the message "Run-time error 3137" Missing semicolon (;) at end of SQL statement ".

My guess is that it expects the SQL statement to complete earlier, before WHERE? But without WHERE, how can I add append to a specific row id?

Private Sub buttonAdd_Click()
Dim strSQL As String
strSQL = "INSERT INTO [tblB]" & _
    "VALUES (ID, [Name], [Some value], [Some other value])" & _
    "SELECT * FROM tblA" & _
    "WHERE ID = '" & Me.ID & "' " & _
    ";"

DoCmd.RunSQL strSQL
End Sub
+2


source to share


3 answers


The syntax is incorrect, you need to remove the "VALUES" keyword.
ID, [Name], [Some value] and [Some other value] are assumed to be tblB column names (some hesitation on my part with the last two names having "value").
The VALUES () SQL syntax is used to provide instantaneous values, but since you are getting values ​​from tblA, the query should look like this:

strSQL = "INSERT INTO [tblB] " & _
    "(ID, [Name], [Some value], [Some other value]) " & _
    "SELECT * FROM tblA " & _
    "WHERE ID = '" & Me.ID & "' " & _
    ";"


Edit . I also added spaces between the tokens. Nice catch Nick D, thanks for noticing this!

+3


source


You put field names in values ​​clause instead of table name, and between tbla and where there is a space. Both the value clause followed by the selection and the missing space can trigger the error message themselves.

strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
   "SELECT * FROM tblA " & _
   "WHERE ID = '" & Me.ID & "'"

The semicolon at the end is not required at this time. This is only needed to separate requests if you are running multiple requests at once.



Also, if the ID field is numeric, you shouldn't have apostrophes around the value:

strSQL = "INSERT INTO [tblB] (ID, [Name], [Some value], [Some other value])" & _
   "SELECT * FROM tblA " & _
   "WHERE ID = " & Me.ID
+2


source


mjv is correct. You must remove the keyword VALUES

and also include spaces between keywords, for example:

"SELECT * FROM tblA" & _
"WHERE ID = '" & Me.ID & "' " & _

the above will be linked as "...FROM tblAWHERE ID..."

0


source






More articles:


All Articles