Three Simple Access Database Administration Tips

You've mastered Microsoft Access: making tables, writing queries, creating user friendly forms, and developing reports that serve your business. But what you probably didn't learn in your training (or self taught book and internet reading), is that regularly performed administration tasks will make your life a lot easier.

Here are a few Microsoft Access tips to keep your database running smoothly:

1. Repair and Compact your database regularly, especially if you do lots of forms and report development. Compacting the database is just like when you run a defrag on your hard drive. It cleans up temporary files and decreases the overall size of your database.

To perform the Repair and Compact of your database, go to the Tools menu and select Database Utilities. You'll find the Repair and Compact command there.

Perform this function at least once a week to keep your database in tip top shape.

2. Make a back up copy of your database. Stuff happens - this is the CYA step. If you store your database on a public server, database files can sometimes mysteriously get moved or corrupted. Or maybe you accidentally deleted a bunch of records. Having a back up copy can be a real time saver if something goes wrong with your database.

It is a good idea to do this at the end of every work day if your users do lots of data entry each day. Save it to a different server or on a CD/DVD.

3. Make a development copy of your database. One of the major annoyances of Microsoft Access is that you can't make changes to forms or reports if there are other users in the database. Rather than keeping your users out of the database, and to prevent yourself from having to do your work at off hours, create a development copy of the database.

Use this copy of the database to create new reports and forms or to update existing ones. Then, when you have the object just as you like it, import it into the main copy of your database. You will have to be sure other users are out of the database when you import, but it will only take a few seconds.

To bring in your new form or report, go to File, Get External Data and then select Import. Locate the copy of your development database and then click on the Import button. You will get a pop up box with a list of the objects in that database. Highlight the names of any forms or reports you want to import and then click OK. If the form or report already exists in your database, it will be named with a number behind it rather than copying over the existing file. Just be sure to delete the old copy of the report/form and then rename the new version to take the number off the end of the name.

These three quick Microsoft Access administration tips can save you lots of time and frustration if used regularly. Both you and your users will be glad you took the time to do these simple yet important database tasks.