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)
' -----------------------------SNIP 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
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.

Hi Lenny,
Did you try using ADO in a function returning the recordset? Here is an
example that I use all the time (I have stripped out Error handling).

Dim ClientID as Integer

Public Sub CallingSub()
Dim rs as New ADODB.Recordset

'This will call the ReturnRS function and return a recordset.
set rs = ReturnRS.Clone

End Sub


Public Function ReturnRS () as ADODB.Recordset
Dim DB as New ADODB.Connection
Dim rs as new ADODB.Recordset

'set the connection string
DB.Connectionstring = SomeConnectionString
DB.CursorLocation = adClientSide
DB.Open

'Get the recordset object and hold it in memory
rs.Open "Select * from tblClients where ClientID = " & ClientID, DB,
adOpenStatic, adLockPessimistic, adCmdText
set rs.ActiveConnection = nothing

'Now assign it to the function
set ReturnRS = rs.Clone

'Finally, close everything

rs.close
set rs = nothing
set DB = nothing


End function


Hope this helps.


--
David Hodgkins, MCSD, MCDBA, MCSE
JSTAR Software Solutions
4402 Sweet Cherry Ln.
Kalamazoo, MI 49004
www.jstarsoftware.com - Home of AutoCompact
269-382-2931

"lenny" <lwintfeld (AT) libertycorner (DOT) net> wrote