VBA Syntax to execute a pass-through?

[Robert]

What is the syntax to use in a VBA script to execute a pass-through? Thanx

[Rick Brandt]

"Robert" <ro***********@boeing.com> wrote in message
news:I4********@news.boeing.com...

What is the syntax to use in a VBA script to execute a pass-through? Thanx

By "execute" do you mean that the pass-through contains an "action" statement
that will insert, update, or delete records? Or do you want to use a PT that
returns records?

Is this a saved PT query visible in the db window or do you want to construct it
in the vba code?

If it was a saved PT query that ran an "action" statement then you would execute
it the same as an Access action query.

CurrentDB.Execute "NameOfPassThrough", dbFailOnError
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Robert]

Thanx, in fact the pass thru does a 'delete'. I replaced an argument in a
'DoCmd.openQuery' with a PT and when I ran the VBA returned a 'database in
use' message. The existing line of code is

DoCmd.OpenQuery "active_database_delete_action_budget_PT", acNormal, acEdit

the pass thru looks like

DELETE
FROM Active_Database
WHERE ((Active_Database.[Plan Code]='SSTEST') AND ((Active_Database.[User
Code])='BSS') AND ((Active_Database.Budget)='G4028') AND
((Active_Database.[SOW #])=' '));

Is this correct syntax?


"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:2r*************@uni-berlin.de...

"Robert" <ro***********@boeing.com> wrote in message
news:I4********@news.boeing.com...

What is the syntax to use in a VBA script to execute a pass-through?

Thanx
By "execute" do you mean that the pass-through contains an "action" statement that will insert, update, or delete records? Or do you want to use a PT that returns records?

Is this a saved PT query visible in the db window or do you want to construct it in the vba code?

If it was a saved PT query that ran an "action" statement then you would execute it the same as an Access action query.

CurrentDB.Execute "NameOfPassThrough", dbFailOnError
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

"Robert" <ro***********@boeing.com> wrote in message
news:I4********@news.boeing.com...

Thanx, in fact the pass thru does a 'delete'. I replaced an argument in a 'DoCmd.openQuery' with a PT and when I ran the VBA returned a 'database in use' message. The existing line of code is

DoCmd.OpenQuery "active_database_delete_action_budget_PT", acNormal, acEdit
the pass thru looks like

DELETE
FROM Active_Database
WHERE ((Active_Database.[Plan Code]='SSTEST') AND ((Active_Database.[User
Code])='BSS') AND ((Active_Database.Budget)='G4028') AND
((Active_Database.[SOW #])=' '));

Is this correct syntax?

Well, that depends totally on the database your talking to (which you
didn't mention).

By definition a Pass-Through query has to be written in the SQL syntax of
the server you are sending the SQL to. If you are using SQL Server, the
syntax will not be the same as in Access. The use of parenthesis and
brackets in your statement makes it look like SQL that was lifted directly
from an Access query and this will most likely not be correct. However
your ODBC driver should tell you about any syntax errors that are
encountered.

What happens when you try to execute the query?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Robert]

The query comes from an Access application. The Access query takes a long
time to run due to the chat that occurs between Access and SQL Server. I
converted the query to a PT inorder to make the delete run on the SQL Server
directly. When the PT Query is run from Access the response time is
significantly faster. However, when I run VB application code which calls
the PT query I get a popup,

"Another Analyst is updating DB now. Try again in 15 seconds"

Of course that is a user defined error Message. However, what I am not
certain of is this 'on error' condition due to syntax when I try to execute
the PT. The Access query works fine in the VB code. The PT query works
fine if I run it outside of VB.
"Rick Brandt" <ri*********@hotmail.com> wrote in message
news:2r*************@uni-berlin.de...

"Robert" <ro***********@boeing.com> wrote in message
news:I4********@news.boeing.com...

Thanx, in fact the pass thru does a 'delete'. I replaced an argument in

a

'DoCmd.openQuery' with a PT and when I ran the VBA returned a 'database

in

use' message. The existing line of code is

DoCmd.OpenQuery "active_database_delete_action_budget_PT", acNormal,

acEdit

the pass thru looks like

DELETE
FROM Active_Database
WHERE ((Active_Database.[Plan Code]='SSTEST') AND ((Active_Database.[User Code])='BSS') AND ((Active_Database.Budget)='G4028') AND
((Active_Database.[SOW #])=' '));

Is this correct syntax?

Well, that depends totally on the database your talking to (which you
didn't mention).

By definition a Pass-Through query has to be written in the SQL syntax of
the server you are sending the SQL to. If you are using SQL Server, the
syntax will not be the same as in Access. The use of parenthesis and
brackets in your statement makes it look like SQL that was lifted directly
from an Access query and this will most likely not be correct. However
your ODBC driver should tell you about any syntax errors that are
encountered.

What happens when you try to execute the query?
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com