Results 1 to 6 of 6

Thread: Hide queries in Access 2007

  1. #1

    Thread Starter
    Registered User
    Join Date
    Aug 2016
    Posts
    3

    Hide queries in Access 2007

    Hi. I am using the following to hide tables so that when the db is is exported the tables dnt show. How do i do this for queries as well.

    Public Sub TestHideMe()
    HideTable "t1"
    HideQuery "Rental Query Query"
    End Sub

    Public Sub TestShowMe()
    ShowTable "t1"
    ShowQuery "Rental Query Query"
    End Sub

    Public Sub HideTable(ByVal sTableName As String)
    Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = dbHiddenObject
    End Sub

    Public Sub ShowTable(ByVal sTableName As String)
    Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
    End Sub

  2. #2
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,260

    Re: Hide queries in Access 2007

    This thread is sort of in the wrong place. I do believe you're showing us MS-Access macros, and not VB6 code.

    I'd recommend making a post in the "Database Development" area.

    The code will be quite similar, but VB6 will have something a bit different for its "Application" and "CurrentDb" objects.

    However, I'll also share some of my knowledge with you. This knowledge is DAO based (and not MS-Access macro based). Also, I virtually never use queries that are directly in the .MDB or .ACCDB file. Rather, I build my queries in VB6, and execute them from there. So, I'm not sure about queries stored in a database file.

    But I do have experience creating tables from the DAO, and making those hidden. It's not nearly as easy nor as obvious as one might think. There is a dbHiddenObject attribute, which you mention above. However, this does NOT do what you think. It does NOT do the same thing as selecting the properties of a table and marking it as hidden. In fact, here's a comment in my code I've written to myself:
    Code:
        '
        ' There is a dbHiddenObject attribute but this attribute is NOT what it seems.
        ' This is used to create temporary tables that are deleted upon the next database compression.
        ' Also, tables created with this attribute are COMPLETELY hidden and will not even show when
        ' other hidden tables show.  In other words, do NOT use the dbHiddenObject attribute.
        ' The REAL hidden bit is &H80000000, as is shown in the code below.
        '
    Here, I'll post my entire DAO procedure for adding a new table to a database. You can possibly find some good tips for also making your queries hidden:

    EDIT: Just an FYI. The dbTheDatabase is a global object variable in my code, dimmed as "Public dbTheDatabase As DAO.Database".

    Code:
    Private Enum dbDataTypeEnum
        dbText = 10
        dbMemo = 12
        dbBoolean = 1
        dbInteger = 3
        dbLong = 4
        dbSingle = 6
        dbDouble = 7
        dbDate = 8
    End Enum
    Private Type dbFieldAddType
        fdfName As String
        fdfType As dbDataTypeEnum
        fdfSize As Long
        fdfDefaultValue As Variant
        fdfAllowZeroLength As Boolean ' For strings.
        fdfRequired As Boolean
        fdfDescription As String
    End Type
    
    Private Sub DbAddTable(sTableName As String, TheFields() As dbFieldAddType, Optional bHidden As Boolean = False)
        ' Make sure database is open, and EXCLUSIVE access is a good idea.
        ' Fields are added in the order in which they appear in TheFields.
        Dim tdf As DAO.TableDef ' TableDef
        Dim fdf As DAO.Field ' FieldDef
        Dim i As Long
        '
        Set tdf = dbTheDatabase.CreateTableDef(sTableName)
        For i = LBound(TheFields) To UBound(TheFields)
            DbAddFieldDetails tdf, TheFields(i), , True
        Next i
        '
        dbTheDatabase.TableDefs.Append tdf
        If bHidden Then tdf.Attributes = tdf.Attributes Or &H80000000 ' This uses the REAL hidden bit, and not dbHiddenObject.
        dbTheDatabase.TableDefs.Refresh
        '
        ' Now we have to go back and set the unicode compression on the text/memo fields.
        For i = LBound(TheFields) To UBound(TheFields)
            If TheFields(i).fdfType = dbText Or TheFields(i).fdfType = dbMemo Then
                Set fdf = tdf.Fields(TheFields(i).fdfName)
                DbSetProperty fdf, "UnicodeCompression", dbBoolean, True ' Always just set to true.
                tdf.Fields.Refresh
            End If
        Next i
        '
        Set fdf = Nothing
        Set tdf = Nothing
    End Sub
    I'd try the following line on your queries as a first attempt to set them as hidden:
    Code:
       If bHidden Then tdf.Attributes = tdf.Attributes Or &H80000000 ' This uses the REAL hidden bit, and not dbHiddenObject.
    Good Luck,
    Elroy

    EDIT2: Here are a couple of other procedure called by the above. The inclusion of these should make the above "DbAddTable" procedure complete (i.e., where it'll run in a new project, also with the "Public dbTheDatabase As DAO.Database" code.

    Code:
    Private Sub DbAddFieldDetails(tdf As DAO.TableDef, TheField As dbFieldAddType, Optional BeforeThisField As String = "AtTheEnd", Optional bIgnoreUnicodeCompression As Boolean = False)
        Dim fdf As DAO.Field ' FieldDef
        Dim i As Long
        Dim iBeforeOrder As Long
        '
        ' Ensure all fields have a unique ordinal value. ie: reset ALL ordinals.
        tdf.Fields.Refresh ' This makes sure that any prior addition is refreshed, especially when order (BeforeThisField) is important.
        For i = 0 To tdf.Fields.Count - 1
            tdf.Fields(i).OrdinalPosition = i
        Next i
        tdf.Fields.Refresh
        '
        If TheField.fdfName = "AutoID" Then
            Set fdf = tdf.CreateField(TheField.fdfName, dbLong, 4)
            fdf.Attributes = fdf.Attributes + dbAutoIncrField
        Else
            ' Field size is ignored by CreateField when it's not needed (according to DAO documentation).
            Set fdf = tdf.CreateField(TheField.fdfName, TheField.fdfType, TheField.fdfSize)
            If TheField.fdfType = dbText Or TheField.fdfType = dbMemo Then
                fdf.AllowZeroLength = TheField.fdfAllowZeroLength
            End If
            If Not IsNull(TheField.fdfDefaultValue) Then
                fdf.DefaultValue = TheField.fdfDefaultValue
            End If
            fdf.Required = TheField.fdfRequired
        End If
        '
        ' If AfterThisField <> "AtTheEnd" Then we need to figure out where to add it.
        If BeforeThisField = "AtTheEnd" Then
            fdf.OrdinalPosition = tdf.Fields.Count
        Else
            iBeforeOrder = tdf.Fields(BeforeThisField).OrdinalPosition
            For i = tdf.Fields.Count - 1 To iBeforeOrder Step -1
                tdf.Fields(i).OrdinalPosition = i + 1
            Next i
            tdf.Fields.Refresh
            fdf.OrdinalPosition = iBeforeOrder
        End If
        '
        tdf.Fields.Append fdf
        '
        If Not bIgnoreUnicodeCompression Then
            ' Created properties must be set AFTER the field is appended.
            If TheField.fdfType = dbText Or TheField.fdfType = dbMemo Then
                DbSetProperty fdf, "UnicodeCompression", dbBoolean, True ' Always just set to true.
            End If
        End If
        '
        tdf.Fields.Refresh
        Set fdf = Nothing
    End Sub
    
    Private Sub DbSetProperty(obj As Object, sPropertyName As String, iPropertyType As Long, vValue As Variant)
        Dim prop As DAO.Property
        '
        If DbPropertyExists(obj, sPropertyName) Then
            obj.Properties(sPropertyName) = vValue
        Else
            Set prop = obj.CreateProperty(sPropertyName, iPropertyType, vValue)
            obj.Properties.Append prop
            obj.Properties.Refresh
            Set prop = Nothing
        End If
    End Sub
    
    Private Function DbPropertyExists(obj As Object, sPropertyName As String) As Boolean
        Dim s As String
        '
        On Error Resume Next
        s = obj.Properties(sPropertyName).Name
        DbPropertyExists = (Err = 0)
        On Error GoTo 0
    End Function
    Last edited by Elroy; Aug 3rd, 2016 at 09:13 AM.
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

  3. #3

    Thread Starter
    Registered User
    Join Date
    Aug 2016
    Posts
    3

    Re: Hide queries in Access 2007

    Quote Originally Posted by Elroy View Post
    This thread is sort of in the wrong place. I do believe you're showing us MS-Access macros, and not VB6 code.

    I'd recommend making a post in the "Database Development" area.

    The code will be quite similar, but VB6 will have something a bit different for its "Application" and "CurrentDb" objects.

    However, I'll also share some of my knowledge with you. This knowledge is DAO based (and not MS-Access macro based). Also, I virtually never use queries that are directly in the .MDB or .ACCDB file. Rather, I build my queries in VB6, and execute them from there. So, I'm not sure about queries stored in a database file.

    But I do have experience creating tables from the DAO, and making those hidden. It's not nearly as easy nor as obvious as one might think. There is a dbHiddenObject attribute, which you mention above. However, this does NOT do what you think. It does NOT do the same thing as selecting the properties of a table and marking it as hidden. In fact, here's a comment in my code I've written to myself:
    Code:
        '
        ' There is a dbHiddenObject attribute but this attribute is NOT what it seems.
        ' This is used to create temporary tables that are deleted upon the next database compression.
        ' Also, tables created with this attribute are COMPLETELY hidden and will not even show when
        ' other hidden tables show.  In other words, do NOT use the dbHiddenObject attribute.
        ' The REAL hidden bit is &H80000000, as is shown in the code below.
        '
    Here, I'll post my entire DAO procedure for adding a new table to a database. You can possibly find some good tips for also making your queries hidden:

    EDIT: Just an FYI. The dbTheDatabase is a global object variable in my code, dimmed as "Public dbTheDatabase As DAO.Database".

    Code:
    Private Enum dbDataTypeEnum
        dbText = 10
        dbMemo = 12
        dbBoolean = 1
        dbInteger = 3
        dbLong = 4
        dbSingle = 6
        dbDouble = 7
        dbDate = 8
    End Enum
    Private Type dbFieldAddType
        fdfName As String
        fdfType As dbDataTypeEnum
        fdfSize As Long
        fdfDefaultValue As Variant
        fdfAllowZeroLength As Boolean ' For strings.
        fdfRequired As Boolean
        fdfDescription As String
    End Type
    
    Private Sub DbAddTable(sTableName As String, TheFields() As dbFieldAddType, Optional bHidden As Boolean = False)
        ' Make sure database is open, and EXCLUSIVE access is a good idea.
        ' Fields are added in the order in which they appear in TheFields.
        Dim tdf As DAO.TableDef ' TableDef
        Dim fdf As DAO.Field ' FieldDef
        Dim i As Long
        '
        Set tdf = dbTheDatabase.CreateTableDef(sTableName)
        For i = LBound(TheFields) To UBound(TheFields)
            DbAddFieldDetails tdf, TheFields(i), , True
        Next i
        '
        dbTheDatabase.TableDefs.Append tdf
        If bHidden Then tdf.Attributes = tdf.Attributes Or &H80000000 ' This uses the REAL hidden bit, and not dbHiddenObject.
        dbTheDatabase.TableDefs.Refresh
        '
        ' Now we have to go back and set the unicode compression on the text/memo fields.
        For i = LBound(TheFields) To UBound(TheFields)
            If TheFields(i).fdfType = dbText Or TheFields(i).fdfType = dbMemo Then
                Set fdf = tdf.Fields(TheFields(i).fdfName)
                DbSetProperty fdf, "UnicodeCompression", dbBoolean, True ' Always just set to true.
                tdf.Fields.Refresh
            End If
        Next i
        '
        Set fdf = Nothing
        Set tdf = Nothing
    End Sub
    I'd try the following line on your queries as a first attempt to set them as hidden:
    Code:
       If bHidden Then tdf.Attributes = tdf.Attributes Or &H80000000 ' This uses the REAL hidden bit, and not dbHiddenObject.
    Good Luck,
    Elroy

    EDIT2: Here are a couple of other procedure called by the above. The inclusion of these should make the above "DbAddTable" procedure complete (i.e., where it'll run in a new project, also with the "Public dbTheDatabase As DAO.Database" code.

    Code:
    Private Sub DbAddFieldDetails(tdf As DAO.TableDef, TheField As dbFieldAddType, Optional BeforeThisField As String = "AtTheEnd", Optional bIgnoreUnicodeCompression As Boolean = False)
        Dim fdf As DAO.Field ' FieldDef
        Dim i As Long
        Dim iBeforeOrder As Long
        '
        ' Ensure all fields have a unique ordinal value. ie: reset ALL ordinals.
        tdf.Fields.Refresh ' This makes sure that any prior addition is refreshed, especially when order (BeforeThisField) is important.
        For i = 0 To tdf.Fields.Count - 1
            tdf.Fields(i).OrdinalPosition = i
        Next i
        tdf.Fields.Refresh
        '
        If TheField.fdfName = "AutoID" Then
            Set fdf = tdf.CreateField(TheField.fdfName, dbLong, 4)
            fdf.Attributes = fdf.Attributes + dbAutoIncrField
        Else
            ' Field size is ignored by CreateField when it's not needed (according to DAO documentation).
            Set fdf = tdf.CreateField(TheField.fdfName, TheField.fdfType, TheField.fdfSize)
            If TheField.fdfType = dbText Or TheField.fdfType = dbMemo Then
                fdf.AllowZeroLength = TheField.fdfAllowZeroLength
            End If
            If Not IsNull(TheField.fdfDefaultValue) Then
                fdf.DefaultValue = TheField.fdfDefaultValue
            End If
            fdf.Required = TheField.fdfRequired
        End If
        '
        ' If AfterThisField <> "AtTheEnd" Then we need to figure out where to add it.
        If BeforeThisField = "AtTheEnd" Then
            fdf.OrdinalPosition = tdf.Fields.Count
        Else
            iBeforeOrder = tdf.Fields(BeforeThisField).OrdinalPosition
            For i = tdf.Fields.Count - 1 To iBeforeOrder Step -1
                tdf.Fields(i).OrdinalPosition = i + 1
            Next i
            tdf.Fields.Refresh
            fdf.OrdinalPosition = iBeforeOrder
        End If
        '
        tdf.Fields.Append fdf
        '
        If Not bIgnoreUnicodeCompression Then
            ' Created properties must be set AFTER the field is appended.
            If TheField.fdfType = dbText Or TheField.fdfType = dbMemo Then
                DbSetProperty fdf, "UnicodeCompression", dbBoolean, True ' Always just set to true.
            End If
        End If
        '
        tdf.Fields.Refresh
        Set fdf = Nothing
    End Sub
    
    Private Sub DbSetProperty(obj As Object, sPropertyName As String, iPropertyType As Long, vValue As Variant)
        Dim prop As DAO.Property
        '
        If DbPropertyExists(obj, sPropertyName) Then
            obj.Properties(sPropertyName) = vValue
        Else
            Set prop = obj.CreateProperty(sPropertyName, iPropertyType, vValue)
            obj.Properties.Append prop
            obj.Properties.Refresh
            Set prop = Nothing
        End If
    End Sub
    
    Private Function DbPropertyExists(obj As Object, sPropertyName As String) As Boolean
        Dim s As String
        '
        On Error Resume Next
        s = obj.Properties(sPropertyName).Name
        DbPropertyExists = (Err = 0)
        On Error GoTo 0
    End Function
    so will it be right like this
    Code:
    Public Sub HideTable(ByVal sTableName As String)
        Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = &H80000000
    End Sub

  4. #4

    Thread Starter
    Registered User
    Join Date
    Aug 2016
    Posts
    3

    Re: Hide queries in Access 2007

    Thanks Elroy. will play around with this to see if i can get the queries to hide.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    35,045

    Re: Hide queries in Access 2007

    Actually, rather than creating a different thread, just let any mod know and we can move this thread to the right forum. In this case, I'm not sure which forum would be most appropriate. It IS database related, but it also appears to be VBA related, and not really about SQL, so perhaps Office Development would be better?
    My usual boring signature: Nothing

  6. #6
    PowerPoster Elroy's Avatar
    Join Date
    Jun 2014
    Location
    Near Nashville TN
    Posts
    6,260

    Re: Hide queries in Access 2007

    shabzo,

    Yeah, sorry. I suppose I could have been a bit more clear about that.

    Yes, anywhere in my VB6 code that you see "dbTheDatabase", if you replaced it with "Application.CurrentDb", you should be good to go.

    Also, if you just remove all the "DAO." prefixes from the object variable declarations, that should also take care of those as well.

    I believe, with those two changes, you will have successfully converted my VB6 code to MS-Access VBA Macro code.

    Best Of Luck,
    Elroy
    Any software I post in these forums written by me is provided “AS IS” without warranty of any kind, expressed or implied, and permission is hereby granted, free of charge and without restriction, to any person obtaining a copy. Please understand that I’ve been programming since the mid-1970s and still have some of that code. My contemporary VB6 project is approaching 1,000 modules. In addition, I have a “VB6 random code folder” that is overflowing. I’ve been at this long enough to truly not know with absolute certainty from whence every single line of my code has come, with much of it coming from programmers under my employ who signed intellectual property transfers. I have not deliberately attempted to remove any licenses and/or attributions from any software. If someone finds that I have inadvertently done so, I sincerely apologize, and, upon notice and reasonable proof, will re-attach those licenses and/or attributions. To all, peace and happiness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width