Making Access Backup Friendly
This month's theme is Backups, but Access does not have its own true
back up functionality so I will focus on making your Access database
backup friendly. This article will probably be somewhat shorter than you
may be used to here, but I hope you will get something out of it.
The nicest thing about Access is that everything is stored in one
file for one database. As far as making your database backup friendly
this is a huge time saving feature. Most older database programs stored
every object as its own file. Since Access does half the work for you in
keeping all the files together, you can make it even easier for the
Backup operator.
Divide and Conquer!
One of the best changes you can make in your database is to split the
database into front- and back-end databases. You will have two databases
to deal with but this has several advantages. First, the front end will
remain static, unless you make changes to the structure itself that is.
This means that backups of this portion of the database need to take
place much less frequently. All you really need to back up the front end
is a simple copy after each update to the structure of the database. The
back end is where all of your data is stored and this will obviously
change much more frequently but since it does not have all of the
front-end material it is much smaller and faster to back up.
There are additional perks to dividing your database. You decrease
network traffic if the database is on the network. Only the raw data
must go across the network rather than all of the support files. You can
place a copy on the front end on the users computer and link them to the
network-stored back end. In addition to the reduced network traffic, you
also gain the ability to have multiple users access the database at the
same time. Still another advantage gained by using this method is that
you can allow users with varying levels of file permissions to access
the database at the same time. Basically, you can set the file
permissions on the back end through NTFS security to bolster any
security you set up within Access.
Compact? What the heck do I need to powder my Nose for?
If your data changes frequently in the back-end database, it is an
excellent idea to compact and repair your database on a regular basis.
Clicking on Tools, Database Utilities, Compact and Repair Database does
this. A little lesson in the way Access handles changes in the database
is probably helpful here to show you why this is important. When you
make a change in anything in your Access database, Access keeps track of
it. You may not have access to that information but it is there.
Therefore, even when you delete data or make minute changes to a form,
these add up fairly quickly. When you compact and repair and database,
it cleans out this cache of changes. Be careful with this though, it
also resets Auto number fields. It does not reset it to zero unless you
have no records. What it does is reset it so that the last number
actually used is the last number it recognizes. Therefore if you added
fifty records, deleted them, then ran compact and repair, it would reset
the auto number field to where it was before you added the records. If
you have been working on your database making frequent changes. Note the
file size of your database and then run compact and repair and check it
again. You should see a fairly drastic reduction.
A New Database? What is Wrong with the Old One?
This may sound like strange advice, but it does work. When you have
completed work on your database and you are about to deploy it, create a
new blank database and import all the objects from your newly finished
database. As mentioned above Access tends to hang on to information even
if it is no longer needed. While compact and repair do a lot to
alleviate this, there is still some information it doesn't get rid of.
A
Bonus Tip - This also tends to work when a database stops
functioning as expected. Sometimes it just starts to get a little
corruption and this process eliminates that!
Start Smart
Of all the tricks and tips to improve your database to make it more
backup friendly, this one will prove most valuable. Plan your database
before you even touch the keyboard. By creating relationships to move
redundant data out of your tables and creating lookup tables so that
smaller units of data are stored to provide you with full data, you will
keep the size of the database down. This will thrill the person backing
up your data. When data does change it will require that much fewer
changes be required to bring your data current. As has been stated in
this column on several occasions, the key to a good database is proper
planning. This is another case where this advice comes into play.
Final Thoughts
While directly backing up your database from within Access is a bit
overkill, making it backup friendly is not a difficult task. All the
advice here also makes your database much faster and easier for the end
user as well as making it easier on the backup person. I hope you have
enjoyed this brief trek into making your database backup friendly.
As always, I am open to ideas for future articles. I am still
planning on an article on Union Queries in the near future and I would
love to do an article on something suggested by one of the readers.
Thanks for reading and enjoy the rest of the issue!
James La Borde works
in the computer department at a Credit Union,
where he uses Access, SQL Server, VBA, and ODBC daily. He also
teaches online Access classes at
Eclectic
Academy.
,
