Locating a file before deleting it from a database.

[bigdavidt]

I know how to select an Access table programmatically and I know how to remove a table programmatically from an Access database. Is there a way to locate or otherwise confirm the existence of an Access table programmatically before selecting or removing it from the database?

Thank you in advance.

 

[ajrimmer]

Look in the table MSysObjects - you may want to unhide system objects (tools - options - view) first. You can query this table just like any other.

 

[genomon]

Also have a look at the TableDef/TableDefs collection in Access help.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[genomon]

Actually, here is a function we use to test for existence of objects. Unfortunately I am unable to credit the otiginal author:

Function ObjectExists(strObjectType As String, strObjectName As String, Optional strdb As String) As Boolean
' Pass the Object type: Table, Query, Form, Report, Macro, or Module
' Pass the Object Name
     
     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim I As Integer
     If strdb = "" Then
          Set db = CurrentDb()
     Else
          Set db = DBEngine.Workspaces(0).OpenDatabase(strdb)
     End If
      
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
          'MsgBox "ob type = " & strObjectType & "  collection name = " & tbl.name & "*  ob name = " & strObjectName & "*"
               If tbl.name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For I = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(I).name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next I
     ElseIf strObjectType = "Macro" Then
          For I = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(I).name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next I
     Else
          'MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."