Hi quartz

"quartz" wrote:

I have the following subroutine which calls the following function. This
opens an ADO connection and creates a disconnected recordset that is passed
back to the calling sub.

QUESTION: Is it possible to now query the disconnected recordset?

Yes

If so, how

I don't know "just joking"

how do I create a connection to the recordset? Could someone please add the code
needed to do so in the following Subroutine? Thanks much in advance...

This obviously is not how this would be written for production but
it will give you an idea of whats involved. The first query is written
to sheet1!A! and that rs is queried and the results are written to
Sheet1!E3 or whereever the cod e says

Just look at the comments in the code and replace as necessary.


Private Sub btnRun_Click()
Dim strSQL As String
Dim rsADO As Recordset
strSQL = "SELECT EMPLID FROM PSADM.PS_EMPLOYEES WHERE EMPLID = '999999999';"
Set rsADO = RunQuery(strSQL)

'/// here we will write the rs into excel
'///I assume that is where its going
Worksheets("Sheet1").Range("A1").CopyFromRecordset rsADO

Dim cnn As ADODB.Connection
Dim Rs As Recordset
Dim db_Name As String

Set cnn = New ADODB.Connection
'Change the path to your file
db_Name = ("C:\Documents and Settings\The Cat Man\My Documents\ADO Test.xls")

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0" & ";Data Source=" & db_Name _
& ";Extended Properties=""Excel 8.0;HDR=Yes;"";"

Set Rs = New ADODB.Recordset

Rs.ActiveConnection = cnn

'Here we test to see if we are connected

If cnn.State = adStateOpen Then
MsgBox "Welcome to! " & db_Name, vbInformation, App_Name
Else
MsgBox "Sorry. No Data today."
End If

'Retreive the records using one of the following statements

'Returns entire recordset
'Rs.Open "Select * from [Sheet1$A1:A100]"
'Search any range by a column heading (this will search on a number)
'Rs.Open "Select * from [Sheet1$A1:C100] where OrderID = " & 200 & ""
'Search any range by a column heading (this will search on a string)
Rs.Open "Select * from [Sheet1$A1:C100] where OrderID = 'Boston'"

' here we write the results of the new query/re
Worksheets("Sheet1").Range("E1").CopyFromRecordset Rs

'Close the connection
cnn.Close
Set cnn = Nothing

'Destroy the Recordset
Set Rs = Nothing

End Sub


Good Luck
TK