Solved

Access 2003 Pass Through Query using VBA

Posted on 2011-02-18
13
480 Views
Last Modified: 2012-05-11
My code is not deleting the record I need deleted.  Can any one tell me what is wrong?

Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfTemp As QueryDef

Set dbs = CurrentDb()

Set qdfPassThrough = dbs.CreateQueryDef("DeleteFormA")

qdfPassThrough.Connect = _
"ODBC;DSN=NewEis1;Description=NewEis1;APP=2007 Microsoft Office system;DATABASE=EIS;Trusted_Connection=Yes;TABLE=dbo.SCANFORMA"


qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"

qdfPassThrough.ReturnsRecords = No

dbs.QueryDefs.Delete "DeleteFormA"
dbs.Close
0
Comment
Question by:restockett
  • 6
  • 5
  • 2
13 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 34928556
Where are you executing the pass-through query?
OM Gang
0
 

Author Comment

by:restockett
ID: 34928643
it is an event procedure triggered by a command button onmy form.
0
 
LVL 28

Expert Comment

by:omgang
ID: 34928718
If you run a select query with the same criteria does it return the expected record?
OM Gang
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:restockett
ID: 34928742
It is a sql pass through query and yes it deletes the record.

DELETE SCANFORMA
WHERE SCANFORMA.IMAGE like '000000006467'
0
 
LVL 84
ID: 34928929
The syntax for most SQL databases is:

DELETE FROM YourTAble WHERE <Your Criteria>

Note there is no * in that syntax.
0
 

Author Comment

by:restockett
ID: 34929085
i took out the *

the code now says

qdfPassThrough.SQL = "DELETE FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"

but it still does not delete the record.

0
 
LVL 28

Expert Comment

by:omgang
ID: 34929132
OK, bear with me as I am still confused.  Is the code you posted originally the event procedure from the command button on the form?  If so, then which line of code is actually executing the pass-through query?
OM Gang
0
 

Author Comment

by:restockett
ID: 34929177
yes clicking the command button excecutes the code i sent earlier

This is the odbc connect string
qdfPassThrough.Connect = _
"ODBC;DSN=NewEis1;Description=NewEis1;APP=2007 Microsoft Office system;DATABASE=EIS;Trusted_Connection=Yes;TABLE=dbo.SCANFORMA"

and this (I thought) executed the code.
qdfPassThrough.SQL = "DELETE FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"


did i leave something out?
0
 
LVL 28

Expert Comment

by:omgang
ID: 34929252
Yes, you left something out.
This simply assigns a the string between the quotes as the SQL statement for the querydef object
qdfPassThrough.SQL = "DELETE FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"

Now the querydef object has both connection info and an SQL statement.  Next, we need to execute it

qdfPassThrough.Execute


Don't overlook LSM's comment about the correct syntax for the SQL statement.  The pass-through query will be executed on the server so needs to be valid for that environment.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 34929302
Dim dbs As Database
Dim qdfPassThrough As QueryDef
Dim qdfTemp As QueryDef

Set dbs = CurrentDb()

Set qdfPassThrough = dbs.CreateQueryDef("DeleteFormA")

qdfPassThrough.Connect = _
"ODBC;DSN=NewEis1;Description=NewEis1;APP=2007 Microsoft Office system;DATABASE=EIS;Trusted_Connection=Yes;TABLE=dbo.SCANFORMA"


qdfPassThrough.SQL = "DELETE * FROM SCANFORMA WHERE SCANFORMA.IMAGE = '000000006467'"

qdfPassThrough.ReturnsRecords = No

qdfPassThrough.Execute

dbs.QueryDefs.Delete "DeleteFormA"
dbs.Close
    'delete object variable
Set dbs = Nothing
0
 

Author Comment

by:restockett
ID: 34929323
thank you so much.  I awarded the points to you and lsm.  did you get them?
0
 
LVL 84
ID: 34930641
The question is not yet closed, so please try your closure attempt again.
0
 
LVL 28

Accepted Solution

by:
omgang earned 250 total points
ID: 34997474
restockett, as LSMConsulting indicated this question is NOT yet closed.  You need to accept a post, or posts, as answer(s).  Please close this question.
Thanks,
OM Gang
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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