Multiple MDB files???

[brba]

I have a databse with an Access front-end on everyone's individual PC sharing
an Access back-end on the server. The tables in the back-end data file are
linked into the front-end. As we increase the functions of the database, and
thusly the number of tables (currently my question is whether or not it makes
more sense to create multiple back-end data files or if just one would be OK.
RIght now, my back-end is 32MB in size...not large, but getting larger every
day. I'm leaning towards splitting the back-end into about 5 different files
and repointing the links.

Any comments?

 

[Scott N Weber, MCP]

It is definately better because it doesn't have all of your eggs in one
basket. Furthermore when you update information in a single table, you are
updating a smaller file altogether. However you may also encounter a
reduction in speed when another user goes to open up the same file that is
already open by another user. To work around this just open up a table in
each mdb when the program loads.
For more information on that check out Tony's page at
http://www.granite.ab.ca/access/performanceldblocking.htm

Good Luck, BTW we have 27 back end mdb files. Some still get very large.

 

[Allen Browne]

No need to split it up at all. A single back end will be fine.

32MB is quite small. Even in the hundreds of megabytes, a single back end
would be preferable and more efficient, e.g. there is only one set of
connections to check and maintain. It also simplifies maintenance and
backup, especially if being maintained by non-technical people (as Access
databases often are.)

 

[Craig Alexander Morrison]

All related tables MUST be kept in the same MDB if you are to enforce
referential integrity.

If you have some temporary tables that need to be accessed by all users then
consider putting them in separate MDBs but not the main tables of your
relational database they should be kept together in one database file with
RI enforced.

If the scale grows considerably, 32MB is small, consider using SQLS2000 or
DB2.

 

[Craig Alexander Morrison]

Why do you not enforce referential integrity?

If size is an issue use SQLS2000 or DB2 but NEVER have related tables in
different MDBs.

Your advice is absolutely wrong, if you care about the data. If you could
not give a damn about data consistency then your advice would certainly help
ensure the data could become inconsistent.

 

[Craig Alexander Morrison]

Not to mention RI!

Which is the most important reason by miles.