We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

CurrentDb.Execute "INSERT INTO tbl(f1, f2) VALUES(v1, v2)" does not work NOR cause an error

StrayJay
StrayJay asked
on
Medium Priority
2,569 Views
Last Modified: 2010-05-18
Hi everybody!

I'm writing a VBA app for Access and I'm running into a problem that I don't understand, and I'm not making progress.  Time to ask you for help...

My app contains the following two lines:

        l_sQuery = "INSERT INTO tblOrdered(f1, f2, f3) VALUES('" & _
                                              Rst1("Productname") & "','" & _
                                              Nz(Me.Form.[Customer]) & "','" & _
                                              Nz(Me.Form.[Address]) & ");""
        Debug.Print l_sQuery
        CurrentDb.Execute l_sQuery

When I execute this code, the Debug.Print shows that l_sQuery contains exactly what I'd expect. Even the 'CurrentDb.Execute l_sQuery'-statement is executed without causing an error.  But when I check tblOrdered afterward, no new records appear to have been inserted in that table!  What am I doing wrong?  I've used code like this many times before but 'never ran into this problem before...

Second question: the fieldnames are actually more descriptive; I abbreviated them to keep this example simple.  The person who designed the table named one column '#Hours', meaning: the number of hours that it took to assemble the product.  The CurrentDB.Execute statement reports an error when I use fieldnames containing a '#' sign.  How can I circumvent this?  Is there an escape character that I should use?  I cannot change the definition of the table, as it may require also changing a bunch of reports.

I'll give 200 point for the answer to the first question, and 50 to the second...

Thanks in advance!

StrrayJay.
Comment
Watch Question

Dim l_sQuery As String
l_sQuery = "INSERT INTO tblOrdered(f1, f2, f3) VALUES ('" & Rst1("Productname") & "','" & Nz(Me.Form.[Customer],"") & "','" & Nz(Me.Form.[Address],"") & "');"
Debug.Print l_sQuery
CurrentDb.Execute l_sQuery

You missed a '

Post what it prints if it still doesn't work.

Andrew
Yeah, right here:

Nz(Me.Form.[Address]) & ");""

You need:

Nz(Me.Form.[Address]) & "');""

And by the way, what is the reason for:

Nz(Me.Form.[Address])

It should be:

Nz(Me.Form.[Address],"")

Andrew
<< Even the 'CurrentDb.Execute l_sQuery'-statement is executed without causing an error. >>

Well, you think it is, but it isn't. Sometimes it doesn't alert you for errors.

Your other problem (long-term) will be if you have any ' in the Customer name, product name, or address. Then it won't insert (and may not even generate an error).

To prevent that, you can do:

_sQuery = "INSERT INTO tblOrdered(f1, f2, f3) VALUES (" & Chr(34) & Rst1("Productname") & Chr(34) & "," & Chr(34) & Nz(Me.Form.[Customer],"") & Chr(34) & "," Chr(34) & Nz(Me.Form.[Address],"") & Chr(34) & ");"

Chr(34) produces a " (double-quote), which will then allow ' (single quotes). You can further use the Replace function to remove any " that may, for some reason, end up in any of those three fields.

Andrew

Author

Commented:
Hi Andrew!

My apologies, but: when I formulated my questions, I copied the code and removed some extraeneous stuff.  Accidentally, I also removed that apostrophe but in the original code it's there.  In fact, I'm convinced the SQL statement as it's executed IS correct.  It's just that the data aren't stored in the table.

<< Even the 'CurrentDb.Execute l_sQuery'-statement is executed without causing an error. >>
>> Well, you think it is, but it isn't. Sometimes it doesn't alert you for errors.

Apparently. :-)  What are some of the reasons for this to happen?  Just to get an idea; it may help me solve my problem...

Commented:
Use [#hours] to prevent the error.

For trouble shooting I would copy/paste the build SQL string in a new query in the query editor and execute it from there to see the error message and/or syntax error.

But first I would try:
       l_sQuery = "INSERT INTO tblOrdered(f1, f2, f3) VALUES(" & chr(34) & _
                                              Rst1("Productname") & chr(34) & "," & chr(34) & _
                                              Nz(Me.Form.[Customer]) & chr(34) & "," & chr(34) & _
                                              Nz(Me.Form.[Address]) & chr(34) & ");"

Nic;o)

Author

Commented:
Bedankt, Nico!

I should have known that [#hours]; it's just that I've been working on the other problem for so long now, my mind's going numb. :-)  Still, 50pts for you.  (that's points, not pesetas...)

An earlier remark by Andrew made me look into Execute a bit more.  Apparently, it is possible to display the number of records affected by the latest Execute statement.  When I tried this, the result is indeed zero.  Unfortunately, I cannot find the reasons why executing a valid SQL statement can result in zero affected rows.  Access does not object to me entering the same record manually (by oprening the table and entering the data in the proper fields).

Ideas, anyone?
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Nico!

Nope, that can't be it, either.  I just tried this:

  l_sQuery = "INSERT INTO Staff(Name, Title, Remark) VALUES('My Name','Title','TEST');"
  Debug.Print l_sQuery
  CurrentDb.Execute l_sQuery, dbFailOnError
  Debug.Print CurrentDb.RecordsAffected

These are the only fields defined in the table, and as you can see, none of them are empty.  Still, it resulted in this output:

  INSERT INTO Staff(Name, Title, Remark) VALUES('My Name',Title','TEST');
   0

And it caused no error... I'm really hating working with Access right now! :-)

Author

Commented:
That is, of course:

  INSERT INTO Staff(Name, Title, Remark) VALUES('My Name','Title','TEST');
   0

I made a typo deleting my name and title, and once again deleted the apostrophe.

Commented:
Try to add a space aftre the tablename and values clause like:
INSERT INTO Staff (Name, Title, Remark) VALUES ('My Name','Title','TEST');

Nic;o)

Commented:
Try adding [ ] around your field names, they look a little suspect

INSERT INTO Staff ([Name], [Title], [Remark]) VALUES ('My Name','Title','TEST');

Good Luck!

Dave

Author

Commented:
Hi All!

Unlike what I wrote in my "04/07/2004 06:59AM PDT"-comment, the problem (in my original code) appeared to be an NULL field after all.
The code that I quoted in my comment did return zero as the number of affected rows, but a record was added to the table all the same.  So no error occurred in adding the record, just the return value is inaccurate.  I'm not sure why, but that's not that big a deal right now (though I'd like to know!).

So, all points go to nico5038; fifty for his "04/07/2004 06:02AM PDT"-comment, and 200 for the one posted 44 minutes later.

Many thanks to the other posters for trying to help!

StrayJay.

Commented:
Glad it's solved, Null values are sometimes nasty ones, had my share :-)

Success with the application !

Nic;o)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.