Using INSERT INTO with VALUES fails with Run-time error '3078'-CodePudding

I've been beating my head against the wall on this for two weeks! I've been to dozens of sites for help and they all give the same advice (none quite address my particular question, though) which doesn't help since the all (including official Microsoft) tell me to do what I'm doing.

Here's my query:

Dim strSQL As String
        
strSQL = """INSERT INTO tblVolunteers " & vbCrLf & _
            "VALUES (" & [txtTitle] & "," & [txtFirstName] & "," & [txtMiddle] & "," & [txtLastName] & "," & [txtEmail] & _
            "," & [txtPhone] & "," & [txtChurch] & "," & [txtGroup] & "," & [txtCouncil] & "," & [chkParCo] & "," & _
            [txtMailAdd] & ");"""
    
CurrentDb.Execute strSQL

And here's what Microsoft has to say:

Run-time error '3078'

The Microsoft Access database engine cannot find the input table or query ""INSERT INTO tblVolunteers VALUES (Mr.,John,L.,Smith,[email protected],800-555-1212,St. Smith's,Smith,1234,-1,10 Smith St. Smithville, TX 77777-3333);"". Make sure it exists and that its name is spelled correctly.

So why is it looking for a table or query when not only have I specified VALUES but it has correctly picked up all the values from the form?

CodePudding user response：

You could either use my function CSql and concatenate the values like this:

strSQL = "INSERT INTO tblVolunteers " & _
    "VALUES (" & CSql([txtTitle]) & "," & CSql([txtFirstName]) & "," & CSql([txtMiddle]) & "," & _ 
    CSql([txtLastName]) & "," & CSql([txtEmail]) & "," & CSql([txtPhone]) & "," & CSql([txtChurch] & "," & _ 
    CSql([txtGroup]) & "," & CSql([txtCouncil]) & "," & CSql([chkParCo]) & "," & CSql([txtMailAdd]) & ");"

or you could skip this mess and use DAO for much cleaner coding and easier debugging:

Dim Records As DAO.Recordset
Dim Sql     As String

Sql = "Select * From tblVolunteers"
Set Records = CurrentDb.OpenRecordset(Sql, dbOpenDynaset, dbAppendOnly)
Records.Append
    Records!Title.Value = Me!txtTitle.Value
    Records!FirstName.Value = Me!txtFirstName.Value
    Records!Middle.Value = Me!txtMiddle.Value
    Records!LastName.Value = Me!txtLastName.Value
    Records!Email.Value = Me!txtEmail.Value
    Records!Phone.Value = Me!txtPhone.Value
    Records!Church.Value = Me!txtChurch.Value
    Records!Group.Value = Me!txtGroup.Value
    Records!Council.Value = Me!txtCouncil.Value
    Records!ParCo.Value = Me!chkParCo.Value
    Records!MailAdd.Value = Me!txtMailAdd.Value
Records.Update
Records.Close

CodePudding user response：

Basically you need double quotes qaround the text, so for that you can use CHR(34)

strSQL = "INSERT INTO tblVolunteers " & vbCrLf & _
        "VALUES (" & CHR(34) & [txtTitle] & CHR(34) & "," & CHR(34) &  [txtFirstName] & CHR(34) &  "," & CHR(34) &  [txtMiddle] & CHR(34) &  "," & CHR(34) &  [txtLastName] &  CHR(34) & "," & CHR(34) &  [txtEmail] & CHR(34) &  _
        "," & CHR(34) &  [txtPhone] & CHR(34) &  "," & CHR(34) &  [txtChurch] & CHR(34) &  "," & CHR(34) &  [txtGroup] & CHR(34) &  "," & CHR(34) &  [txtCouncil] & CHR(34) &  "," & CHR(34) &  [chkParCo] & CHR(34) &  "," & CHR(34) &  _
        [txtMailAdd] & CHR(34) &  ");"