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

Question

I've been to dozens of sites. None address my particular question. All (including official Microsoft) tell me to do what I'm doing.

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

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,jlsmith@email.com,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.

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

There are multiple problems here. The whole statement must not be included in double quotes, but the individual text arguments should be included in single quotes. And are you sure that a table named "tblVolunteers" exists? — PMF, Oct 24 '21 at 15:16
Single quotes within the statement always results in a syntax error. — Michael E. Ehinger, Oct 24 '21 at 18:02
Single quotes within the statement always yield a syntax error (my head was broken like an Easter egg before I figured out what it was, MSoft being unwilling to flag the actual error). MSoft actually recommends the the triple-double quote that I used. I can't say why the single quote didn't work but please note that the error message shows exactly what I should get. Only Access wants for some reason to find a table or query instead of using the specified VALUEs. (Note that this query does work, except . . . !) — Michael E. Ehinger, Oct 24 '21 at 18:07
Single quotes work when used correctly. If you really must concatenate variables with SQL, use Gustav's [`CSql()` function](https://stackoverflow.com/a/36494189/3820271). It handles all sorts of variables and prevents SQL injection. — Andre, Oct 24 '21 at 18:45
In your case, simply wrapping text with single quotes doesn't work because of `St. Smith's`. — Andre, Oct 24 '21 at 18:46
Please be aware of [SQL injection](https://bobby-tables.com/msaccess) — Hans Keﬆing, Feb 17 '22 at 09:29

Gustav · Answer 1 · 2021-10-25T14:15:25.237

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.AddNew
    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

Well, it's the first time Access flagged the "bad" code instead of the whole thing: Records.Append gets append highlighted in blue with the message "Compile error: Method or data member not found." — Michael E. Ehinger, Oct 25 '21 at 11:59

nbk · Answer 2 · 2021-10-24T16:19:35.677

0

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) &  ");"

edited Oct 24 '21 at 16:19

answered Oct 24 '21 at 15:51

nbk

45,398
8
30
47

Basically you need double quotes qaround the text, so for that you can use CHR(34) – Michael E. Ehinger Oct 24 '21 at 18:00
That didn't work but I wonder why you thought it would? Again, the triple-double quotes I used are, according to MSoft, preferred and single-quotes within the string always yielded a syntax error. And, if you look at the error message, the string sent to SQL is exactly right. – Michael E. Ehinger Oct 24 '21 at 18:09
i have performed such queries `INSERT INTO tblVolunteers VALUES ("Mr.","John","L.","Smith","jlsmith@email.com","800-555-1212","St. Smith's","Smith","1234","-1","10 Smith St. Smithville","TX 77777-3333")` so it should work try it in access – nbk Oct 24 '21 at 18:27
besides the triple quoes are before the database fileds and not around the hole stirng as you postted, so you had it wrong. is stuill wonder why it didn't work with the double quotes in access it shows no errors so it should do in vba – nbk Oct 24 '21 at 18:31
There are no quotes around the values because they are not literal text but the contents of textboxes and combo boxes on the form: strSQL = """INSERT INTO tblVolunteers " & vbCrLf & _ "VALUES (" & [txtTitle] & "," & [txtFirstName] & "," & [txtMiddle] & "," & [txtLastName] & "," & [txtEmail] & _ "," & [txtPhone] & "," & [txtChurch] & "," & [txtGroup] & "," & [txtCouncil] & "," & [chkParCo] & "," & _ [txtMailAdd] & ");""" That's the way MSoft shows it and I don't see how it could work otherwise. – Michael E. Ehinger Oct 24 '21 at 22:52
But I tried it anyway and got: Run-time error '2465' Microsoft Access can'tfine the field '|1' referred to in your expression. I thought that might refer to the checkbox (which has a value of -1) so I deleted the CHR(34) & from in front of it but still got exactly the same error. – Michael E. Ehinger Oct 24 '21 at 23:00
have you run your query in access, so you can chekc which format access wanst als text value have to be in quotes. – nbk Oct 24 '21 at 23:03
It really looks like it wants to work (forgive my earlier scepticism) but every attempt yields new and different errors. Thanks. – Michael E. Ehinger Oct 25 '21 at 12:03

score 0 · Answer 3 · answered Oct 27 '21 at 02:33

0

use Access Query Design View..... start with just a single field, and then build field by field...

you can toggle it to SQl View to see the syntax

answered Oct 27 '21 at 02:33

Cahaba Data

624
1
4
4

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

3 Answers3