Application Databases & Query Databases

[vbasean]

In my short exposure to Access databases I noticed something that is somewhat
troubling.

In my opinion you can look at an Access database in two frames of referrence:
1) As an application
2) As a area for obtaining information from data

What normally happens is people use the application database for both. So
what's the problem? You get an application database that is cluttered with
queries and temporary tables. The major problem with this is that no one can
tell what the dependent tables and queries are and what the temporary ones
are.

A small suggestion to the Beginner Access community: if you plan to use your
database for more than just an application (which is basically the case 99%
of the time for people who are not developers) create 2 databases. Create
one solely for the application with only the tables, queries, modules, macros
that you need to run the application. Create another (with linked tables)
for your querying and research. This way, whenever someone else comes in
contact with your database (and if it's of any value, someone eventually
will.) They will not be confused with all the excess un-dependant stuff that
goes along with daily activity.

Thanks,
~Your Friend Chris

 

[Larry Linson]

You don't have to search very deeply into the archives of this and similar
newsgroups to see the advice repeated, over and over, from experienced and
capable users and developers of Access: split your database. Make a copy and
remove all the queries, forms, reports, macros, and modules to use as your
"back end" data store. Make another copy and link the tables in the back
end, then delete the tables from the front end (and change the name of the
links to match the tables you just deleted).

Note that this goes even farther than vbasean advises. It leaves no data
tables in the front end but places them all in the back end in a shared
folder (an exception would be lookup data for Combo Boxes, etc., things that
never, or nearly never, change). Then if you need (and it can usually be
avoided) to use temporary tables, you can visit MVP Tony Toews' site and
find the suggestions for creating a temporary database to hold your
temporary tables. Then, when you are done with them, you can delete the
links, and then delete the entire temporary database -- which will avoid a
good many "database bloating" problems.

I commend vbasean for his discoveries, and think he'll be inordinately
well-pleased when he goes the step farther. (But among readers of these
newsgroups, I think he'll find far fewer users of monolithic Access DBs that
he assumes from some of the questions asked here.

Larry Linson
Microsoft Office Access MVP

Larry Linson
Microsoft Office Access MVP