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