ADO Close

[lbargers]

Hi all,

I have a procedure that opens an MS Access database and updates an MS Excel spreadsheet with the data. This process is taking 20 plus minutes to complete because I am closing the ado recordset after each time data is read in from the Access table. Is there a way around the call to "Close" the ado recordset before the sql statment is executed.

Thanks for any help on this issue...

Larry....

 dbFullName = "C:\Documents and Settings\Larry.Bargers\Desktop\Test Data\CHIPSforACAPS.mdb"

    Set conn = New ADODB.Connection
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbFullName & ";"         ' open the ATLAS database
    
    Set recSet = New ADODB.Recordset
    'recSet.CursorLocation = adUseServer     'uses Cursortype "adUseServer" for faster more efficient retrieval of records
        
    rowValue = 2
    
    Do Until rowValue = 14675
    
        strSQL = "Select * from CHIPSforACAPS WHERE [APPLICATION NUMBER] = '" & Me.Cells(rowValue, 2) & "'"
        recSet.Open strSQL, conn, adOpenDynamic, adLockOptimistic, adCmdText
           
        Me.Cells(rowValue, 50) = recSet![DENIAL CODE 2]
        Me.Cells(rowValue, 51) = recSet![DENIAL CODE 3]
        Me.Cells(rowValue, 52) = recSet![PRE-APPROVAL]
        
        recSet.Close
        
        rowValue = rowValue + 1
    Loop

[Dan_H]

At the mo, you are querying the database for every row that you want - the beauty of recordsets is you can return every row you need all at once, then loop through the recordset to extract the data. this method shoud be a lot quicker.

You will need to rebuild the SQL query so it will return all the rows you want, then you can do something like this to loop through the recordset

Do While Not RecSet.EOF
    'Get stuff into excel cells as you like
    Me.Cells(RowValue, 50) = RecSet.Fields("Whatever")
    RowValue = RowValue + 1
    RecSet.MoveNext
Loop

Without seeing the database data its a bit difficult for me to suggest how to modify the SQL query, but maybe just SELECT * FROM CHIPSForACAPS would do it (depends if you need to do any more filtering).

[Thread1]

Just like what Dan_H has just said you can retrieve all the rows into a recordset and then loop through all the records or use the Find method to search each record a lot quicker. But it will depend on the number of records your MDB has versus the rows in your Excel..

Anyways, have you tried to use the <Connection>.Execute instead of open-close recordset?.. As long as there's an index in the field ([APPLICATION NUMBER]) I think that would suffice.

dbFullName = "C:\Documents and Settings\Larry.Bargers\Desktop\Test Data\CHIPSforACAPS.mdb"

    Set conn = New ADODB.Connection
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbFullName & ";"         ' open the ATLAS database    
        
    rowValue = 2
    
    Do Until rowValue = 14675
    
        strSQL = "Select [DENIAL CODE2], [DENIAL CODE 3], [PRE-APPROVAL] from CHIPSforACAPS WHERE [APPLICATION NUMBER] = '" & Me.Cells(rowValue, 2) & "'"
        set recSet = conn.Execute(strSQL)

        if not recSet.Eof then
            Me.Cells(rowValue, 50) = recSet.Collect(0)
            Me.Cells(rowValue, 51) = recSet.Collect(1)        
            Me.Cells(rowValue, 52) = recSet.Collect(2)
        end if
        
        rowValue = rowValue + 1
    Loop

   set recSet = nothing

[amarjitamarNew]

dbFullName = "C:\Documents and Settings\Larry.Bargers\Desktop\Test Data\CHIPSforACAPS.mdb"

    Set conn = New ADODB.Connection
    
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & dbFullName & ";"         ' open the ATLAS database    
        
strSQL = "Select [DENIAL CODE2], [DENIAL CODE 3], [PRE-APPROVAL] from CHIPSforACAPS WHERE [APPLICATION NUMBER] 
between 2 and 14675 "

 set recSet = conn.Execute(strSQL)

 recset.movefirst
 if not recSet.Eof then
    Me.Cells(rowValue, 50) = recSet.Collect(0)
    Me.Cells(rowValue, 51) = recSet.Collect(1)        
    Me.Cells(rowValue, 52) = recSet.Collect(2)
    recset.movenext
 end if

set recSet = nothing

Try this to see whether it works.

Regards
Amarjit

[lbargers]

Thanks for all the responses!! The code now executes within a minute or so!

You guys never cease to amaze!!!!