Object Identification

[Oorang]

Hello all,
I am trying to create a function that returns the acObjectType type using just the object name. The function I built seems a little cumbersome I was wondering if anyone could suggest refinements:

Private Sub Test()
    MsgBox AccessObjectType("test")
End Sub
Private Function AccessObjectType(strObjName As String) As Access.AcObjectType
    On Error Resume Next
    'Written by Aaron Bush 10/19/2006
    'Free for Public use, please retain Notice.
    Dim ao As Access.AccessObject
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Set ao = Access.CurrentProject.AllForms.Item(strObjName)
    If Not ao Is Nothing Then
            AccessObjectType = acForm
            Exit Function
            End If
    Set ao = Access.CurrentProject.AllReports.Item(strObjName)
        If Not ao Is Nothing Then
            AccessObjectType = acReport
            Exit Function
            End If
    Set ao = Access.CurrentProject.AllMacros.Item(strObjName)
        If Not ao Is Nothing Then
            AccessObjectType = acMacro
            Exit Function
            End If
    Set ao = Access.CurrentProject.AllDataAccessPages.Item(strObjName)
        If Not ao Is Nothing Then
            AccessObjectType = acDataAccessPage
            Exit Function
            End If
    Set ao = Access.CurrentProject.AllModules.Item(strObjName)
        If Not ao Is Nothing Then
            AccessObjectType = acModule
            Exit Function
            End If
    For Each td In CurrentDb.TableDefs
        If td.Name = strObjName Then
            AccessObjectType = acTable
            Exit Function
            End If
    Next td
    For Each qd In CurrentDb.QueryDefs
        If td.Name = strObjName Then
            AccessObjectType = acQuery
            Exit Function
            End If
    Next qd
    AccessObjectType = acDefault
End Function

 

[giacomo]

I don't think that what you have is too cumbersome, as far as I know (but I could be wrong) there is no overall container for objects that you could loop through to make your code more efficient.

On a side note, although I am sure you're really good about giving all of your objects distinct names, you should keep in mind that you could potentially have a table, report, and form all with the same name. If this were the case your code would always return acForm as the type.

hth,
Giacomo

 

[Oorang]

Excellent point, I'm glad you thought of that.
How about this then:

Private Function AccessObjectType(strObjName As String) As Access.AcObjectType
    On Error Resume Next
    'Written by Aaron Bush 10/19/2006
    'Free for Public use, please retain Notice.
    Dim ao As Access.AccessObject
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim bytObjFound As Byte
    Set ao = Access.CurrentProject.AllForms.Item(strObjName)
    If Not ao Is Nothing Then
        AccessObjectType = acForm
        bytObjFound = bytObjFound + 1
        End If
    Set ao = Access.CurrentProject.AllReports.Item(strObjName)
    If Not ao Is Nothing Then
        AccessObjectType = acReport
        bytObjFound = bytObjFound + 1
        End If
    Set ao = Access.CurrentProject.AllMacros.Item(strObjName)
    If Not ao Is Nothing Then
        AccessObjectType = acMacro
        bytObjFound = bytObjFound + 1
        End If
    Set ao = Access.CurrentProject.AllDataAccessPages.Item(strObjName)
    If Not ao Is Nothing Then
        AccessObjectType = acDataAccessPage
        bytObjFound = bytObjFound + 1
        End If
    Set ao = Access.CurrentProject.AllModules.Item(strObjName)
    If Not ao Is Nothing Then
        AccessObjectType = acModule
        bytObjFound = bytObjFound + 1
        End If
    Set td = CurrentDb.TableDefs(strObjName)
    If Not td Is Nothing Then
        AccessObjectType = acTable
        bytObjFound = bytObjFound + 1
        End If
    Set qd = CurrentDb.QueryDefs(strObjName)
    If Not qd Is Nothing Then
        AccessObjectType = acQuery
        bytObjFound = bytObjFound + 1
        End If
    If bytObjFound <> 1 Then AccessObjectType = acDefault
End Function

 

[giacomo]

I think you got it

 

[Oorang]

Actually I found two more issues, the first of which is that if it finds an object and tries and fails on the next object type "ao" is not reset to nothing, so it falls through to the counter. The other issue is that you cannot redundaant name a table and a query but that peice of code wouldn't be able to tell the difference if there was also a form. So I added a search switch that will just return table,query, or default.

