469,898 Members | 1,530 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Calling a recordset from a function.

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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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

Nov 12 '05 #1
4 5343
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.Openrecordset 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.OpenRecordset (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.ie> 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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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

Nov 12 '05 #2
"Gerry Abbott" <pl****@ask.ie> 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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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.OpenRecordset(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

Nov 12 '05 #3
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(sqlParameter As Long) As DAO.Recordset
Dim mySQL As String

mySQL = " ..........."
Set AgendaRS = CurrentDb.OpenRecordset(mySQL)
End Function
---------------------------------------------------------------


"Larry Linson" <bo*****@localhost.not> wrote in message
news:gx*****************@nwrddc01.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.Openrecordset 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.OpenRecordset (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.ie> 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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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


Nov 12 '05 #4
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.com> wrote in message
news:bo**********@hercules.btinternet.com...
"Gerry Abbott" <pl****@ask.ie> 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 = ".............my sql..............."

Set db = CurrentDb
Set myQdf = db.CreateQueryDef("")
myQdf.SQL = mySql
Set db = Nothing
Set PassRS = myQdf.OpenRecordset()
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.OpenRecordset(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

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Johan Lachonius | last post: by
5 posts views Thread by Zlatko Matić | last post: by
30 posts views Thread by Tim Marshall | last post: by
1 post views Thread by Amanda | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.