Recordset very slow to open/close

A

Arnaud Lesauvage

Hi group !

I am querying an Access database in an Atuocad VBA Module.
The query is a count(*) on 2 joined tables. One of the table is
local, the other one is linked.

When I run the query in the Access database, it runs very fast (~1
second).
When I run it in VBA, the recordset is very slow to open *and*
very slow to close !
I really don't understand why !

Here is my code :

Dim oConn As ADODB.Connection
Dim oRs As New ADODB.Recordset

oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
oConn.Properties("Data Source") = strDBPath
oConn.Properties("Jet OLEDB:System database") = strSecurePath
oConn.Open UserID:=strUser, Password:=strPass

oRs.CursorLocation = adUseServer '(I added this code to check if
server side cursor was faster, but it doesn't change anything)

oRs.Open "SELECT Count(*) AS theCount FROM A LEFT JOIN B ON ...
WHERE ...", oConn, adOpenForwardOnly, adLockReadOnly
lngMyCount = oRs("theCount")
oRs.Close
oConn.Close


As I mentionned earlier, the .Open method takes more than 5
seconds where it takes 1 second in Access, and the .Close method
takes more than 2 seconds (maybe 3 seconds) !

Does anyone have an idea ?

Thanks a lot !

Regards
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

copy recordset to CSV file 1
Recordset adLockReadOnly but still "currently locked by another user"! 1
Word 2003 print dialog very slow to open 0
VERY SLOW to open 8
Slow to open & close documents 0
using VBA to insert records from access database to sql server 8
Code very slow 2
Word is slow to open and very slow to close 1

Top