PASSTHRU DELETE QUERY - ACCESS

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
ilfocorpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ilfocorpAuthor Commented:
Thanks Jim.  What would an example of a fully constructed statement look like?

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

ic
Nick67Commented:
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
Gustav BrockCIOCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.