-
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
-
Re: ADO Close
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).
-
Re: ADO Close
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
-
Re: ADO Close
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
-
Re: ADO Close
Thanks for all the responses!! The code now executes within a minute or so!
You guys never cease to amaze!!!!