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.
Access VBA not dropping temporary table and thus preventing the table from being updated
Lang, Tammy (CFIA/ACIA)
0
Reputation points
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