Optimizing performance of linked tables 1

[storer]

In Access help files, I found the following regarding improving performance of linked tables:
"Create an empty table in the linked database and link the table in the main database." I'm not clear on which is the "main database," the back end or front end? I'm assuming it's the back end, but......

It goes on the say: "Use the OpenRecordset method to open the linked table." Where do I use this method, what "event"? As an On Open event for the opening form? I wasn't sucessful in finding a good example(or at least one I could understand) of how to write the code. Any help would be greatly appreciated!

 

[3587]

The main database is the front end. Create your table in the back end and link it to the front end (Main) database. By selecting File, Get External Data, Link Tables. Point to the back end and select the table(s) you want to link. When the link is established you should see a right arrow next to the table name in the front end (Main). Now you can use the table as if it were part of the front end (Main) database.

 

[RickSpr]

The help file topic you're referring to is primarily aimed at increasing performance of non-Jet linked tables. Since you refer to the back end/front end, I conclude that your application is a split Access database. I'm not sure your performance will increase significantly using this technique.

If you want to try, though, here is what the help file is talking about. Create a dummy table in the back end and link it in the front end. When your application starts up, you want to open a recordset on that linked table, and leave it open until your application exits. One way to do this would be to create a Recordset variable in the Declarations section of a standard module, and use it to open the dummy table in your application's startup code or in the Open event of your first form. Be sure you then close it (and set the variable to Nothing) before exiting your application, probably in the Close event of your last form.

If you have a form which is open (it doesn't have to be visible) whenever your application is running, put the recordset variable in the Declarations section of that form's module. Open the dummy table in the Open event of that form, and close it in the Close event.

Most often, though, the best way to improve performance in a Jet database is by creating indexes. You would do this in the back end database. For each table that's slow (or that's in a slow query), think about the sequence you retrieve records in. If you often retrieve records in a sequence other than the primary key, you should create an index over the fields that define that sequence. You can create multiple indexes, one for each sequence you use heavily.

If possible, the record sequence you use most often should match your primary key. When you compact the database, Jet reorders the records in this sequence so they are physically next to each other. That minimizes disk head movement and saves time.

If you have a complex query involving joins on many tables, breaking it up into subqueries with fewer tables can help. Decide which join does the most to limit the number of records returned. Then move those two tables to a separate query, and use that query to replace to the two tables in your original query. By doing this, you can minimize the number of rows Access reads from the other tables, only to throw them away again when it tries to join them to the tables that do the most limiting.

Speaking of joins, any fields you use in a join should be indexed for maximum performance. Rick Sprague