Access does not block records on Sharepoint site

[plihu]

I have a simple Access database, with one table: tblVerificationNr, and columns: ID, Verification, mID, getDate. I've stored it in Sharepoint folder (So Access database was created localy, and then just moved to SP folder).


Tables in Access database seems to not work properly - When multiple users opens Access database, they can edit the same record in same table in same time. In return database saves only last modification.
Have you faced this issue before? I wonder if it seems like bug in SP configuration or rather Access configuration? (My first guess was that it's some delays in transferring information to the sever, but everything seems to be working fine for e.g. Excel files - when one user opens it in edit mode - it becomes exclusive instantly - rest of users can have read-only mode)

[ranman256]

Why would 2 people edit the exact same record at the same time?

[plihu]

ok, going more into details:
Verification column have prefilled values from 3000-9999. My goal was to create VBA macro (in Excel) which will return Verification which has not been used before, AND which is +1 value gotten previously by any user.
To do that macro searches for first free value in mID column, place new mID there, puts current date in getDate column, and returns corresponding Verification. (code below)
Macro seems to be working well, when 1 user uses that (it returnes new unique Verification every time, even if you lauch it instantly), but when 2 users run that macro in (more less) same time, they got the same Verification number.
(So let's say that user with mID1234 launches macro. Macro is able to find free record, leave mID, date, and returns corresponding Verification (let's say 3100). But when second user launches macro very fast he gets the same verification number (and overwrites data left by user mID1234).

Option Explicit
Const sDBPath AsString="\\SP_Address\Mydatabase.accdb"
Public errDescription AsString

Sub Main()



    Dim ws As Worksheet
    Set ws = setSheetByCodename(ThisWorkbook,"shtTestDatabase")

    Dim sID AsString
    sID = ws.Range("rngID")

    Dim sVerificationNr AsString
    sVerificationNr = updateAndGetVerificationNumber(sID)

    Dim sOutputPhrase AsString
    sOutputPhrase ="mID: "& sID &", VerificationNr: "& sVerificationNr
    ws.Range("rngVerificationNr")= sOutputPhrase


EndSub

'main procedure - connects to Access Database in sDBPath, searches returns first free verification number (which has no value in Mid field)
Function updateAndGetVerificationNumber(sMyMID AsString)AsString

    Dim ErrorOccured AsBoolean
    Dim cn AsObject
    Dim rs AsObject
    Dim sCon AsString
    Dim sSelectSQL AsString
    Dim sUpateSQL AsString
    ErrorOccured =True

    OnErrorGoTo errQuit

    sSelectSQL ="SELECT TOP 1 tblVerificationNr.VerificationNumber, tblVerificationNr.Mid, tblVerificationNr.getDate FROM tblVerificationNr WHERE (((tblVerificationNr.[mID]) = '"& sMyMID &"')) ORDER BY tblVerificationNr.getDate DESC, tblVerificationNr.ID"
    sUpateSQL ="UPDATE (SELECT TOP 1 tblVerificationNr.VerificationNumber, tblVerificationNr.Mid, tblVerificationNr.getDate FROM tblVerificationNr WHERE (((IsNull([tblVerificationNr].[mID]))<>False)) ORDER BY tblVerificationNr.VerificationNumber)  AS a SET a.mID = '"& sMyMID &"', getDate = '"& Now &"'"


    sCon ="Provider=Microsoft.ACE.OLEDB.12.0; Data Source="& sDBPath
    Set cn = CreateObject("ADODB.Connection")
    cn.Open sCon


    'Added the following four lines
    Set rs = CreateObject("ADODB.RECORDSET")
    rs.ActiveConnection = cn

    cn.Execute sUpateSQL

    rs.Open sSelectSQL

    updateAndGetVerificationNumber = rs.Fields(0)

    ErrorOccured =False

errQuit:
    OnErrorResumeNext
    IfNot rs IsNothingThen rs.Close
    IfNot cn IsNothingThen cn.Close
    Set cn =Nothing
    OnErrorGoTo0

    If ErrorOccured Then
        updateAndGetVerificationNumber =""
        errDescription ="Verification number could not be found in database. Possible reasons:"& vbCrLf & _
            "- You dont' have access to Database"& vbCrLf & _
            "- Database is offline,"& vbCrLf & _
            "- Current Database is full."
    EndIf

EndFunction

[ssanfu]

Don't know how you don't see errors in the IDE for the code. (I deleted lines I didn't see errors on):

Option Explicit
Const sDBPath AsString="\\SP_Address\Mydatabase.accdb"
Public errDescription AsString

Sub Main()

    Dim sID AsString
    Dim sVerificationNr AsString
    Dim sOutputPhrase AsString

EndSub


Function updateAndGetVerificationNumber(sMyMID AsString)AsString

    Dim ErrorOccured AsBoolean
    Dim cn AsObject
    Dim rs AsObject
    Dim sCon AsString
    Dim sSelectSQL AsString
    Dim sUpateSQL AsString


    OnErrorGoTo errQuit


errQuit:
    OnErrorResumeNext

    OnErrorGoTo0

    If ErrorOccured Then

    EndIf

EndFunction

Any RED text is missing spaces.

Does the code execute without errors?

When multiple users opens Access database, they can edit the same record in same table in same time. In return database saves only last modification.

Is the Access dB split into a FE and a BE?
Does EACH user have a FE on their computer?