Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel / Access ODBC connection
"Rasmus" wrote in message ... Hopefully this is the right group to ask as this is mostly an Excel question more than Access: I have an Access database which is now a total of app. 100 MB (big!). I'm connection to it in Excel using the following code: Sub OpenAccessConn() Set cnnConn = New ADODB.Connection With cnnConn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Open "Z:\MyDatabaseBackend.mdb" ' Network drive on other machine End With End Sub But over my 100mbit network it takes FOREVER especially if I'm making 100s of requests (where the above code is run for each request) to the database from my Excel sheet. Is there a faster ODBC driver available or can I set some 'flags' to speed up the connection ? I always compact and repair the database regularly. Rasmus I don't think the connection is the problem. Why don't you keep the connection open until the requests have been done? What does your SQL stataments look like? /Fredrik |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel / Access ODBC connection
Hopefully this is the right group to ask as this is mostly an Excel question
more than Access: I have an Access database which is now a total of app. 100 MB (big!). I'm connection to it in Excel using the following code: Sub OpenAccessConn() Set cnnConn = New ADODB.Connection With cnnConn .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0" .Open "Z:\MyDatabaseBackend.mdb" ' Network drive on other machine End With End Sub But over my 100mbit network it takes FOREVER especially if I'm making 100s of requests (where the above code is run for each request) to the database from my Excel sheet. Is there a faster ODBC driver available or can I set some 'flags' to speed up the connection ? I always compact and repair the database regularly. Rasmus |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel / Access ODBC connection
"Fredrik Wahlgren" wrote in message
... I don't think the connection is the problem. Why don't you keep the connection open until the requests have been done? What does your SQL stataments look like? /Fredrik D'oh! Obviously there was too many "set cnnconn = nothing" in there! Thanks! Rasmus |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ODBC connection from Excel 2008 on Mac OS X | Excel Discussion (Misc queries) | |||
Excel connection to ODBC database | Excel Discussion (Misc queries) | |||
How do I set up my ODBC Connection from AS/400 file in Excel 2003 | Excel Discussion (Misc queries) | |||
ODBC Connection | Excel Programming | |||
ODBC connection by udl | Excel Programming |