Results 1 to 40 of 40

Thread: [MS Access] - Hide Database Objects

  1. #1

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Arrow [MS Access] - Hide Database Objects

    Ever need to set a Table or other Access DB object to Hidden programmatically? Well here's the way to do it that
    makes the object hidden EVEN if the user unchecks the "Show Hidden Objects" feature in the Options dialog box.

    There are no ways to show the Object unless you programmatically turn it back on.

    Before:


    After:


    VB Code:
    1. 'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
    2. '
    3. 'Distribution: You can freely use this code in your own
    4. '              applications provided that this copyright
    5. '              is left unchanged, but you may not reproduce
    6. '              or publish this code on any web site, online
    7. '              service, or distribute as source on any
    8. '              media without express permission.
    9. '
    10. 'Requirements:
    11. 'MS Access version 97 (8.0) - 2003 (11.0)
    12. '
    13. '
    14. 'In a Module:
    15. Option Explicit
    16. Option Compare Database
    17.  
    18. Public Sub HideTable(ByVal sTableName As String)
    19.     Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = dbHiddenObject
    20. End Sub
    21.  
    22. Public Sub ShowTable(ByVal sTableName As String)
    23.     Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
    24. End Sub
    25. '
    26. '
    27. '*********************************************************
    28. '
    29. 'Example usage:
    30. 'Behind a Form
    31. '
    32. '
    33. Option Explicit
    34. Option Compare Database
    35.  
    36. Private Sub cmdHide_Click()
    37.     HideTable "Table1"
    38. End Sub
    39.  
    40. Private Sub cmdShow_Click()
    41.     ShowTable "Table1"
    42. End Sub
    Gangsta Yoda
    Attached Images Attached Images   
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  2. #2

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    A question was asked and I will reply here since its beneficial to all.

    Will the attribute remain when the db is closed or Access is closed and re-opened?

    When you close and re-open either the DB or Access the hidden state of the table will remain. So if you forget the name of the table
    you will need to iterate through the TableDefs collection to retrieve the name. No other way to find it.

    Also, when you call the sub to hide or show the table you may have to press F5 to Refresh the view in Access.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [MS Access] - Hide Database Objects

    And may I further ask, if the table is hidden by your snippet, could I still use it in VB? Like query it using "SELECT * FROM HiddenTable"? I have not tried using it though.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  4. #4

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Ok, I wrote a small quick connection and recordset procedure and it connects and retrieves records in a recordset
    just like as if it was visible.

    VB Code:
    1. Option Explicit
    2. 'Add a reference to MS ActiveX Data Objects 2.x Library
    3. Private Sub Command1_Click()
    4.     Dim oConn As ADODB.Connection
    5.     Dim oRs As ADODB.Recordset
    6.    
    7.     Set oConn = New ADODB.Connection
    8.     oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    9.     oConn.Open
    10.    
    11.     Set oRs = New ADODB.Recordset
    12.     oRs.Open "SELECT * FROM Table1;", oConn, adOpenKeyset, adLockOptimistic, adCmdText
    13.     If oRs.BOF = True And oRs.EOF = True Then
    14.         MsgBox "No Recs"
    15.     Else
    16.         MsgBox "Records"
    17.     End If
    18.     oRs.Close
    19.     Set oRs = Nothing
    20.     oConn.Close
    21.     Set oConn = Nothing
    22. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    It also will not show up in the Database properties dialog Contents tab window.
    No "Table1" displayed in the listing.
    Attached Images Attached Images  
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  6. #6
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [MS Access] - Hide Database Objects

    It would be great if you would also show its equivalent code (hiding a specific table) in VB6.0.
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  7. #7

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Ok, ok. Here is the VB6 way but now the thread may be moved to CodeBank - Classic VB6
    Or maybe create a duplicate thread for VB6.

    VB Code:
    1. Option Explicit
    2. 'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
    3. '
    4. 'Distribution: You can freely use this code in your own
    5. ' applications provided that this copyright
    6. ' is left unchanged, but you may not reproduce
    7. ' or publish this code on any web site, online
    8. ' service, or distribute as source on any
    9. ' media without express permission.
    10. '
    11. 'Requirements:
    12. 'MS Access version 97 (8.0) - 2003 (11.0)
    13. '
    14. '
    15. 'Add a reference to MS ActiveX Data Objects 2.x Library
    16. 'Add a reference to MS Acccess xx.0 Object Library
    17. Private moApp As Access.Application
    18.  
    19. Private Sub Command1_Click()
    20.     Dim oConn As ADODB.Connection
    21.     Dim oRs As ADODB.Recordset
    22.    
    23.     Set oConn = New ADODB.Connection
    24.     oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    25.     oConn.Open
    26.     Set oRs = New ADODB.Recordset
    27.     oRs.Open "SELECT * FROM Table1;", oConn, adOpenKeyset, adLockOptimistic, adCmdText
    28.     If oRs.BOF = True And oRs.EOF = True Then
    29.         MsgBox "No Recs"
    30.     Else
    31.         MsgBox "Records"
    32.     End If
    33.     oRs.Close
    34.     Set oRs = Nothing
    35.     oConn.Close
    36.     Set oConn = Nothing
    37. End Sub
    38.  
    39. Public Sub HideTable(ByVal sTableName As String)
    40.     moApp.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 1 'dbHiddenObject
    41. End Sub
    42.  
    43. Public Sub ShowTable(ByVal sTableName As String)
    44.     moApp.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
    45. End Sub
    46.  
    47. Private Sub Command2_Click()
    48.     HideTable "Table1"
    49. End Sub
    50.  
    51. Private Sub Command3_Click()
    52.     ShowTable "Table1"
    53. End Sub
    54.  
    55. Private Sub Form_Load()
    56.     Application.OpenCurrentDatabase "D:\RobDog888.mdb", False
    57.     Set moApp = Application
    58. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  8. #8
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [MS Access] - Hide Database Objects

    I tried to use it (yap, only now) but I am getting an error in the ff. line...

    VB Code:
    1. Private Sub Form_Load()
    2.     On Error GoTo HandleError
    3.     [B]Application.OpenCurrentDatabase "C:\test.mdb", False[/B]
    4.     Set moApp = Application
    5.     Exit Sub
    6. HandleError:
    7.     MsgBox Err.Description
    8. End Sub

    The error is:

    Description: Method 'OpenCurrentDatabase' of object '_Application' failed Number: -2147417851

    I am using ADO 2.8 and Access 10.0

    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  9. #9

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    I wrote the example on ADO 2.7 and Access 2003. Do you have the same parameter requirements for the function? Is your Application object the only one or are you using other Office references like Word's Application object?

    Try this for a test if you have other Application objects, change the db path to yours.

    VB Code:
    1. Option Explicit
    2. 'Add a reference to MS Acccess xx.0 Object Library
    3. Private moApp As Access.Application
    4.  
    5. Private Sub Form_Load()
    6.     [b]Access[/b].Application.OpenCurrentDatabase "D:\RobDog888.mdb", False
    7.     Set moApp = Application
    8. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  10. #10
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [MS Access] - Hide Database Objects

    I just copy-pasted your code and added the two references...

    The parameters are filepath , Exclusive and bstrPassword... Still no success, I even tried it with 2.7 but still does not work, maybe it wont work on Access 2000?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  11. #11

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Access 10.0 is Access XP and 9.0 is Access 2000.

    The parameters are correct.

    Try this then...

    VB Code:
    1. Option Explicit
    2. 'Add a reference to MS Acccess xx.0 Object Library
    3. Private moApp As Access.Application
    4.  
    5. Private Sub Form_Load()
    6.     Set moApp = New Access.Application
    7.     moApp.OpenCurrentDatabase "D:\RobDog888.mdb", False
    8. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  12. #12
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,123

    Re: [MS Access] - Hide Database Objects

    It's working now, it seems I got some error due to wrong references, I've got Access 2000 but I could only reference Microsoft Access 10.0 and not with 9.0, it seems there was 2002 installed in this machine, I un-installed it and everything went fine... Thanks a lot, your snippet is really cool!
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  13. #13

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Glad it is sorted out now. I even realized I could have wrote the VB6 code Form_Load better. Dont know what I was thinking at that time.

    Post #11 is the best code for the Form_Load.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  14. #14
    New Member
    Join Date
    May 2006
    Posts
    5

    Talking Re: [MS Access] - Hide Database Objects

    Just a side note. Hiding tables with this method will cause them to be deleted when you perform a compact and repair. This is because dbhiddenobjects are considered to be Temp Tables.

  15. #15

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Good note that I think I can come up with a work-around soon. Stay tuned to this same bat channel.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  16. #16
    New Member
    Join Date
    May 2006
    Posts
    15

    Re: [MS Access] - Hide Database Objects

    Quote Originally Posted by RobDog888
    Good note that I think I can come up with a work-around soon. Stay tuned to this same bat channel.
    Rob,

    Did you ever come up with a work-around for this? If so, it would fit my needs to a T.

    Thanks,

    John

  17. #17
    New Member
    Join Date
    May 2006
    Posts
    5

    Re: [MS Access] - Hide Database Objects

    I'm not Rob, but I find using dbSystemObject is a good way to prevent people from messing around. With that flag you can only interact with the data programmatically.

  18. #18
    New Member
    Join Date
    May 2006
    Posts
    15

    Re: [MS Access] - Hide Database Objects

    Quote Originally Posted by Oorang
    I'm not Rob, but I find using dbSystemObject is a good way to prevent people from messing around. With that flag you can only interact with the data programmatically.
    That is very interesting. Can you elaborate or tell me where I can learn more about dbSystemObject?

    Thanks

  19. #19

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    You would just switch the dbHiddenObject constant or "1" to use dbSystemObject. So far the only drawback I can tell is that in Access 97-2003 if the user checks the "Show Hidden and system Objects" it will show the object you were trying to hide, thus defeating the pirpose of hiding the table.

    But now in Access 2007 there is no "Show..." option (at least I havent found it yet) so in 2007 this may be better to use. Or just use my original way but dont do a C&R or change the tables property back before the C&R and then change it back after.

    Code:
    'In Access VBA IDE
    
    Public Sub TestHideMe()
        HideTable "Table1"
    End Sub
    
    Public Sub TestShowMe()
        ShowTable "Table1"
    End Sub
    
    Public Sub HideTable(ByVal sTableName As String)
        Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = dbSystemObject 'dbHiddenObject
    End Sub
    
    Public Sub ShowTable(ByVal sTableName As String)
        Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
    End Sub
    Attached Images Attached Images  
    Last edited by RobDog888; Jun 30th, 2008 at 07:45 PM.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  20. #20
    New Member
    Join Date
    May 2006
    Posts
    15

    Re: [MS Access] - Hide Database Objects

    RD,

    Thanks for the reply.

    I sorta figured it out regarding dbSystemObject showing. Too bad.

    Yeah, I think I'll do exacting what you describe with an on/off cycle before C&R. The only problem with that is that sometimes our app won't open the database at all. Then we normally use JetComp. If we do that now, we'll be snookered.

    If you can figure a way to make the table (we only need one to hide) visible before we JetComp, I'd love to hear it.

    Thanks.

    John

  21. #21

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    You can use ADO or ADOX to access the table without using the AOM instead as I'm sure you "could" adjust the property of the table that way too.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  22. #22
    New Member
    Join Date
    May 2006
    Posts
    15

    Re: [MS Access] - Hide Database Objects

    RD,

    I'm not sure I understand you. We use JetComp when our VB app cannot open the database. If we use ADO, won't we still have to use the

    Application.OpenCurrentDatabase "D:\RobDog888.mdb", False

    before we adjust the property? If so, would it work even if the connection string in VB won't? If so, that would be great.

    Thanks,

    John

  23. #23

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    No you would just use the ADO with the proper conn string but if you cant open the db with ADO then you will be fored to use the JetComp which will wipe your hidden tables.

    I guess the next q would be why does your db get corrupted so often?
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  24. #24
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Re: [MS Access] - Hide Database Objects

    Quote Originally Posted by RobDog888
    You can use ADO or ADOX to access the table without using the AOM instead as I'm sure you "could" adjust the property of the table that way too.
    RD,

    Would you have any sample code for doing this with ADO?

    Thanks

  25. #25
    New Member
    Join Date
    Jul 2008
    Posts
    6

    Re: [MS Access] - Hide Database Objects

    So far, I've been able to find this:
    Code:
    Dim oCat As ADOX.Catalog
    Set oCat = New ADOX.Catalog
    oCat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & strDB
    Dim oTbl As ADOX.Table
    Set oTbl = New ADOX.Table
    Set oTbl = oCat.Tables(strOldTable)
    oTbl.Properties("Jet OLEDB:Table Hidden In Access").value = True
    But, this does not do the same thing. It sets the Hidden attribute on the table and allows it to be seen if Hidden is selected in the View box.

    Is there some other attribute that works like the Access application code?

    Thanks.

  26. #26
    New Member
    Join Date
    Aug 2008
    Posts
    2

    Re: [MS Access] - Hide Database Objects - install??

    I have a user where I do not want them to see any of the database objects, just the form to do the update of the couple fields they are allowed.

    I splitted the MS-Access db with forms, reports, objects in one file and tables in the other.

    Then I made two of the front ends. One for the controller person in-charge and one for the data entry person.

    I see your VB code. I am not a VB coder.

    How would install this to hide the tables, reports and other forms, from the user that I only want to have access to one query to do the one update?

    Thanks Russ

  27. #27

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    You have a couple of choices...

    Upon opening of the database you can hide all related toolbars and menus with VBA code (CommandBars collection). Or you can use Workgroup security to prevent access to whatever objects but they can see the objects only (not the data, just the object names). Or you can use code like I posted to set the objects to hidden but if they have access to the IDE they could always reset the attributes if they knew what the object(s) names are.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  28. #28
    New Member
    Join Date
    Aug 2008
    Posts
    2

    Re: [MS Access] - Hide Objects - newbie how to implement?

    I am not sure how to install the VB, with the DB split, I would think it would only effect the one user that I want to block and work out great!

    Thx for your inventing and publishing your code

    Russ

  29. #29
    New Member
    Join Date
    Sep 2008
    Posts
    1

    Re: [MS Access] - Hide Database Objects

    Good Day RobDog888! How about queries? Is there any way of hiding them
    programmatically? Thanks

  30. #30
    New Member
    Join Date
    Dec 2008
    Posts
    3

    Re: [MS Access] - Hide Database Objects

    I have never used VB scripting before, but this script to hide tables works great. Thank you RobDog888!

    Question: can I also hide forms in the same way? And if so, how should I alter the code?

    Thank you very much. Ketting
    Last edited by Ketting; Dec 27th, 2008 at 11:46 AM.

  31. #31
    New Member
    Join Date
    Dec 2008
    Posts
    3

    Re: [MS Access] - Hide Database Objects

    Dear RobDog,

    I have sent you numerous messages. If the answer to my question is that you do not know how to programmatically hide forms (or queries) in Access 2003, please just say so. This will also help, because then I can accept that it may not be possible (for you and thus for me and other users of this forum). Thank you very much.

    Ketting

  32. #32

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    You sent me 2 PM's. I volunteer my time here on the site and work a full time job (currently getting ready to rollout a huge release) as well as run my own software consulting business on the side so my time it very limited lately.

    I will have a little time tonight so I will test it out for you then.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  33. #33
    New Member
    Join Date
    Dec 2008
    Posts
    2

    Re: [MS Access] - Hide Database Objects

    Yes, I have the same issue for Forms as well. It would save me a lot of time.
    Ketting, when you solve the problem, could you send me the solution as well?

    Thanx!
    Tony

  34. #34

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Doesnt seem to be a straight forward way to read/write to a Forms Attribute property. You can however do it manually by right clicking the form object in the db's main view and select Properties.... Then check the box "Hidden". Note: no eeay way to restore it if using Access 2007 as there is no longer a Show Hidden objects menu item. This will also not mke it hidden if the user has Show Hidden objects selected.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  35. #35
    New Member
    Join Date
    Dec 2008
    Posts
    2

    Re: [MS Access] - Hide Database Objects

    I use the version 2003, and I have already found that property. The way I would prefer is by using code, but I didn't find a programmatical solution. Thanx RobDog888 for your time. So, for 2003 it is not possible to create a programmable show/hide solution for forms? It's almost unbelievable. Ketting, how did/do you solve the problem?

  36. #36

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    I will try more tomorrow night as its 3am now lol. But I iterated the properties of a form and the Attribute property, like used on a table, is not there or accessible. Sure you could probably go into the System table and figure out the proper value to OR to the proper column for it but Im sure you guys can check that out while I sleep
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  37. #37
    New Member
    Join Date
    Dec 2008
    Posts
    3

    Re: [MS Access] - Hide Database Objects

    @ TonyNL, I have made a lot of trials and errors but I haven't figured out how to programatically hide forms in Access 2003.

    @ RobDogg: thanks for your time! I know how to hide a form using the form properties, but as you stated in your original posting: it is better to do it programmatically (see below).

    I hope someone can come up with a solution.

    Ketting

    Ever need to set a Table or other Access DB object to Hidden programmatically? Well here's the way to do it that
    makes the object hidden EVEN if the user unchecks the "Show Hidden Objects" feature in the Options dialog box.

    There are no ways to show the Object unless you programmatically turn it back on.

  38. #38

    Thread Starter
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: [MS Access] - Hide Database Objects

    Seems it only applies to Tables and Queries for now.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  39. #39
    New Member
    Join Date
    Sep 2018
    Posts
    1

    Re: [MS Access] - Hide Database Objects

    Hi
    Super newbie question -- where do i put this code?

    I have a database where the tables are hidden - the person who built it no longer works for us and i need to update the data so i was going to try and unhide with code as they were hidden with code

    but i dont even know where to put the code to do that

    Thanks!!





    Quote Originally Posted by RobDog888 View Post
    Ever need to set a Table or other Access DB object to Hidden programmatically? Well here's the way to do it that
    makes the object hidden EVEN if the user unchecks the "Show Hidden Objects" feature in the Options dialog box.

    There are no ways to show the Object unless you programmatically turn it back on.

    Before:


    After:


    VB Code:
    1. 'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
    2. '
    3. 'Distribution: You can freely use this code in your own
    4. '              applications provided that this copyright
    5. '              is left unchanged, but you may not reproduce
    6. '              or publish this code on any web site, online
    7. '              service, or distribute as source on any
    8. '              media without express permission.
    9. '
    10. 'Requirements:
    11. 'MS Access version 97 (8.0) - 2003 (11.0)
    12. '
    13. '
    14. 'In a Module:
    15. Option Explicit
    16. Option Compare Database
    17.  
    18. Public Sub HideTable(ByVal sTableName As String)
    19.     Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = dbHiddenObject
    20. End Sub
    21.  
    22. Public Sub ShowTable(ByVal sTableName As String)
    23.     Application.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
    24. End Sub
    25. '
    26. '
    27. '*********************************************************
    28. '
    29. 'Example usage:
    30. 'Behind a Form
    31. '
    32. '
    33. Option Explicit
    34. Option Compare Database
    35.  
    36. Private Sub cmdHide_Click()
    37.     HideTable "Table1"
    38. End Sub
    39.  
    40. Private Sub cmdShow_Click()
    41.     ShowTable "Table1"
    42. End Sub
    Gangsta Yoda

  40. #40
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,943

    Re: [MS Access] - Hide Database Objects

    You'd only need the ShowTable sub, and that would have to be on a module (a macro in Access). However, you'd also have to run that sub, which is probably what the macro would do, but I haven't written a macro for Access.
    My usual boring signature: Nothing

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