Private Function AccessObjectType(strObjName As String, _
    Optional boolFuzzy As Boolean = False) As Access.AcObjectType
    On Error Resume Next
    'Written by Aaron Bush 10/19/2006
    'Free for Public use, please retain Notice.
        'If you set boolFuzzy to true, function will only return these values:
            'acTable
            'acQuery
            'acDefault
    Dim ao As Access.AccessObject
    Dim td As DAO.TableDef
    Dim qd As DAO.QueryDef
    Dim bytObjFound As Byte
    If Not boolFuzzy Then
        Set ao = Access.CurrentProject.AllForms.Item(strObjName)
        If Not ao Is Nothing Then
            Set ao = Nothing
            AccessObjectType = acForm
            bytObjFound = bytObjFound + 1
            End If
        Set ao = Access.CurrentProject.AllReports.Item(strObjName)
        If Not ao Is Nothing Then
            Set ao = Nothing
            AccessObjectType = acReport
            bytObjFound = bytObjFound + 1
            End If
        Set ao = Access.CurrentProject.AllMacros.Item(strObjName)
        If Not ao Is Nothing Then
            Set ao = Nothing
            AccessObjectType = acMacro
            bytObjFound = bytObjFound + 1
            End If
        Set ao = Access.CurrentProject.AllDataAccessPages.Item(strObjName)
        If Not ao Is Nothing Then
            Set ao = Nothing
            AccessObjectType = acDataAccessPage
            bytObjFound = bytObjFound + 1
            End If
        Set ao = Access.CurrentProject.AllModules.Item(strObjName)
        If Not ao Is Nothing Then
            Set ao = Nothing
            AccessObjectType = acModule
            bytObjFound = bytObjFound + 1
            End If
        End If
    Set td = CurrentDb.TableDefs(strObjName)
    If Not td Is Nothing Then
        AccessObjectType = acTable
        bytObjFound = bytObjFound + 1
        End If
    Set qd = CurrentDb.QueryDefs(strObjName)
    If Not qd Is Nothing Then
        AccessObjectType = acQuery
        bytObjFound = bytObjFound + 1
        End If
    If bytObjFound <> 1 Then AccessObjectType = acDefault
End Function

 

[Andrew Fergus]

Hi

You probably have your function working by now, but there is usually another way of achieving these sorts of things. This was mentioned in an earlier post :

I don't think that what you have is too cumbersome, as far as I know (but I could be wrong) there is no overall container for objects that you could loop through to make your code more efficient.

The hidden table 'MSysObjects' contains a list of objects within the database. In that table is a field called 'Name' which pretty much speaks for itself but there is also a field called 'Type' which contains a numerical value. Insofar as I have worked out, the values of the 'type' field are as follows :

1 = Tables
2 = ? (MSysDb?)
3 = Access system objects (e.g. 'Tables', 'DataAccessPages', 'Reports', 'Forms', 'Modules' etc.) - this may be relevant for what you are doing
5 = Queries (but queries where the leftmost character = ~ are unsaved queries used in forms and reports etc.)
6 = Linked Tables
8 = Relationships
-32768 = Forms
-32766 = Macros
-32764 = Reports
-32761 = Modules
-32756 = Web Pages
-32758 & -32757 = ???

There was a thread recently where someone wanted to identify the linked tables in a database and I supplied a VBA answer here :
http://www.mrexcel.com/board2/viewtopic.php?t=237742

It's not exactly the same but it shows how you can interact with that table - you could even use functions like DCount on your 'strObjName' variable to work out how many of items with that name exist in the database and a Dlookup and Case Statement to work out the object type. If you unhide the table and browse through it you may find something there that can help you (menu option Tools > Options > View > tick 'Hidden Objects')

Do you mind my asking what you are using this function for?

Cheers, Andrew

 

[Andrew Fergus]

Here is some sample code that pretty much achieves what you want. You may want to handle the unusual cases where I have marked them within the code :

Private Function GetObjType(strTestName As String) As Access.AcObjectType

Dim intCount As Integer

intCount = DCount("[Id]", "MSysObjects", "[Name] = '" & strTestName & "'")

If intCount = 0 Then
    'There are no objects with that name
    'Insert your own error handler here, e.g.:
    'GetObjType = acDefault
    'If you leave this blank it may return the object type of table
ElseIf intCount > 1 Then
    'There are multiple items with the same name
    'Insert your own error handler here, e.g.:
    GetObjType = acDefault
Else
    'There is only one item with that name
    'Find the type number
    Select Case DLookup("[Type]", "MSysObjects", "[Name] = '" & strTestName & "'")
        Case 1, 6
            GetObjType = acTable
        Case 5
            GetObjType = acQuery
        Case -32768
            GetObjType = acForm
        Case -32766
            GetObjType = acMacro
        Case -32764
            GetObjType = acReport
        Case -32761
            GetObjType = acModule
        Case -32756
            GetObjType = acDataAccessPage
        Case Else
            'This is a system Object
            'insert your own error handler here
            'otherwise it may return the object type of table
    End Select
End If

End Function

HTH, Andrew