Multi user support for Access db

Question

What I have is a database that records customers and suppliers with their addresses in 3 tables, tblCustomers, tblSuppliers and tblAddresses using Access.

Such as:

[tblCustomers] <== [tblAddresses] ==> [tblSuppliers]

For example, when I enter a customer's detail, after checked all the required fields I'll insert a new record into [tblAddresses] then insert customer detail into [tblCustomers] where [tblCustomers].AddressFK = [tblAddresses.AddressPK].

The problem is I get tblAddresses.AddressPK by selecting the latest record and this does not handle multi-user. When 2 or more persons are creating new customers or suppliers at the same time it links to the wrong address. e.g.

1. creating a new customer
2. creating a supplier

when (1) and (2) save the record at the same time, they both creating new address record in tblAddresses and I don't know which belongs to which.

Initially, I thought since both customer and supplier share the same address fields I could put their addresses in the same table but now it sounds like a bad idea.

Does anyone have any idea how to solve this?

I'm open to anything, at worse I'll redesign the database.

PS: I forgot to mentioned that I'm using Access built in VBA not .NET.

Update: Thanks for everyone's inputs, I've figured out a different way to manage this. @@ identity sounds like a good idea but I have not used it (I've changed data entry
procedure instead) so I'm not sure if I should mark it as an answer or not. But thanks,
I learned something new. ^^

Answer 1

@LocTrang: In reality A customer might have more than one address (billing, delivery, alternative delivery). The relationship will look like

1. [Customer]-1:1----0:m[Address] > Customer-Address cannot exists without a corresponding customer details.

Same goes for suppliers. A supplier might have more than one address (billing, warehouse) the relationship between supplier and address table will look the same as customer vs address.

Usually customer and supplier will have their own address table but if you would like to keep both addresses in one table you will design your address table like this

1. Address_ID > Primary1
2. Owner_ID > Primary2 (*)
3. Address_type >( can be primary key from address_type table)
4. Address_line1
5. Address_line2
6. City
7. ... rest of he columns

this design will prevent data inconsistency in your database. (*) However: to achieve this, the owner_id must be unique throughout your database or at least between customer and supplier table. Since the Autonumber field in both customer and supplier table will start from 1 you cannot use it as owner_id as its not unique between those two tables. It is best to use "GUID/uuid" as unique_id in your tables or construct a custom unique field like supplier_ID starts with "S_" and customer_id starts with C_

Optionally you might create another table called Address_type as such:

1. Address_type_id > Primary key, Autonumber
2. Address_type
3. Description

and stick it to the address table.

now to answer your question: if you are using Access forms to input your customer details you can create a subform for address and link the master and child form via LinkMasterField and LinkChildField. This way you create customer record first and then when you move to the address subform newly created customer_Id is pre-populated for you. like her: (C_ID being unique customer/owner_id)

use the after insert event to auto update your customer unique id

Private Sub Form_AfterInsert()
    Me.txt_c_id.value = "C_" & Me.txt_Customer_id.value
End Sub

if you are using VBA to enter customer details, try to encapsulate your data execution by transactions. this way you are always safe regardless how many other users are creating records:

vba code:

Private Sub btn_add_new_Click()
    Dim MyDB As DAO.Database
    Dim MyRs As DAO.Recordset

    Set MyDB = CurrentDb

    Dim Last_ID As Long

    Dim SQL_GET As String
    Dim SQL_SET As String

    DBEngine.BeginTrans
        On Error GoTo ERROR_TRANS:
        SQL_SET = "INSERT INTO TBL_Customer(C_name,C_contact) VALUES('Second Customer','Second Contact')"
        MyDB.Execute SQL_SET, dbFailOnError

        SQL_GET = "SELECT MAX(Customer_id) AS LAST_ID FROM TBl_Customer"
        Set MyRs = MyDB.OpenRecordset(SQL_GET)
        Last_ID = Nz(MyRs("LAST_ID"), 0)

        'Since access does not provide triggers we update the customer unique id manually
        If Not Last_ID = 0 Then
            SQL_SET = "UPDATE TBL_Customer SET C_ID = 'C_" & Last_ID & "' WHERE TBL_Customer.Customer_id = " & Last_ID
            MyDB.Execute SQL_SET, dbFailOnError
        End If

        'Now add the address record via vba
        SQL_SET = "INSERT INTO TBL_Address(Owner_ID, type, Address_line1, Address_line2, city) VALUES('C_" & Last_ID & "','Billing','01 Main Street','Flat 2','London');"
        MyDB.Execute SQL_SET, dbFailOnError
    DBEngine.CommitTrans

    MsgBox "Customer inserted. New customer ID = C_" & Last_ID, vbInformation, "Success"
EXIT_ROUTINE:
    On Error Resume Next
    Set MyDB = Nothing
    Set MyRs = Nothing
    Exit Sub
ERROR_TRANS:
    On Error Resume Next
    DBEngine.Rollback
    MsgBox "Sorry there was a problem while creating new customer record", vbExclamation, "Unable to insert"
    Err.Clear
    GoTo EXIT_ROUTINE
End Sub

I hope, you have understood and will make better use of this answer.