Solved

Access 2003 Pass Through Query using VBA

Posted on 2011-02-18
13
468 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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 …

920 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now