By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,761 Members | 1,071 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,761 IT Pros & Developers. It's quick & easy.

Access DB locking in Multiuser Environment

P: 4
The Situation:
Peer to Peer Network (no Server)
Access 2000 DB running on one PC, is accessed by up to 5 users (not FE/BE)
I have written the system - my first in Access.
Farily heavy usage - 500 to 600 updates per day (is that heavy ? I dunno!)
I have about 100 linked databases - 1 database per client.
The linked client databases live in a folder in the same area as the "master" DB

All was fine up until about 6 months ago when...

The Problem:
The system just keeps locking !! and it seems to be getting worse - about 4 times today. All my staff have to exit Access and the "master" PC has to be rebooted in order to get back on. At first we blamed it on going into the same client at the same time - but it sometimes seems to lock up when someone goes into a linked DB (i.e. client record) 20 seconds after that last person came out.

Its driving me (and my staff) mad !!

Any help gratefully received

Chris
Jun 6 '07 #1
Share this Question
Share on Google+
6 Replies

puppydogbuddy
Expert 100+
P: 1,923
The Situation:
Peer to Peer Network (no Server)
Access 2000 DB running on one PC, is accessed by up to 5 users (not FE/BE)
I have written the system - my first in Access.
Farily heavy usage - 500 to 600 updates per day (is that heavy ? I dunno!)
I have about 100 linked databases - 1 database per client.
The linked client databases live in a folder in the same area as the "master" DB

All was fine up until about 6 months ago when...

The Problem:
The system just keeps locking !! and it seems to be getting worse - about 4 times today. All my staff have to exit Access and the "master" PC has to be rebooted in order to get back on. At first we blamed it on going into the same client at the same time - but it sometimes seems to lock up when someone goes into a linked DB (i.e. client record) 20 seconds after that last person came out.

Its driving me (and my staff) mad !!

Any help gratefully received

Chris
Conventional wisdom says Access is not reliable in a multi-user shared environment unless it split into FE/BE. You seem to know that, and still seem to want to run the risk of DB corruption, so I won't pursue that line of thought here, except to give you a link to an excellent article by Albert Kallal on splitting your DB.

http://www.members.shaw.ca/AlbertKal...plit/index.htm
(http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm)

If you are going to continue using Access without splitting it, then I hope that each user has his/her own copy of MS ACCESS on their local PC. For your lock conflicts, see below for a trick method of reducing general locking conflicts that I obtained from the tips page of www.aadconsulting.com that involves creating a combobox and a textbox, and adding the code shown below. This trick might solve your problem with the locking....no guarantees.

What's in the .ldb file
For every Access database opened for shared use, an .ldb file is created to store computer and login names, and to place extended byte range locks. The .ldb file always has the same name as the opened .mdb and is located in the same folder.

The Jet database engine uses .ldb file information to prevent users from writing data to pages that other users have locked, and to determine who has other pages locked. If Jet detects a lock conflict with another user, it reads the .ldb file to get the computer and login name of the user who has the file or record locked.

In most lock conflict situations, Access raise a generic Write conflict message that allows you to save the record, copy it to the Clipboard, or drop the changes you made. In some circumstances, you may receive the following error message:

Couldn't lock table <table name>; currently in use by user <security name> on computer <computer name>.
1. In the form create a TextBox that sits exactly over the ComboBox

2. Set the ComboBox's Visible property to False

3. The TextBox is Visible and holds a value according to needs of the application: it may be a bound control or, as in the sample code below, unbound with the value assigned on the Form_Load event.

4. Clicking on the TextBox hides it and displays the ComboBox. After the user has updated the ComboBox, the value is assigned to the TextBox and the ComboBox hidden again.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me!txtHideShow.SetFocus
  3.     Me!txtHideShow.Text = "Test"
  4. End Sub
  5.  
  6. Private Sub txtHideShow_Click()
  7.     Me!cmbHideShow.Value = Me.txtHideShow.Value
  8.     Me!cmbHideShow.Visible = True
  9.     Me!cmbHideShow.SetFocus
  10.     Me!txtHideShow.Visible = False
  11. End Sub
  12.  
  13. Private Sub cmbHideShow_AfterUpdate()
  14.     Me!txtHideShow.Value = Me.cmbHideShow
  15.     Me!txtHideShow.Visible = True
  16.     Me!txtHideShow.SetFocus
  17.     Me!cmbHideShow.Visible = False
  18. End Sub
  19.  
Jun 6 '07 #2

P: 4
Thanks for that.

It seems that conventional wisdom is going to win! and I think I will look into splitting the DB.

Regarding the latter part of your answer - we're not getting a locking error, at all, ever. So I'm not convinced it's a locking problem?

Maybe I should have used the word "hanging" in my original post rather than locking.

Also, I've just run a routine on my linked databases (100 or so of them) to compact and repair. It's reduced the database folder from 130mb to 27mb - I don't know if that will help - time will tell

Anyway, thanks again for the advice.
Jun 6 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
Chris,

Your statement below changes everything:

Maybe I should have used the word "hanging" in my original post rather than locking.

Yes, you should have. Daily compact & repair, and refreshing of your table links (using VBA Code) should improve your system performance.
Jun 6 '07 #4

Expert 100+
P: 218
Chris

Just a quick thought...

You should think seriously about using SQL Server 2005 Express as your data engine and using Access as your front-end app. Lots of good reasons for doing this (some have been raised in this thread).

There are some well-trodden paths along this route and the gotchas are pretty well known. Also SQL Express is FREE!

HTH

Steve
Jun 6 '07 #5

P: 4
Thanks again.

I will implement the "compact and repair" as a daily task. I'll find out how to refresh the links by code (havne't done this before) and try that too.
Jun 7 '07 #6

P: 4
Thanks Steve,

I've got SQL Server express on my machine. So I'll have a look at that - during a quiet moment (that'll be May 2009 at this rate!)

Thanks for your help
Jun 7 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.