Locking Table in Multi User 5

[zevw]

I have multi users (on different machines on the network) accessing a certain table to read a record. I want to lock the table when 1 user reads the table, so the other user gets a message that the table is locked, and will not be able to read that record until the table is released.

Is there a way how to write this in code?

 

[CautionMP]

zevw,
Yes, if you are opening a table using a DAO recordset in code:

Set r = CurrentDB.OpenReocrdset([i]SomeTable[/i], dbOpenTable , [b]dbDenyRead[/b])

You might also look a using a standard query and setting the Record Locks to All Records using the query property box.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[zevw]

Will this work with linked Tables, I don't think so!

 

[CautionMP]

zevw,
Option 1: I don't know.
Option 2: Yes.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT+08:00) Singapore

 

[zevw]

CMP

Thanks, I don't know if you are saying "Yes" or "You don't know". Actually I have tried something similar to this in the past and it did not work. I will test it again in the next day and let you know.

All the best

 

[payback]

I use a locking table and appropriate code to -
1. Flag the record as locked when the first user accesses it
2. Keep it "locked" and let anyone else who tries to access it know that they can not
3. When the first user as finshed with that record, "unlock" it

I know that Access etc. has its own locking routines, but I have found this particularly suitable when I have a form and subform where I want to control all. It also gets around the situation where a second user can get into a record and only finds that they can not save it after changes because someone else it locked.

I think the code is already posted somewhere in Tek-Tips already so a search should find it.

 

[zevw]

CMP

I Got to test it right now

and I got the

Error 3219: Invalid Operation

This is my line of code

Set tblBatch = CurrentDb.OpenRecordset("BatchNos", dbOpenTable, dbDenyRead)

Please note: The "BatchNos" table is a linked table

 

[CautionMP]

zevw,
A linked table is more like a query than a table so I don't think it will work, but you just found that out.

Create a query based on the linked table [tt](SELECT * FROM BatchNos;[/tt]). With the query in design view open the Query Properties dialog, change Record Locks from No Locks to All Records. It won't prevent other users from viewing the records but it will keep them from changing them.

CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[zevw]

Are you suggesting creating a recordset based on the query? Is there a way how to know if the query is now locked by another user?

Well If we are at this point how does a AutoNumber field work, 2 users will not get the same even if one is accessing the table. I don't believe that Access will crash because 2 users are trying to create the same autonumber at the same second. How does that work, I would like to implement that logic for myself?

 

[CautionMP]

zevw,
No, just suggesting using a built in query property to loak all the records in the table.

