Common DB - Should I create multiple Databases or just two

[SK]

Greetings,

Just a general question on DB design(For Access 2000). I
have read a lot about the benefits of splitting a DB into
Fron-end and back-end and like that approach. However, I
want to know if I can do the same in my situation:

I am dealing with a number of databases here(10-15).
There are several tables(15 or so) which are common to
almost all of these databases. However, most of these
databases have specific tables(varying from 0 - 5 per
database), queries, Forms, reports, modules, etc..

So I have thought of creating a Common(or Central) DB
which will house all the common tables. At the same time
I will have other DBs which link to the central DB but
also have tables, Forms, queries,reports, etc. which are
pertaining to that database. Benefits of this are:

- Users will not have to maintain only one set of tables.
- I do not have a huge single DB
- N/w traffic will be less
- Maintenance will be easier.....

In this way, I will have a Central DB and x number of
other databases which link to this Central DB.

My Questions:

1. Is this the best design approach that will not
prohibit me from using other Access features?

2. I am using relationships in each DB but I think that I
cannot build a relationship between a linked table(i.e. a
table not present in current DB) and a table that is
present in current. Please advise if this can be done as
it will save me a lot of time where I am writing VBA code
to validate data before updating data in several tables.

3. How will I be able to get the benefit of Front-end and
back-end approach in my situation?

I have applied Workgroup security to these databases,
created modules(usng ADO) and done several other good
things. My aim is to make these databases work as multi-
user secured DBs.

Please advise.

Thanks in advance.

SK

 

[Tony Toews]

I am dealing with a number of databases here(10-15).
There are several tables(15 or so) which are common to
almost all of these databases. However, most of these
databases have specific tables(varying from 0 - 5 per
database), queries, Forms, reports, modules, etc..

So I have thought of creating a Common(or Central) DB
which will house all the common tables. At the same time
I will have other DBs which link to the central DB but
also have tables, Forms, queries,reports, etc. which are
pertaining to that database. Benefits of this are:

- Users will not have to maintain only one set of tables.
- I do not have a huge single DB
- N/w traffic will be less
- Maintenance will be easier.....

In this way, I will have a Central DB and x number of
other databases which link to this Central DB.

My Questions:

1. Is this the best design approach that will not
prohibit me from using other Access features?

Yes but I'd do things a bit differently. I'd put all the tables in
the common backend. Not just the common tables but all of them. The
only downside would be if you need to restore one or more of the
tables.

Once you've imported that particular MDBs tables into the shared MDB
you then delete the tables from the current MDB, link to the required
tables in the shared MDB and then distribute the new FE to those who
require it.

So this isn't something you have to do all at once. You could do a
different groups tables every few days or week or so.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the utillity see
the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

2. I am using relationships in each DB but I think that I
cannot build a relationship between a linked table(i.e. a
table not present in current DB) and a table that is
present in current. Please advise if this can be done as
it will save me a lot of time where I am writing VBA code
to validate data before updating data in several tables.

You can't have relational integrity outside an MDB.

3. How will I be able to get the benefit of Front-end and
back-end approach in my situation?

You could then give each set of users their own FE linked to just
their own tables.

I have applied Workgroup security to these databases,
created modules(usng ADO) and done several other good
things. My aim is to make these databases work as multi-
user secured DBs.

I know nothing about security so won't comment there.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Larry Linson]

. . . about security so won't comment there.

A good place to get security questions answered, however, is
microsoft.public.access.security.

I'd also suggest you reconsider using ADO with Jet databases. You can set
the reference to the DAO 3.6 Library in any module's window's Tools |
References (it's not set by default) and use DAO -- it is the native
language of Jet, is typically faster than ADO with Jet, and is more complete
than the combination of ADO and ADOX.

Larry Linson
Microsoft Access MVP