How to wait for execution control to return from dropping a table?

Question

I have the following code:

CurrentDb.Execute "DROP TABLE [" & DatabaseName & "].[" & TableName & "];"

Which will drop a table from an MS Access database. Typically this is being done using a database on a shared network.

How can I ensure the DROP method completes before allowing the control to move on to the next line in my code?

This is being done in VBA MS Access 2003; but I would be interested if the version impacts the answer.

Answer 1

You can write a loop with your CurrentDb.Execute command inside. Inside the loop, you store in a variable the number of tables called TableName. Until this number = 1, you can't exit from the loop. When the number becomes at last zero, you can exit from the loop and continue execution.

Answer 2

Here is my take on the Loop method:

Public Function DeleteTable(DatabaseName as String, TableName as String) as Boolean
     DeleteTable = False

        CurrentDb.Execute "DROP TABLE [" & DatabaseName & "].[" & TableName & "];"
        Do
        Loop Until TableExists(TableName, DatabaseName) = False     

     DeleteTable = True
End Function

Public Function TableExists(TableName As String, DatabaseName As String) As Boolean   

        If 0 = CurrentDb.OpenRecordset("SELECT COUNT(*) As Count FROM [" &   DatabaseName & "].[MSysObjects] WHERE [Name] = '" & TableName & "';").Fields("Count").Value Then
             TableExists = False
        Else
             TableExists = True
        End If
End Function