PDA

View Full Version : Does an Object exist?



mud2
04-05-2005, 08:30 PM
How do I determine if an object (Form, table, Query...) exists before I make one? I want VBA to determine this. I suppose something like
If Exists("Querya")......which, of course, is too direct and straightforward!

___
04-06-2005, 04:10 AM
Does this help?

Option Compare Database
Option Explicit

Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean
Dim db As Database
Dim tbl As TableDef
Dim qry As QueryDef
Dim i As Integer

Set db = CurrentDb()
ObjectExists = False

Select Case strObjectType

Case "Table"
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tbl

Case "Query"
For Each qry In db.QueryDefs
If qry.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next qry

Case "Form", "Report", "Module"
For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next i

Case "Macro"
For i = 0 To db.Containers("Scripts").Documents.Count - 1
If db.Containers("Scripts").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next i

Case Else
MsgBox "Must be a Table, Query, Form, Report, Macro, or Module"
End Select

End Function


HTH

SJ McAbney
04-06-2005, 08:14 AM
Personally, I'd enumerate the second parameter and ensure the DAO was explicitly reference. Still ___'s code though. :)


Option Compare Database
Option Explicit

Enum otObjectTypes
otTable
otQuery
otForm
otReport
otMacro
otModule
End Enum

Function ObjectExists(strObjectType As String, bytObject As Byte) As Boolean
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim qry As DAO.QueryDef
Dim i As Integer

Set db = CurrentDb()
ObjectExists = False

Select Case bytObject

Case otTable
For Each tbl In db.TableDefs
If tbl.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next tbl

Case otQuery
For Each qry In db.QueryDefs
If qry.Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next qry

Case otForm, otReport, otModule
For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next i

Case otMacro
For i = 0 To db.Containers("Scripts").Documents.Count - 1
If db.Containers("Scripts").Documents(i).Name = strObjectName Then
ObjectExists = True
Exit For
End If
Next i

Case Else
MsgBox "Must be a Table, Query, Form, Report, Macro, or Module"
End Select

End Function

Norie
04-08-2005, 07:18 AM
That code won't compile.