1

I am trying to sort out some VBA in Access today with very limited knowledge. I have constructed the code below for a simple insert into a table within the same access database.

I am getting the error "Missing semicolon (;) at end of SQL statement."

However I have the semicolon in there. I can only assume I have made a rookie error with syntax somewhere but after spending far too long trying I am stumped, any pointers much appreciated!

Dim StrSQL As String
Dim InDate As Date
Dim VacNum As String
Dim Stage As String

InDate = Now()
VacNum = Me.Vacancy_No
Stage = Me.Current_Stage

StrSQL = "INSERT INTO Stage (Vacancy_ID,StageDate,Stage) VALUES ('" & VacNum & "','" & InDate & "','" & Stage & "' );"

DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL
DoCmd.SetWarnings True
jarlh
  • 42,561
  • 8
  • 45
  • 63
LCousins
  • 41
  • 1
  • 7

1 Answers1

1

Your syntax is at risk for SQL injection, and that's probably why you're getting this message.

The likely cause of this error is that one of your field contains a single quote, making the SQL malfunction.

The proper solution to this is to parameterize:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", "INSERT INTO Stage (Vacancy_ID,StageDate,Stage) VALUES (@Value1,@Value2,@Value3 );")
qdf.Parameters("@Value1") = VacNum 
qdf.Parameters("@Value2") = InDate 
qdf.Parameters("@Value3") = Stage 
qdf.Execute
Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Thankyou, that worked well, I will find some time later today to look into and understand how its working, i would always rather do things the proper way, now i know what to look up. Many thanks – LCousins Dec 05 '17 at 10:29
  • You can review [this answer](https://stackoverflow.com/a/1099570/7296893), it might provide you more insight. Also, one of the many sources on SQL injection might provide some insight. – Erik A Dec 05 '17 at 10:34