Why would you use different files for different tables?

[Melvis]

Hi,

I have been given a project that was done by an intern who no longer works
at my company. In his database design, he put the master table in a seperate
file from the supporting tables and forms. The only thing in this other file
is the master table. He then linked the table into the database with the
forms and such.

I am wondering why you would do this. It seems to load very slowly, and
locks people out even when they are not in the same record. Anyone have any
ideas or suggestions for how to improve this?

~MATT

 

[Nikos Yannacopoulos]

Matt,

What you are describing sounds like a split database, with a Front End
(everything but the data tables) and a Back End (just the data tables),
which is a standard acrchitecture for a multi-user database. The idea is
that the back end is stored in a shared folder on a server, and each user
has their own copy of the front end (normally on their own local HDD). This
architecture allows for multi-user access while preventing corruptions
(which occur very frequently in a monolithic - a.k.a. all in one - database,
when opened by several users at the same time). Performace under such a
design can be greatly improved by implemeting a few tricks like the one
described in Tony Toews' article:

http://www.granite.ab.ca/access/performancefaq.htm

I suggest you take some time to browse through the multiuser newsgroup, it
will answer many of your questions.

HTH,
Nikos

 

[Melvis]

I can see why you would split a database for front-end/backend use, but he
put the two files in the same folder and everyone accesses the same file. All
the users have on their desktop is a shortcut to the file with the forms and
other tables. Does it still make any sense?

 

[Chris Nebinger]

That is not the standard architecture, but it is still
favorable over one big .mdb file.

If you have the data (tables) seperarted from the
application (queries, forms, reports, and code), then you
only need back up the application once, and then back up
the data frequently.

Also, you can work on a copy of the application, make the
necessary changes, and overwrite the application .mdb file
without fear of damaging the data.

Finally, you can also have different .mdb application
files, one for accounting, one for sales, etc. that
connect to the same data .mdb.


Chris Nebinger

 

[Nikos Yannacopoulos]

Melvis,

I strongly suggest you copy the front end to each user's PC, and put a
shortcut on their desktop to launch their local copy instead of the one on
the server. Much safer, and most likely quite faster. The downside is you
need to distribute a new copy if you make changes to the front end, but
there are solutions to automate the process if you foresee that happening
more than once in a blue moon. Here's a good one:

http://www.granite.ab.ca/access/autofe.htm

Also, you might want to check out the following, if you are experiencing
performance issues:

http://www.granite.ab.ca/access/performancefaq.htm

Tony Toews is the good man to thank for both.

HTH,
Nikos