PASSTHRU DELETE QUERY - ACCESS

ilfocorp
ilfocorp used Ask the Experts™
on
Hello.  I have an Access 2010 ODBC passthru delete query that is working fine if I want to delete all the records.
DELETE FROM dbo.tblFW_T30_Base

However, I want to delete certain records based on selections in a form so I used the following;

DELETE FROM dbo.tblFW_T30_Base
WHERE (((WeekEndingDt)=[forms]![frmImpT30File]![cmbEOMDate]) AND ((Location)=[forms]![frmImpT30File]![cmbT30Loc]));

However I get the following error;
ODBC - call failed.
[Microsoft][SQL Server Native Client 10.0][SQL Server] incorrect sysntax near '!'.(#102)

Can someone help me resolve this problem.  Thank you.

ic
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
A Pass through query is sent to the backend server,so your SQL cannot have any vba expressions or  form references in it.  You must construct the statement fully  including all values , and then execute.

Jim

Author

Commented:
Thanks Jim.  What would an example of a fully constructed statement look like?

ic

Author

Commented:
I will research a fully constructed passthru statement in vba and see what I come up with.  Thanks again.

ic
Most Valuable Expert 2014
Commented:
a fully constructed statement
It must be T-SQL in syntax that SQL Server understands.

You must construct a string like

DELETE FROM dbo.tblFW_T30_Base
WHERE WeekEndingDt='28-Nov-2015' AND Location='The Dead Zone';

And change your passthroughs .SQL to the string, and then execute it.

It will be the same syntax as that used when running SQL Server Management Server
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Change the SQL tol, say:

DELETE FROM dbo.tblFW_T30_Base
WHERE WeekEndingDt = '{0}' AND Location = '{1}';

Then run code like this:
Dim qd As DAO.QueryDef
Dim SQL As String

Set qd = CurrentDb.QueryDefs("NameOfYourQuery")
SQL = "DELETE FROM dbo.tblFW_T30_Base WHERE WeekEndingDt = '{0}' AND Location = '{1}';"
SQL = Replace(SQL, "{0}", Format([forms]![frmImpT30File]![cmbEOMDate]), "yyyy\/mm\/dd"))
SQL = Replace(SQL, "{1}", [forms]![frmImpT30File]![cmbT30Loc])
qd.SQL = SQL
qd.Execute

Set qd = Nothing

Open in new window

If Location is numeric, remove to wrapping single quotes.

/gustav

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial