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.
-
Private Sub Form_Load()
-
Me!txtHideShow.SetFocus
-
Me!txtHideShow.Text = "Test"
-
End Sub
-
-
Private Sub txtHideShow_Click()
-
Me!cmbHideShow.Value = Me.txtHideShow.Value
-
Me!cmbHideShow.Visible = True
-
Me!cmbHideShow.SetFocus
-
Me!txtHideShow.Visible = False
-
End Sub
-
-
Private Sub cmbHideShow_AfterUpdate()
-
Me!txtHideShow.Value = Me.cmbHideShow
-
Me!txtHideShow.Visible = True
-
Me!txtHideShow.SetFocus
-
Me!cmbHideShow.Visible = False
-
End Sub
-
4
