multiple .mdb files or single .mdb ?

[mich h]

Hi all.
Can't find the appropriate topic to post my questions, so
I think .gettingstarted is as
good as any...
Client wanted to develop a database system using access
(.mdb files), and accessed using
vb using ado db controls.

1. Considering the client will access the database around
4000 times/day (querying/modifying), is it better to have
multiple .mdb files with small number of tables on each
of .mdb files, or is it better to have a single .mdb files
with all the tables inside ? or does it really matter?
2. If it is better to have multiple .mdb files, what is
the ideal number of tables per file?
3. It should be possible to lock a certain table so that a
second person accessing the table can't write into that
table until the first person (who locked the table)
finished updating it and unlocked it. How?

Cheers,
mich

 

[John Vinson]

1. Considering the client will access the database around
4000 times/day (querying/modifying), is it better to have
multiple .mdb files with small number of tables on each
of .mdb files, or is it better to have a single .mdb files
with all the tables inside ? or does it really matter?

4000 /day isn't all that busy: 4000/hr begins to get snarky. The
disadvantage of multiple .mdb files is that it is impossible to
maintain relational integrity across multiple databases using the
builtin Access RI; you'll need some (complex and difficult to manage
code) to do so.

That said you MUST!!! split the database: the Tables should all be in
a single database on the server; each user should have a "frontend"
containing the user interface and links to the tables. The backend
must be regularly backed up and compacted, probably daily at this
level of usage, at a time when there are no users attached to it.

2. If it is better to have multiple .mdb files, what is
the ideal number of tables per file?

As many as are needed to implement the data model for your business
need. This might be one, it might be hundreds. To quote Einstein in a
somewhat similar context: "A theory should be as simple as possible -
but NO SIMPLER!"

3. It should be possible to lock a certain table so that a
second person accessing the table can't write into that
table until the first person (who locked the table)
finished updating it and unlocked it. How?

You'll need code to do this reliably - the first user will need to
open an exclusive recordset based on the table (using either DAO or
ADO code, whichever you're going to be using) and the second user will
need to have code that traps the "opened exclusively by another user"
error.