Problem returning a recordset from a Sub in vba

[lenny]

Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query works fine, but
passing the resulting recordset back to the sub's caller is not working

out.
Things I tried:
I attempted creating a Function that returns the recordset.
I tried passing (ByRef) a recordset into a sub, and having the sub
populate the record set.
The problem is that the recordset IS correctly created and populated in

the Function or Sub but it's empty when passed back to the caller!! I'm

guessing this is a scoping problem, but I just don't see it.
I'm using DAO.DBEngine to make the database connection.
Here is an abridged version of the program, showing only the portions
that would be of interest in solving my problem
Public QueryResult As Recordset 'Global recordset declaration
Const Dim SQLQUERYCPFB as String = "<some known valid sql query here>"
Const Dim USER as String = "<the usernanme>"
Const Dim PWD as String = "<the password>"
Const Dim TODBCDSN As String = "LCCM TEST Database" 'Data Source name
Const Dim TQDATABASE As String = "martt2" 'test database name
Const Dim SQLQUERYFIRSTPART As String = "<a piece of a query>"
Const Dim SQLQUERYNODATE As String = "<a piece of a query>"
'Dummy starting point
Public Sub Main
MakeOLR xxx, yyy
End Sub
'Do a bunch of stuff with Excel spreadsheets (not shown here) then make

a query via a subroutine call
Public Sub MakeOLR(WkbPathAndName As String, AccountID As Integer)
' -----------------------------S*NIP OUT LOTS OF EXCEL
CODE--------------------------*---
'Query the Database requesting data on today's loans for the
account the user selected
Dim Account as String
Account = "<some partial query>" 'gotten from excel sheets
QueryLoans Account, QueryResult 'call sub that does the query
passing a piece of a query and the recordset to be populated
QueryResult.MoveFirst
Dim cNr As Integer, cusip As String ' test variables to see if the

recordset has something in it
cNr = QueryResult.Fields.Count ' what I find is 0. should be 11
cusip = QueryResult.Fields(0) ' what I find is an empty string
should be a 10 character string
End Sub
Public Sub QueryLoans(Account As String, QueryResult as Recordset)
Dim ws As Workspace
Dim conn As Connection
'ODBC Direct workspace which will host a connection to the
database using USER, PWD
Set ws = DAO.DBEngine.CreateWorkspace("*", USER, PWD, dbUseODBC)
'Create a database connection.
Dim str as String
str = "ODBC;DSN=" & TODBCDSN 'for testing
Set conn = ws.OpenConnection(TQDATABASE, dbDriverNoPrompt, True,
str)
'Compose the query for today's loans
Dim DatabaseDate As String
DatabaseDate = "2005-03-07" 'TESTING ONLY
'SQLQUERYFIRSTPART and QLQUERYNODATE are fixed strings defined in
the Const / Global section
Query = SQLQUERYFIRSTPART & Account & SQLQUERYNODATE & "'" &
DatabaseDate & "'"
'Now make the query
Set QueryResult = conn.OpenRecordset(Query, dbOpenDynamic) 'obtain a

result in passed in recordset
'Here we test to see if ther is something interesting in the
recordset. The result is that there is a reasonable value for cNr (11)
and for cusip (a short string representing a real database value). I do

it twice to see if the read is somehow destructive. It isn't
Dim cNr As Long, cusip As String
QueryResult.MoveFirst
cNr = QueryResult.Fields.Count
cusip = QueryResult.Fields(0)
cNr = QueryResult.Fields.Count
cusip = QueryResult.Fields(0)
End Sub
So to reiterate:
The connection to the database inside Sub "QueryLoans" goes fine.
Inside Sub "QueryLoans" the recordset QueryResult (which is both Global

and is passed as a parameter)yields reasonable results. But inside Sub
MakeOLR, the recordset results in a .Count of 0 and a .Fields(o) of "",

both of which are incorrect.
The QueryResult recordset (I presume) is passed in ByRef, which is the
default way VBA passes parameters. On top of that The QueryResult
recordset is declared Global. But somehow, it gets reset when Sub
QueryLoans returns!!!!
I get similar behavior when I rewrite QueryLoans(...) as a function
that returns a recordset.
As of now, I'm stumped.
Thanks in advance for any advice!
Lenny Wintfeld
ps- the database I'm connecting to is an Oracle database (using
ODBCDirect). I post the problem here because the connection to the
database goes smoothly; it's the handling of the returned recordset
that's the problem. And it seems here's the place where Database VBA
and ODBC experts hang out.... -L.

