MultiAccess Tables

Hello Gurus,

I have written a program that enters orders into a table called orders. The table is a visual foxpro table and is accessed on the network through an ODBC connection.

Recently I have been having problems with multiple people using the table. It seems when they try to perform an insert the program locks up and locks the table so it cannot be used exclusively till the server is rebooted. I believe this may be from multiple people trying an insert at the same time.

Here is the code I use to do the insert.

Public gCN As ADODB.Connection

OpenDB
' Insert the next Order record
strSQL = "Insert into order (ordnum,date,time,source,xfer,ccnum,expdate,routingnum,acctnum,checknum,remitamt,paymeth,exported,custno) values('" & UCase(strOrdNoOut) & "','" & strDate & "','" & strTime & "','" & UCase(cbSrc.List(cbSrc.ListIndex)) & "','" & cbXFer.Text & "','" & UCase(txtCCNum) & "','" & UCase(MaskExpDate) & "','" & UCase(txtRoutNum) & "','" & UCase(txtCheckAcctNum) & "','" & UCase(txtCheckNum) & "','" & UCase(txtRemitAmt) & "','" & UCase(bPayMethod) & "','N','" & UCase(strCustNoOut) & "')"

gCN.Execute strSQL

Here is the open DB sub
Public Sub OpenDB()
Set gCN = New ADODB.Connection ' Global Connection object.
gCN.ConnectionString = "DSN=OrderEntry"
gCN.Mode = adModeReadWrite
gCN.Open
End Sub

Could anyone tell me of a way I could set this up so that multiple users can do inserts at the same time? I do no think the method I am using is correct for a multiuser environment.

If anyone would like more info, just let me know.

Thanks,
Solturus

Check your record locking ... Is it table level? Page level? Row level? Ideally you should be using row level ... Also you should have error trapping such that you can go into a "holding pattern" (if you will) waiting for an opportunity to perform your write operation.