Divide and Conquer: Debugging SQL statements created in VBA code
How do you debug the appropriate use of ' (single quote) or & (and) in the following SQL strings being built in VBA code?
A simple example from a newsgroup posting is as follows.
SQL = "INSERT INTO TABLENAME VALUES('" & Forms!Test!name & "', '" & var "')"
I debug these kinds of problems by throwing a ' (single quote) in there and seeing if I still get an error. Then I move the ' over to the right until I get the error message.
But what about multi line SQL code such as the following.
' Append records to the ServiceRecordsTech table
strSql = "INSERT INTO ServiceRecordTechs ( srtServiceTechID, srtServiceID, srtHours ) " & _
" IN '" & strDatabasePathandName & "' " & _
"SELECT ServiceTech.stID, ServiceRecords.srID, ServiceRecords.srLabourHours " & _
"FROM ServiceTech " & _
"INNER JOIN ServiceRecords ON ServiceTech.stServiceTech = ServiceRecords.srServicePerson " & _
"IN '" & strDatabasePathandName & "';"
First I always have the Edit toolbar displayed in the VBA IDE. (I don't recall if this is a standard toolbar so to add the Edit Toolbar right click anywhere on the ToolBar and select the Edit Toolbar. )
Then I use the Comment Block/Uncomment Block buttons on the last few lines of code. I also have to remove the "& _".
strSql = "INSERT INTO ServiceRecordTechs ( srtServiceTechID, srtServiceID, srtHours ) " & _
" IN '" & strDatabasePathandName & "' " & _
"SELECT ServiceTech.stID, ServiceRecords.srID, ServiceRecords.srLabourHours "
' "FROM ServiceTech " & _
' "INNER JOIN ServiceRecords ON ServiceTech.stServiceTech = ServiceRecords.srServicePerson " & _
' "IN '" & strDatabasePathandName & "';"
CurrentDb.Execute strSql, dbFailOnError
If I don't get an error message then I uncomment another line or two and add in the missing "& _" and keep on going.