DoCmd.DeleteObject acTable - "could not lock table because it is already in use"

Question

I'm getting the following error when trying to delete a table:

Run-time error '3211'

The database engine could not lock table 'RuleViolations1516' because it is already in use by another person or process.

Here is the offending procedure, with a comment showing the line throwing the error:

Public Sub ImportRuleViolations()

    DoCmd.Close acForm, "frmImportRuleViolations"

    If _
        TableExists("RuleViolations1516") = True _
    Then

        Debug.Print "Table RuleViolations1516 already exists"

        DoCmd.DeleteObject acTable, "RuleViolations1516" ' <-- EXECUTION STOPS HERE
        Debug.Print "...old table deleted..."

        DoCmd.TransferSpreadsheet acTable, _
                  10, _
                  "RuleViolations1516", _
                  Forms!frmImportRuleViolations.txtRuleViolationsPath & Forms!frmImportRuleViolations.txtRuleViolationFile, _
                  -1
        Debug.Print "...new data imported."

    ElseIf _
        TableExists("RuleViolations1516") = False _
    Then

        Debug.Print "Table RuleViolations1516 does not already exist"

        DoCmd.TransferSpreadsheet acTable, _
                          10, _
                          "RuleViolations1516", _
                          Forms!frmImportRuleViolations.txtRuleViolationsPath & Forms!frmImportRuleViolations.txtRuleViolationFile, _
                          -1
        Debug.Print "...new data imported."

    End If

    Dim db As DAO.Database
    Dim tDef As TableDef, fld As DAO.Field

    Set db = CurrentDb

    db.TableDefs.Refresh

    ' LRN

        Set tDef = db.TableDefs("RuleViolations1516")
        Set fld = tDef.CreateField("newLRN", dbText, 20)
        fld.OrdinalPosition = 2
        tDef.Fields.Append fld

        db.Execute _
            "UPDATE RuleViolations1516 Set newLRN=[Learner Ref]", dbFailOnError

        ' delete old field
        tDef.Fields.Delete "Learner Ref"
        tDef.Fields.Refresh

        ' rename new field
        tDef.Fields("newLRN").name = "LRN"
        tDef.Fields.Refresh

        Set fld = Nothing
        Set tDef = Nothing

    ' AimRef

        Set tDef = db.TableDefs("RuleViolations1516")
        Set fld = tDef.CreateField("newAimRef", dbText, 20)
        fld.OrdinalPosition = 7
        tDef.Fields.Append fld

        db.Execute _
            "UPDATE RuleViolations1516 Set newAimRef=[Aim Reference Number]", dbFailOnError

        ' delete old field
        tDef.Fields.Delete "Aim Reference Number"
        tDef.Fields.Refresh

        ' rename new field
        tDef.Fields("newAimRef").name = "AimRef"
        tDef.Fields.Refresh

        Set fld = Nothing
        Set tDef = Nothing

    Set db = Nothing

    DoCmd.OpenForm "frmImportRuleViolations"

End Sub

The offending sub also makes reference to another function:

Public Function TableExists(name As String) As Boolean

    TableExists = DCount("*", "MSysObjects", "Name = '" & name & "' AND Type = 1")

End Function

The above sub and function run on their own separate module (not tied to a form module).

The table RuleViolations1516 is not open when I run the sub. The form frmImportRuleViolations uses the RuleViolations1516 table in some queries behind some subforms, but as you can see from the sub, I've closed this form on the first line.

Any pointers would be appreciated.

Update:

frmImportRuleViolations has 2 subforms on it... removing them (temporarily) stops the issue. I need the subforms on the form though.. how can I get around this?

Answer 1

As I understand your updated question, you have a form called frmImportRuleViolations and that has a subform I'll call frmImportRuleViolationsSubform1.

frmImportRuleViolationsSubform1 references the RuleViolations1516 table somewhere on it.

I'm assuming you are also triggering this reload from a button or other control on frmImportRuleViolations. If so, then that makes sense as the cause of your error. Access may not have given up every reference to RuleViolations1516 by the time it gets to the close event.

That and a form closing and reopening itself can get kind of hairy.

One fairly easy thing to do would be to drop and load the table before the frmImportRuleViolationsSubform1 is opened. As a user (and a developer) that's what I would expect - I open the form and it's right up to date. And worst case situation closing the form and re-opening it gives me a refresh.

Failing that (it absolutely has to be reloaded after the form is closed); then I would make whatever is closing it open frmImportRuleViolations as a modal window, and then put reload of the table code after the call to frmImportRuleViolations since it will wait until control is returned to the calling window.

You could also instead load the data into a temporary staging table, and then delete the contents of RuleViolations1516 and repopulate it from the staging table. This would probably be the route I would take as it would eliminate the closing and opening of the frmImportRuleViolations form. You'd just have to tell the table on the sub-form to refresh (which I'll leave an an exercise for the reader).

---

Aside from that, a couple style notes:

'' this 
If _
    TableExists("RuleViolations1516") = True _
Then

'' could be equally written as
If TableExists("RuleViolations1516") Then
'' the "= True" is implied and not required

And

'' this
ElseIf _
    TableExists("RuleViolations1516") = False _
Then

'' is redundant to the previous if. A simple else would do, since we
'' know if the last time it ran, it wasn't true, it must be false

And the DoCmd block looks like it does the same thing in both the if and else, so I'd be inclined to extract it like this:

If TableExists("RuleViolations1516") then
    Debug.Print "Table RuleViolations1516 already exists"
    DoCmd.DeleteObject acTable, "RuleViolations1516" 
    Debug.Print "...old table deleted..."
else
    Debug.Print "Table RuleViolations1516 does not already exist"
end if

DoCmd.TransferSpreadsheet acTable, _
    10, _
    "RuleViolations1516", _
    Forms!frmImportRuleViolations.txtRuleViolationsPath & _
        Forms!frmImportRuleViolations.txtRuleViolationFile, _
    -1
Debug.Print "...new data imported."

That way when you come back to change the name of the file (or whatever) you are only changing it in one place, etc. and goes along with the DRY principle - "DRY - don't repeat your self".

I like the use of underscores so that what you have coded does not run off the edge of the screen like some extract from war and peace. It makes it much more readable.

What you've done is not wrong. It's just not what most developers would do. And as much as we write code for the computer to function the way we want, we also want the next developer to touch your code 5 years down the line to easily understand what you are doing. And code always lasts longer than you think it will :^)

Good luck!