• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 513
  • Last Modified:

Access 2003 Pass Through Query using VBA

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
restockett
Asked:
restockett
  • 6
  • 5
  • 2
1 Solution
 
omgangIT ManagerCommented:
Where are you executing the pass-through query?
OM Gang
0
 
restockettAuthor Commented:
it is an event procedure triggered by a command button onmy form.
0
 
omgangIT ManagerCommented:
If you run a select query with the same criteria does it return the expected record?
OM Gang
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
restockettAuthor Commented:
It is a sql pass through query and yes it deletes the record.

DELETE SCANFORMA
WHERE SCANFORMA.IMAGE like '000000006467'
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The syntax for most SQL databases is:

DELETE FROM YourTAble WHERE <Your Criteria>

Note there is no * in that syntax.
0
 
restockettAuthor Commented:
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
 
omgangIT ManagerCommented:
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
 
restockettAuthor Commented:
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
 
omgangIT ManagerCommented:
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
 
omgangIT ManagerCommented:
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
 
restockettAuthor Commented:
thank you so much.  I awarded the points to you and lsm.  did you get them?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The question is not yet closed, so please try your closure attempt again.
0
 
omgangIT ManagerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 6
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now