Linked Tables 1

[paulminne]

Hi,

I have 3 databases created in Access 2000. Database 1 (Contact Database) is used as a central store for all contact information. Database 2 (Service Database) is used by another department to keep records of all services, and database 3 (Compliance Database) is used by another department as a job tracking system.

The way they are currently setup, both the service and compliance databases have a linked table to the contact database.

Therefore, with the way both databases can access the one linked table at the same time is there going to be any corruption or errors which may arise?

I have taken into account the fact that if both databases are accessing the same record from the contact database at the same time, there will be problems.

If anyone else has anything to add, or other problems I may come across in future, I would be interested to hear there opinions.

Thanks.

- Paul

 

[Darrylles]

Hi,

By default, you should have no problems (not even if both db's are referencing the same record in the contacts db).

If 2 people reference the same record, one will change the record before the other and it'll be locked, the second user will get a message to 'try again later' if they attempt to update).

You can set your own 'record locking strategy'.
You can stop the 2nd user even 'opening' an already open record for instance (although this stops users from opening to simply 'view' the details).

If you want to know how, I can't tell you directly, but you can always post another question on "Setting 'record level strategy'", or look at Access help / MS knowledge base.

As far as I am aware, a 'linked' table is regarded as a table by Access.

Hope this gets you closer.

Regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[paulminne]

Hi Darrylle,

Thanks for the information. I think I should be right without putting any record locking in place, primarily because there are only two main users of the database, and thousands of records. Therefore the chances of the same record being referenced are very small.

Regards,

- Paul

 

[Darrylles]

Hi Paul,

Chances be damned.

Seeing that there are only 2 users, why don't you test it to give you peace-of-mind?

Tell one to open a record, then the other to open the same record.

Tell one to edit that record, then tell the other to attempt to edit that record.

Hey-presto, instant answer (positive or negative).

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk