00319: How to speed up queries in Access via the BASIS ODBC Driver

Title:

How to speed up queries in Access via the BASIS ODBC Driver

Description:

Sometimes MS Access seems very slow when querying or scrolling/browsing through a linked table. In short, there are three ways to improve this: 

1) Use SQLPassThrough queries 
2) Decrease Access’ refresh time 
3) Use indexed fields in your queries 

Resolution:

To use SQLPassThrough queries in Access 95: 

1) Click the Queries tab 
2) Select New then OK to the dialog that appears. At this point you will notice an additional menu item called Query. 
3) Close down any of the windows that appear except for the “Query 1: Select Query” window
4) Select “Query / SQL Specific / Pass-through” from the menu. This turns on the SQL pass-through. 
5) Select “View / Properties” or click the “Properties” toolbar button. 
6) Click on the “ODBC Connect Str” field. This will cause a ‘…’ button to appear on the right. By clicking on it, you can select an ODBC datasource as defined in your 32-bit ODBC administrator. 
7) Select the datasource in question and wait for the connection to be made. A dialog may appear asking to save the password string. It’s probably reasonable to just click “No” if you are not connecting to a dataserver, otherwise go ahead and click “Yes.” 

8) Now you can type an SQL statement in the Query 1 window like: “select * from TABLE” 
9) Select “Query / Run” from the menu, and voila! You now have the equivalent table for browsing, but you will note that the scrolling is much smoother and faster. 

Access also has configurable refresh times for ODBC access. By default, these refresh times are set to update the screen very infrequently. By decreasing this time, queries and scrolling through a table will be much quicker as Access will update the screen more often. 

For more information, refer to additional article SQL Optimization.

Last Modified: 01/29/1998 Product: BASIS ODBC Driver Operating System: Windows

BASIS structures five components of their technology into the BBx Generations.