Reports and Record Locking

[MarkNie]

Hi

I hope someone can shed some light on this for me. I have a 2000 Access DB with FE & BE in the same folder on a server. Last month when we used the DB it was working fine. I have since created new reports and now I get a error everytime I try and go into the report. There are about 20 users using the DB at the same time.

Error 1:

Tracker can't find the hyperlink to the report.

I then checked the queries to see if they are working fine and they are. I then tried to open the report from the in the main menu box and it then gave me the following error.

Error 2:

Could not lock table 'tblTracking'; currently in use by user 'Admin' on machine xxxxxxxx.

Any help on this would be greatly appreciated

 

[lameid]

The first thing I would do is find the owner of Machine xxxxxxxx and get them out of the database and then try again. If it works, great if not, hopefully the error will be different. If it works the next step is to figure out what that user is doing to get an exclusive lock on tblTracking and prevent it.

 

[MarkNie]

Hi lameid

I have checked all the machines and none of them have any type of exclusive lock on them.

There are some other reports in the db that work fine and they are using the same tables in the queries.

Could it be something to do with the actual reports, as when I go into the querie which the reports use they work fine. But when I click on the reports it gives me the error: Could not lock table 'tblTracking'; currently in use by user 'Admin' on machine xxxxxxxx.

Any ideas

Thanks for your help so far.

Regards
Mark

 

[lameid]

First thought, try a compact and repair.

Secondly try importing the report into the same database and failing that test in another database file with linked tables.

Lastly try removing any grouping in the report. Grouping often causes the query to be executed multiple times. If that allows it to run you may be able to add grouping and it work or have to play with something. Obviously any field that returns "#Name?" all the way down is a problem as is any field that returns "#Error" for any value.

Oops one more thought: if you have a separate backend, try doing a compact and repair on it.

 

[MarkNie]

Hi Lameid

I have done all that you suggested but still no luck. It seems that all the reports and queries I did before the beginning of this month works fine and all the ones I did after are not working.

I have just created a simple querie but when I try and go into it it says that:
The database engine could not lock tanle tblTracking because it is already in use by another person or process.

But when I click on the tblTracking to see if there is a lock or a problem it opens fine.

So lost on this one, might there be something in the options which creates the new queries and reports in a different way??

Thanks again for your help

 

[lameid]

I am at a complete loss... If it were access security, importing to a separate file would fix the problem (since you could view the table). If it were Windows security, you would not be able to open the database/linked backend (hence table from the frontend perspective).

If it was a real locking issue, you would not be able to open the table.... Unless there is something on one of the report events that is trying to write to the table or otherwise gets a lock? What happens when you just open the query that is the report's record source?

You could try the old stand by of importing everything to a new file. If it is a security or other issue one would assume that this would break all the other objects too.

The only other thought I have is make sure your office patches are up to date.

One final thought... I don't know that reports support hyperlinks, I just never stopped to think about it. Forms do.