CODEWITHSUNDEEP
ms-accessvba
phramusca
phramusca

Reputation: 123

Ms Access lock table while inserting data

In a Access 2003 database, I have an "Inscriptions" (subscription) database with a primary key on 2 fields idPersonnel (employee) and idSession. I have made a form so that user can select a session (in a listbox), then one or more employee (another listbox) and suscribe them to that session by using a button, which, on VBA side, first check that there is enough room on the session (defined by "MaxParticipants" field on "Sessions" table, linked to "Inscriptions" table on idSession), then insert data in "Inscriptions" table

This is working fine in a single-user environnement, but fails if 2 people want to join some employees on the same session at the same time, as I have a confirmation message between check and insertion. Therefore 2 users can select employees, get the confirmation message (at this point both are told there is enough room), resulting in having more people than expected joined to the session. Fortuneatly, if both users try to insert the same employee(s) to that table, one will get a duplicate error, but insertion will be made if employees are different.

On another DB engine, such as SQL server, I would use a stored procedure that would lock the table, do the check and the insertion then unlock the table.

But it does not seem to be possible in MS Access. What are the possibilities in MS Access to prevent a session from having more than maximum number of participants ? Any help is appreciated.

Upvotes: 1

Views: 2927

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123849

One way to accomplish your goal would be to do the INSERT in a transaction, count the participants for that session, and roll back the transaction if the new total exceeds the limit:

Option Compare Database
Option Explicit

Sub AddParticipant()
    Dim cdb As DAO.Database, cws As DAO.Workspace, _
            qdf As DAO.QueryDef, rst As DAO.Recordset

    ' test data
    Const idPersonnelToAdd = 4
    Const idSessionToAdd = 2

    Set cdb = CurrentDb
    Set cws = DBEngine.Workspaces(0)
    cws.BeginTrans
    Set qdf = cdb.CreateQueryDef("", _
            "PARAMETERS prmIdPersonnel Long, prmIdSession Long; " & _
            "INSERT INTO Inscriptions (idPersonnel, idSession) " & _
            "VALUES (prmIdPersonnel, prmIdSession)")
    qdf!prmIdPersonnel = idPersonnelToAdd
    qdf!prmIdSession = idSessionToAdd
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set qdf = cdb.CreateQueryDef("", _
            "PARAMETERS prmIdSession Long; " & _
            "SELECT " & _
                "Count(*) AS NumParticipants, " & _
                "First(MaxParticipants) AS Limit " & _
            "FROM Inscriptions INNER JOIN Sessions " & _
                "ON Inscriptions.idSession = Sessions.idSession " & _
            "WHERE Sessions.idSession = prmIdSession")
    qdf!prmIdSession = idSessionToAdd
    Set rst = qdf.OpenRecordset(dbOpenSnapshot)
    If rst!NumParticipants <= rst!Limit Then
        cws.CommitTrans
        Debug.Print "INSERT committed"
    Else
        cws.Rollback
        Debug.Print "INSERT rolled back"
    End If
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set cws = Nothing
    Set cdb = Nothing
End Sub

Upvotes: 2

Related Questions