[pietlinden]

For starters, it looks like you're mixing up Functions and Subs. Subs
are UNABLE to return a value. So trying to return a recordset from a
sub is never going to work. You could create a function that takes
whatever arguments you need, and then connect to the Oracle DB (using
ADO if you want) and then returning a recordset.

Public Function OracleRecordset(arglist) As Recordset
dim rs as adodb.recordset
....
OracleRecordset = rs
rs.close
set rs = nothing
End Function

Start there and see what happens.

[jimfortune]

lenny wrote:

Hi,
I've been trying to use a Sub or Function in VBA to connect to a
database, make a query and return the recordset that results from the
query. The connection to the database and the query works fine, but
passing the resulting recordset back to the sub's caller is not working
out.
Things I tried:
I attempted creating a Function that returns the recordset.
I tried passing (ByRef) a recordset into a sub, and having the sub
populate the record set.

I'm not an expert yet, but I looked at a sample Subroutine where I pass
a recordset (different use though) and noticed that I also passed the
name of the database object variable used to create the recordset.

Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
....
Call FillForm(cbxIndex.Text, MyRS, MyDB)

In my case it allowed me to preserve the recordset. Note: I didn't
need to use a ByRef. Maybe just the database object going out of scope
caused your problem. I hope this helps.

James A. Fortune

[lenny]

I found the problem and fixed it. I'm working in an environment where
the DAO and ADO libraries are both available. If I qualify the
declaration of the Recordset variable (which is an object that's
available in both libraries) the problem goes away. For instance Dim
QueryResult As DAO.Recordset or Dim QueryResult As ADODB.Recordset. In
my case, I completely rewrote the problematic code to use ADO, and
qualified all ADO objects with ADODB and class objects as "Set
<classobjectvariable> = New ADODB.<classObjectName>.

[Steve Jorgensen]

On 30 Mar 2005 07:05:27 -0800, "lenny" <lw*******@libertycorner.net> wrote:

I found the problem and fixed it. I'm working in an environment where
the DAO and ADO libraries are both available. If I qualify the
declaration of the Recordset variable (which is an object that's
available in both libraries) the problem goes away. For instance Dim
QueryResult As DAO.Recordset or Dim QueryResult As ADODB.Recordset. In
my case, I completely rewrote the problematic code to use ADO, and
qualified all ADO objects with ADODB and class objects as "Set
<classobjectvariable> = New ADODB.<classObjectName>.

Well, you may have made it work, but I'll bet it still has problems. In the
code you presented, you created DAO objects, but did not properly tear them
down, then you tried to use the recordset in a procedure after its connection
object went out of scope.

If you are doing something similar in ADO, you can also expect strange
results. The only time you should ever try to use a recordset after its
database or connection instance is no longer being maintained is if you make
it a disconnected ADO recordset.

[bruce]

I agree with Steve. I used to use this function all the time to get a
table-type recordset based on a table regardless of whether it was
local or a linked table:

Function ExtRecordset(strTableName As String) As Recordset
'
'''''''''''''''''''''''''''''''''''''''''''''''''' '''''''''''''''''''''''''''''''''''''''''''''''''' '''''''
Dim db As Database
Dim strConnect As String

Set db = CurrentDb()

strConnect = db.TableDefs(strTableName).Connect
If Len(strConnect) = 0 Then ' this is _not_ an
attached table
Set ExtRecordset = db.OpenRecordset(strTableName, dbOpenTable)
Else ' this is a linked
table

Set db = DBEngine.Workspaces(0).OpenDatabase(Mid(strConnect ,
InStr(1, strConnect, "=") + 1))
Set ExtRecordset = db.OpenRecordset(strTableName, dbOpenTable)
End If

End Function

While this works, the database object that the recordset is a child of
should go out of scope when the function exits. Logically this should
foul up the returning recordset object but it doesn't. I stopped using
it because it made me very afraid...

Bruce

[lenny]

Thanks for your replies and critiques. In my ADO version I declared the
connection object global; though I I could have narrowed the scope some
and declared it in the caller.

Another alternative I guess would have been to invent a new class to do
the all the database nitty gritty work and then declare an instance of
the class in the caller....

But the current formulation seems ok for a small program.
-Lenny