Table Lock Errors in Multi-User App

[Ian W]

I have an app that inserts a new record into one of our backend tables.

As the system as been getting a good thrashing over the last few days I have run into a number of table locked errors from some of my access apps telling me that the table im trying to write to is locked by admin on my .net app machine.

I am trying to figure out if there is a way around this? Backend db's are being used by roughly 25 users with numerous Access apps reading and writing to the tables.

My .Net app begins by opening up the table like so...

If Not SerialConnect.State = ConnectionState.Open Then
            SerialConnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
           & "J:\Db_Backend\sd.mdb ;User Id=admin;Password=;"
            Try
                SerialConnect.Open()
                SerialCommand.Connection = SerialConnect
            Catch ex As Exception
                Error_Handle("Open_Databases (Serial)", ex.Message)
                Exit Sub
            End Try
        End If

When I insert a new record I use...

        With SerialReader 'Datareader to pull values from another table.

            SerialCommand.CommandText = "INSERT INTO Serial_SAT_Audit_Trail (SAT_PSN_IX, SAT_Serial, SAT_Client, SAT_PSN_PIN, SAT_Description, SAT_Batch, " _
            & "SAT_Kit_Batch, SAT_PSS_Text, SAT_Event, SAT_BNA_IX, SAT_Trace, SAT_Date_Time, SAT_Station, SAT_Action_By, SAT_Extended)" _
            & "VALUES (" & !PSN_IX & ",'" & !PSN_Serial & "','" & Client & "','" & Pin & "','" & Description & "'," & !PSN_Batch_Number & "," & !PSN_Kit_Batch & ", " _
            & "'" & Status_Text(!PSN_PSS_IX) & "','" & Event_Text(event_ix) & "'," & !PSN_BNA_IX & ",'" & !PSN_Stock_Trace & "','" & Date.Now & "','" & Get_Station_Name() & "','" & CurrentUser & "', '" & extended & "')"

        End With

        SerialReader.Close()

        Try
            SerialCommand.ExecuteNonQuery()
        Catch ex As Exception
            Write_Audit_Trail = False
            Error_Handle("Write_Audit_Trail (Write Record)", ex.Message)
            Exit Function
        End Try

Is there something that I could alter in my code that would prevent me from getting record lock errors.

I did think about using Datasets or Datatables but i'm not sure if this would solve my issues?

Any help much appreciated.