Multiple Entries for one machine in the .ldb file

I'm getting multiple entries for one computer in my .ldb file. The best guess I've come up with so far is that this occurs when a machine/user runs compact and repair on it's frontend file while others are using the database. The only way to resolve this problem is to have everyone get out of the database entirely so that the .ldb lock file gets deleted automatically.

We're using Access 2007 on XP Pro SP3 on almost all of our computers, with SP2 for Office 2007 installed. The backend file is an Access 2000 .mdb database stored on a Server 2003 file server. There are different frontend files for each user. Some of them are stored locally and others are stored on a network drive. The frontend files are also an Access 2000 .mdb format.

Several users leave their database open overnight. This doesn't appear to be a factor (other than the fact that the .ldb file never has a chance to clean up) since I've narrowed it down to the compact and repair process of the frontend file.

Is anyone familiar with this problem? I've used access a long time and never had this problem before.

Harlan

If all your data is in the backend the frontend does not change so there is no need to compact and repair the frontend.

Galaxiom said:

If all your data is in the backend the frontend does not change so there is no need to compact and repair the frontend.

Click to expand...

Well, I'll have to challenge that a little (sorry).

Some frontends which do use temp tables and the constant data in and out can cause some bloating which needs to be controlled every once in a while. So, there may be a need, at some level potentially, to compact a frontend.

Although I have to say that we manage pretty well without having to do so because we have it set up that everyone gets a new frontend to each database when they log in to their computer (part of ScriptLogic).

I don't mean to be an idiot but that's clearly not the answer I was looking for. There is some data stored locally such as user settings and for some reason over time the files do increase in size. Some of the users are savvy enough to build tables, queries and reports so that likely has something to do with it. I like to run compact and repair on their files if and when their are complaints about performance problems, just to make sure that their local frontend file isn't corrupted. I had one corrupted the other day and had to replace it with a new copy.

I find it ironic that the very thing I'm trying to do to improve performance is likely the cause of our performance issues. After this lock file gets several entries in it for one computer the performance of the entire database system goes down the tubes. Running process monitor reveals that Access tries for a long time to do something with the lock file and then finally gives up. This occurs every time users do any little thing in the database so I get complaints when this lock file is "corrupted".

I am no expert with compact & repair but if you use a command-Line switch would the FE not perform the compact without accessing the BE?

Throwing one into the pot here - may be wrong....

boblarson said:

Well, I'll have to challenge that a little (sorry).

Click to expand...

No need to apologise, Bob. The differences of opinion are the most informative threads on this site.

Some frontends which do use temp tables and the constant data in and out can cause some bloating which needs to be controlled every once in a while. So, there may be a need, at some level potentially, to compact a frontend.

Click to expand...

I also split the front end from the local temporary tables (the SideEnd). IMO the same principles of splitting the program from the primary data should apply to temporary tables. Indeed the danger of corruption due to the "constant in and out" makes temporary tables as important to split as the backend. (Except you can get a new front end if it breaks. But you also loose your customisation.)

If you don't write to the front end, it cannot be corrupted and it will never need to be compacted.

The easy location for a SideEnd is a specific folder in the C drive. However this is not always practical and is particularly clunky if you have to set user permissions. If the drive letters are not standard it is a real problem. However, when suitable, the linking can then be to a fixed path.

A more sophisticated approach is to locate SideEnds in one of the the User's folders where they have automatic permissions. However this requires code to dynamically link the tables. Use environment variables in your code so you get them no matter where they are moved to.

See the Environ function
http://office.microsoft.com/en-us/access/HA012288311033.aspx

Here are some good candidates.

%APPDATA%
Default: "C:\Documents and Settings\{username}\Application Data"
(Saved as part of a user's roaming profile)

%USERPROFILE%\Local Settings
Default: "C:\Documents and Settings\{username}\Local Settings"
(You should follow convention and continue down to a folder dedicated to your application in the Application Data subfolder. Not part of a roaming profile.)

%TEMP% - default: C:\DOCUME~1\{username}\LOCALS~1\Temp

I prefer the first two as they are normally hidden folders.

However I would not store most user settings locally. They can be held in the back end so that the user experience is consistent wherever they logon. And they will be backed up on a server. If using Roaming Profiles you can keep the user settings SideEnd in their Application Data folder.

The best place for temporary tables, especially if they are large, is the user's Local Settings folder as it is not part of the user's Roaming Profile.