Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/Access connection via ADO
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel/Access connection via ADO
"Loane Sharp" wrote ...
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/de...adorequery.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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2010 (64 bit) External Data connection with Access | Excel Discussion (Misc queries) | |||
reconnecting a workbook connection from Access-Excel | Excel Discussion (Misc queries) | |||
Excel data connection to Access | Excel Discussion (Misc queries) | |||
Can Excel and access update each other like a two way connection | Excel Discussion (Misc queries) | |||
Cut the umbilical cord? Access connection to Excel | Excel Programming |