Menu
I use MS Access macros and queries to build my application. I use some temporary import files, and need to either run a macro, or some VBA, to test if they exist, and then if they do, to delete them.
My table name is "TempImport1"
I've researched this via Google searches and have some VBA that might work. I have cut/pasted VBA code under a button in the past, and it worked, but not this time. How do I put the code into a module or a click sub button?
Function IsTable(sTblName As String) As Boolean
'does table exists and work ?
'note: finding the name in the TableDefs collection is not enough,
' since the backend might be invalid or missing
On Error GoTo TrapError
Dim x
x = DCount("*", sTblName)
IsTable = True
Exit Function
TrapError:
Debug.Print Now, sTblName, Err.Number, Err.Description
IsTable = False
End Function
841
In Access VBA, deleting Table can be done by DoCmd. DeleteObject Method.
When you are finished using the temporary table, you can use the DeleteObject action to delete it. This action has the same effect as selecting an object in the Navigation Pane and then pressing the DEL key, or right-clicking the object in the Navigation Pane and clicking Delete.
To delete a table field in Access, open the table that contains the field to delete in design view. Click the row selector of the row that you wish to delete. Click the “Delete Rows” button in the “Tools” group on the “Design” tab of the “Table Tools” contextual tab in the Ribbon.
To delete the TempImport1 table if it exists just use the below function.
Function DeleteTables()
If Not IsNull(DLookup("Name", "MSysObjects", "Name='TempImport1' AND Type = 1")) Then
DoCmd.DeleteObject acTable, "TempImport1"
End If
End Function
Once the function has been created, create a macro, add the action run code then type in DeleteTables() in to the Function Name.
You then have a macro to run to delete the table if it exists.
197
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
