Can a function return a DAO.Recordset?

  • Thread starter Thread starter Helge
  • Start date Start date
H

Helge

Hi

Developing in Access 2002:
I have a function that reads from an SQLServer an creates a DAO.Recordset.
When I create the recordset inside the function it is valid and OK. But when
I try to return the user-defined type (that keeps the recordset) to the
caller, the recordset is invalid.
Why?


The code:

In some module:
Type RS_CIC ' Create user-defined type.
l_RS As DAO.Recordset
End Type
.....................
Public Function get_select_RS(as_select_SQL As String) As RS_CIC
Dim rstTemp As DAO.Recordset
<some code>
Set rstTemp = gc_con_CIC.OpenRecordset(as_select_SQL, dbOpenDynamic)
Set my_RS_CIC.l_RS = rstTemp
get_select_RS = my_RS_CIC
<<Here is the recordet OK>>
End Function
.....................
The Caller:
Dim l_RS As DAO.Recordset
Dim my_new_RS_CIC As RS_CIC
my_new_RS_CIC = get_select_RS("SELECT col1, col2 FROM table1;")
Set l_RS = my_new_RS_CIC.l_RS
<<Here the recordset (l_RA) is invalid>>

regards,
Helge
 
Helge,
You missed the Set keyword in your assignment operator:
Set get_select_RS = my_RS_CIC

Alex.
 
Please disregard the previous post. I was not careful reading ....
I slightly modifyed your sample an dit works fine on my machine:

Type RS_CIC ' Create user-defined type.
l_RS As DAO.Recordset
End Type

Public Function get_select_RS(as_select_SQL As String) As RS_CIC
Dim rstTemp As DAO.Recordset
Dim my_RS_CIC As RS_CIC
'<some code>
Set rstTemp = CurrentDb.OpenRecordset(as_select_SQL,dbOpenDynaset)
Set my_RS_CIC.l_RS = rstTemp
get_select_RS = my_RS_CIC
'<<Here is the recordet OK>>
End Function
'....................
Sub Caller():
Dim l_RS As DAO.Recordset
Dim my_new_RS_CIC As RS_CIC
my_new_RS_CIC = get_select_RS("SELECT * FROM test;")
Set l_RS = my_new_RS_CIC.l_RS
Debug.Print l_RS(0)
End Sub
 
Hi
Thanks for your answer.
Your example also worked for me, BUT it seems that my problem is:
Instead of using "Currentdb" I'm using an ODBC -source as host for my
tables.
That is, I connect using the following code i function
get_select_RS(as_select_SQL As String) As RS_CIC

Dim wrkODBC As DAO.Workspace
Dim gc_con_CIC As DAO.Connection
Dim rstTemp As DAO.Recordset
Dim my_RS_CIC As RS_CIC

Set wrkODBC = CreateWorkspace("", "admin", "", dbUseODBC)
Set gc_con_CIC = wrkODBC.OpenConnection("", , ,
"ODBC;DATABASE=mydatabase;UID=myuserid;PWD=mypasswd;DSN=myDSN")
Set rstTemp = gc_con_CIC.OpenRecordset(as_select_SQL, dbOpenDynamic)

When the data is returned from this *ODBC* source, the RS is invalid at the
end of the "Caller".
Any idea?

regards,
Helge
 
Try to choose a different cursor type and make sure it's a client-side
cursor. If that does not work either (it should), try disconnected
recordset.
Alex.
 
Back
Top