Ms Access lock table while inserting data -

Ms Access lock table while inserting data -

in access 2003 database, have "inscriptions" (subscription) database primary key on 2 fields idpersonnel (employee) , idsession. have made form user can select session (in listbox), 1 or more employee (another listbox) , suscribe them session using button, which, on vba side, first check there plenty room on session (defined "maxparticipants" field on "sessions" table, linked "inscriptions" table on idsession), insert info in "inscriptions" table

this working fine in single-user environnement, fails if 2 people want bring together employees on same session @ same time, have confirmation message between check , insertion. hence 2 users can select employees, confirmation message (at point both told there plenty room), resulting in having more people expected joined session. fortuneatly, if both users seek insert same employee(s) table, 1 duplicate error, insertion made if employees different.

on db engine, such sql server, utilize stored procedure lock table, check , insertion unlock table.

but not seem possible in ms access. possibilities in ms access prevent session having more maximum number of participants ? help appreciated.

one way accomplish goal insert in transaction, count participants session, , roll transaction if new total exceeds limit:

class="lang-vbs prettyprint-override">option compare database alternative explicit sub addparticipant() dim cdb dao.database, cws dao.workspace, _ qdf dao.querydef, rst dao.recordset ' test info 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 inscriptions (idpersonnel, idsession) " & _ "values (prmidpersonnel, prmidsession)") qdf!prmidpersonnel = idpersonneltoadd qdf!prmidsession = idsessiontoadd qdf.execute dbfailonerror set qdf = nil set qdf = cdb.createquerydef("", _ "parameters prmidsession long; " & _ "select " & _ "count(*) numparticipants, " & _ "first(maxparticipants) limit " & _ "from inscriptions inner bring together sessions " & _ "on inscriptions.idsession = sessions.idsession " & _ "where sessions.idsession = prmidsession") qdf!prmidsession = idsessiontoadd set rst = qdf.openrecordset(dbopensnapshot) if rst!numparticipants <= rst!limit cws.committrans debug.print "insert committed" else cws.rollback debug.print "insert rolled back" end if rst.close set rst = nil set qdf = nil set cws = nil set cdb = nil end sub

ms-access access-vba