HOw to create multi user application using vb6 and access.

You do not need to use linked tables.  This is a good idea in an Access application, but not need in VB6.

Here are some comments which I hope will help you:~)

Your code should work for maintaining tables. But when creating transactions it may pay to change your code.  There are very few good examples showing how to create multi-users apps using sql statements. Also it depends if you are using ADO or DAO.

Here is the approach that I would go for:

1) When you create your app use a starter exe to shell to your real app say your starter exe could be called MyApp.exe. When you compile your real app give is a name like MyApp10.exe, then next time MyApp11.exe, etc.  Each time you compile you edit the text file MyApp.TXT with the name of the next EXE file.

When you have hundreds of users all using the same exe file, you can never get all of the users to quit, so relasing updates is very hard. So give each user a short-cut to the start exe, say MyApp.exe, which is on a network drive. In this way you can relase updates without needing to ask all users to exit.

2) Table updates.

You already have a good approach, but make sure you put all updates in a transaction block that looks a bit like this:

Global ADO as New zADO ' place all databse functions in a class module, in this way your tabel updates all look and do the same. The following is not VB code but is simplefied to show the logic used for multiuser updates.


OK = ADO.OpenConnectionOK(CN)
lTry = 0

Do ' Start of update look

    OK = False ' set flag

    ADO.BeginTransaction CN ' start a databas transaction
   
    OK = ADO.UpdatesRunOK(CN) ' Run the database updates

     If OK Then
         OK = ADO.CommitTransactionOK(CN)
     End If
     
     If OK Then
         Exit Do
     End If

     OK  = ADO.RollBackTranbsaction

     lTry = lTry + 1
     If lTry > 3 Then
         OK =   MsgBox("Unable to save data "+ADO.getLastError(CN), vbExclamation+vbRetryCancel, "Error")
          If OK <> vbRetry Then
            OK = False
            Exit Do ' the updates have failed
           End If
     End If  
   
     Sleep 2000 ' wait for 2 seconds

Loop

ADO.CloseConnection CN

If Not OK Then
    MsgBox "Unable to save data "+ADO.getLastError(CN), vbExclamation, "Error"
End If

The key points about the update loop:

a) It will retry if the update failed. (Say sombody is editing  a table's structrue no updates can take place.)
b) All updates are performed using the same code. Making it easy to switch to SQL Server or MySQL, etc.
c) The connection is only open while the data is being updated. Allowing database maintenance operations.

3) When creating transaction you need to have one of 2 approaches

3.1) Creating transactions

When you create a transaction you can insert records into a table, you need to pickup the Identity (Autonumber or counter) for the new record. How you get the identity depends on which version of access or SQL Server you are using.  But Select @@Ideintiy normally works after you ran the Insert SQL statement. You then add sub-transactions using the identity for the header record so you do not end up with any duplicate records. Again all done using a transaction loop like the one above.

3.2) Locking records

Using ADO you do not lock a record until you move to it within a recordset.  SO you cannot use raw SQL for this function.

Here is a function that locks a record:

sql = "Select ........ where [MyID]=1"
OK = ADO.OpenConnection(CN)
ADO.BeginTransaction
OK = ADO.OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)
OK = ADO.MoveWaitOK(RS)
RS("MyField") = NewValue
OK = ADO.UpdateRS(RS)
OK = ADO.CommisTransactionOK(CN)

Function MoveFirstWaitOK(RS As ADODB.Recordset, Optional psngTimeOutSeconds As Single = 20) As Boolean

' wait until the first record in a recordset is ok to amend

Dim dtWait As Date
Dim dtWait2 As Date
Dim OK As Boolean
Const OneSecond As Double = 1# / (1440 * 60#)
dtWait = Now + OneSecond * psngTimeOutSeconds
OK = False
Do Until Now > dtWait
    ' lock the record
    OK = MoveFirstOK(RS)
    If OK Then Exit Do
    dtWait2 = Now + OneSecond * 2
    OK = False
    Do Until Now > dtWait2
        DoEvents
    Loop
Loop
MoveFirstWaitOK = OK
End Function

Public Function MoveFirstOK(RS As ADODB.Recordset) As Boolean

' this is used in a pesemistically locked server-side recordset

On Error GoTo ErrorTrap
RS.MoveFirst
MoveFirstOK = True
Exit Function

ErrorTrap:
MoveFirstOK = False
End Function

Public Function OpenRSOK(CN As ADODB.Connection, RS As ADODB.Recordset, SQL As String, Optional CursorType As ADODB.CursorTypeEnum = adOpenForwardOnly, Optional LockType As LockTypeEnum = adLockReadOnly, Optional CommandType As ADODB.CommandTypeEnum = adCmdText, Optional CursorLocation As ADODB.CursorLocationEnum = adUseServer, Optional AppendOnly As Boolean = False) As Boolean

' Opens any type of recordset return true is OK

' to open for locking updates
'OK = OpenRSOK(CN, RS, SQL, adOpenDynamic, adLockPessimistic, adCmdText, adUseServer)

' to open for fast readonly
' but don't use client with large tables
'OK = OpenRSOK(CN, RS, SQ, adOpenStatic + adOpenForwardOnly, adLockReadOnly, adCmdText, adUseClient)

Set RS = New ADODB.Recordset

On Error Resume Next
Err.Clear

RS.CursorLocation = CursorLocation
If AppendOnly Then
    ' **** Warning only works with Access
    ' but even then it don't work.
    ' so it is best to use some sql a bit like this
    ' where there will never be a record zero
    ' SQL = "Select * from [myTable] Where [MyID]=0
    ' and open the table for normal updates as appendonly does not seem to work
    ' as it says in the manual
    RS.Properties("Append-Only Rowset") = True
End If
RS.Open SQL, CN, CursorType, LockType, CommandType

If Err.Number <> 0 Then
    Set RS = Nothing
    OpenRSOK = False
Else
    OpenRSOK = True
End If

End Function

Function UpdateOK(RS As ADODB.Recordset, Optional pbUpdateBatch As Boolean = False) As Boolean
' Recordset update
' trap any errors with an update statement
ErrN = 0
ErrD = ""
Err.Clear
If Not IDE Then ' when testing you want to see all errors
    On Error Resume Next
End If
If pbUpdateBatch Then
    RS.UpdateBatch
Else
    RS.Update
End If
If Err.Number = 0 Then
    UpdateOK = True
Else
    ErrN = Err.Number
    ErrD = Err.Description
    UpdateOK = False
End If

End Function

Another cool way to handle table updates is to use disconnected recordsets.

So when you app starts you can create some dummy disconnected recorsets with the specs for your key tables.
In this way you can put the data into a clone of your dummy recordset and pass the clonde to a generate function that will update any table in your system, thereby cutting down the code needed to handle multi-user senarios to just one function. Also making it easy to debug as the code only needs to be written once.

Also when you have a disconnected recordset you can prepare the data off-line then attach it to a connecttion and table , then run an update.  The problem with this is that SQL server and access sometimes screws up and seems to be unable to handle large batches (it thinks it has worked, does not give any errors, but fails to update some records). So it is better to generate the sql statement and update each record.

inthedark is right
thes links could also help you
https://www.experts-exchange.com/questions/20033347/Mutli-user-access-to-a-MS-Access-database.html
https://www.experts-exchange.com/questions/20762779/Multiuser-VB-Application-How.html