Need help with runtime error 3211. Also have design question. 1

[Bill3129]

I continue to receive Run-time error 3211. The message reads: "The database engine could not lock table 'Current Table' because it is already in use by another person or process".

The table in question is created from the On Click event of a Command Button. The table is populated by an append query that sorts transactions by Post Date. The table assigns a Transaction Number to the appended data.

Private Sub Log_Click()
Dim db As DAO.Database
Dim tblcurr As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tblcurr = db.CreateTableDef("Current Transactions")
Set fld = tblcurr.CreateField("TRANS_NUMBER")

fld.Type = dbLong
fld.Attributes = dbAutoIncrField
tblcurr.Fields.Append fld

tblcurr.Fields("TRANS_NUMBER").DefaultValue = "GenUniqueID()"

With tblcurr

.Fields.Append .CreateField("DATE CREATED", dbDate)
.Fields("DATE CREATED").DefaultValue = "Date()"
.Fields.Append .CreateField("CARD NUMBER", dbText)
.Fields.Append .CreateField("CH LAST NAME", dbText)
.Fields.Append .CreateField("CH FIRST NAME", dbText)
.Fields.Append .CreateField("CYCLE END DATE", dbDate)
.Fields.Append .CreateField("POST DATE", dbDate)
.Fields.Append .CreateField("STTLMT AMT", dbCurrency)
.Fields.Append .CreateField("TYPE", dbText)
.Fields("TYPE").DefaultValue = "Transaction"
.Fields.Append .CreateField("REF NUMBER", dbText)
.Fields.Append .CreateField("MERCHANT ALIAS", dbText)
.Fields.Append .CreateField("LOG", dbText)
.Fields("LOG").DefaultValue = "_NO"

End With

db.TableDefs.Append tblcurr

DoCmd.SetWarnings False
DoCmd.OpenQuery "Find Current Transactions2"
DoCmd.OpenForm "Current Transactions"
DoCmd.Close acForm, "Enter Transaction Exp Names2"

End Sub

From the Form Current Transactions the user is able to select which transactions need to be placed in the Problem Log. It is here that I run into trouble. I have attempted to provide navigation that will allow the user to quit, and return to the main menu.

Private Sub Return_to _Exception_Entry_Click()

Docmd.Close acForm, "Current Transactions Subform"
DoCmd.Close acForm, "Current Transactions"
DoCmd.DeleteObject acTable, "Current Transactions"
DoCmd.OpenForm "Exception Menu"

End Sub

Through research I also understand that my logic is flawed. With multiple users entering data I will not be able to delete this table.

Two questions come to mind.

1) How I get around the error message?
2) What design changes do I need to make in order to make this work with multiple users?

I hope I have properly explained the situation. I appreciate the time and effort many of you place in attempting to help others.

 

[earthandfire]

Since this is a multi user database, split it so that the tables are in the shared back end on the server and the user interface etc is in the front end that is on each user's local machine. Then you can create / append to / query / delete the temporary table in the front end without any conflicts.

Hope this helps.