473,836 Members | 2,099 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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( "qryInsertRecor ds")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNu mber, Finance.FirstNa me,
Finance.LastNam e, Finance.Amount, Finance.AddedBy FROM Finance"

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

'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!

Nov 23 '05 #1
3 12613
IL***@NETZERO.N ET 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( "qryInsertRecor ds")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNu mber, Finance.FirstNa me,
Finance.LastNam e, Finance.Amount, Finance.AddedBy FROM Finance"

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


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

After modifying the SQL use...

loDB.Execute "qryInsertRecor ds", dbFailOnError
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 23 '05 #2
Yes, I just found out that too.
Thanks Rick.

Rick Brandt wrote:
IL***@NETZERO.N ET 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( "qryInsertRecor ds")

loQdf.SQL = "INSERT INTO Orders (CheckNumber, FirstName, LastName,
Amount, AddedBy ) SELECT Finance.CheckNu mber, Finance.FirstNa me,
Finance.LastNam e, Finance.Amount, Finance.AddedBy FROM Finance"

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


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

After modifying the SQL use...

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


Nov 23 '05 #3
IL***@NETZERO.N ET 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.Execut e "qryInsertRecor ds", 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.ReturnsRec ords = 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
Nov 24 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
6466
by: Adam Louis | last post by:
I would like help resolving this problem. I'm a novice who's been hired to query a hospital database and extract useful information, available to me only in a dynamically generated, downloadable .mdb. The query below query runs correctly and without error, but any attempt to save it causes Access to crash without a message, leaving the .ldb file. Opening the DB reveals it saved a blank "query1". I've upgraded to Jet SP 8, and I'm running...
6
10233
by: Access Newbie | last post by:
I'm using Access 2000 and I'm trying to create a pass-through query to append all the data from a local table to a remote table. I'm using the SQL query editor in MS Access to create the query (I don't want to do this in VB code). I've set up the ODBC connection string in the query properties, which has been tested and works: ODBC;DSN=dev001 student99;Description=dev001...
1
2415
by: JM | last post by:
Hello, Using Access 2000 queries, you can reference(pass) form values directly using syntax like Forms!frmPaint!txtColor. I want to do a pass through query to SQL Server 2000, but I don't know how to pass the form values to SQL server without resorting to VB code. These canned queries populate other elements of the form, and that's another reason I don't want to write additional code. These queries were originally set up merely...
2
4442
by: ILCSP | last post by:
Hello, I'm in the process of changing our 'normal' Access 2000 update queries to Update Pass Through Queries. We have a SQL server 2000 database and we're using an Access 2000 database as our front end. In the criteria of one of our update query fields, we use both the isnull(field1) and Not IsNull(field2) then Field3, otherwise, set the updated field as null. Here's my converted Pass Through Query : UPDATE Vsel SET VSel.Cert1 =...
2
2892
by: ILCSP | last post by:
Hello again, I'm still in the process of changing ms access queries to Pass through queries. We're using a SQL Server 2000 database with an Access 2000 DB as our front end. I trying to convert this ms access query to a pass through and I'm having quite a hard time doing it, so I hope you guys can help me. I have a clients form and in this form there are 2 combo boxes where a user can select a client's Type and Status. One for each. ...
1
2066
by: riki71 | last post by:
hallo developerfriends, I have a urgent problem with transfering Data from a DB2-Server to Access97. Tranfer runs by following steps: 1. creating pass-through-query 2. runing action-query based on pass-through (insert into) Problem: for about 50.000 Datasets Access97 needs about 1min. To long for the users. The responce time of the Pass-Through is 5 to 10 sec. Transfering Data about 50 sec.
2
3220
by: gumby | last post by:
I would like to call this stored procedure, but I am unable to pass parameters to the @Start and @End. Is thier a way to pass parameters to a pass through query from MS Access? SELECT COUNT(dbo.tblPersActionHistory.PersActionID) AS , .fn_FindStartPayPeriod(dbo.tblPersActionHistory.PersActionID, 2) AS FROM dbo.tblPersActionLog INNER JOIN
5
6330
by: marshmallowww | last post by:
I have an Access 2000 mde application which uses ADO and pass through queries to communicate with SQL Server 7, 2000 or 2005. Some of my customers, especially those with SQL Server 2005, have had pass-through queries fail due to intermittent connection failures. I can easily restablish a connection for ADO. My problem is with pass-through queries.
13
3740
by: magickarle | last post by:
Hi, I got a pass-through query (that takes about 15 mins to process) I would like to integrate variables to it. IE: something simple: Select EmplID from empl_Lst where empl_lst.timestamp between !! And !! Not sure how to do so (should it be a query in Access or a macro) The connection would be ODBC.
0
9812
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10828
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10534
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10244
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9364
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7775
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5814
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4444
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3103
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.