How to determine if a database object exists before creating, deleting, or modifying (1 Viewer)

[llyal]

How to determine if a database object exists before creating, deleting, or modifying


What VBA code is used to check to see if a database object (table, etc) exists or not before creating, deleting, or modifying object with VBA code? In this situation i am dealing with Access97.

Thank you!

Llyal

 

[dennyryan]

I'm kind of doubling up on my example. Maybe it's not that useful, but ehre's the code for how I check to see if a table exists or not and then delete it if it does:

Private Sub CheckExists(strTableName As Sring)
' check to see if the table exists, if it does then delete it since the next step
' in the process is to run a MakeTable query

Dim dbClient As Database
Dim tdf As TableDef
Set dbClient = CurrentDb

For Each tdf In dbClient.TableDefs
If tdf.Name = strTableName Then
DoCmd.DeleteObject acTable, trTableName
Exit Sub
End If
Next

End Sub

 

[llyal]

I found how to test for a specific database object (which is a good idea since i have a DB with hundreds of objects!); basically, you try to access the object and then trap the error generated when the object does not exist, then act open the error. My sample code is as follows for a temp table recreate:

'*** assume proper declarations
Set objDB = CurrentDb

On Error Resume Next
strTest = objDB.TableDefs("tblTestCreate").Name
If Err <> 3265 Then
objDB.Execute "DROP TABLE tblTestCreate;"
End If

objDB.Execute (strSQL)
'*** (this creates table)

objDB.Close
Set objDB = Nothing