Link to home
Create AccountLog in
Avatar of Sabina_Compassi
Sabina_CompassiFlag for Switzerland

asked on

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

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
Avatar of Barry Cunney
Barry Cunney
Flag of Ireland image

Has everyone who is accessing the ACCDE app the exact same permissions to the network location?
Avatar of Jim Dettman (EE MVE)

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.

Avatar of Sabina_Compassi

ASKER

@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.
<<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.
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
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.
Still looking for a workaround. Even if not perfect.
Sabina
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account