quartz

The problem is that the Workspace object is falling out of scope, so all of
its child object's fall out of scope. You need to preserve the Workspace
object either by creating a module level variable for it, or by passing it
into a sub. You might rewrite it as follows

Sub DAORecordsetQuery(ByRef rs As DAO.Recordset, _
ByRef ws As DAO.Workspace, _
argConnectionString As String, _
argSQL As String, _
argTimeout As Integer)

Dim cn As DAO.Connection

Set ws = DAO.CreateWorkspace("", "", "", dbUseODBC)
Set cn = ws.OpenConnection("", , , argConnectionString)
cn.QueryTimeout = argTimeout
Set rs = cn.OpenRecordset(argSQL, dbOpenDynaset)

End Sub

Sub test()

Dim rs As DAO.Recordset
Dim ws As DAO.Workspace

DAORecordsetQuery rs, ws, myConn, mysql, 100

Do While Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Loop

End Sub

You pass rs and ws ByRef because ws is the top level object and you need to
preserve it and rs is the object you need. cn will stay in scope because ws
does.

If you use ADO instead of DAO, you don't have to jump through all those
hoops. In ADO, Recordset is a top level object, so you don't need to
preserve the connection. You do, however, have to remove the ODBC; from the
start of the connection string (which I do with the Right() function).

Function ADORecordsetQuery(sConn As String, _
sSql As String) As ADODB.Recordset

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open sConn
rs.Open sSql, cn

Set ADORecordsetQuery = rs

End Function

Sub test2()

Dim rs As ADODB.Recordset

Set rs = ADORecordsetQuery(Right(myConn, Len(myConn) - 5), mysql)

Do While Not rs.EOF
Debug.Print rs.Fields(0).Value
rs.MoveNext
Loop

End Sub

If you switch to ADO, you'll have to mess with some of the properties of rs
in the function to mirror the dbOpenDynaset that you use now. I think
you'll need to change the CursorLocation property, but I'm not sure about
that.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com

"quartz" wrote in message
...
I wrote a function that uses DAO to retrieve data from an Oracle database
into a recordset. The query runs fine and retrieves data into the
recordset,
but when I try to use the recordset in the calling subroutine, the
recordset
object is "no longer valid". My function appears below. Can someone please
correct this code? (please correct for line wrapping)

Function DAORecordsetQuery(argConnectionString As String, argSQL As
String,
argTimeout As Integer) As DAO.Recordset

Dim ws As DAO.Workspace
Dim cn As DAO.Connection
Dim rs As DAO.Recordset

Set ws = CreateWorkspace("", "", "", dbUseODBC)
Set cn = ws.OpenConnection("", , , argConnectionString)
cn.QueryTimeout = argTimeout
Set rs = cn.OpenRecordset(argSQL, dbOpenDynaset)
Set DAORecordsetQuery = rs

End Function

Thanks in advance.

"Dick Kusleika" wrote ...

The problem is that the Workspace object is falling out of scope

In ADO, Recordset is a top level object, so you don't need to
preserve the connection.

That's right. The Recordset has an ActiveConnection property which
retains a reference to the Connection object.

If you switch to ADO, you'll have to mess with some of the properties of rs
in the function to mirror the dbOpenDynaset that you use now.

The 'dynaset' is known to the non-DAO world as a keyset cursor e.g.
CursorType=adOpenKeyset in ADO.

I think
you'll need to change the CursorLocation property, but I'm not sure about
that.

To use a keyset cursor in ADO, the cursor location must be server
side.

Jamie.

--