2

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] & "'"
Improve this question

1 Answer 1

Reset to default
6

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Hi and thanks for that but I get a data type mismatch when running the code, in the immediate window I can see that the contents of the fields are correct but then get that error (3464). Any idea what could be wrong?
Is the datatype of your RAC_DATA_ENTRY.RAC_CAP_VALS field numeric instead of text? If so, discard the single quotes before and after Me.[RAC_CAP_VALS].Value like this: " WHERE [RAC_CAP_VALS] = " & Me.[RAC_CAP_VALS].Value & ";"
Great, that worked, thanks - just got to iron out the myriad of other hiccups now!!
@RACdB if it works, mark the answer as CORRECT (click the checkmark).

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.