Locking record on MS ACCESS append/insert

Question

I have split MSACCESS database with about four users.

In setting up the "master table" I made a bad choice but I will have to live that for a while. I did not use AutoNumber on the prime field simply used index no-dupicate

On the client programs using VBA,I acquire a reference number - "ENTRYNUM" by doing a Dmax on the relevant field in the table and adding 1.

There is just a few coding lines between acquiring the number and writing a record using it. BUT it appears that even in that tiny interval another user can start the same sequence and acquire the SAME reference number,

Oh WOE!
I cannot do any major changes since the system is full active and busy but there surely must be something simple to fix this or I am just being stupid and overlooking the obvious !

I have record locking on

A subset of the relevant VBA is as below and there is no other intervening code missed out

DoCmd.SetWarnings False
DoCmd.OpenQuery "EqCnt"
 DoCmd.SetWarnings True



DC = DMax("ENTRYNUM", "tbl_TRANSACTIONS")
If IsNull(DC) Then
ENVAL = 1
Else
ENVAL = DC + 1

End If


g_INPUTSOURCE = "ENTERED"
g_ROUTE = Me.RT_PICK
'a = Me.DELADDPC
G_PACKNOTE = Me.PACKNOTE
Me.DELADDPC.SetFocus
  Me.DELADDPC.Selected(0) = True


'b = Me.DELADDPC
g_DELADDR = Me.DELADDPC
g_STATUS = "QUEUED"


'MsgBox ("enval = " & ENVAL)
g_ENTERTIME = Now()



strSQL = "INSERT INTO tbl_TRANSACTIONS ( ENTRYNUM, _
OPERATOR, TRANSTATUS, INPUTSOURCE, CustomerID, SiteID, _
TRANERRFLAG, DELDATE, DELADDR,SPECINST, ROUTE, _
TAGFLAGS, OWNER, ENTERTIME,PACKNOTE,PRIORITY) SELECT '"

strSQL = strSQL & ENVAL
strSQL = strSQL & "' AS Expr1, '"

strSQL = strSQL & g_OPERATOR
strSQL = strSQL & "' AS Expr2, '"

strSQL = strSQL & g_STATUS
strSQL = strSQL & "' AS Expr3, '"

strSQL = strSQL & g_INPUTSOURCE
strSQL = strSQL & "' AS Expr4, '"

strSQL = strSQL & g_CUSTOMERID
strSQL = strSQL & "' AS Expr5, '"

strSQL = strSQL & g_SITEID
strSQL = strSQL & "' AS Expr6, '"

strSQL = strSQL & ig_TRANERRFLAG
strSQL = strSQL & "' AS Expr7, '"


strSQL = strSQL & g_DELDATE
strSQL = strSQL & "' AS Expr8, '"

strSQL = strSQL & g_DELADDR
strSQL = strSQL & "' AS Expr9, '"

strSQL = strSQL & g_SPECINST
strSQL = strSQL & "' AS Expr10, '"

strSQL = strSQL & g_ROUTE
strSQL = strSQL & "' AS Expr11, '"


strSQL = strSQL & g_TAGFLAGS
strSQL = strSQL & "' AS Expr12, '"

strSQL = strSQL & g_OWNER
strSQL = strSQL & "' AS Expr13, '"

strSQL = strSQL & g_ENTERTIME
strSQL = strSQL & "' AS Expr14, '"

strSQL = strSQL & G_PACKNOTE
strSQL = strSQL & "' AS Expr15, '"

strSQL = strSQL & g_PRIORITY
strSQL = strSQL & "' AS Expr16;"



'MsgBox (strSQL)
CurrentDb.Execute strSQL

I am still puzzled that the small interval should be so vulnerable but meanwhile I am looking for a quick fix to stop possible dupliaction of this sort - So far all the methods I have thought of imply possible the same vulnerability.

I also am wondering that, under the circumstances, if the Execute was the best way to carry this out. I have tried using

 With CurrentDb

        .Execute strSQL, dbFailOnError
        Debug.Print .RecordsAffected & " were added"

    End With

as a possible way to detect a problem.

Can I plead for a clearer head to have a think !

Locking record on MS ACCESS append/insert

0 Answers0