Maintenance of MS Access Back End

[CPGDeveloper]

Hello~

I've created and administer an Access DB application -- 20+ end users -- split db -- each user has a copy of an accde executable file on their desktop, while the back end is on a network server. The front end is programmed to shut down automatically @ 8pm every night.

Once in awhile (every 3-ish weeks or so), the back end will "freeze" -- nobody is in the db, there is no lock file, but the database can only open read only, no record can be edited, and no record can be added. This only seems to happen first thing in the morning -- as people start their day and start to use the db. It has yet to happen in the middle of the day.

This does seem to clear itself up if I give it a couple of hours, but of course the business cannot wait a couple of hours. When this happens, I make a copy of the back end, and re-issue a front end linked to this copy and make that our new back end.

Is there a way to prevent this? I do compact and repair the back end once a week or so -- do i need to do that every day? I am not considering simply creating a new back end every week or so as a preventative measure. But is there something else that might be going on? Is it something in my coding that might be causing the back end to 'lock' like this?

 

[welshgasman]

I cannot offer a reason to the problem, but they used to so this in a bank I worked in for Excel workbooks and it annoyed the hell out of me as I had placed the workbooks on my MRU list.

Why when you make copy, why can you not rename the old to anything other than the real name, just a date added would do, and then rename the copied version to the correct backend name.?

Then you would not need to muck around with new FE distribution.?

Just a thought.?

 

[xenou]

you might need to compact and repair more often if the db is expanding in size too much every day.

 

[CPGDeveloper]

Thanks for the suggestion -- however when the Back End is in this 'locked' read-only state, it cannot be renamed -- I think we're going to go ahead and simply create a new back end every week as a preventative measure.

I cannot offer a reason to the problem, but they used to so this in a bank I worked in for Excel workbooks and it annoyed the hell out of me as I had placed the workbooks on my MRU list.

Why when you make copy, why can you not rename the old to anything other than the real name, just a date added would do, and then rename the copied version to the correct backend name.?

Then you would not need to muck around with new FE distribution.?

Just a thought.?

 

[xenou]

Just out of curiousity is there anything in your BE other than data tables? What is the file size of the BE? Also I guess FWIW do you expect in time to be increasing the number of users?

 

[Micron]

The front end is programmed to shut down automatically @ 8pm every night.

That is something that raises a red flag. If anyone left a half-finished form edit in play and you're forcing a shut down you can expect things like this to happen. Not sure myself, but I would even worry about any connections that any open fe had to the be file when you terminate that fe even if there is no editing going on.

 

[Legacy 98055]

Are your front ends referencing your back end by way of mapped drives? If so, try using the full network path in your linking.

 

[CPGDeveloper]

Thanks for all the suggestions -- I only have Tables in the Back End, and I do use the full network path in linking my tables. The point about shutting down automatically and the issues it might cause is something I'll have to think on a bit -- I have it shut down at 8pm as theoretically nobody will be working that late -- and I do have the fe create a persistent connection on open and it releases that connection on close. But I might play with the timing of this.

In the mean time, I've simply replaced the front end every Saturday morning for the last 3 weeks and so far we haven't had any issues...

 

[Micron]

One other possibility comes to mind. One (or more) users or a process doesn't have the proper permissions on the folder, i.e. the db can be opened but the lock file cannot be created. This will force the db to be opened exclusively by that user or process, in which case you would not see a lock file as you report. Replacing the be would no doubt override this issue. However, the exclusivity would exist as long as the db was opened and I don't know what that could mean in your case. Thus the idea may not be valid, but I thought I'd throw it in here as a potential.