Sharing a database without locking other users out

[litrelord]

Hi,

We have a database that gets it’s data from another database (both access) by using linked tables that we then run queries from.

There are 2 or 3 copies of the database containing the linked tables but if anyone is using one of them no-one else can open their queries. We receive an error saying the file (the one which the tables are linked to which contains the original data) is in use.

Is there a way to get round this as people seem to have a habit of opening the database and then leaving it open which stops anyone else from using it. I thought there would probably be a way of having it collect the data from the tables just once and then running the queries off that rather than access the tables every time.

I don’t have immediate access to the original database but can do what I like to the database with the linked tables.

Thanks

Nick

 

[SydneyGeek]

How you deal with it depends on which version of Access you use.

Access 97 automatically locks 2K of data when you access a record -- say, up to 30 records.

Access 2000 and higher let you lock individual records, which gets around most of the problem. You need to set this for EACH user, because the first user in the system determines the granularity of the record locking:
Tools | Options | Advanced, then check Open databases with record-level locking.

Note: Above info from Access 2000 Developer's Handbook, Enterprise Edition, pages 31-32.

Denis

 

[litrelord]

Hi,

I had a look and this already seems to be checked.

Which database does this need to be checked on, the source database or the database with the linked tables (the one with the queries that we run)?

Thanks

Nick

 

[SydneyGeek]

I'd guess the front end (the one with the links) because the locking granularity varies depending on how you view the records (via a form, ADO, DAO, or other means). But it wouldn't hurt to look at the backend (the one you don't have immediate access to ... :wink: ). Try making sure that it's checked on ALL machines with the database.

Hope it helps

Denis