Proper way to run an Action Pass Through Query from Access 2000

[ILCSP]

Hello, I'm fairly new to the concept of running action pass through
queries (insert, update, etc.) from Access 2000. I have a SQL Server
2000 database and I'm using a Access 2K database as my front end.

I'm using a blank pass through query which gets the Transact-SQL part
inserted from a button in my form. After inserting the Transact-SQL
code into the pass through query, I 'open the recordset' to make the
query run.

However, everytime this is successful I get a 3325 runtime error:
"Pass-through query with ReturnsRecords property set to True did not
return any records". I've tried to set the Return Records query
property to 'No' but it does not insert any records if I do. I have to
catch that 3325 error in the error handler, but I don't want that error
to show up at all.

This also happens with my Update Pass Through Queries. What I want is
to just insert the SQL code in the pass through query, run it so it
inserts or updates and that's it. I don't want to return a recordset,
or I don't want to return errors. I know I could do this with a store
procedure, but I'm hoping this could work too.

This is what I do from my button:
---------------------------------------------------
On Error GoTo ExitHere

' Using a Pass Through query to insert the new records
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef

Dim rs As DAO.Recordset

' set the variable values
Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qryInsertRecords")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNumber, Finance.FirstName,
Finance.LastName, Finance.Amount, Finance.AddedBy FROM Finance"

' opening the new recordset
Set rs = loQdf.OpenRecordset()

'clear variables
Set loQdf = Nothing
Set loDB = Nothing

' exiting form
ExitHere:
Exit Sub

'error handling
HandleErr:
If Err.Number = 3325 Then GoTo ExitHere ' help! I don't want this
error to show!
MsgBox Err.Number & ": " & Err.Description, , "Insert Record"
Resume ExitHere
Resume
-------------------------------------------------------

Any help would be apprecitated.

Thanks!

[Rick Brandt]

IL***@NETZERO.NET wrote:

Hello, I'm fairly new to the concept of running action pass through
queries (insert, update, etc.) from Access 2000. I have a SQL Server
2000 database and I'm using a Access 2K database as my front end.

I'm using a blank pass through query which gets the Transact-SQL part
inserted from a button in my form. After inserting the Transact-SQL
code into the pass through query, I 'open the recordset' to make the
query run.

However, everytime this is successful I get a 3325 runtime error:
"Pass-through query with ReturnsRecords property set to True did not
return any records". I've tried to set the Return Records query
property to 'No' but it does not insert any records if I do. I have
to catch that 3325 error in the error handler, but I don't want that
error to show up at all.

This also happens with my Update Pass Through Queries. What I want is
to just insert the SQL code in the pass through query, run it so it
inserts or updates and that's it. I don't want to return a
recordset, or I don't want to return errors. I know I could do this
with a store procedure, but I'm hoping this could work too.

This is what I do from my button:
---------------------------------------------------
On Error GoTo ExitHere

' Using a Pass Through query to insert the new records
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef

Dim rs As DAO.Recordset

' set the variable values
Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qryInsertRecords")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNumber, Finance.FirstName,
Finance.LastName, Finance.Amount, Finance.AddedBy FROM Finance"

' opening the new recordset
Set rs = loQdf.OpenRecordset()

You don't "open" action queries. You "execute" them.

After modifying the SQL use...

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

[ILCSP]

Yes, I just found out that too.
Thanks Rick.

Rick Brandt wrote:

IL***@NETZERO.NET wrote:

Hello, I'm fairly new to the concept of running action pass through
queries (insert, update, etc.) from Access 2000. I have a SQL Server
2000 database and I'm using a Access 2K database as my front end.

I'm using a blank pass through query which gets the Transact-SQL part
inserted from a button in my form. After inserting the Transact-SQL
code into the pass through query, I 'open the recordset' to make the
query run.

However, everytime this is successful I get a 3325 runtime error:
"Pass-through query with ReturnsRecords property set to True did not
return any records". I've tried to set the Return Records query
property to 'No' but it does not insert any records if I do. I have
to catch that 3325 error in the error handler, but I don't want that
error to show up at all.

This also happens with my Update Pass Through Queries. What I want is
to just insert the SQL code in the pass through query, run it so it
inserts or updates and that's it. I don't want to return a
recordset, or I don't want to return errors. I know I could do this
with a store procedure, but I'm hoping this could work too.

This is what I do from my button:
---------------------------------------------------
On Error GoTo ExitHere

' Using a Pass Through query to insert the new records
Dim loDB As DAO.Database
Dim loQdf As DAO.QueryDef

Dim rs As DAO.Recordset

' set the variable values
Set loDB = CurrentDb
Set loQdf = loDB.QueryDefs("qryInsertRecords")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNumber, Finance.FirstName,
Finance.LastName, Finance.Amount, Finance.AddedBy FROM Finance"

' opening the new recordset
Set rs = loQdf.OpenRecordset()

You don't "open" action queries. You "execute" them.

After modifying the SQL use...

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

[Tim Marshall]

IL***@NETZERO.NET wrote:

Yes, I just found out that too.
Thanks Rick.

Rick Brandt wrote:

You don't "open" action queries. You "execute" them.

After modifying the SQL use...

loDB.Execute "qryInsertRecords", dbFailOnError

Apologies for coming onto this so late (I've been away), but another
thing you may wish to add, just _before_ the execute statement Rick
showed you, is:

loDB.ReturnsRecords = False

A common complaint of folks new to action PTQs is that Access throws an
irritating advisory message: "Pass-through query with ReturnsRecords
property set to True did not return any records". Setting the
ReturnsRecords property to false will tell Access it's OK that the
statement doesn't return records and you won't get the advisory.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me