If you need to run a complicated query that returns a large recordset, ADO 2.1 gives you the best of both worlds. Sometimes you just need to put a recordset into an AddItem type grid, or prepare it for a report. So, if you need to process the recordset as soon as the first record is fetched, you should start processing in the Execute_Complete event of the connection object. If you can also use a disconnected recordset, you can set the ActiveCon-nection property equal to nothing. This code might be the fastest way to process a large recordset with ADO:
Private WithEvents m_adoConEvent As ADODB.Connection' the RS that enables the event Fetch_Complete ' to be fired off Attribute Private WithEvents m_adoRstEvent As ADODB.Recordset Private Sub GetRecordSet() Dim sSQL As String 'A large or complicated SQL statemtent sSQL = "select a large complicate query" Set m_adoConEvent = New ADODB.Connection Set m_adoRstEvent = New ADODB.Recordset m_adoConEvent.Open "Connection String" m_adoRstEvent.CursorLocation = adUseClient Me.Caption = "Started" 'Do something to tell the user where the process is 'at. Have the command execute and fetch at the same 'time without interrupting workflow. m_adoRstEvent.Open sSQL, m_adoConEvent, _ adOpenStatc, adLockReadOnly, adCmdText _ Or adAsyncFetch Or adAsyncExecute End SubPrivate Sub m_adoConEvent_ExecuteComplete(ByVal _ RecordsAffected As Long, ByVal pError _ As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ ByVal pCommand As ADODB.Command, ByVal pRecordset As _ ADODB.Recordset, ByVal pConnection As ADODB.Connection) Do Until pRecordset.EOF 'start processing the recordset LoopEnd SubPrivate Sub m_adoRstEvent_FetchComplete(ByVal pError _ As ADODB.Error, adStatus As ADODB.EventStatusEnum, _ ByVal pRecordset As ADODB.Recordset) Set pRecordset.ActiveConnection = Nothing 'this will speed processing timeEnd SubPrivate Sub m_adoRstEvent_FetchProgress(ByVal Progress _ As Long, ByVal MaxProgress As Long, _ adStatus As ADODB.EventStatusEnum, ByVal _ pRecordset As ADODB.Recordset) 'let the user know work is happeningEnd Sub
By using both adAsyncFetch and adAsyncExecute, you can start processing even while you’re returning data.