Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    outside the rim

    Unanswered: The Mystery of Record Locking

    I'm hoping there is actually a fix and this doesn't become another "that's the way it is" scenario.

    Here’s the setup: Access 97, multi-user, split into Front end and Back end, both on the server. The database is set to default, “optimistic” record locking.

    3 tables, 1 “master” and two “child” - nothing out of the ordinary.
    1 query to put them together (the master table has a “department” code, linked to the department name in the child table. Ditto for a “Type Code”)

    2 forms.
    The first form is a “lookup” only. Both the form and query are set to “Snapshot” and “No Locks”. The form is set to no add, no delete, but allow edits (although all bound controls are locked and disabled, edits are required to allow the user to select a value from a combo box and enter search strings in unbound controls).
    The second form is an “Add/Edit” form. This form is unbound. It uses Recordsets to make the changes – it opens the table as dynaset, makes the change, updates it and then closes it, which occurs when the Update button is pressed – the table is not held open for more than the time to execute the code (< 1 second).

    Here’s what happens. User #1 opens the program, uses the Lookup. Meanwhile, User #2 goes in and makes a change to a record. User #1 tries to exit, but gets a “No Memory” error and is unable to close the Lookup form. User #2 closes the Add/Edit form and User #1 still can not close the Lookup form. User #2 completely exists the program, and then User #1 can finally close the Lookup form.

    It actually doesn’t require 2 users, 1 user can duplicate the problem by opening two instances on their PC.

    Although the reported error is consistently “No Memory” some late night attempts to figure out the problem revealed an underlying problem. If the problem is duplicated in development mode (full windows, full menus, etc.) the “Record locked by user ‘So and So’, can not save at this time” error precedes the “No Memory” error.

    I tried to fix by requery the recordset before closing it. I tried by requery the form just prior to closing it. Either the recold is remaining locked or the form is not recognizing the record was unlocked. Either way, the Lookup form is not intended to save data (and in fact the data can not be changed since all bound controls are disabled). I can trap the error – but it will not let the form close, trapped or not, using code or the close button.

    What can I do?

  2. #2
    Join Date
    Feb 2004
    When I do a "lookup" form in that scenario, I use unbound controls which I populate with data using Visual Basic and OpenRecordsets.

    The best method I've found for multiuser database applications is to create a workspace table on the backend which is essentially a replica of the table in question with an added field, which can be used to store a unique value, such as the User Name or the ComputerName (I use the ComputerName, myself).

    Then I set the RecordSource for the front end form to something like:
    SELECT * FROM wrkspcTable WHERE wrkspcTable.UniqueKey=ComputerName();

    * Note: the above example assumes that you have a function called ComputerName that retrieves the ComputerName from the Windows Registry.

    Next, in the form's Open event, I populate the workspace table with data from the main table along with the designated unique key value (i.e. the ComputerName).

    Finally, to save any of the work from the workspace table, I create a Command Button on the form which calls a Visual Basic function to pass back the changes to the main table.

    For example, let's say I have a BE-FE database, and in the back-end a table called JobLog, and a table called JobLog_Temp. The fields are defined thus:



    In the front end I have links to these two tables, and a form to view/edit the data, with a RecordSource: SELECT * FROM JobLog WHERE ComputerID=ComputerName();

    I open a form. The form's open event performs an insert query something like this:

    "INSERT INTO JobLog_Temp ( ComputerID, JobID, JobDescription, JobDate, JobNotes) SELECT ComputerName(), JobID, JobDescription, JobDate, JobNotes FROM JobLog WHERE JobID='Criteria';"

    Since the form only pulls back records which are unique to the ComputerName, the only records I can view/edit are those specific to my machine.

    I close the form. The form's close event deletes the old records from the JobLog Table with the matching JobID and then appends the records back from the workspace, matching only my machine.

    In a multi-user scenario, with two users opening the same form, the JobLog_Temp table might contain data something like this:

    ComputerID | JobID | JobDescription | JobDate | JobNotes
    Machine1 | 123 | Repair | 4/1/2004 | Took too long
    Machine2 | 124 | Repair | 4/1/2004 | Took even longer

    In this example, there are two records in the JobLog_Temp table. However, Machine1 can only view/edit the first record, and Machine2 can only view/edit the second record. Thus, there is no danger of a recordlock conflict.

    Even if both machine's were to view the same order, something like this:

    ComputerID | JobID | JobDescription | JobDate | JobNotes
    Machine1 | 123 | Repair | 4/1/2004 | Took too long
    Machine2 | 123 | Repair | 4/1/2004 | Took too long

    What transpires on one machine will not adversely affect activity on the other machine. This is because each machine is working with, in effect, a replica of the original recordset, with Machine1 only working with the Machine1-tagged records, and Machine2 only working with Machine2-tagged records. Thus there is no overlapping of efforts.

    The only risk of recordlocking can occur if both users close the form at the same time and both machines attempt to modify the matching records in the main table. This risk is surprisingly small, however.

    This is a somewhat simplified portrayal of the way I design my multi-user database apps in Access. The application of such is a little more complex, but I'm sure this gives a basic idea.

    So, all rambling aside, what was I trying to say again?... Oh yes. If you try this approach with your forms, you might find a better way to accomplish what you are trying to do with your current two forms, and you might altogether eliminate the crash-scenario you are currently facing. If you wish to discuss these methods in more detail, feel free to drop me a line in Email.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts