docmd.runsql (Access XP)

Topic

New Reply

I have written a window in Access that links through ODBC to a SQL database that needs to have the ability to either save or cancel. The form has been designed with this in mind and all the controls do not have a source so that when you hit save there is a docmd.runsql that inserts or edits the entries into the appropriate table. If you click on Close without Save obviously it just closes the window and ignores the changes.

The script works when the output script is run in Query Analyser but when run in Access VB it comes up with the following message.

“Syntax error (missing operator) in query expression “Description of Stuff” from updates’.

The VB Script is:

strSQL = “update updates ” & _
” set updatedate = ” & txtUpdateDate & _
“, trfrom = ‘” & TRFrom & _
“‘, trto = ‘” & TRTo & _
“‘, trreason = ‘” & TRReason & _
“‘, bmapproval = ” & chkBMApproval & _
“, bmapprovaldate = ‘” & BMApprovalDate & _
“‘, acapproval = ” & chkAccountsApproval & _
“, acapprovaldate = ‘” & ACApprovalDate & _
“‘, updatedescription = ‘” & txtDescription & _
“‘ from updates where updateid = ” & txtUpdatesID
End If
DoCmd.RunSQL strSQL

The debug.print sql is

update updates set updatedate = 16/09/2003,
trfrom = ‘Someone’,
trto = ‘Somewhere’,
trreason = ‘Because’,
bmapproval = -1, bmapprovaldate = ’01/09/2003′,
acapproval = -1, acapprovaldate = ’01/09/2003′,
updatedescription = ‘Description of stuff’
from updates where updateid = 10046

which works fine in query analyser.

Can anyone tell me what I’m doing wrong because it’s now beginning to really annoy me!!!

Many thanks.

Reply | Quote

Replies

DoCmd.RunSQL only handles Access/Jet SQL. It does not recognize the wildcards and date delimiters that T-SQL (SQL Server) use. That’s the reason it works in Query Analyzer but you get a syntax error in Access.

Reply | Quote

DoCmd.RunSQL only handles Access/Jet SQL. It does not recognize the wildcards and date delimiters that T-SQL (SQL Server) use. That’s the reason it works in Query Analyzer but you get a syntax error in Access.

Reply | Quote

You have enclosed the last two of the date values in single quotation marks, but you haven’t done so with the first one.
Is this the mistake?
In Access (Jet SQL) date values have to be enclosed in number signs. But I think the single quotes are OK for T-SQL.
If DoCmd.RunSQL only accepts Jet-SQL-statements, then you have to send your T-SQL-statement to your SQL Server
in another way. Try using the ADO-command-object.

Reply | Quote

You have enclosed the last two of the date values in single quotation marks, but you haven’t done so with the first one.
Is this the mistake?
In Access (Jet SQL) date values have to be enclosed in number signs. But I think the single quotes are OK for T-SQL.
If DoCmd.RunSQL only accepts Jet-SQL-statements, then you have to send your T-SQL-statement to your SQL Server
in another way. Try using the ADO-command-object.

Reply | Quote

What you are doing is creating what is referred to as an “unbound form” in Access circles. However there are a number of traps in doing that sort of thing. For one thing, any sql string done in code must use dates formatted according to “USA standard” mm/dd/yyyy – a real pain in the neck if you are working in another part of the world as you apparently are. In addition, you probably should put a semi-colon ( at the end of the SQL String. Finally, the way we typically do this is to build a query in the Query tool, get it to the point where it works, and then take the SQL String and deconstruct it into code as you have done. Using the SQL Server Query Analyzer can lead you down a twisty path that dead-ends, as others have noted.

Another alternative we use more often, is to use either DAO – works best on ODBC linked tables – or ADO which uses a native mode connection to SQL, and simply do Updates or Inserts in code without using any SQL commands. Finally, it is possible to create bound forms where you have a choice of updating or not updating when the form is closed or you move to a different record – it involves using the Dirty property of the form and various form events.

Reply | Quote

Thanks for all the help. I tried what everyone was suggesting and eventually I copied the output code to the query tool in Access and found that instead of having :

From updates where updateid = 12345

as the last part of the code as you would in T-SQL you ignore that and just put:

where updates.updateid = 12345

Once again thanks for the help

Reply | Quote

Thanks for all the help. I tried what everyone was suggesting and eventually I copied the output code to the query tool in Access and found that instead of having :

From updates where updateid = 12345

as the last part of the code as you would in T-SQL you ignore that and just put:

where updates.updateid = 12345

Once again thanks for the help

Reply | Quote

What you are doing is creating what is referred to as an “unbound form” in Access circles. However there are a number of traps in doing that sort of thing. For one thing, any sql string done in code must use dates formatted according to “USA standard” mm/dd/yyyy – a real pain in the neck if you are working in another part of the world as you apparently are. In addition, you probably should put a semi-colon ( at the end of the SQL String. Finally, the way we typically do this is to build a query in the Query tool, get it to the point where it works, and then take the SQL String and deconstruct it into code as you have done. Using the SQL Server Query Analyzer can lead you down a twisty path that dead-ends, as others have noted.

Another alternative we use more often, is to use either DAO – works best on ODBC linked tables – or ADO which uses a native mode connection to SQL, and simply do Updates or Inserts in code without using any SQL commands. Finally, it is possible to create bound forms where you have a choice of updating or not updating when the form is closed or you move to a different record – it involves using the Dirty property of the form and various form events.

Reply | Quote