How query a disconnected ADO recordset - possible?

[Guest]

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? If so, 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...

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)
End Sub

Public Function RunQuery(argSQL) As ADODB.Recordset

Dim cnADO As Connection
Dim rsADO As Recordset
Set cnADO = New ADODB.Connection
cnADO.CursorLocation = adUseClient
cnADO.ConnectionString =
"PROVIDER="";DRIVER={DriverName};SERVER=ServerName;DBQ=DBQName;UID=;PWD=;"
cnADO.CommandTimeout = 0
cnADO.Open
Set rsADO = New ADODB.Recordset
rsADO.MaxRecords = 0 '0 = everything
Set rsADO = cnADO.Execute(argSQL)
Set rsADO.ActiveConnection = Nothing 'Disconnect rs
Set RunQuery = rsADO.Clone(adLockReadOnly)
If rsADO.State = adStateOpen Then rsADO.Close
Set rsADO = Nothing
cnADO.Close
Set cnADO = Nothing
End Function

 

[Guest]

Hi quartz

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

 

[Tim Williams]

A disconnected recordset will support both .Filter and .Sort : one or
both of these might meet your needs.

I would have to ask why you don't just do all the search in the
original query though.... More details on why you need to "query"
the recordset might provoke some more useful alternatives.

Tim

 

[onedaywhen]

Is it possible to now query the disconnected recordset?

Not really. There may be other ways to achieve the same results. I use
the recordset Filter method a lot. You can create a JOIN between ODBC
sources using the Jet OLE DB providers or MS Access/Excel ODBC drivers
e.g. to create a JOIN between a text file and a SQL Server table.
Something else to consider is data shaping e.g. to create a
hierarchical recordset (i.e. a recordset of recordsets), append a new
writable column to the recordset, etc. For some articles on SHAPE, see:

http://msdn.microsoft.com/library/d...0/htm/mdmschierarchicalcursorsdatashaping.asp
Jamie.

--

 

[onedaywhen]

Is it possible to now query the disconnected recordset?

Not really. There may be other ways to achieve the same results. I use
the recordset Filter method a lot. You can create a JOIN between ODBC
sources using the Jet OLE DB providers or MS Access/Excel ODBC drivers
e.g. to create a JOIN between a text file and a SQL Server table.
Something else to consider is data shaping e.g. to create a
hierarchical recordset (i.e. a recordset of recordsets), append a new
writable column to the recordset, etc. For some articles on SHAPE, see:

http://msdn.microsoft.com/library/d...0/htm/mdmschierarchicalcursorsdatashaping.asp
Jamie.

--

 

[onedaywhen]

Is it possible to now query the disconnected recordset?

Not really. There may be other ways to achieve the same results. I use
the recordset Filter method a lot. You can create a JOIN between ODBC
sources using the Jet OLE DB providers or MS Access/Excel ODBC drivers
e.g. to create a JOIN between a text file and a SQL Server table.
Something else to consider is data shaping e.g. to create a
hierarchical recordset (i.e. a recordset of recordsets), append a new
writable column to the recordset, etc. For some articles on SHAPE, see:

http://msdn.microsoft.com/library/d...0/htm/mdmschierarchicalcursorsdatashaping.asp
Jamie.

--