Question Split FE/BE record locking?

jonathanchye

Registered User.
Local time
Today, 16:11
Joined
Mar 8, 2011
Messages
448
Hi all,

I am having a very frustrating problem that is now more noticable as my users increases. I am running MS Access 2010 with all my backends split into FE/BE format.

The BE is stored on a network shared drive where all users (should) have full rights. The FE is saved locally on each users desktop. The clients settings are all set to ensure no record locking and the database opens in shared mode. I use queries as recordsource and avoid dlookups where possible. None of my forms have record level locking (all set to No locks)

The problem I have now is that once any client opens any table in the BE the whole BE is locked (laccdb file created). This poses a problem when I run my nightly compact and repair script or when I need to make changes to a table in the BE.

I do not understand why it is locked (no exclusive rights to edit) in this way. I am pretty sure I never had this problem before but maybe back then I do not have so many concurrent users. I also have some hidden forms for maintenance purposes that runs periodical checks with the network BE file... Now some users encounter "this table is locked..." error.

Even opening a form in design mode on the FE seem to create a laccdb file on the BE! It seems to create the lacddb file if any of my forms have a recordsource set as "Dynaset" or "Dynaset - Inconsistent updates"

This thread best describes my problems : http://bytes.com/topic/access/answers/853588-back-end-database-lock-up

I am trying to find out from our IT dept. if our shared drive is a UNIX system (this is the only other possible cause I can see now)

I am just hoping if anyone else here could offer more insight...
 
Last edited:
I've just got sent this error from a user : "The expression On Open you entered as the event property setting produced the following error: The databse has been palced in a state by user "Admin" on machine XXXX that prevents it from being opened or locked"

Background : The On Open code checks a "Versions" table on the network drive just to ensure the client is on the same version as the server. I happen to have the BE file opened at that moment but I have the form in Design mode. Apparently this locks the whole thing up so the FE running this code fails and crashes out :(

I find this all very restrictive (Too restrictive in fact). I don't recall having this problem months earlier so is this some sort of stealth Microsoft update?
 
Why do you have the Back End opened? Are you doing some maintenance? And why does the back end have a Form. Is this Form just a splash screen or does it do something.

You said all of your Back Ends. How many do you have?
 
Why do you have the Back End opened? Are you doing some maintenance? And why does the back end have a Form. Is this Form just a splash screen or does it do something.

You said all of your Back Ends. How many do you have?

Hi,

Yes, at this time I was doing some maintenance on the BE hence why I have it open. The BE only has one form which shows me a summary of all the Backends I have and their versions.

I have around 5 separate backends at the moment. However all of the FEs first do a query to the "Versions" database. This "Versions" database is located on the network share and is not split as it basically only contains 2 tables. 1 table to keep track of client/server versions and the other table to keep track of maintenance carried databases. I have one form in "Versions" and this just basically allows me to quickly change Version numbers and set Maintenance time/dates. All my other backends have only tables in them.

I've noticed this locking happening to all the other databases as well but it is more noticable in "Versions" because of how often it is accessed.

Logically I don't see why it should be locked as such. I would've thought splitting your DB into FE/BE (which each client having their own FE) would eliminate this problem. By right, since I don't have any record locking in my forms the tables shouldb't be locked even though they are accessed?

I've done a very simple test. I've created a test DB with one table and one form accessing the table (Dynaset) and proceeded to split them. I've turned all record locking off and there's no code running in my FE form.

Once I open the FE a record locking file is created for the dummy BE. If I then switch into Design View, the record locking file is still present. The file is only deleted when I close the form (but leaving the FE still open). Is this normal?
 
The problem I have now is that once any client opens any table in the BE the whole BE is locked (laccdb file created). This poses a problem when I run my nightly compact and repair script or when I need to make changes to a table in the BE.

This file will be created even if you are the only person who has it opened. You should still be able to open it up for Table Changes, provided you are the only person. You should still be able to do a Compact and Repair. You should always be able to copy regardless of how many uses are on board.

Do you think it may be better to do these things in the early hours of the morning.
 
This file will be created even if you are the only person who has it opened. You should still be able to open it up for Table Changes, provided you are the only person. You should still be able to do a Compact and Repair. You should always be able to copy regardless of how many uses are on board.

Do you think it may be better to do these things in the early hours of the morning.

I can schedule the maintenance some other time - that's not a problem. The problem is I am getting sproradic complains from users and from the screenshot it is because the code failed to run as the BE is locked. I am scratching my head why the code can't run as all it does is to query the BE table (not even edit!)

I have tried and I cannot Compact and Repair unless no one is in the BE :(
I guess the next logical solution is to think about using SQL (I heard migration from MS Access 2010 to MS SQL is rather painless...) to avoid these problems.

Some of the FE are open almost 24/7 and the annoying thing is they lock up every BE they connect to, not just its source BE.

I am almost sure this wasn't a problem before and also cannot explain why this is a problem now... The FE seems to lock up the whole BE and not just the table its "connecting" to...

The fact that 90% of the FE users run CITRIX also kind of make troubleshooting even harder :P
 
edit: Just to clarify - I seem to be able to edit data from the FE, I just can't make any changes (Design View) to any tables in the BE.
 
I don't know the full answer to your problem.

I do however know that SQL Server is not the answer, and unless you have a good knowledge of it stay clear until you can get someone to teach you the ropes. Perhaps you could learn this at home as an STA. (Spare Time Activity)

I would suggest that you undo all the settings to do with Record Locking. Bring them back to the default. I have not experienced the problems you are experiencing and I have had about 20 Different Front Ends (Not Copies but different programs) all running at the same time. These Fronts ends even shared the different Back Ends. Sometimes 5 or 6 of them. This was done in A 97 with user Level Security. I never had this sort of problem.

So the long and short is that I feel it is a setting that you changed. But which one I do not know.
 
I don't know the full answer to your problem.

I do however know that SQL Server is not the answer, and unless you have a good knowledge of it stay clear until you can get someone to teach you the ropes. Perhaps you could learn this at home as an STA. (Spare Time Activity)

I would suggest that you undo all the settings to do with Record Locking. Bring them back to the default. I have not experienced the problems you are experiencing and I have had about 20 Different Front Ends (Not Copies but different programs) all running at the same time. These Fronts ends even shared the different Back Ends. Sometimes 5 or 6 of them. This was done in A 97 with user Level Security.

Sothe long and short is that I feel it is a setting that you changed. But which one I do not know.

Good advice, thanks. Move to SQL will lessen my headache ( I have some programming skill + basic SQL skills) but it would be a major move for the company due to lots of reasons - backups being one major headache.

I will a look at the settings but just very confused why this is happening. Why can't I edit other tables when a FE has one of the tables in the BE open ?
 
Earlier versions of Access allowed design changes when multiple users had the db open but A2007 + do not unless you have a source code control product such as SourceSafe installed. The bottom line is - don't do it!! You should not be making design changes while users are accessing data. Make your design changes to your design master, test them, then incorporate them off-line.

If you are getting locking errors, go over the user's permissions with a fine tooth comb. Some new user may not have create or delete permission and so the lock file creation/deletion is being adversely impacted.
 
Move to SQL will lessen my headache

I thought I said not to move to SQL. That is unless you are very confident.
 

Users who are viewing this thread

Back
Top Bottom