ADO Close

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....

Code:

---


 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

---

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

Code:

---

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).

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.

Code:

---

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

---

Quote:

---

Code:

---

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

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

You guys never cease to amaze!!!!