Excel/Access connection via ADO

[Loane Sharp]

Hi there

I'm using a piece of VBA code to connect to an Access database via ADO from
within Excel.

I'm using a For ... Next loop to access the database many times, and my
queries have a very similar SQL statement structure.

I'm opening and closing the connection to the database with each loop, which
must be very inefficient!!! Certainly the procedure takes an enormous amount
of time to run.

There must be a way to access the database, keeping the connection open?!
I've come across the .Restartable property / .Requery method in the DAO
context, which seems to be the thing I need, but which isn't supported with
ADO.

Please help
Best regards
Loane

 

[Jamie Collins]

I'm using a piece of VBA code to connect to an Access database via ADO from
within Excel.

I'm using a For ... Next loop to access the database many times, and my
queries have a very similar SQL statement structure.

I'm opening and closing the connection to the database with each loop, which
must be very inefficient!!! Certainly the procedure takes an enormous amount
of time to run.

There must be a way to access the database, keeping the connection open?!
I've come across the .Restartable property / .Requery method in the DAO
context, which seems to be the thing I need, but which isn't supported with
ADO.

You can keep the connection open and keep using it until you need to
close it e.g.

Dim oCon As ADODB.Connection
....
oCon.Open
For i = 0 to 99
oCon.Execute "INSERT INTO ...
Next
oCon.Close

BTW the ADODB.Recordset object does support the Requery method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthadorequery.asp

IMO ADO is better than DAO, even when the data source is Jet (for
which DAO is biased). I think DAO is only still used by hardcore MS
Access developers who predate the ADO technology <g>.

Jamie.

--