When you said Autonumber it reminded me of something. Take a look at this FAQ, it covers how to lock a table for viewing and just happens to be about autonumbers (not to mention it's written by someone who knows a lot more about Access than I do.)

faq700-184

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[zevw]

I know this FAQ, I tried the code and again it seems not to be working on a linked tabled. Actually I don't understand how you have a multi user environment without a linked table. However the code did not work.

I actually sent an email to Michael and he said it has been so long that he does not remember anymore what I he was doing.

In any case if you have any other ideas I am open for suggestions.

Thansk for all your help!!!!

 

[CautionMP]

zevw,
Back to your original question. As near as I can tell Access won't let you 'lock' a table for viewing, but you can lock the database the table resides in. Here is a routine, based roughly on MichaelRed's FAQ, that uses ADO. For this to work you will need to make sure that you have a reference to one of the Microsoft ActiveX Data Objects M.n libraries or you will need to change the connection and recordset declarations to generic (late bound) objects.

Be sure to update the database path and SQL statement in bold below to match your actual data.

Sub Exclusive_Link()
On Error Resume Next
Dim adoConn As ADODB.Connection
Dim adoRS As ADODB.Recordset
Dim lngRetries As Long

Set adoConn = New ADODB.Connection

Exclusive_Link_OpenConn:
lngRetries = lngRetries + 1
Debug.Print "loop: " & lngRetries
With adoConn
  .Provider = "Microsoft.Jet.OLEDB.4.0"
  .Properties("User ID") = "Admin"
  .Properties("Data Source") = [b]"C:\TestDB.mdb"[/b]
  'This will open the DB for Exclusive access
  .Properties("Mode") = [b]adModeShareExclusive[/b]
  .Properties("Locale Identifier") = "1033"
  .Properties("Persist Security Info") = False
  .Properties("Jet OLEDB:System database") = "C:\SYSTEM.MDW"
  .Properties("Jet OLEDB:Engine Type") = 5
  .Properties("Jet OLEDB:Database Locking Mode") = 1
  .Properties("Jet OLEDB:Global Partial Bulk Ops") = 2
  .Properties("Jet OLEDB:Global Bulk Transactions") = 1
  .Properties("Jet OLEDB:Create System Database") = False
  .Properties("Jet OLEDB:Encrypt Database") = False
  .Properties("Jet OLEDB:Don't Copy Locale on Compact") = False
  .Properties("Jet OLEDB:Compact Without Replica Repair") = False
  .Properties("Jet OLEDB:SFP") = False
  .Open
End With

'Check to see if there was an error opening the connection
If Err.Number <> 0 Then
  'check if there has been less than 50 attempts
  If lngRetries < 50 Then
    'Clear the errors and try again
    Err.Clear
    adoConn.Errors.Clear
    GoTo Exclusive_Link_OpenConn
  Else
    MsgBox "Could not establish Exclusive link"
    GoTo Exclusive_Link_Exit
  End If
End If

'If you get here the connection was succesful
Set adoRS = New ADODB.Recordset
With adoRS
  .Open [b]"SELECT * FROM Table1;"[/b], adoConn, adOpenDynamic, adLockOptimistic
  'Create a new record
  .AddNew
  'Output the New autonumber to the immediate window
  Debug.Print .Fields("ID")
  'Save the newly created record
  .Update
End With

Exclusive_Link_Exit:
On Error Resume Next
adoRS.Close
Set adoRS = Nothing
adoConn.Close
Set adoConn = Nothing
End Sub

Hope this helps,
CMP

P.S. This has nothing to do with an Access Linked Table, the recordset opened with this routine exists in code only.

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[zevw]

I know it was quiet long since you responded, I apologize for this delay, I was very busy with other projects. I really appreciate all your time and effort to try to help me.

Basically after reading your code and your solution, I think this would not work in the context of my environment.
What I am trying to do is, supply each user with the next highest number in a Purchase Order Database. To do that, I would have to Query the P.O. Database. If I will lock the database and another user is in middle of modifying a P.O. the Database would be locked until the process is done.

I am thinking out loud. Maybe I will use your solution with a hitch. Create a Database (Lets Call it MaxPO.mdb) with a table that contains the highest P.O. Number. When I start my process I will first lock this DATABASE, and go to my Purchase Order Database to get the highest number. Another user might try the same thing but will not be able to, because the database is locked and will get an error message, or it will retry until the DATABASE is unlocked. If the number is less than the number in the MaxPO.mdb Database than I will requery the P.O. Database again.

How does that sound?

 

[CautionMP]

zevw,
No worries on the delay, we all have bills to pay.

I'm pretty sure that is the concept behind NichaelRed's FAQ and the adapted code that I posted, but we never stated that the unique number table resides in a different database then the one you are using. This is the only way to establish an exclusive connection to an Access database.

If you make the following change and update the SQL statement

...
  .Properties("Data Source") = "C:\[b]MaxPO.mdb[/b]"
...
With adoRS
  .Open "[b]SELECT * FROM Table1;[/b]", adoConn, adOpenDynamic, adLockOptimistic
...

And make the routine return the PO number I think you will have a good starting place.

Hope this helps,
CMP

P.S. You could also look at using a flat file to house the PO counter???

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[MichaelRed]

CautionMP

With adoRS
  .Open "SELECT * FROM Table1;", adoConn, adOpenDynamic, adLockOptimistic
  'Create a new record
  .AddNew
  'Output the New autonumber to the immediate window
  Debug.Print .Fields("ID")
  'Save the newly created record
  .Update
End With

MichaelRed

 

[MichaelRed]

Sorry about the early exit from the above. for Caution MP, I like the concept of updating the rouuutine to the "new' features in Access. I specifically when I wrote the original routine, did not save a new record to the table. Doing so cause the rtable itself to 'grow' which i considered a BAD thing. Further, I do not see where your routine actually selects the latest / highest value from the table or performs any 'calculation' of a new/next value. Finally, If you are going to update/improve on a venerable procedure, you could do others a favor and post a more complete version with some explination as a faq?




MichaelRed

 

[zevw]

CMP

Thank you so much, just got to test it, and it works man it works!!!!. I actually created 2 instances on the same machine and started one paused it, and ran the second and Boom it gives me the message "Could not establish Exclusive link". I appreciate all your effort and patience in helping me.

Michael

Actually I got to see your FAQ before I posted this question, and emailed you on it and we were building our solution on your FAQ.

So Thanks to both of you for your contributions.

Hope to reciprocate one day!

Good Luck

 

[JerryKlmns]

Hello everybody!

I have a system ready to go for UAT implementing the FAQ discussed previously. To be honest, I took it for granted that the solution was working. Now I 'm in distress. I can't diggest the working result right now, I' ll have to read everything carefully, for third time.

zevw
Would you kindly summarize the working result just to help me diggest it? No need for code just the logic. And to make sure, you have a user reading data from the linked table and someone else adding a new record at that time to the linked table. Of course a third trying to add a new record is forced to wait or retry.

The funny thing is that when I bumped into this thread the Project Manager of the system walked by me!

 

[zevw]

In Summary:

I have a program that is used by multi users a P.O. System when a user would create a new P.O. the program would get the highest number and give the user the next number (highest number plus 1). Now I had conflicts where 2 users would get the same number or batch number for the P.O.

By the wisdom of these wonderful people, I created a seperate external database (not linked) which keeps track of the highest number. When a user queries the next number, it locks the database and increments that number by 1. Once the database / table was updated, it releases and unlocks the database and the next user can get the next number. I have not had a conflict since.

Hope this helps, enjoy the journey it took me some time and I keep learning.

 

[JerryKlmns]

So, a third mdb is the way to go.

Thanx, now I can go home and build that solution during my trip and set it up on Monday.

Enjoy your weekend everybody.