VBA to check if table, form, report exists in DB

[bstice]

Hello all,

I am writing some code to clean up a string of DBs that I manage. I am curious, is there a way with VBA to check the database to see if a table, form or report exists in that db?

I would construct an if statement like below.

if tableA exists(need the code here) then delete tableA(i know the code to delete)

THanks for the help. I always award reputation points.

B

 

[Vonnie]

If you are talking about WITHIN a db then this kind of code works:
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
If obj.Name = "tblTEMP" Then
DoCmd.DeleteObject acTable, obj.Name
End If

dbs.AllForms, etc.

 

[WatchSteveDrum]

Ahh, thank you for posting this; this is exactly what I was looking for! However, I tried to implement it for forms, and I'm getting an "Object doesn't support this method or property" error message. Can anyone spot how I'm being stupid?

Public Sub FillForm(FormName As String, ...
...
Dim obj As AccessObject, dbs As Object
...
Set dbs = Application.CurrentData
For Each obj In dbs.AllForms
    If obj.Name = FormName Then
        DoCmd.DeleteObject acForm, FormName 'DELETE form
    End If
Next obj

Thanks in advance!

 

[Vonnie]

The form you are deleting isn't open, correct?

 

[Vonnie]

Set dbs = Application.CurrentProject

 

[boblarson]

Which line does it highlight when it errors out? That will tell a lot.

 

[The_Doc_Man]

The collection used to be "Documents" if the form wasn't open at the time. And if it is open so that it is in the "Forms" collection, I don't think you CAN delete it.

A "document" to Access is any form or report that isn't open at the time. I believe you can look at the .Type property of the document without having to open it to learn what it is. So instead of traversing the .Tables collection, traverse the .Documents collection.

 

[rapsr59]

Since you already know the name of the form that you are deleting and you are sending the form name to the procedure why not just use...

DoCmd.DeleteObject acForm, strFormName

And you can omit the rest of the code.

To make sure you are not trying to delete the form that is running the code you can add...

If strFormName = Me.Name then
     'Don't try to delete this form
     msgbox "You cannot delete this form as it is presently in use."
ElseIf IsLoaded(strFormName) Then
     Msgbox "You cannot delete this Form as it is presently open."
Else
     'Delete the form object in strFormName
     DoCmd.DeleteObject acForm, strFormName
End If

Regards, Richard

 

[WatchSteveDrum]

Set dbs = Application.CurrentProject

Thank you. This solved it. Forgot that tables and queries are in Application.CurrentData while forms and reports are in Application.CurrentProject