Why does using TableDefs cause table to be used/locked?

Joe Overman
Joe Overman used Ask the Experts™
on
I have a the attached functions which allow me to easily delete a table if it needs it.  When I use the first function (erasetable) there are no problems.  But when I use the blnCheckTable function to see if the table exists, when I do go to delete the table I get a runtime error 3211.  What is really strange is I don't get this error everywhere in the database.  So, is there something I can do to unlock the table, perhaps closeout the tabledefs reference?
'**************Functions used to erase tables*****************Start
Function EraseTable(strTableName As String)
    'Delete the table pased into the function
    If blnCheckTable(strTableName) = True Then DoCmd.DeleteObject acTable, strTableName
End Function

Function blnCheckTable(strTableName As String) As Boolean
    'check to ensure the table name exists in the table definitions
    'if any error occurs then check table = false
On Error GoTo subexit:
 
    If CurrentDb.TableDefs(strTableName).Name = strTableName Then
        blnCheckTable = True
    End If

subexit:
End Function
'**************Functions used to erase tables*****************End

Open in new window

ErrorMsg.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try this revision

Function blnCheckTable(strTableName As String) As Boolean
dim db as dao.database
set db=currentdb
    'check to ensure the table name exists in the table definitions
    'if any error occurs then check table = false
On Error GoTo subexit:
 
    If db.TableDefs(strTableName).Name = strTableName Then
        blnCheckTable = True
    End If
 
subexit:
End Function
Top Expert 2016
Commented:
you can also, use this simple code to delete the table

Function EraseTable(strTableName As String)
    'Delete the table pased into the function
on error resume next
     DoCmd.DeleteObject acTable, strTableName
End Function

Author

Commented:
First suggestion did not resolve the issue.  The second will but not what I want to do.
Leigh PurvisDatabase Developer
Commented:
>> The second will but not what I want to do.
Why not?
Can you give a reason for your decision to not implement such a method?
You can easily attempt the deletion, and infer from an error raised if it existed or not.
If you need to make some decision before the deletion, then fair enough - but there's no evidence of your doing that in the question.
As an all in one alternative:
If DCount("*","msysObjects","[Name] = '" & strTableName & "' AND Type In (1,4,6)") > 0 Then
    CurrentDb.Execute "DROP Table [" & strTableName & "]"
End If

You could also make sure that you're not relying on VBA destroying the implicit object it's had to create to satisfy your previous code. Leaving your deletion method the same - and going the whole hog with your object variable creation...
However I'd tend towards the above personally.

Function EraseTable(strTableName As String) 
    'Delete the table pased into the function 
    If blnCheckTable(strTableName) = True Then DoCmd.DeleteObject acTable, strTableName 
End Function 


Function blnCheckTable(strTableName As String) As Boolean 
    'check to ensure the table name exists in the table definitions 
    'if any error occurs then check table = false 
On Error GoTo subexit: 

    Dim db as DAO.Database
    Dim tdf as DAO.TableDef

    Set db = CurrentDb
    Set tdf = db.TableDefs(strTableName)
    If Not tdf Is Nothing Then 
        blnCheckTable = True 
    End If 

subexit:
Set tdf = Nothing
Set db = Nothing
End Function 

Open in new window

Author

Commented:
The reason the second suggestion does not work in this case is because of a decision which must be made before the deletion.
LPurvis your alternative suggestion works.
However, in the process of creating a sanatized version of the database to post for a complete picture, i figured out what was causing the table to be locked.  The form that is being opened has a list box and that list box record source sql was causing the table to be locked.  So, moving the record source assignment to the load event rather than open, allows the table to remain unlocked and my original code works fine (like everwhere else in the database).
Anyway, thanks for the help experts.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial