Passing objects by value instead of by reference

[aspenbordr]

I have a program broken into a set of modules. It basically looks like
this:

'---main()---

recset = get_records() 'put SQL data recordsets into
'a recordset array
do_stuff recset 'perform operations on that recordset

'--------

Within the get_records() module, I would like to be able to open the
data connection, retrieve the data, pass the data off to main(), then
close all data connections and restore all variables. However, when I
try to close the connection after passing the recordset array, I get
"object is closed" when I try to do any operations on recset in
do_stuff. I assume this is because I am passing my objects by
reference.

I would like to avoid passing a connection object to main and closing
it there, to simplify the code.

Therefore, how can I pass by value to avoid this problem?

Thanks
Andrew

 

[Tom Ogilvy]

when you close the connection, I suspect the recordset is released. You
need to transfer the data to an array.

 

[Jake Marx]

Hi aspenbordr,

You could try setting the ActiveConnection property of the Recordset to
Nothing after you retrieve your records. That should make it
"disconnected", and you should be able to close the connection when you
want.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[aspenbordr]

I could do that, but I would lose all of the recordset member
functions, such as RecordCount and Fields.count, which would severely
impact my existing code. Therefore, do you know of any workaround (such
as passing by value) that would enable me to close the connection
without requiring me to make substantial changes to my code body?

Or what would happen if I just never manually closed the recordset? I
assume it would create a memory leak...but how severe? That is likely
what I will do if there is no other way :-(.

Andrew

 

[Guest]

Here is some code that I use for retrieveing from an Access Database

Private Const m_cDBLocation As String = "C:\Sales Projections
Analysis.mdb"

Public Function RunQuery(ByVal strSelect As String, ByVal strFrom As String, _
ByVal strWhere As String, ByVal strOrderBy, ByVal blnConnected As Boolean)
As ADODB.Recordset
Dim strConnection As String

On Error GoTo ErrorHandler
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
m_cDBLocation & ";"

Set RunQuery = New ADODB.Recordset
With RunQuery
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockBatchOptimistic
End With

RunQuery.Open strSelect & " " & strFrom & " " & strWhere & " " &
strOrderBy, strConnection, , , adCmdText
If blnConnected = False Then Set RunQuery.ActiveConnection = Nothing
End Function

Sub Example()
Dim rst As ADODB.Recordset

Set rst = RunQuery("Select *", "From tblRegions", "", ";", False)

End Sub

It returns either a connected or disconnected recordset depending on the
last argument of the function which will close the active connection (or
not). In terms of your question By Ref or By Val that does not really apply
to objects as what you are passing is just a pointer to the object so it is
always by ref.

 

[aspenbordr]

Looks like that did the trick. Thanks for the tip.

Andrew

Hi aspenbordr,

You could try setting the ActiveConnection property of the Recordset to
Nothing after you retrieve your records. That should make it
"disconnected", and you should be able to close the connection when you
want.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

I have a program broken into a set of modules. It basically looks like
this:

'---main()---

recset = get_records() 'put SQL data recordsets into
'a recordset array
do_stuff recset 'perform operations on that recordset

'--------

Within the get_records() module, I would like to be able to open the
data connection, retrieve the data, pass the data off to main(), then
close all data connections and restore all variables. However, when I
try to close the connection after passing the recordset array, I get
"object is closed" when I try to do any operations on recset in
do_stuff. I assume this is because I am passing my objects by
reference.

I would like to avoid passing a connection object to main and closing
it there, to simplify the code.

Therefore, how can I pass by value to avoid this problem?

Thanks
Andrew

 

[NickHK]

aspenbordr,
Can't you .Clone the recordset ?

NickHK

 

[Jamie Collins]

In terms of your question By Ref or By Val that does not really apply
to objects as what you are passing is just a pointer to the object so it is
always by ref.

The pointer may be passed ByVal or ByRef and there *is* a difference
e.g.

Sub test()
Dim rs As Object
Set rs = CreateObject("ADOR.Recordset")
rs.Source = "Original"
test1 rs
MsgBox rs.Source
test2 rs
MsgBox rs.Source
End Sub

Function test1(ByVal rs As Object)
Dim rs1 As Object
Set rs1 = CreateObject("ADOR.Recordset")
rs1.Source = "Hijacked"
Set rs = rs1
End Function

Function test2(ByRef rs As Object)
Dim rs1 As Object
Set rs1 = CreateObject("ADOR.Recordset")
rs1.Source = "Hijacked"
Set rs = rs1
End Function