Daily Access Tips

I have programmed in Microsoft Access for over 14 years and have found the following items most important in creating the best possible database design. These were discovered to be the main items that caused slow performance, errors, user difficulties, and data corruption. Violation of any of these guidelines will result in some minor or major problems. Apply uniform design layouts for forms. This will help with the learning curve for employees new to the application.

To increase performance of form and drop down list loading, use stored queries for your record sources because they have already been optimized by Access. Note: the Access form wizard will create Select SQL statements for record sources, and these are not optimum.

Use validation rules to help prevent data entry errors by restricting what information can be entered in a field. Use a message box or onscreen area to let the user know what is needed. Using the field’s tag property is an easy way to encode the type(s) of validation each field needs, one can then loop through the fields collection to do the validations. Index the fields that you will need for criteria selection in your queries and fields used in queries that join the tables, to increase performance speed.

Optimize the data types to use the least amount of memory and storage and increase processing speed.

Always put code error handling in your routines so you have control of what logic to apply. A MDE file will stop executing without telling you or your user anything useful about the error. Don’t use macros (that do not allow error trapping) except for custom menus for the same reason.

Allow users only a copy of the front-end access program file, which contains everything but the data, never using the original front-end program file. This prevents the possibility of unforeseen corruption. Always split the Access application between a front-end and a back-end (Data). Place the back-end data MDB on the root of the network share, for quicker reads and writes. Compact data back-end on a regular basis. This will check to see if any table indexing is corrupted also if any data records are corrupt. This practice also reduces the file size.

Assign one person, and in his absence a second, to back up the data and store it off site on a regular basis without overwriting the last back up. How often you back up is determined by how much data are you willing to re-enter and how much time might have gone by until the loss is detected.

When compacting, make sure your subversion of Access is up to date so there is no reissuing of previously issued auto numbers, and that you make a back-up copy of the original MDB. Do not use Memo data type fields over a network. They have different indexing that is much more susceptible to corrupting during a write-back to the server. In 14 years, I have only seen record level table corruption of Memo data type fields. Use a main menu form or some other non-data entry form that is always kept open and is linked to a small application table. This will stop the repeated opening and closing of the LDB (record locking file) when each form is opened then close before opening another form. This will prevent a slowdown over a network connection.

Use auto number field for linking tables to dependent children tables. They are difficult for users to modify and offer better indexing performance.

Use lookup dropdown lists for text fields, which store the long integer auto number of the lookup table. If historical data is needed in a lookup table whose text is likely to change, ensure that the text value in the lookup table is also stored in the main table along with the long integer key of the lookup table.

Instead of using a multi-field key that prevents duplicate records, use code to check for a potential duplicate before allowing the record creation to continue. If automating other parts of Microsoft Office, use late binding so you do not have to worry about with what version of other Office software, such as Word, Excel or Outlook, is being used on the individual desktop machines.

Use the same name in both parent and child table, when using an auto number field from the master table to the corresponding long integer field in the dependent child table to add in maintenance and understanding of the relationships. Don’t use record sets in a multi-user environment unless the record set is read only snapshot and there are no updates that could conflict with the concurrency of other users. Solution, wrap the updates in a transaction which upon failure notifies of write conflicts and asked the user to get other users off before retrying.

Here are just a few more important points. Always use Option Explicit in VBA code to ensure no undeclared variables. Keep your Jet 4.0 Engine version up to date. On large record set updates, use record set operations with BeginTrans and CommitTrans to speed up record set updates by temporarily storing them in a memory buffer first. Finally, avoid right joins in queries if at all possible for performance unless program or business logic requires it.

Robert Heifler, Microsoft Access Programmer/Database Developer of 14 years – Email:info@MyAccessProgram.com – Please visit http://www.MyAccessProgram.com for free video tutorials.