Ideas on different office implementations

[Scott Viney]

G´day All,

I have a database thats used in one office at the moment. They want to use
it in several other offices in the future. Same company different
locations. It already has a table with one record for listing the office
details IE address,phone, fax etc for the first offiice. There is also one
report that uses these office details for the report header.

What I want to know is. Whats the best or simplest way to implement
multiple offices? I can easily add the details of the offices to the office
table, but I dont know how to set it up so that if the database is in
office2 it will use their details for the headings.

Any help would be appreciated,
Scott V

 

[MacDermott]

Before we get into your question, some other ones need to be asked:
Will each office have its own data, and not worry about other offices' data?
Or do the offices need to see each others' data?
If they need to see each others' data, are they networked?
If they're networked, are they in the same building? (LAN) or at a
distance from each other (WAN)? Or are they connected by something like
Terminal Server or Citrix?
The answers to these questions are crucial to your next steps...

 

[Scott Viney]

G'day MacDermott,

Thanks for your reply, my answers to your questions are below. Bit of
background. This is a simple database to create a quotation for different
customers. Iam really learning on the run, as I did this as a bit of fun to
help out my partners business. They do them by hand (excel) and everyone
has a different format it looks terrible.

Before we get into your question, some other ones need to be asked:
Will each office have its own data, and not worry about other offices'
data?

Yes I imagine every office will have some of its own data for IE clients,
employees
but also there are some tables that I would like to keep the same for each
office IE products, product brand, product presentation.

Or do the offices need to see each others' data?

At this moment I dont think each office needs to see the others particular
data, only the data that is common to all, IE relating to the products.

If they need to see each others' data, are they networked?

In the main office yes computers are networked, I have split the database
here and put the FE on each computer, the BE is on a server. The other
offices are in other locations around the country and the other offices have
dialup internet connections

 

[MacDermott]

Let's look at the tables which should be the same in every office -
(products, product brand, product presentation)
How often do these get updated?
Do the outlying offices need to be able to update them, and have this
integrated into "everybody's" data?
Or can the central office just send out updates?

 

[Scott Viney]

Morning MacDermott,

Here are the tables I have and what I think:

Tables Same Data Every Office
--------------------------------
tblBrand (Product Brand Names)
tblPresentations (Product Presentations)
tblProducts (Products)
--------------------------------
Probably updated reguarly with new brands, products, and presentations. I
would rather have the control of updating these as ppl updating things in
other offices willy nilly with no idea what they are doing will definitely
cause chaos. And then yes this new data has to be integretated into every
other office.
--------------------------------

Tables Varying Data Each Office
---------------------------------
tblCustomer (Customer Details)
tblEmployee (Employee Details)
tblQuotationDetails (Quote Details)
tblQuotations (Quotes)

Tables Data More or Less Static (Same Every Office)

 

[MacDermott]

You might consider using 2 or 3 separate "back end" files.
One would be for your "local" tables (Tables Varying Data Each Office); it
would stay in the local office and be maintained there.
A second one (Tables Same Data Every Office) would be maintained in the main
office; when there are changes, that office could send out a new mdb file
with the same name as the old one; the local office would simply copy this
over the old file.
Your third group (Tables Data More or Less Static) could be a separate mdb
file, or it could be combined with the second one.

Does this make sense?

 

[Scott Viney]

MacDermott,

That all makes sense, and seems like the simplest and cleanist solution.

As far as separating the BE files is it just a matter of saving the existing
BE file to other files and deleting the tables not required in each new BE
file. Will there be any problems with the relationships between the tables?

How can I have a blank BE for say the customers? Do I just create a new
database file and import the table without data?

Only other thing is how to implement which office the database is in? IE
for the report. Should I create another table that just holds the current
office name and is created the first time the database is started? Not sure
how to do this. Or do you have a better solution?

Once again, thankyou for you time and info,
Scott V

 

[MacDermott]

Answers interspersed

MacDermott,

That all makes sense, and seems like the simplest and cleanist solution.

As far as separating the BE files is it just a matter of saving the existing
BE file to other files and deleting the tables not required in each new BE
file. Will there be any problems with the relationships between the

tables?

That should work fine. You can't have relationships between tables in
separate databases - or at least you can't enforce referential integrity.

How can I have a blank BE for say the customers? Do I just create a new
database file and import the table without data?

Yes, that should work fine. When you import tables, be sure to click the
Options button and specify that you want to include relationships.

Only other thing is how to implement which office the database is in? IE
for the report. Should I create another table that just holds the current
office name and is created the first time the database is started? Not sure
how to do this. Or do you have a better solution?

One good way would be to have a table in your "local" back-end.
You could write code which runs from an autoexec macro on startup; it would
check for data in that table, and prompt the user if there isn't any...