Pass Recordset object between subs/functions

[huyeote]

Hi there,

I'm trying to code up a function in ACCESS 2000 VBA to return a
recordset object to a sub. However, I couldn't make the recordset
returned as the recordset object I got inside the function disappered
as the ADODB connection was closed at the end of the function. I tried
to assign the recordset to a public recordset variable and this method
had the same result. So can anyone tell me how to pass a recordset
object to another sub?

Thanks,

Huyeote



My codes are like:

'---------------------------codes start here
-------------------------------------------------------

Public rstAcctNo_temp as ADODB.Recordset

Function GetRecordset(strSourceFile as String) AS ADODB.Recordset

Dim rstConn As ADODB.Connection

Dim strSQL As String
Dim strConn As String 'for access connection string

strConn = "Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=" & strSourceFile & ";"

strSQL = "SELECT DISTINCT A.AccountNo, A.Supplier, A.Customer,
A.BillingType " _
& "FROM table1 AS A"

Set rstConn = New ADODB.Connection

'Open connection to source DB
rstConn.Open ConnectionString:=strConn

If rstConn.State = adStateOpen Then
'assign data to public variable
Set rstAcctNo_temp = rstConn.Execute(strSQL)

Set Get_rstAcctNo = rstAcctNo_temp

End If

rstConn.Close
End Function


Sub Collating()
Dim rstAcctNo As ADODB.Recordset
................
Set rstAcctNo = Get_rstAcctNo(filePath) 'where rstAcctNo is
nothing after the connection inside the function is closed.
...................
End Sub

 

[Robert Morley]

The trick is not to close the recordset. You're returning a *copy* of the recordset, you're returning the same one. So if you
close the recordset at the end of the function, the recordset you're returning is also closed, since it's the same thing. Try
something like this:

Public Function MyRS() As ADODB.Recordset
Dim rs as ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open 'whatever parameters you need
Set MyRS = rs
Set rs = nothing
End Function

Then to use it, it would look something like this:

Public Sub UseMyRS()
With MyRS()
Do While Not .EOF
.MoveNext
Loop
.Close
End With
End Sub

You may also want to look into disconnected recordsets, but I'd suggest reading up on them first and then ask any questions you may
have if you go that route.


Rob

 

[Robert Morley]

Oops...that was supposed to read "You're *not* returning a copy"

 

[huyeote]

Oops...that was supposed to read "You're *not* returning a copy"





- Show quoted text -

Thank you Robert for the advice. Now I made my code working. But would
this solution create unnecessary burden to system recources as the the
memory is not freed up after the call of the function?

Huyeote

 

[Robert Morley]

For as long as the recordset remains open, yes, it'll be taking system
resources. If you close it and set the recordset object variable to nothing
at some point, then they'll be freed. If you're more worried about server
resources than the client's system resources, then have a look at ADO's
disconnected recordsets.

The other options, which are generally less system-intensive, but more work
for you, are: a) to store the entire recordset in an array, or b)
encapsulate it within a class module which is designed to retain all the
data in memory. Either way, the recordset is being closed and its resources
freed once you've read in all the data, and only enough memory is used to
store your data, without a lot of unnecessary overhead.

It *is* a lot of work to do things either of these ways, though. The first
one will lead to a lot of unintuitive code blocks as you reference things in
an arbitrary spot in an array; the second will be far more intuitive and a
lot more work as well.

And, of course, with either of the above methods, you lose all the
conveniences of a recordset, such as searching, sorting, etc., unless you
build it yourself.


Rob

 

[Jamie Collins]

Oops...that was supposed to read "You're *not* returning a copy"

Oops... that was supposed to read, "Oops...that was supposed to read
"You're not returning a *recordset*, you are returning a four-byte
reference (pointer) to the recordset."."

Here's a simplified VBA example (well, it was simple before I added
all those Debug.Print lines <g>):

' ---<code>--
Option Explicit
Private module_rs As ADODB.Recordset

Sub Main()
Debug.Print "1", "module_rs", VarPtr(module_rs), ObjPtr(module_rs)

Set module_rs = function_rs

Debug.Print "5", "module_rs", VarPtr(module_rs), ObjPtr(module_rs)
End Sub

Private Function function_rs() As ADODB.Recordset
Dim local_rs As ADODB.Recordset

Debug.Print "2", "module_rs", VarPtr(module_rs), ObjPtr(module_rs)
Debug.Print "2", "function_rs", VarPtr(function_rs),
ObjPtr(function_rs)
Debug.Print "2", "local_rs", VarPtr(local_rs), ObjPtr(local_rs)

Set local_rs = New ADODB.Recordset

Debug.Print "3", "module_rs", VarPtr(module_rs), ObjPtr(module_rs)
Debug.Print "3", "function_rs", VarPtr(function_rs),
ObjPtr(function_rs)
Debug.Print "3", "local_rs", VarPtr(local_rs), ObjPtr(local_rs)

Set function_rs = local_rs

Debug.Print "4", "module_rs", VarPtr(module_rs), ObjPtr(module_rs)
Debug.Print "4", "function_rs", VarPtr(function_rs),
ObjPtr(function_rs)
Debug.Print "4", "local_rs", VarPtr(local_rs), ObjPtr(local_rs)

End Function
' ---</code>--

VarPtr() gives the reference to the variable and ObjPtr() gives the
reference to the object to which the variable points. Executing Sub
Main gave me this:

1 module_rs 2116064 0
2 module_rs 2116064 0
2 function_rs 1306284 0
2 local_rs 1306280 0
3 module_rs 2116064 0
3 function_rs 1306284 0
3 local_rs 1306280 49288888
4 module_rs 2116064 0
4 function_rs 1306284 49288888
4 local_rs 1306280 49288888
5 module_rs 2116064 49288888

The above demonstrates that only one recordset object instance was
created (at memory address 49288888 for me) but there were up to three
references to the instance.

A COM object does not get destroyed until its reference count reaches
zero. If you haven't done anything but run the code, running it a
second time will show that the recordset still exits in memory:

1 module_rs 2116064 49288888
2 module_rs 2116064 49288888
2 function_rs 1307888 0
2 local_rs 1307884 0
3 module_rs 2116064 49288888
3 function_rs 1307888 0
3 local_rs 1307884 49286680
4 module_rs 2116064 49288888
4 function_rs 1307888 49286680
4 local_rs 1307884 49286680
5 module_rs 2116064 49286680

The original object (49288888) not get destroyed because module_rs
(2116064), being a module-level variable, retains the final reference
count on it; it finally gets destroyed at stage 5 on the second pass
when the module_rs gets pointed to the new instance (49286680).

Jamie.

--

 

[Jamie Collins]

If you're more worried about server
resources than the client's system resources, then have a look at ADO's
disconnected recordsets.

That sounds a good suggestion to me.

One important thing for OP to consider is to ensure they are only
fetching the data they actually require i.e. rather than the whole
table's contents.

Jamie.

--