VBA‎ > ‎

SQL insert command fails if your data has a dash ' in it

We had an Access database with the following VBA SQL insert command in it:
 
DoCmd.RunSQL "insert into scantemp (picture) values ('" & strFileJPG & "')"
 
This command would fail if the variable strFileJPG contained a dash ' in it.   For example, if strFileJPG was:
 
\\server\O'DELL\Document.jpg
this would error out.
 
The fix that worked was to use the chr(34) syntax instead:
 
Docmd.runSQL = "insert into scantemp (picture) values (" & Chr(34) & strFileJPG & Chr(34) & ")"

chr(34) is the ASCII code for the double quote character.
 
Comments