CurrentDb.Execute "INSERT INTO tbl(f1, f2) VALUES(v1, v2)" does not work NOR cause an error
Posted on 2004-04-07
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]) & ");""
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!