Error 3211 lock table (1 Viewer)

InFlight

User
Local time
Tomorrow, 04:30
Joined
Jun 11, 2015
Messages
122
Hi
I am getting this error on this code. it happens on the CurrentDb.Execute

Error 3211 (The database engine could not lock table 'tbl_Names'
because it is already in use by another person or process)


Code:
Private Sub P2_Combo_NotInList(NewData As String, Response As Integer)

    On Error GoTo P2_Combo_NotInList_Error

    Dim oRS_P2 As DAO.Recordset, i As Integer, sMsg As String
    Response = acDataErrContinue

    If MsgBox("Name is not in list. Add it?", vbYesNo) = vbYes Then
        i = DMax("[ID]", "tbl_Names") + 1
        Set oRS_P2 = CurrentDb.OpenRecordset("tbl_Names", dbOpenDynaset)
        oRS_P2.AddNew
        oRS_P2.Fields(0) = i
        oRS_P2.Fields(1) = NewData
        
        oRS_P2.Update
        oRS_P2.Close
        Set oRS_P2 = Nothing
        
        P2_Combo = i
        P2_Combo.Requery
        
        CurrentDb.Execute " ALTER TABLE tbl_Names ADD COLUMN " & NewData & " Long", dbFailOnError
        
    End If

    On Error GoTo 0
    Exit Sub

P2_Combo_NotInList_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure P2_Combo_NotInList, line " & Erl & "."


End Sub
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:30
Joined
Oct 29, 2018
Messages
13,101
Is your combobox using the same table as row source?
 

InFlight

User
Local time
Tomorrow, 04:30
Joined
Jun 11, 2015
Messages
122
Yes it is
 

Micron

AWF VIP
Local time
Today, 10:30
Joined
Oct 20, 2018
Messages
3,471
Without regard to the syntax of the make table sql or why the table is locked when you've closed the recordset, I'm wondering, are you adding new values into new columns? That wouldn't pass the smell test.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:30
Joined
Oct 29, 2018
Messages
13,101
I tried going to another control, but still get it
It doesn't matter which control you go, if the table structure you're trying to modify is currently in use, you'll get an error. Why are you adding a new column anyway?
 

InFlight

User
Local time
Tomorrow, 04:30
Joined
Jun 11, 2015
Messages
122
It doesn't matter which control you go, if the table structure you're trying to modify is currently in use, you'll get an error. Why are you adding a new column anyway?
I am keeping results who played who and games won
 

Micron

AWF VIP
Local time
Today, 10:30
Joined
Oct 20, 2018
Messages
3,471
You are on the wrong path. Suggest you research and understand normalization as it applies to databases. To add table columns because you get new data is just wrong and a waste of time trying to fix what you have.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 07:30
Joined
Oct 29, 2018
Messages
13,101
I am keeping results who played who and games won
Hi. Thanks for the clarification. Shouldn't you be storing the results as records then, instead of columns? I don't think you need the CurrentDb.Execute line in your code. Try just commenting it out or deleting it. Can you post your table structure?
 

Users who are viewing this thread

Top Bottom