CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
RACdB
RACdB

Reputation: 23

SQL statement in VBA

I am trying to run the following SQL statement in ACCESS 2013 VBA but am getting errors due to wrong formatting (in this case I get "Semicolon (;) missing from end of statement"). Could anybody tell me what I am doing wrong in the code below please? 

Dim dbs As dao.Database

Set dbs = CurrentDb()

dbs.Execute "INSERT INTO TEMP2 ([Study_Date], [Created_By], [Part_Number], 
[Upper_Tolerance], [Lower_Tolerance], [ID21_Number]) VALUES ([Study_Date], 
[Created_By], [Part_Number], [Upper_Tolerance], [Lower_Tolerance], [ID21_Number]) 
FROM RAC_DATA_ENTRY 
WHERE [RAC_CAP_VALS] = '" & Me.[RAC_CAP_VALS] & "'"

Upvotes: 2

Views: 176

Answers (1)

HansUp
HansUp

Reputation: 97131

Don't use VALUES when you're pulling data from one table to INSERT into another. Use SELECT instead.

This example uses just two of your fields. Add in the others you need.

Dim strInsert As String
strInsert = "INSERT INTO TEMP2 ([Study_Date], [Created_By])" & _
    " SELECT [Study_Date], [Created_By] FROM RAC_DATA_ENTRY" & _
    " WHERE [RAC_CAP_VALS] = '" & Me.[RAC_CAP_VALS].Value & "';"
Debug.Print strInsert '<- view this in Immediate window; Ctrl+g will take you there
dbs.Execute strInsert, dbFailOnError

Notes:

  1. A semicolon at the end of the statement is optional. Access will consider the statement valid with or without it.
  2. Value is not actually required following Me.[RAC_CAP_VALS], since it's the default property. I prefer to make it explicit.
  3. dbFailOnError gives you better information about failed inserts. Without it, a problem such as a primary key violation would fail silently.
  4. Debug.Print strInsert allows you to inspect the statement you built and are asking the db engine to execute. If there is a problem, you can copy the statement text from the Immediate window and paste it into SQL View of a new Access query for testing.

Upvotes: 6

Related Questions