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.
--
HTH...

Jim Thomlinson


"aspenbordr" wrote:

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

Jim Thomlinson wrote:
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