Access and ODBC

[M_Mike]

I am having a problem with an access database that is connecting to a sql server. When trying to run any queries it is very slow. I think the problem might be caused by the ODBC link and I was wondering if there are any alternatives to using ODBC to connect access and sql.

 

[KenHigg]

How complex is the query and how many records are they hitting?

 

[M_Mike]

The queries link quite a few tables one of which contains approx 12,000,000 records.

 

[KenHigg]

Hum... My first guess is that it's just the query, not so much Access. I think I would look at the index thing first...

 

[The_Doc_Man]

Trust me. Short of moving the entire table to your own machine and running the query locally (which defeats the idea of sharing data...), putting the query on an SQL server is far better than many other alternatives. You might try two things:

1. Making the query a pre-stored query.

2. If more than one criterion is active, split the query so that it is in two parts - then make the innermost query the most restrictive one. Make all subsquent criteria work on the result of the most restrictive query.

 

[KenHigg]

Trust me. Short of moving the entire table to your own machine and running the query locally (which defeats the idea of sharing data...), putting the query on an SQL server is far better than many other alternatives. You might try two things:

1. Making the query a pre-stored query.

2. If more than one criterion is active, split the query so that it is in two parts - then make the innermost query the most restrictive one. Make all subsquent criteria work on the result of the most restrictive query.

Would the same query execute faster if it was created in SQL Server as a view?