2007 Split database access issues

Question

I have an MS Access 2007 split database. The Back End database (accdb) contains 1 table. It is on a server share. One user who updates the table has full NTFS permissions to the file. Several Front End users have NTFS read access to the file. The Front End database (accdb) contains queries and a link to the Back End database file. Each Front End user has a local copy of the Front End database.

Here is the issue; if the Back End database needs to be updated, and Front End users have the Front End database open – The Back End database is Read Only. If I have all Front End users close their local copy of the database, the Back End database can then be updated. However, if the Back End file is opened, (Being Updated) and Front End users open their local copy of the database – They cannot run queries.

What I am trying to accomplish is Front End users can open their local copy of the database and run queries at anytime, and the Back End database can be opened and updated at anytime. Is there a way to do this? It is very frustrating when the Back End database is being updated, that users cannot run queries - And if Front End users are running queries, the Back End database can only be opened with read only access.

Thanks, Tom

Answer 1

If what you mean by 'updating' is entering records, and not changing table structure - you should be able to change the locking type of your forms so that they will only lock the record that's being updated, instead of the entire table being updated. Ideally, you based your forms and reports off of queries, right?

If you are looking to do more, you might be better served by getting into SQL Server Express. It's a free product through Microsoft, and it's handy when writing multi-user applications that don't need the full feature-set of SQL Server. This would allow you finer control over who can do what when and with what tables - and you get the added benefit to your overall performance.