Object Identification

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
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:
Code:
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

giacomo

Well-known Member
Joined
Feb 20, 2002
Messages
1,796
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

Well-known Member
Joined
Mar 4, 2005
Messages
2,071
Excellent point, I'm glad you thought of that.
How about this then:
Code:
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
 

Oorang

Well-known Member
Joined
Mar 4, 2005
Messages
2,071

ADVERTISEMENT

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.

Code:
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :

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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,675
Messages
5,549,367
Members
410,911
Latest member
AniEx
Top