Multiple Users with Access Database

[Zempten]

Hello

I'm currently exploring the use of an access database for a shipping company that constantly adds appointments and tracks changes to appointments across multiple warehouses. At first, we tried to use excel's share worksheet feature but that was quite the disaster because appointments or changes to appointments were lost. I've read up on splitting the database into a front end (that is put locally on each individual's desktop) and a back end that is placed onto the network.

My main concern is how long does it take to update the data from the front end to the back end? If I had say UserA add an appointment and UserB add an appointment at the same time. How long would I have to wait to see those 2 appointments being made? It is a concern for us because we want to avoid triple booking appointments in the same time slot. So if UserA's appointment is already the 3rd appointment in a time slot I do not want to have UserB's appointment being made.

Thanks for any advice/tips you can share and thank you for your time

 

[Joe4]

Welcome to the Board!

As long as you are all on the same network and have good connection speed, the updates are pretty much instantaneous.

 

[Zempten]

That's great news! Thanks! I also have one more concern if you don't mind addressing.

It's my understanding that using queries to pull information from your tables can end up slowing down with the more fields you have. So originally I had a 15 field table that I broke down into a 7 field (Table A) and a 8 field table(Table B). Table A stores the appointment/customer information and Table B stores information that our warehouse does to prepare for the order. I'm using the order number as the primary key so that I can setup a relationship between those two tables. However, when I add records to Table A the order number is not entered into Table B. Do I have to manually enter the order number into Table B? I don't think that's the solution since I thought relationship between tables was to eliminate repetitive data entry. Or would it be a better approach to extract the order number from Table A into Table B through a query? or VBA coding?

Thanks again for your expertise!

 

[Joe4]

Adding information to Table A will not automatically update Table B.
If you join Table A and Table B together in a query, and use that query as the control source of your entry Form, then adding a record there will probably update both tables.

By the way, in designing tables, the number of fields should NOT be the determining factor in how you design your tables. You should use Rules of Database Normalization when designing your tables (see: Description of the database normalization basics). And 15 fields is not that many for a table!

If your tables are designed properly, you will not have many records repeating the same information, nor will you have a lot of blank fields.

 

[Zempten]

Thank you very much Joe4. I have looked at the normalization rules and some examples I could find through a google search. I'm still a bit unconfident if I'm splitting my fields properly due to primary key and composite keys dependency. I had no idea I could update a query that joined tables to update each table seperately! Thank you very much! This is going to be very helpful for creating a order status history table I think.