We help IT Professionals succeed at work.

lock file not updated or not closed for accde, how can this be prevented?

Sabina_Compassi
on
I have a problem on the network drive where my access database resides split in a frontend (accde) and data-backend (accdb). Multiple users are using the same frontend to get to the data.

I have observed, that a lock file is generated (see below why I think so), that prevents the frontend retrieving the data of the backend. The frontend can be opened without problem, the error occurs, when actions are made, that require data from the backend.
This only happens, when the frontend database is an accde database. No issues observed with a frontend that is an accdb.
After everybody closes the database (frontend and thereby the dataend), the lockfile (laccdb) persists. The lock file points to the frontend. After killing the lock file (with appropriate administrator rights, it is not possible to simply delete it), retrieval of data is possible again. Both using accde frontends or accdb fronends.  

While I know now, how to manage the issue when it happens (killing laccde with the appropriate rights), I would like to know how I can prevent this to happen. It seems that the likelihood for not updating the lockfile is higher with accde than with accdb. Why's that? What can I do?
(I cannot distribute the accdb frontend, it must be the accde).  

Thanks for any help you can give me
Comment
Watch Question

Has everyone who is accessing the ACCDE app the exact same permissions to the network location?
Jim Dettman (EE MVE)President / Owner
GOLD EXPERT
Fellow
Most Valuable Expert 2017

Commented:

Couple of things:

1. A LDB file is generated for each DB that is opened.  That means there is one for the front end and one for the back end.

2. In order to create a LDB file, every user needs full permissions on the directory where the LDB resides.  This means read, create, update, and delete.  Note that you should have those both on the directory itself and the share.

3. Last person out will cause the LDB file to be deleted *unless* a user disconnected abnormally (ie. did a ctrl/alt/del with the database open).  In that case, the user entry is left in the LDB to indicate which user disconnected abnormally.  So that is a "normal" thing that can happen in regards to the LDB file.

4. Users should not be sharing the front end.  Sharing a backend (with data only) is fine, but sharing a FE has never been a good idea.

5. Are you aware that a SAN is not a supported platform for Access?  Not saying you can't use it, but Access is only supported by Microsoft under Windows.  Over the years, I have bumped into a situation or two where because of the SAN OS, an Access DB would simply not work right.  

  So, do you have the ability to have the DB's anywhere else?

6. Make sure that these DB's are not being virus scanned.  Not a problem as much as it once was, but every now and then, we'll see a situation where it is and the anti-virus scanner tries to "fix" the DB, which causes all kinds of problems (make sure .LDB's and workgroup files are skipped to!)

Jim.

Author

Commented:
@BCunney: Yes, everyone should have exactly the same permissions to the network locations, for both, dataend and frontend.

@JDettman
1.) This is true
2.) I will check, but the locations were designed such that everyone has the full permissions
3.) Agree with you. I think that some users did disconnect abnormally, and I think that some users do so often (e.g. since closing is slow due to datacompression, they may abort)
4.) Agree fully, however I was forced to put share the frontend. Previously the frontend was distributed on local machines, where it was working properly for more than 10 years..... (the database was migrated and upgraded all the time, do not worry about the 10 year old db)
5.) I wouldn't know that, but we're on MIcrosoft Windows anyway.
6.) let me check out, whether the virus scanner checks on the Laccde file and whether that can be skipped.

All in all, since I am forced (apparently and unfortunately) to have the frontend shared, and as, most probably the issues come from people not closing the fronend properly, any hint how I could -under these circumstances- avoid the problem to occur? Could I put in VBA code to run on shut down that would before shutting down closing the app properly? Any other ideas?

Thanks again.
Jim Dettman (EE MVE)President / Owner
GOLD EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<4.) Agree fully, however I was forced to put share the frontend. Previously the frontend was distributed on local machines, where it was working properly for more than 10 years..... (the database was migrated and upgraded all the time, do not worry about the 10 year old db)
>>

   This is your biggest problem most likely.  Access DB's with objects that are shared just don't seem to work well and corrupt often.  And if you have users disconnecting abnormally, then it becomes a really problem.

  Why did the dirstibution of the FE stop?

<<5.) I wouldn't know that, but we're on MIcrosoft Windows anyway.>>

  The SAN is running an Windows OS?

Jim.

Author

Commented:
Jim,
unfortunately, I have no liberty to get the frontend on local machines again. Also, I MUST use the compiled ACCDE (it seems that the ACCDB behaves a bit differently).
So the question remains, what options do I have to prevent the file getting corrupt when users  disconnect abnormally?
Sabina
Jim Dettman (EE MVE)President / Owner
GOLD EXPERT
Fellow
Most Valuable Expert 2017

Commented:
Sabina,

<<So the question remains, what options do I have to prevent the file getting corrupt when users  disconnect abnormally?>>

  There are none.  It is what it is.   It's the way Access is designed and built.  There's no way to avoid it.

Jim.

Author

Commented:
Still looking for a workaround. Even if not perfect.
Sabina
President / Owner
GOLD EXPERT
Fellow
Most Valuable Expert 2017
Commented:
Sabina,

<<Still looking for a workaround. Even if not perfect. >>

 There is no work around.  If a user terminates abnormally, the LDB file will be left behind, the DB will have it's corrupt flag set, and new users will not be allowed in until the LDB file is deleted.

  That is the way it works and there is no way to change it with any setting or options.

Jim.