Shared database locked - cannot update - Access 2000 - Help!

[ollyb303]

Hi,

I have a problem with a multi-user db in Access 2000.

The db is for logging calls to my company and will be used by up to 30 people, though currently around 10 are using it.

I have split the db into front/back end and each user is using a local copy of the front end, connected to the back end via our network.

It was all working fine when we tested it (across 2 different sites) and had no problems, but now, some new users are using it and I keep getting a problem where they cannot save/update a record and get the message "cannot update - currently locked" - this then means that all users start to experience the sameproblem. If everyone logs out it solves the problem.

Default Open Mode = shared
Default Record Locking = No locks
Open databases using record-level locking = true

Please can someone help with this - I'm not sure why this is happening and need to sort it out quickly.

Many thanks,

[ollyb303]

can anyone help with this?

[ollyb303]

Have I phrased my question badly or is it just that no-one knows why I could be having this problem? Some sort of response would be greatly appreciated.

Many thanks,

Olly

[Stewart Ross]

Have I phrased my question badly or is it just that no-one knows why I could be having this problem? Some sort of response would be greatly appreciated.

Many thanks,

Olly

Such impatience, Olly - generally, no response means we don't know either!

-Stewart

[ollyb303]

Thanks for replying Stewart

I'm really stuck now! :)

[Stewart Ross]

Hi again Olly. Did your lock difficulties begin when the new users started using the DB? As Access creates an LDB share file while users are accessing the DB I wonder if there is a network issue with the new users, perhaps momentary disconnections which leave the LDB file in an unknown state until everybody logs out? Just guessing, really, as it does not appear to be an Access issue. Could be anything, network card, switch, router or whatever.

Not much help, I know, but you appear to be doing everything right with Access itself...

-Stewart

[Scott Price]

Just as an added thought, have you done a decompile on the production version of your database? You could potentially be experiencing corruption introduced from a variety of different sources, however, the most likely is that during the development process of writing code, compiling, saving and testing, the code goes through a number of different states of compilation.

Try this working on a COPY of your database: call up the command window (start menu, Run) and type in something like this:

"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile "c:\My Documents\MyDatabase.mdb"

The first file path refers to where your local version of Access is stored on your computer. The second file path refers to where your copy of the particular database .mdb file is located on your computer.

Regards,
Scott

[Scott Price]

I should mention that you should do a Compact-Repair both before and after you do the decompile.

Regards,
Scott

[ollyb303]

Thanks Scott/Stewart,

Stewart - yes it was when new users started using it that the problem occurred. I'm not aure about problems with the network however as we successfully share several other dbs in the same way.

Scott, I've tried what you suggested (compact/repair, decompile, compact/repair) - should anything have visibly "happened" during decompile? I ceratinly didn't see anything, the database simply seemed to open.

Many thanks for your help,]

Olly

[Scott Price]

I should have mentioned that the database needs to be closed during while executing the decompile command.

The main thing you should notice is a decrease in size of the mdb file. In many cases it will lose about 50% of its size... i.e. a 4mb database will become 2mb.

You should also notice a significant increase in speed of opening (if it's been slow opening before...)

Regards,
Scott

[ollyb303]

That's Ok, it was closed before I ran the decompile and opened after I ran the command - it has pretty much halved the size of it - I'll redistribute the front end and see how I get on.

Thanks again,

Olly

[Scott Price]

Hope it works for you! If not, check to make sure that the .ldb file is being released when the users log out. It sounds like it is, since you mentioned earlier that the problem disappears when all users log out.

Here is an excellent thread reply that contains some good information and suggestions on how to avoid record locking problems.

Regards,
Scott