Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2492
  • Last Modified:

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

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.
0
StrayJay
Asked:
StrayJay
  • 5
  • 4
  • 3
  • +1
1 Solution
 
nexusnationCommented:
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
0
 
nexusnationCommented:
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
0
 
nexusnationCommented:
<< 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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
StrayJayAuthor 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...
0
 
nico5038Commented:
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)
0
 
StrayJayAuthor 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?
0
 
nico5038Commented:
Perhaps one of the fields from rst1 or on the form contains Null value(s).
You need to allow zero length fields explicitly in the table otherwise the query will fail. And also the required option needs to be set to "No" when an empty field is allowed.

Nic;o)
0
 
StrayJayAuthor 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! :-)
0
 
StrayJayAuthor 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.
0
 
nico5038Commented:
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)
0
 
flavoCommented:
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
0
 
StrayJayAuthor 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.
0
 
nico5038Commented:
Glad it's solved, Null values are sometimes nasty ones, had my share :-)

Success with the application !

Nic;o)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now