The two tables below list the hints which follow in a loose order of priority. The ratings given are somewhat arbitrary and are an attempt to answer the question "which tip should I look at applying first." Because of the differences between ODBC and .MDB access, sometimes a rating of "1" for MDB won't be as appropriate for ODBC. Reading the detailed descriptions in the next section should help.
The first table contains tips that apply to Microsoft Access data formats and which generally apply to other ISAM formats such as Paradox and Xbase. If you never use ODBC data formats, then this is for you.
Tips for Microsoft Access data formats
Rating | Tip# | Title |
|---|---|---|
1 |
1 |
Replace Find with Seek |
1 |
2 |
Use table-type Recordsets instead of dynaset or snapshot-types |
1 |
40 |
Replace use of .Sort with specifying an index (or a query) |
1 |
26 |
Replace use of .Filter with Seek (or a query) |
1 |
20 |
Use the DB_APPENDONLY flag if you're adding records to dynasets, esp ODBC |
1 |
12 |
Always wrap transactions around your DAO code |
2 |
13 |
Return to your location using bookmarks instead of using Find |
2 |
27 |
Re-query recordsets instead of reopening them |
2 |
43 |
Avoid refreshing collections unless you absolutely have to |
2 |
14 |
In inner loops, use Field objects instead of myRS!fieldname (Q112724) |
2 |
23 |
For snapshots, select just the fields you need instead of all fields |
2 |
7 |
Store queries instead of using dynamic SQL text - esp low memory machines |
2 |
3 |
Open attached tables directly by opening the database directly |
2 |
42 |
Add the DB_DENYWRITE flag if no one else will be writing |
3 |
11 |
Replace DAO code loops with the equivalent SQL statements - but not always |
3 |
17 |
Cache tabledef and field property collection references if used many times |
3 |
8 |
Parameterize queries instead of using dynamic SQL text (especially for ODBC) |
3 |
22 |
Replace short memo fields with long text fields |
3 |
15 |
Store infrequently updated tables in the local MDB |
3 |
32 |
'Posting' queries are faster than explicit code |
3 |
25 |
Try InStr() instead of Like if you're not concerned with foreign accent chars |
3 |
28 |
Dim objects and Set them rather than using lengthy references |
3 |
41 |
Replace floating point numbers with integral numbers |
3 |
36 |
Compile modules and queries before distributing your application |
3 |
35 |
Clone dynaset recordsets instead of opening new ones |
3 |
31 |
Replace old ListTables (etc.) code with collection based code |
3 |
24 |
Split .mdb files that have lots of tables to minimize DAO collection initialization time |
4 |
9 |
Open databases exclusive and read-only if for fastest single-user network performance |
4 |
33 |
Refresh current field values with Move 0 instead of MoveNext/Previous |
4 |
18 |
Speed finds by creating a temporary indexed table and seeking |
4 |
19 |
Replace repeated execution of dynamic SQL with a temporary query |
4 |
34 |
Abandon an Edit or AddNew with Move 0 |
5 |
21 |
Marginal: replace variants with specific data types |
5 |
30 |
Take advantage of default collections and drop the extra name |
5 |
29 |
Replace text collection lookup with ordinals (but field object is better) |
5 |
39 |
Replace Variant string functions with '$' functions if using strings |
The second table contains tips that apply to ODBC databases. Since many tips apply to both data formats, there is quite a bit of overlap between the tables. If you are trying to speed up access to ODBC data in particular, this table will probably be of more interest.
Tips for ODBC data formats
Rating | Tip# | Title |
|---|---|---|
1 |
12 |
Always wrap transactions around your DAO code |
1 |
40 |
Replace use of .Sort with specifying an index (or a query) |
1 |
6 |
With ODBC dynasets, use CacheStart, CacheFill and CacheSize functionality |
1 |
38 |
On ODBC data use find only on indexed fields, otherwise open a new cursor |
1 |
26 |
Replace use of .Filter with Seek (or a query) |
1 |
10 |
With ODBC SQL statements, use pass-through where possible |
1 |
20 |
Use the DB_APPENDONLY flag if you're adding records to dynasets, esp ODBC |
2 |
43 |
Avoid refreshing collections unless you absolutely have to |
2 |
23 |
For snapshots, select just the fields you need instead of all fields |
2 |
27 |
Re-query recordsets instead of reopening them |
2 |
4 |
Use snapshots instead of dynasets for remote data (but beware of memos!) |
2 |
13 |
Return to your location using bookmarks instead of using Find |
2 |
7 |
Store queries instead of using dynamic SQL text - esp low memory machines |
2 |
37 |
For ODBC data with OLE or memo fields use dynasets instead of snapshots |
2 |
5 |
Supply the DB_FORWARDONLY option on ODBC snapshots |
2 |
14 |
In inner loops, use Field objects instead of myRS!fieldname (Q112724) |
3 |
11 |
Replace DAO code loops with the equivalent SQL statements - but not always |
3 |
15 |
Store infrequently updated tables in the local MDB |
3 |
17 |
Cache tabledef and field property collection references if used many times |
3 |
8 |
Parameterize queries instead of using dynamic SQL text (especially for ODBC) |
3 |
32 |
'Posting' queries are faster than explicit code |
3 |
41 |
Replace floating point numbers with integral numbers |
3 |
36 |
Compile modules and queries before distributing your application |
3 |
28 |
Dim objects and Set them rather than using lengthy references |
3 |
35 |
Clone dynaset recordsets instead of opening new ones |
3 |
22 |
Replace short memo fields with long text fields |
3 |
31 |
Replace old ListTables (etc.) code with collection based code |
4 |
33 |
Refresh current field values with Move 0 instead of MoveNext/Previous |
4 |
16 |
Reduce your ODBC time-outs to get faster return to DAO |
4 |
18 |
Speed finds by creating a temporary indexed table and seeking |
4 |
19 |
Replace repeated execution of dynamic SQL with a temporary query |
4 |
34 |
Abandon an Edit or AddNew with Move 0 |
5 |
30 |
Take advantage of default collections and drop the extra name |
5 |
29 |
Replace text collection lookup with ordinals (but Field object is better) |
5 |
21 |
Marginal: replace variants with specific data types |
5 |
39 |
Replace Variant string functions with '$' functions if using strings |