Lock Record for Editing on a Linked table

[khalid]

Okay Guys, the scenario is:
I have linked tables front end application running on network drive. The data is becoming huge and the users are becoming more, as a result the application become slower.

Now, It has been decided to get the necessary tables data in a local tables when application starts, update it locally and then update the original database on server finally.



Everything is done and working fine. The application is very fast and users are happy.

The problem starts here:
I want to lock the Edited record on main database when one user starts updating the same record, so if another user want to edit the same record he should get a message the record is editing by another user.

What is the right way to lock the record? considering that the user is updating his table locally.
Before updating the record locally what should I do on the main database for the same record on the server?

I am thinking that as soon as the user start updates, The on dirty event of the form there should be an update query which should update the yes/no field (RcdLck field) set to true in the main table and when another user start updating for the same record he/she should get message "The record is locked and being updating by another user."

Is there any best idea to achieve the same?
Thanks!

[jgelpi16]

Khalid - What if you created a single table that exists in your "main" back-end. Use it as a "tracking" table. When a user begins to edit a record append the recordID, and whatever other identifiers you need, to the table. When other users try to begin edits on records reference that table, if the recordID/PK field exists in that one table, prevent the user from editing. Would that work with your logic/structure?

[khalid]

Khalid - What if you created a single table that exists in your "main" back-end. Use it as a "tracking" table. When a user begins to edit a record append the recordID, and whatever other identifiers you need, to the table. When other users try to begin edits on records reference that table, if the recordID/PK field exists in that one table, prevent the user from editing. Would that work with your logic/structure?

This is also a good idea of creating a temp table on the back-end and record the editing table FK to that temp table and once editing complete delete the edited record from the temp table,
When I drag down the application, I found that its not possible only to get all the data for all the tables locally, coz there are APPEND issues, newly local generated IDs issues, Subform Data issues, and much more... I have to keep some tables connected and linked to the main database to avoid all these hurdles.

I am thinking to do:

Public Sub OpenCon()
   Set Connection = New ADODB.Connection
   ' Open a database connection using a client library cursor.
   With Connection
      .ConnectionString = "myConnectionString"
      .CursorLocation = adUseClient
      .Open
   End With
End Sub

and then call the OpenCon function to open the adLockPessimistic recordset.

Private Sub Command11_Click()
OpenCon '- Open the connection
        Dim rst As ADODB.Recordset
        Set rst = New ADODB.Recordset
        With rst
            .ActiveConnection = Connection
            .CursorType = adOpenKeyset
            .LockType = adLockPessimistic
            .Open "Select * FROM myTable Where ID = 4"
                !MyName = Me.MyName
                !MyNumber = Me.MyNumber
            .Update
            .Close
        End With
Connection.Close
Set Connection = Nothing
End Sub

What's your openion please?
Thank you for your consideration and the idea!

[jgelpi16]

This is also a good idea of creating a temp table on the back-end and record the editing table FK to that temp table and once editing complete delete the edited record from the temp table,
When I drag down the application, I found that its not possible only to get all the data for all the tables locally, coz there are APPEND issues, newly local generated IDs issues, Subform Data issues, and much more... I have to keep some tables connected and linked to the main database to avoid all these hurdles.

My knowledge of ADO recordsets and connections as mentioned are a bit limited. I can follow your logic, but I cannot provide much input on this method. User ajetrumpet and/or ruralguy would both be excellent sources for your issue.