Split / Multiple Users

[jas580]

I have three mdb files:

1. Data- where data is uploaded, stored, cleaned, etc.
2. MasterIDs- where I enter identifying information about the data
3. UserForm- the user environment with forms, charts, etc. with tables
linked from 'Data' and 'MasterIDs' (users don't enter data other than
selecting drop-downs)

I'm now ready to set the db up for multiple users and I'm not sure how to go
about this. Does splitting just link to tables that have been moved to a
back end db? Is that basically what I've already done with the dbs mentioned
above or is there more to it?

Thanks!

-jeff

 

[Smartin]

I have three mdb files:

1. Data- where data is uploaded, stored, cleaned, etc.
2. MasterIDs- where I enter identifying information about the data
3. UserForm- the user environment with forms, charts, etc. with tables
linked from 'Data' and 'MasterIDs' (users don't enter data other than
selecting drop-downs)

I'm now ready to set the db up for multiple users and I'm not sure how to go
about this. Does splitting just link to tables that have been moved to a
back end db? Is that basically what I've already done with the dbs mentioned
above or is there more to it?

Thanks!

-jeff

Hi Jeff,

I don't use the splitter myself but it looks like you've got the right
design. All the data is in the back end (BE) (in your case, it appears
to be two back ends) and the UI is in the front end (FE).

When you roll this out you will have one copy of the production BE
files in some location all the users can access, and each user should
have their own copy of the FE. Don't let users open the FE from a
common location.

 

[John W. Vinson]

I have three mdb files:

1. Data- where data is uploaded, stored, cleaned, etc.
2. MasterIDs- where I enter identifying information about the data
3. UserForm- the user environment with forms, charts, etc. with tables
linked from 'Data' and 'MasterIDs' (users don't enter data other than
selecting drop-downs)

I'm now ready to set the db up for multiple users and I'm not sure how to go
about this. Does splitting just link to tables that have been moved to a
back end db? Is that basically what I've already done with the dbs mentioned
above or is there more to it?

Thanks!

-jeff

The only downside I can see to this is that you cannot enforce referential
integrity between tables in different databases; in particular, your Data
tables cannot have any relationships defined with your MasterID tables (which
contain data about those very tables). You will have to programmatically
ensure that you don't have data anomalies such as MasterID records with no
corresponding Data records or vice versa (whichever would be worse for your
comfort); Access will not and cannot enforce these relationships for you, as
it will if the tables share the same .mdb file.

John W. Vinson [MVP]

 

[jas580]

Glad to hear I don't have to change anything! But, now I'm wondering-will I
be able to add/delete/alter records in the BE while users are accessing
records in those tables? And if that is possible, how can users see new
records? Do they have to close/reopen the FE or can they just refresh
relevant queries?

thanks for the help!

-jeff

 

[jas580]

Currently, I run the raw data through append queries that check its existence
in the MasterID db before putting it into final tables. Seems to be working
ok so far, but it does bring up another question: If I combine the two, will
it improve performance for users? The FE has many linked tables to both.

thanks!

-jeff

 

[John W. Vinson]

Currently, I run the raw data through append queries that check its existence
in the MasterID db before putting it into final tables. Seems to be working
ok so far, but it does bring up another question: If I combine the two, will
it improve performance for users? The FE has many linked tables to both.

Only experiment will tell. If the "data" table is in fact just a pass-through
temporary repository, and you won't need to go back in the future and look at
the "raw" data, you can make a very good case for continuing as you are. It's
just more work for you as a developer; you need to decide if it's work that's
worth the effort.

John W. Vinson [MVP]