Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access 2003 Pass Through Query using VBA

Posted on 2011-02-18
13
Medium Priority
?
510 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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 85
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 85
ID: 34930641
The question is not yet closed, so please try your closure attempt again.
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

926 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