"Modulating" an access DB

[1]

I have designed a fairly big DB that consists of the following "groups":
Personnel, Products, Production and many other.
Each of these groups relates to the other.
My intention is to "split" the db so that each group can be added to a
baseline DB as a module. That means that someone should be able to buy the
personnel module and later, if he wants to, be able to purchase the products
module.
The reasons for doing this are easier distribution, easier licence control,
easier product updates, easier development and many other reasons.

The problems that arise are:
Supposingly I split the Back End to several, each consisting of a single
"module", and are all linked to the main BE db that holds the info for the
purchased modules.
1. RI can't be enforsed between linked tables, so how is the personnel table
in the products module (many-many, person responsible for a product and vice
versa) syncronized to the personnel table in the Personnel module?? (perhaps
a trigger needed?)
2. Using the package wizard of the ADE, if a user selects a different
directory to install the application to, how are the references to the
backend corrected???

Any resources are mostly welcome.

Thanx in advance.

 

[Douglas J Steele]

Your tables should all exist in a single back-end database, and all of the
relationships should exist in that database.

Your individual modules are referred to as front-end databases, linked to
that back-end database. Since the relationships are in the back-end, they
will automatically be enforced.

You have to include your own code to relink the front-end(s) to the back-end
if the back-end isn't where you expect it to be. See
http://www.mvps.org/access/tables/tbl0009.htm at "The Access Web" for one
approach.

 

[Jeff Boyce]

I've used the approach Douglas recommends, one comprehensive back-end, to
allow RI to be enforced. A "licensed feature" table in the front-end could
determine which features/functions were available/enabled.

It would be theoretically possible to do as you've suggested, placing
different components/modules/functions in different back-end, and "steering"
them all from a front-end.

To do this, you'd need to create the code in your front-end to handle all of
the RI functions that Access handles if you use a common back-end. Are you
up for the challenge? And I'm thinking you'd need to have your front-end be
comprehensive enough to manage whatever modules/function/components you'd be
adding. Are you looking to have one front-end to handle each different
permutation of components?!

 

[Haris Rashid]

hi,

You mentioned that the groups are related to each other. Given this
scenario, there will be a number of records that will be entered without
thier related records and moving forward as you add a module you start
getting related records. In this situation thers is no point of retaining
referential integrity.

If you need referential integrity you would have to go back and relate all
the records for the missing modules that get added on a later date.

In going for a modular approach you should either break the relation among
groups or introduce procedures to link the data of the existing modules to
the data of the modules that get added later.

It might not be logically possible to break the database into segments. If
the only reason to break is a big Db, you can consider transferrign the Db to
MS SQL.

 

[Jeff Boyce]

You mentioned a "fairly big" DB, but you and I may not have the same
impression of "big". One of the responders has mention migration to SQL
Server.

If your DB is too large for Access to handle well, or if you need to include
serious security, or if you want to add robust backup capabilities, or if
you have too many folks trying to simultaneously use the system, or ...

SQL Server is a great hammer, I use it often. I use it to drive nails,
though. Are you looking to drive nails?