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

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

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