Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
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 "quartz" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
quartz wrote:
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/de...atashaping.asp Jamie. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
quartz wrote:
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/de...atashaping.asp Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How query a disconnected ADO recordset - possible?
quartz wrote:
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/de...atashaping.asp Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
query a recordset | Excel Discussion (Misc queries) | |||
Type recordset/recordset? | Excel Programming | |||
Copy recordset from an Access "make table" query | Excel Programming | |||
DAO query/recordset returns with field names | Excel Programming |