Access Database for multiple Users

[zul336]

Hi All,



I recently created a database which is running perfectly fine on Stand alone environment and behaving exactly the way i want it to. I created this database for a team of about 50 people who will be using it simultaneously.

I recently test run the database with 15 people using it the way they should be and came across few problems. It keep giving me "Record locked by other users" error. I have tried my best to resolve it. Below are the steps which I have already taken.

1. Changed the record type to Edit Records.

2. Splited the database and everyone is using it from own desktop copy.

3. Re-designed the database in such a way they conflict of 2 people working on same record is down to zero.

All this have helped reduced the error in term of how often its happening but its still happening. A little back ground of what database is actually doing. We got this huge Master Table which is storing all the records. each record is assign to each user. In form design each user is clicking on their own name and via macro its displaying records one by one associated to them. The main field which is showing the error is when they select the result type from a Combo box which is saving it into Result field in master Copy. All users are working on records directly from Master Table. After taking all above mention steps if we wait for 10 to 30 seconds after clicking ok on record we can save it but its not looking good on presentation when i will be presenting database to management.

I read into ADO Database record optimization levels but even though I tried my best to find how to setup ADO database type I am still nowhere with it. Can anyone guide me how to setup ADO from scratch or how to fix above record lock issues.

It will be a very big help and I will consider that as a X-Max present from all of you.

Regards

Zul

[orange]

This is the reference many suggest for multi user access.
http://www.kallal.ca/Articles/split/index.htm

Here's another from FMS
http://www.fmsinc.com/tpapers/faster/

[RuralGuy]

AFAIK Access does not actually do record locking by record. It locks the block of memory (buffer) where the record resides which will almost certainly include surrounding records.

[zul336]

AFAIK Access does not actually do record locking by record. It locks the block of memory (buffer) where the record resides which will almost certainly include surrounding records.

Is their a way around my problem?

[RuralGuy]

With native Access I don't think so. Moving to an SQL backend would do it but it is not cheap. Implementing your own retries when locked would also work as long as you are sure no two users are working on the same record.

[orange]

I found this code from one of the Access lists. I haven't tried it, but it may be helpful as far as testing if the lock is still on or released.

<< So it seems that information is available, must be in the LDB file
- does anyone know how to get at it?>>

The information is not available in the LDB file. The LDB file is simply a list of users currently in the database and in all cases, that may not be true.

To really understand who is in the DB, you must look at the user locks placed on the .LDB file.

<<3218 is a record locking error, perhaps 3188 as well. I can trap the error, but I don't know how to identify who is locking the record. >>

The way to do it is to parse the message that Access generates. Below is the code to do that.

Jim.

Function IsLocked(rs As Recordset, UserName As String, MachineName As
String)
' Accepts: a recordset and two string variables ' Purpose: determines if the current record in the recordset is locked,
' and if so who has it locked.
' Returns: True if current record is locked (and sets UserName
' and MachineName to the user with the lock). False if the
' record isn't locked.
' From: Building Applications Chapter 12

Dim ErrorString As String
Dim MachineNameStart As Integer

IsLocked = False
On Error GoTo IsLockedError
rs.Edit 'Try to edit the current record in the
recordset.
rs.MoveNext
rs.MovePrevious
Exit Function 'No error, so return False.

IsLockedError:
If Err = 3260 Then 'Record is locked -- parse error string.
ErrorString = Error$
UserName = Mid$(ErrorString, 44, InStr(44, ErrorString, "'") - 44)
If UserName = "" Then UserName = "(unknown)"
MachineNameStart = InStr(43, ErrorString, " on machine ") + 13
MachineName = Mid$(ErrorString, MachineNameStart, Len(ErrorString) - MachineNameStart - 1)
If MachineName = "" Then MachineName = "(unknown)"
IsLocked = True
End If

Exit Function

End Function

Also here's a link discussing Record level vs Page level locking (acc2000 jet 4.0)
http://msdn.microsoft.com/en-us/libr...ice.10%29.aspx

Here's a reference for ADO
http://support.microsoft.com/kb/306435

More ADO info with sample code
http://www.databasedev.co.uk/multi-u...d-locking.html

[zul336]

With native Access I don't think so. Moving to an SQL backend would do it but it is not cheap. Implementing your own retries when locked would also work as long as you are sure no two users are working on the same record.

" Implementing your own retries" How I will do that, I am not very good with this aspect of Access.

[zul336]

Thanks Orange,

I will look into codes tomorrow as now on Christmas eve family will not be very happy if i am still doing work

I will try it tomorrow and will keep you posted that how it went.

[RuralGuy]

Good links Orange. It looks like ADO is the way to begin. Merry Christmas to you both.

[orange]

And Merry Christmas to you both, also.
Guess it's time to shut this down here as well - it is Christmas eve.