If object exists in mdb

[JimP]

Can you provide code to determine if an object exists within the current mdb
(e.g. table, report, etc)?

How would the code change if the object was in another mdb?

 

[Eric Gerds]

Private Function check_object(str_obj_name As String)
Dim db As Database, rs As Recordset
Set db = CurrentDb()
'Set db = OpenDatabase("path and filename of target db")
Set rs = db.OpenRecordset("SELECT MSysObjects.Name FROM MSysObjects;")
check_object = False
With rs
While Not .EOF
Debug.Print !Name
If str_obj_name = !Name Then check_object = True
.MoveNext
Wend
.Close
End With
Set rs = Nothing
End Function

 

[Eric Gerds]

Sorry my first post didn't properly close db and remove reference to it
replace set db = currentdb() with the remarked opendatabase("etc") line
check http://support.microsoft.com/kb/209953 for more info


Private Function check_object(str_obj_name As String)

Dim db As Database, rs As Recordset
Set db = CurrentDb()
'Set db = OpenDatabase("path and filename of target db")
With db
Set rs = .OpenRecordset("SELECT MSysObjects.Name FROM MSysObjects;")
check_object = False
With rs
While Not .EOF
Debug.Print !Name
If str_obj_name = !Name Then check_object = True
.MoveNext
Wend
.Close
End With
Set rs = Nothing
.Close
End With
Set db = Nothing
End Function

 

[JimP]

Hi Eric,

Thanks for getting me pointed in the right direction.

One thing I need to do though is test for the object type, as the name may
not be unique. Can you tell me if you think the code below will work? It
appears that it should, if the values assisgned to "intObjectType" are
constants that do not change.

==========================
'Function ObjectExists accepts (3) parameters; an ObjectName, an ObjectType,
and an optional DatabaseName
Function ObjectExists(strObjectName As String, strObjectType As String,
Optional strDb As String) As Boolean
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intObjectType As Integer

'Set the database location
If strDb = "" Then
Set db = CurrentDb
Else
Set db = OpenDatabase(strDb)
End If

'Set the object type as integer
Select Case strObjectType
Case "acTable"
intObjectType = "In(1,6)"
Case "acQuery"
intObjectType = "= 5"
Case "acForm"
intObjectType = "= -32768"
Case "acReport"
intObjectType = "= -32764"
Case "acMacro"
intObjectType = "= -32766"
Case "acModule"
intObjectType = "= -32761"
End Select

'Set the SELECT statement
strSQL = "SELECT MSysObjects.Name FROM MSysObjects "
strSQL = strSQL & "WHERE Name = '" & strObjectName & "' AND Type " &
intObjectType & ";"
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)

'Set the return value of ObjectExists, based on whether or not a record
is returned
intCount = rs.RecordCount
ObjectExists = IIf(intCount = 0, False, True)

Set rs = Nothing
Set db = Nothing
End Function


=================

 

[Eric Gerds]

I believe your code should work.
but not all end users might have read permissions to the table MSysObjects
though, so an alt method would be to dim a TableDef , QueryDef , Form,
Report, etc and test from there

Hi Eric,

Thanks for getting me pointed in the right direction.

One thing I need to do though is test for the object type, as the name may
not be unique. Can you tell me if you think the code below will work? It
appears that it should, if the values assisgned to "intObjectType" are
constants that do not change.

</snip>