Access VBA not dropping temporary table and thus preventing the table from being updated

Question

Hello,

This is the code that is being used to update a specific table:

Function MakeTableOnOpen()     
Dim Db As DAO.Database ' Create object variable.          
Set Db = CurrentDb ' Create valid object reference.    

On Error GoTo MakeTableOnOpen_Err     
'Check if table exists, then delete...     
On Error Resume Next         
With Db             
.Execute "DROP TABLE DDL_Entity_ThirdParty;"         
End With     

On Error GoTo 0      
'...otherwise create table     
With Db         
.Execute "qmakEntity_ThirdParty", dbFailOnError     
End With  

MakeTableOnOpen_Exit:     
Exit Function  

MakeTableOnOpen_Err:     
MsgBox Error$     
Resume MakeTableOnOpen_Exit  
                                                                                                                                                                                                                                                                                                                 
Db.Close: 
Set Db = Nothing ' Release the object.      

End Function

This is the error that keeps coming up:

Thanks for helping me figure out where the error resides.

Tammy

Answer 1

Your comment says "Check if table exists" but you're not actually doing that. The table can likely not be dropped if it is in use (bound to an open form) or has relations to other tables. The ostrich approach of "On Error Resume Next" prevents you from seeing what is going on (i.e. getting a runtime error). "On Error GoTo 0" should be changed to "On Error GoTo MakeTableOnOpen_Err" but that is not your current problem.