Marc said:
All 23 departments do have multiple databases. The rule is that all MDB's sit
on a server with a front ends for users needs. data is common to some extent
by Job #, Customer name & Info, Then each databse has it's own info that is
related to the department. The flow process is from Marketing > Sales >
Banking, Architecture,Contact Administration, Estimating, Permits,
Construction, Customer Service.
Database size runs from 12MB - 175MB
Preformance is always a issue.
User base is about 150 total each department right now has less then 15
people.
The reason for 4 DBA's it to keep up with present database change requests,
new forms, reports & fields.
This is an interesting and tough question. Thanks for posting it.
<smile>
Strongly consider using SQL Server to hold the data. It can easily
handle 150 users and has much more reliability. And your corp is
large enough that should be a reasonable expenditure. Actually, when
I think about it, that's a requirement for what you want to do and for
the number of users you want to support.
Then I'd suggest building a system in exactly the above work flow.
Grab the marketing database and convert the data to SQL Server and get
the FE Access MDB running smoothly. Now visit the Sales department
and add their tables to your system and their forms and reports and
get it running smoothly. And so forth.
You may decide to import the forms and reports or build them new.
That's a decision to be made on a case by case system. Hmm, no, these
weren't created by the users so they should be in decent shape. Yeah,
you'll want to import them.
Now there is obviously going to be common data. Conversion is going
to be ugly. Customer autonumber ID 5343 in this department database
is going to be autonumber ID 95494 is that departments database. And
how do you handle John Smith, 1234 Wintergreen Rd vs Jonathon Smith,
1234 Winter Green Road in the other database? Ugly, ugly, ugly.
That's going to take a bunch of manual work in de duping the data. I
posted my thoughts on this earlier today in the thread with the
subject "Looking for sophisticated De-Duping tool"
And you will need a conversion junction table in the main database to
handle the data conversion from the imported tables to the master
tables.
One thing you can do to make life easier on you is to create queries
in the master database which mimic currently existing tables. Say you
have a table called Customer in the master and the virtually identical
table called Cust. Customer has, following my naming conventions,
http://www.granite.ab.ca/access/tablefieldnaming.htm cID, cName,
cAddress1, cCity, cState and cZipCode fields. But Cust has fields
called CustID, CustName, Address, City, Zip. In the master database
create a query called Cust and rename all the fields accordingly.
OTOH you may just want to use Find & Replace to rename all the tables
and fields. I recently did this on a small database of about 5 tables
to get it into the proper naming conventions. I populated a table
with the table and field names and decided on the new table and field
names. I then fed that table to Find & Replace and it renamed all the
objects in the queries, forms, reports and code for me. This would
have the huge advantage that now all your imported objects would all
use the new standard names.
Another concept that could help out is to create a standard form or
three which you import into each departments database. You'd also
link to the master database. Get a user in each department de duping
the data and ensuring that every customer in the departmental database
exists in the master database. Then lock the customer entry form in
the department database and have the users enter all new customers in
the master database and copy down the updates into the departmental
database. Hmm, this may be more trouble than it's worth though.
Also ensure that you are not adding a lot of extra functionality at
this time.
Finally consider this. You have a big job on your hands here. And
you've asked the right people for advice. But it could be a lot
worse. Your departments could mostly be using spreadsheets. <smile>
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