Access locking up with multiple users

[Dragn73]

Hi,

I have a database that i created for our tech support team. I have split the database and have given the 3 users each a shortcut. I als e only installed Access Runtime on their machines.

The first user in pilot testing had no issues and we have just added the other 2 users. At first all was well. However, the 2 new users are now having periodic lock ups where they go to enter a new record and the dbase freezes on them. It seems to release itself in a few minutes, but this does not help my productivity.

My original user is still not having any trouble. Does anyone have any ideas of something that perhaps I missed? Everything I have read, suggests that 3 users should not have any trouble using a split dbase.

Is it runtime that may be causing this? I have considered going to full versions if need be, but then I would need to figure out how to lock it down better.

Thanks
Dave

 

[boblarson]

As long as each person has their own copy of the front-end (not just a shortcut), you will be okay. If you have multiple people accessing the same file, it can cause you no end of grief, even though it's SUPPOSED to work fine. It just really doesn't in many instances so having separate copies of the front-end is best.

You can download a copy of my tool that enables auto-updating of front-ends to your front ends so that you can just worry about making modifications to the master and then when the user next opens it up, if it isn't the latest version it will tell them, close, delete the old, copy the new one and reopen.

You can get it free here: http://downloads.btlarson.com/AWF/Samples/Access_Front-End_AutoUpdating_Utility_rev01-02-2007.zip

 

[Dragn73]

Thank you for the help. After I make a copy of the FE do i need to do anything extra on each of the user's machines?

Also, how does your tool work exactly?

Thanks

 

[boblarson]

First of all you would use my tool to add the tables and code required for autoupdating. It you can find documentation and instructions for it here:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=14815&d=1157932958
Then, once you have it in you can then make copies of the front-end for the users. Once you've distributed it to them the first time you shouldn't have to from that point on.

 

[Dragn73]

Thanks a ton for the help. I will try the copies of the FE first thing tomorrow and see what happens. At this point, I may not have a need for your autoupdate tool, but it sounds cool, so I will definitely keep it on hand for the future. Thanks again

 

[boblarson]

When I first got to my current job, they were using a database for about 75 users (not all concurrent), in a front-end/back-end scenario. But, they were all connecting to the same mdb file front-end. I told them that it was much better to have a separate copy of the front end on each user's machine and my coworkers (I'm in Tech Support) were about to skin me alive since they didn't want to have to touch 75 machines every time the front-end needed updating. So, to calm them I created this tool and then they loved it so much that I posted it here on the forum in the samples area.

They haven't had a case of corruption since they went to the new method.

 

[Dragn73]

I made a copy of my front end and put it on each users machine and they still got a lock up this morning. Maybe I am misunderstanding, but here are my steps. I went to the shared drive where I currently have my FE and BE sitting. I right clicked and copied the FE and then pasted it on each users computer. Am I still misunderstanding? The error that I am getting is "Could not update; currently locked" It is very intermittent, so I know its not in exclusive mode.

Would I need to relink the tables after I put the copy on their computers for any reason?

Thanks

 

[boblarson]

Sounds weird to me. I'm pretty sure I've seen that problem here on the forum before. I just can't remember what caused it. I'll try doing a search and you might try too. Try putting that error message into the search. Also, the Microsoft website is another place to try with that.

Sorry about that.

 

[RuralGuy]

Are your forms bound to queries or directly to the tables?

 

[Dragn73]

Do you mean the record source on the form properties? Its to a query.

Sorry, this is my first real database and i am still trying to get the hang of all the proper terms.

 

[boblarson]

Ah, then you need to make sure that your query is updateable.

 

[Dragn73]

How would I do that?

 

[RuralGuy]

You answered the question I asked. In addition to Bob's suggestions, here's some from Tony Toews. You might try the persistent recordset connection to see if it makes any difference.

 

[boblarson]

How would I do that?

Open the query itself and try typing something into it. If it actually lets you type it and it retains it's value after moving to another record, then it is updateable. Also, at times you can look in the status bar below when opening a query and it will tell you "This query is not updateable."

 

[MicroE]

"having periodic lock ups where they go to enter a new record "

Sounds like you may have a Primary Key issue - are you using an AutoNumber as your PK?

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch16.mspx

http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch16.mspx

It alos could be a corruption in your db - try /decomplile.
http://www.fmsinc.com/free/newtips/access/accesstip7.asp

http://www.myaccesstips.com/deploy/deploy.html

 

[Dragn73]

Ok, I thank all of you for the suggestions. Here is what I have done.
- the query was updateable so no changes made
- the persistant recordset connection was correct
- I do have an autonumber as the PK so no issue
- Only change i have made, is to save it as an mde file and redeploy it that way.

other than these ideas, I am at the end of my rope. I hope the mde file was the key. This whole thing still really doesn't make any sense, but again, thanks for the help on it.

Dave

 

[Dragn73]

FYI, it didn't seem to work, as one of my users just reported another lockup. It didn't appear to last long, but its still frustrating for all. Also of note, I did also try a repair that didn't help.

i will continue to poke around and post any resolution here just in case someone else hits this snag.

Thanks

 

[MicroE]

Is you network platform Microsoft of Novell?

Are there memo fields in your data entry form – if so how many?

Does your query contain 2 or more tables with a one to many relationship? If so, a Main Form / Subform approach should be used.

Look at the design view of each of your Forms, Reports, Modules, and Queries and look at the Record Locks Property of each – make sure it is set to “No Locks”.

Also, try opening MS Access twice on your PC - each instance opening the database. See if you can consistently reproduce the error by entering data in both open sessions (switch back and forth between the two). If you can recreate the steps the users are doing – you may be able to narrow down the field – tables giving you the issue.

 

[dcobau]

I seem to recall that when user1 is accessing a record in the BE user2 will not be able to access records in the vicinity of the record user1 is accessing. I don't remember the details (ie size of the chunk of the db that is not available), I would suggest you search Access help for "Record locks".

Dave

 

[MicroE]

Dave (dcobau) – you are correct if page level locking is being used (good point).

Dave (Dragn73) – at each users workstation select Tool > Options from the menubar in MS Access. From the Advanced tab make sure the checkbox “Open database using record level locking” IS checked.