How to solve error 3211 in Access 2000?

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • switchof
    New Member
    • Aug 2011
    • 9

    #1

    How to solve error 3211 in Access 2000?

    I received the error 3211: database engine could not lock table TF_Classificati on as it's been used by another person or process on the line bolded in the below code: tblWM.Fields.Ap pend fldWM

    I already tried 2 ways:
    1. to close the recordset and set the object to nothing
    2. close the table using the command: DoCmd.Close acTable, "TF_Classificat ion", acSaveYes
    but none works, so any idea?

    Thank you.


    Code:
    Private Sub Command12_Click()
    Dim xlApp As Object
    Dim xlwkb As Object
    Dim xlsheet As Object
    Dim Conn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim dbWM As Object
    Dim tblWM As Object
    Dim fldWM As Object
    Dim CreateField As Boolean
    
    If Combo38.Value = vbNullString Then
        RetValue = MsgBox("You have to enter your initial in the Updater field", vbOKOnly)
    Else
    Set xlApp = CreateObject("excel.application")
    Set xlwkb = xlApp.Workbooks.Open(CurrentProject.Path & "\WM_Feedback.xls")
    Set xlsheet = xlwkb.Sheets(1)
    
    i = 2
    
    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    CapDis$ = vbNullString
    
    Set Conn = CurrentProject.Connection
    
    Do Until xlsheet.Range("A" & i).Formula = vbNullString
            
        strSQL$ = "SELECT * FROM TF_Classification " & _
        "WHERE (TF_Classification.ISIN = '" & xlsheet.Range("A" & i).Value & "') AND (TF_Classification.Flag_Current = " & True & ");"
        Set rs = New ADODB.Recordset
        rs.Open strSQL$, Conn, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            strSQL$ = "UPDATE TF_Classification SET TF_Classification.Flag_Current = False " & _
            "WHERE (((TF_Classification.Flag_Current)=True) And ((TF_Classification.ISIN)= '" & xlsheet.Range("A" & i).Value & "'));"
            DoCmd.RunSQL strSQL$
        Else
        End If
        
        rs.Close
        Set rs = Nothing
        j = 0
        
        Do Until xlsheet.Range(Chr(66 + j) & "1").Formula = vbNullString
            CreateField = False
        
            strSQL$ = "SELECT Classification_Fields.* " & _
            "FROM Classification_Fields " & _
            "WHERE (((Classification_Fields.Field_Type)='" & xlsheet.Range(Chr(66 + j) & "1").Formula & "'));"
            Set rs = New ADODB.Recordset
            rs.Open strSQL$, Conn, adOpenKeyset, adLockOptimistic
            If rs.RecordCount = 0 Then
                CreateField = True
            Else
            End If
            
            'DoCmd.Close acTable, "TF_Classification", acSaveYes
            
            If CreateField And xlsheet.Range(Chr(66 + j) & "1").Formula <> "WKN" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD260" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD270A" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD270B" And xlsheet.Range(Chr(66 + j) & "1").Formula <> "GD332" Then
                Set dbWM = CurrentDb
                Set tblWM = dbWM.TableDefs!TF_Classification
                Set fldWM = tblWM.CreateField(xlsheet.Range(Chr(66 + j) & "1").Formula, dbText, 50)
                [B]tblWM.Fields.Append fldWM[/B]            
    Rem dbWM.TableDefs.Append tblWM
            
            Else
            End If
    Last edited by Stewart Ross; Nov 25 '11, 07:21 PM. Reason: code tags added
  • switchof
    New Member
    • Aug 2011
    • 9

    #2
    Bumping it cause I still didn't find the solution so would need help

    Comment

    Working...