Hi all.
I wish to call a recordset from a function.
Ive tried the following approach,
--------------------------------------------------------
Function PassRS() As Recordset
Dim db As Database
Dim rs As Recordset
Dim myQdf As QueryDef
Dim mySql As String
mySql = ".............m y sql............ ..."
Set db = CurrentDb
Set myQdf = db.CreateQueryD ef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecor dset()
End Function
--------------------------------------------------------
Then i call it like this,
--------------------------------------------------------
Private Sub Command1_Click( )
Call PassRS
With PassRS
.MoveLast
Debug.Print "count " & .RecordCount
End With
End Sub
----------------------------------------------------------
And its seems to work. But how do I destroy the recordset object, and the
qryDef object when i'm done with it?
Thanks in advance for any advice.
Gerry Abbott 4 5718
First, you have gone to unnecessary work just to open a Recordset. There is
neither any need nor any advantage to create a Querydef. Use
db.Openrecordse t and refer directly to the SQL that you've created. Now, we
have eliminated the QueryDef as a problem.
Then if you'll refer directly to CurrentDB, you can eliminate your Database
object variable as a problem.
CurrentDB.OpenR ecordset (mySQL)
That only leaves the Recordset... you can either declare it as a Global
variable, so you can .Close it and Set it to Nothing from anywhere -- that's
a "sure thing". Or, you can just Close and Set to Nothing the Recordset
variable into which the Function delivers it, and hope that clears
everything away. As it was declared in the Function, it should go out of
scope when you exit the Function -- I've never passed a Recordset out of a
function, and, thus, never gave it any thought.
Larry Linson
Microsoft Access MVP
"Gerry Abbott" <pl****@ask.i e> wrote in message
news:bJ******** *********@news. indigo.ie... Hi all.
I wish to call a recordset from a function. Ive tried the following approach,
-------------------------------------------------------- Function PassRS() As Recordset Dim db As Database Dim rs As Recordset Dim myQdf As QueryDef Dim mySql As String
mySql = ".............m y sql............ ..."
Set db = CurrentDb Set myQdf = db.CreateQueryD ef("") myQdf.SQL = mySql Set db = Nothing Set PassRS = myQdf.OpenRecor dset() End Function --------------------------------------------------------
Then i call it like this, -------------------------------------------------------- Private Sub Command1_Click( ) Call PassRS With PassRS .MoveLast Debug.Print "count " & .RecordCount End With End Sub ----------------------------------------------------------
And its seems to work. But how do I destroy the recordset object, and the qryDef object when i'm done with it?
Thanks in advance for any advice.
Gerry Abbott
"Gerry Abbott" <pl****@ask.i e> wrote in message
news:bJ******** *********@news. indigo.ie... Hi all.
I wish to call a recordset from a function. Ive tried the following approach,
-------------------------------------------------------- Function PassRS() As Recordset Dim db As Database Dim rs As Recordset Dim myQdf As QueryDef Dim mySql As String
mySql = ".............m y sql............ ..."
Set db = CurrentDb Set myQdf = db.CreateQueryD ef("") myQdf.SQL = mySql Set db = Nothing Set PassRS = myQdf.OpenRecor dset() End Function --------------------------------------------------------
Then i call it like this, -------------------------------------------------------- Private Sub Command1_Click( ) Call PassRS With PassRS .MoveLast Debug.Print "count " & .RecordCount End With End Sub ----------------------------------------------------------
And its seems to work. But how do I destroy the recordset object, and the qryDef object when i'm done with it?
Thanks in advance for any advice.
Gerry Abbott
Hi Gerrry
I'm not sure why you might need a general function like PassRS() , you might
retain more flexibility to open the recordset within the click sub. Anyway,
if you really did want this sort of function, you might structure it to
handle errors gracefully. What about this as possibility?
Public Function SetRS(strSQL, rst As DAO.Recordset) As Boolean
On Error GoTo Err_Handler
Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenSnapshot)
SetRS = True
Exit_Handler:
Exit Function
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Function
Private Sub Command1_Click( )
On Error GoTo Err_Handler
Dim rst As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM MyTable WHERE ID < 6 ORDER BY ID"
If Not SetRS(strSQL, rst) Then Exit Sub
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
Set rst = Nothing
Exit_Handler:
Exit Sub
Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler
End Sub
Many thanks Larry,
I take your points re refering to the recordsed directly.
Code is much tidier. My belief was that it was necessary,
to use a queryDef object to use SQL to generate a recordset.
(wrong).
The only real benefit for the function now is to hold the SQL,
and so the recordset reference could now be taken out
of the function also.
What function does the queryDef object serve?
Gerry Abbott
----------------------------------------------------------------
Public Function AgendaRS(sqlPar ameter As Long) As DAO.Recordset
Dim mySQL As String
mySQL = " ..........."
Set AgendaRS = CurrentDb.OpenR ecordset(mySQL)
End Function
---------------------------------------------------------------
"Larry Linson" <bo*****@localh ost.not> wrote in message
news:gx******** *********@nwrdd c01.gnilink.net ... First, you have gone to unnecessary work just to open a Recordset. There
is neither any need nor any advantage to create a Querydef. Use db.Openrecordse t and refer directly to the SQL that you've created. Now,
we have eliminated the QueryDef as a problem.
Then if you'll refer directly to CurrentDB, you can eliminate your
Database object variable as a problem.
CurrentDB.OpenR ecordset (mySQL)
That only leaves the Recordset... you can either declare it as a Global variable, so you can .Close it and Set it to Nothing from anywhere --
that's a "sure thing". Or, you can just Close and Set to Nothing the Recordset variable into which the Function delivers it, and hope that clears everything away. As it was declared in the Function, it should go out of scope when you exit the Function -- I've never passed a Recordset out of a function, and, thus, never gave it any thought.
Larry Linson Microsoft Access MVP "Gerry Abbott" <pl****@ask.i e> wrote in message news:bJ******** *********@news. indigo.ie... Hi all.
I wish to call a recordset from a function. Ive tried the following approach,
-------------------------------------------------------- Function PassRS() As Recordset Dim db As Database Dim rs As Recordset Dim myQdf As QueryDef Dim mySql As String
mySql = ".............m y sql............ ..."
Set db = CurrentDb Set myQdf = db.CreateQueryD ef("") myQdf.SQL = mySql Set db = Nothing Set PassRS = myQdf.OpenRecor dset() End Function --------------------------------------------------------
Then i call it like this, -------------------------------------------------------- Private Sub Command1_Click( ) Call PassRS With PassRS .MoveLast Debug.Print "count " & .RecordCount End With End Sub ----------------------------------------------------------
And its seems to work. But how do I destroy the recordset object, and
the qryDef object when i'm done with it?
Thanks in advance for any advice.
Gerry Abbott
Thanks Fletcher,
The main reason to take the recordset code away from my main code, was due
to a complex and cumbersome SQL. But Larry's suggestion requires only one
reference, and so the function similifies down to passing the sql. Your
suggestion for correct handling is well made.
"Fletcher Arnold" <fl****@home.co m> wrote in message
news:bo******** **@hercules.bti nternet.com... "Gerry Abbott" <pl****@ask.i e> wrote in message news:bJ******** *********@news. indigo.ie... Hi all.
I wish to call a recordset from a function. Ive tried the following approach,
-------------------------------------------------------- Function PassRS() As Recordset Dim db As Database Dim rs As Recordset Dim myQdf As QueryDef Dim mySql As String
mySql = ".............m y sql............ ..."
Set db = CurrentDb Set myQdf = db.CreateQueryD ef("") myQdf.SQL = mySql Set db = Nothing Set PassRS = myQdf.OpenRecor dset() End Function --------------------------------------------------------
Then i call it like this, -------------------------------------------------------- Private Sub Command1_Click( ) Call PassRS With PassRS .MoveLast Debug.Print "count " & .RecordCount End With End Sub ----------------------------------------------------------
And its seems to work. But how do I destroy the recordset object, and
the qryDef object when i'm done with it?
Thanks in advance for any advice.
Gerry Abbott
Hi Gerrry I'm not sure why you might need a general function like PassRS() , you
might retain more flexibility to open the recordset within the click sub.
Anyway, if you really did want this sort of function, you might structure it to handle errors gracefully. What about this as possibility?
Public Function SetRS(strSQL, rst As DAO.Recordset) As Boolean
On Error GoTo Err_Handler
Set rst = CurrentDb.OpenR ecordset(strSQL , dbOpenSnapshot)
SetRS = True
Exit_Handler: Exit Function
Err_Handler: MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number Resume Exit_Handler
End Function
Private Sub Command1_Click( )
On Error GoTo Err_Handler
Dim rst As DAO.Recordset Dim strSQL As String
strSQL = "SELECT * FROM MyTable WHERE ID < 6 ORDER BY ID"
If Not SetRS(strSQL, rst) Then Exit Sub
rst.MoveLast
MsgBox rst.RecordCount
rst.Close
Set rst = Nothing
Exit_Handler: Exit Sub
Err_Handler: MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number Resume Exit_Handler
End Sub This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Johan Lachonius |
last post by:
Hi,
I'm using a "classic" asp-page and a .net-component which I want to call
passing a Recordset as a parameter. I have a reference to ADODB in the .net
component which should supply a COM rapper for a Recordset. In the .net
component code looks like:
using ADODB:
public void MyFunction( Recordset rs )
|
by: Andy |
last post by:
Hello,
I have a question regarding how to format a date in VB so that I can
call it from a query and get results. I'm calling functions in the
query because that was the only way I found I could set up a query
with multiple parameters to return data the way I wanted. (I'm reading
up on a filter recordset command that will probably do this much
better, but I haven't got it figured out yet.) The query calls all the
functions and pulls up...
|
by: Zlatko Matiæ |
last post by:
Hello.
How can I call some functions on MSDE when working in .mdb ? Especially
in-line functions which are similar to stored procedures.
How can I use MSDE in-line functions as recordsource for .mdb forms ?
Can I call in-line functions using ADO ? I tried, but it seems that only
stored procedures are allowed (adCmdStoredProc)....
Thanks.
|
by: Tim Marshall |
last post by:
Here's the scenario, A2003, Jet back end, illustrated with some cut down
code at the end of the post:
A proc dims a snapshot recordset (dim rst as Dao.recordset) and opens
it. There are several nested do loops, going through the records in rst
using .movenext. At one point in one of the loops, we'll say the rst is
at record "a". Now, another subprocedure is called, passing rst to it.
In the subprocedure, the recordset goes through a...
|
by: Amanda |
last post by:
Let me see if I can explain this correctly.
In VB.NET, I want to call classes based on a recordset I get back from
a SQL Server database.
Basically, I have a value sending in a table called
cls.CallFunction(strParameter).
In VB.NET cls.CallFunction is a class and a function in my code.
| |
by: Jaime Leivers |
last post by:
Here's a median function in access that you can call in any query. You
could change this to any excel function you wanted. Most people can
find the windows help file that says how to call an excel function but
don't know how to pass an array of the recordset they made into that
function. This uses GetRows that nicely creates an array that can be
passed into excel.
I have this data table for testing. The table name is TestData
...
|
by: klove1209 |
last post by:
Good afternoon.
I need assistance with calling a function on the back end, that takes paremeters for a stored procedure. Then, that function returns the recordset back to the main DB. Below is the code for the procedures on the back and, and what I have so far on the front end.
**This function calls the stored query, with given parameters, and returns the recodset**
Public Function RSFromParameterQuery(strMonthYear As Date, strCase As...
|
by: klove1209 |
last post by:
Good afternoon.
I need assistance with calling a function on the back end, that takes paremeters for a stored procedure. Then, that function returns the recordset back to the main DB. Below is the code for the procedures on the back and, and what I have so far on the front end.
**This function calls the stored query, with given parameters, and returns the recodset**
Public Function RSFromParameterQuery(strMonthYear As Date, strCase As...
|
by: MLH |
last post by:
I have the following saved UNION query named qryPeople2NameInNPaperAd:
SELECT & " " & & " " &
& " " & & ", " & & "
" & AS Item, tblVehicleJobs.VehicleJobID
FROM tblVehicleJobs INNER JOIN tblAddnlOwnrs ON
tblVehicleJobs.VehicleJobID = tblAddnlOwnrs.VehicleJobID Where
tblVehicleJobs.VehicleJobID = GetCurrentVehicleJobID();
UNION SELECT & " " & & " " &
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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...
|
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...
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |