Solved

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

Posted on 2004-04-07
13
2,460 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.
0
Comment
Question by:StrayJay
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 12

Expert Comment

by:nexusnation
ID: 10773988
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 10774000
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
 
LVL 12

Expert Comment

by:nexusnation
ID: 10774039
<< 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
 

Author Comment

by:StrayJay
ID: 10774113
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10774289
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
 

Author Comment

by:StrayJay
ID: 10774496
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 10774762
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
 

Author Comment

by:StrayJay
ID: 10774893
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
 

Author Comment

by:StrayJay
ID: 10774918
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10775097
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
 
LVL 34

Expert Comment

by:flavo
ID: 10784063
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
 

Author Comment

by:StrayJay
ID: 10788924
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10789701
Glad it's solved, Null values are sometimes nasty ones, had my share :-)

Success with the application !

Nic;o)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now