• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 512
  • 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
 
omgangCommented:
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
 
omgangCommented:
If you run a select query with the same criteria does it return the expected record?
OM Gang
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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
 
omgangCommented:
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

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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