Run-time error 3134, Syntax error in INSERT INTO statement

[bchase]

I am using Access 2003 and am receiving the above error for the following code:

Public Sub AddFilenamesToTable(folderspec)

Dim fs, fsFolder, fsFile, fsListOfFiles
Dim strFilename As String, strSQL As String

Set fs = CreateObject("Scripting.FileSystemObject")
Set fsFolder = fs.GetFolder(folderspec)
Set fsListOfFiles = fsFolder.files

For Each fsFile In fsListOfFiles
strFilename = fsFile.Name
If DCount("[EmployeeID]", "[tblUserDatabase]", "[strPictureFilename] =
'" & strFilename & "'") = 0 Then
strSQL = "INSERT INTO tblUserDatabase ( strPictureFilename, strConv,
strClean-Delete, strTess, strStatus, strComments )VALUES ('" & strFilename &
"', #" & fsFile.DateLastModified & "# ) ;"
CurrentDb.Execute strSQL
End If
Next

End Sub




Debug highlights CurrentDb.Execute strSQL

Any suggestions or help would be greatly appreciated!

 

[Scott Shearer]

Your problem is that you need to specify only the columns that into which you
want to insert in the first part of your SQL statement. In addition, the
order that you reference the columns in the first part of the statement needs
to be the same order as in the second part. You reference 6 columns in the
first part of your statement and only provide values for 2.

 

[Klatuu]

Scott is correct, but the error you are getting is a syntax error, so it
hasn't even gotten to where it would error out because the SQL statement is
improperly formed.

strSQL = "INSERT INTO tblUserDatabase ( strPictureFilename, strConv,
strClean-Delete, strTess, strStatus, strComments )VALUES ('" & strFilename &
"', #" & fsFile.DateLastModified & "# ) ;"

There needs to be a space between the ) and the word VALUES

Also, you are using a reserved word as a name. strConv is a VBA function.
strClean-Delete may also be a problem because of the -
It may see it as an operator.

If you are going to use badly formed names, at least enclose them in
brackets to reduce (but not necessarily eliminate) the possiblity of
confusing Access and either throwing errors or getting bad results.