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

"Rasmus" <[email protected]> wrote in message
news:[email protected]...
> 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

"Fredrik Wahlgren" <[email protected]> wrote in message
news:%[email protected]...

> 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