MS Access Lock Linked Tables with VBA

Question

I'm relatively new to Database design, but I've got between 4-6 years of programming experience (Java mostly). I was able to design a database that already works pretty well, even on a network. It's split in Back End and Front End, and each user has a copy of the FE. I use DMax to set the Subscriber's ID because each subscriber needs a unique, successive ID.

My only problem is that when users add a subscriber at the same time, it will only save the last addition. Is there a way to lock the linked table while one user is adding info (with VBA)?

If it is possible, can the other users verify if the table is locked?

Thank you!

EDIT:

This is the code that generates the number. It also checks if there are empty spaces in the succession (example, if there's 5,6,8 it would make it a 7).

            Dim counter As Integer
            counter = 1
            Dim validation As Boolean
            validation = False
            max = Nz(DMax("Numero", "Inscripciones"), 0) + 1
            While validation = False
                If Not IsNull(DLookup("[Numero]", "Inscripciones", "[Numero] = " & Nz([counter], 0))) Then
                    If (counter <= max - 1) Then
                        counter = counter + 1
                    Else
                        validation = True
                    End If
                Else
                    validation = True
                End If
            Wend

The record is not saved in any way until a button is pressed. This number generation happens 5 instructions before I save the record.

Answer 1

This doesn't directly answer your question. But finding the first missing number in a loop is very inefficient, especially if many consecutive numbers already exist.

E.g. if the table has numbers 1..200, your code would call DLookup 200 times. Making it much more probable that two instances are running this loop at the same time, and coming to the same result.

Instead, use a SQL query like this to find the first missing number:

SELECT MIN(i1.Numero + 1) AS Missing
FROM Inscripciones i1
  LEFT JOIN Inscripciones i2
    ON i2.Numero = i1.Numero+1
WHERE i2.Numero IS NULL

(adapted from here)

This will run very fast. Put this right before saving the record, and chances of collisions will be very small. And if an error occurs because of a duplicate number, catch the error and rerun the query.

Note that this query won't find a missing number 1. If that is a relevant case, check for it separately.