Table locking problems with a shared database.

[Sanibel]

I have written a shared Access database but am getting the following error whem testing it in a multiuser environment.

Could not lock table xxx; currently in use by user yyy on machine zzz.

I get this error if user A has a form open and user B tries to run a report accessing data from the same table used in the open form. The report does not update any data and I get the same error if I run the select query on which the report is based. Both users can however open the form at the same time without getting this error.

I did think that tables would only be locked whilst they were being updated not when they are open in a form.
I have opened the database in shared mode and with the no locks option.

Which options should I be using to give users less restrictive access.

Thanks

 

[Remou]

It appears that you have not split the database into front- and back-end.

You may wish to read:

http://msdn.microsoft.com/en-us/library/aa167840(office.11).aspx

 

[Sanibel]

Thanks for the suggestion. I have split the database into a front and back-end. I have created two front-ends and tried opening the form in one and running the report in the other and still get the same "could not lock table" error.

Any further thoughts?

 

[Remou]

Is the report's Record Locks property set to anything? Have you set up record locking for the database or the form?

 

[Sanibel]

Thanks for the advice. The Record Locks property was set to All Records on just a few of my reports, including the one that first highlighted the problem. Setting these to No Locks has resolved the issue.

Thanks for all your helpful suggestions.