[MS Access] - Hide Database Objects

[RobDog888]

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:

'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
'Distribution: You can freely use this code in your own
'              applications provided that this copyright
'              is left unchanged, but you may not reproduce
'              or publish this code on any web site, online
'              service, or distribute as source on any
'              media without express permission.
'
'Requirements:
'MS Access version 97 (8.0) - 2003 (11.0)
'
'
'In a Module:
Option Explicit
Option Compare Database
 
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
'
'
'*********************************************************
'
'Example usage:
'Behind a Form
'
'
Option Explicit
Option Compare Database
 
Private Sub cmdHide_Click()
    HideTable "Table1"
End Sub
 
Private Sub cmdShow_Click()
    ShowTable "Table1"
End Sub

Gangsta Yoda

[RobDog888]

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.

[dee-u]

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.

[RobDog888]

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.

Option Explicit
'Add a reference to MS ActiveX Data Objects 2.x Library
Private Sub Command1_Click()
    Dim oConn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    
    Set oConn = New ADODB.Connection
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    oConn.Open
    
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM Table1;", oConn, adOpenKeyset, adLockOptimistic, adCmdText
    If oRs.BOF = True And oRs.EOF = True Then
        MsgBox "No Recs"
    Else
        MsgBox "Records"
    End If
    oRs.Close
    Set oRs = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub

[RobDog888]

It also will not show up in the Database properties dialog Contents tab window.
No "Table1" displayed in the listing.

[dee-u]

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

[RobDog888]

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.

Option Explicit
'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
'Distribution: You can freely use this code in your own
' applications provided that this copyright
' is left unchanged, but you may not reproduce
' or publish this code on any web site, online
' service, or distribute as source on any
' media without express permission.
'
'Requirements:
'MS Access version 97 (8.0) - 2003 (11.0)
'
'
'Add a reference to MS ActiveX Data Objects 2.x Library
'Add a reference to MS Acccess xx.0 Object Library
Private moApp As Access.Application
 
Private Sub Command1_Click()
    Dim oConn As ADODB.Connection
    Dim oRs As ADODB.Recordset
    
    Set oConn = New ADODB.Connection
    oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\RobDog888.mdb;Persist Security Info=False"
    oConn.Open
    Set oRs = New ADODB.Recordset
    oRs.Open "SELECT * FROM Table1;", oConn, adOpenKeyset, adLockOptimistic, adCmdText
    If oRs.BOF = True And oRs.EOF = True Then
        MsgBox "No Recs"
    Else
        MsgBox "Records"
    End If
    oRs.Close
    Set oRs = Nothing
    oConn.Close
    Set oConn = Nothing
End Sub
 
Public Sub HideTable(ByVal sTableName As String)
    moApp.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 1 'dbHiddenObject
End Sub
 
Public Sub ShowTable(ByVal sTableName As String)
    moApp.CurrentDb.TableDefs(sTableName).Properties("Attributes").Value = 0
End Sub
 
Private Sub Command2_Click()
    HideTable "Table1"
End Sub
 
Private Sub Command3_Click()
    ShowTable "Table1"
End Sub
 
Private Sub Form_Load()
    Application.OpenCurrentDatabase "D:\RobDog888.mdb", False
    Set moApp = Application
End Sub

[dee-u]

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

Private Sub Form_Load()
    On Error GoTo HandleError
    [B]Application.OpenCurrentDatabase "C:\test.mdb", False[/B]
    Set moApp = Application
    Exit Sub
HandleError:
    MsgBox Err.Description
End Sub

The error is:

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

I am using ADO 2.8 and Access 10.0

[RobDog888]

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.

Option Explicit
'Add a reference to MS Acccess xx.0 Object Library
Private moApp As Access.Application
 
Private Sub Form_Load()
    [b]Access[/b].Application.OpenCurrentDatabase "D:\RobDog888.mdb", False
    Set moApp = Application
End Sub

[dee-u]

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?

[RobDog888]

Access 10.0 is Access XP and 9.0 is Access 2000.

The parameters are correct.

Try this then...

Option Explicit
'Add a reference to MS Acccess xx.0 Object Library
Private moApp As Access.Application
 
Private Sub Form_Load()
    Set moApp = New Access.Application
    moApp.OpenCurrentDatabase "D:\RobDog888.mdb", False
End Sub

[dee-u]

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!

[RobDog888]

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.

[Oorang]

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.

[RobDog888]

Good note that I think I can come up with a work-around soon. Stay tuned to this same bat channel.

[JohnHamcoex]

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

[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.

[JohnHamcoex]

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

[RobDog888]

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.

'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

[JohnHamcoex]

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

[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.

[JohnHamcoex]

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

[RobDog888]

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?

[LuckySean]

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

[LuckySean]

So far, I've been able to find this:

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.

[techexpressinc]

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

[RobDog888]

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.

[techexpressinc]

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

[Rap656744]

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

[Ketting]

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

[Ketting]

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

[RobDog888]

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.

[TonyNL]

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

[RobDog888]

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.

[TonyNL]

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?

[RobDog888]

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

[Ketting]

@ 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.

[RobDog888]

Seems it only applies to Tables and Queries for now.

[cdc555]

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!!

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:

'Copyright © 2005 by RobDog888 (VB/Office Guru™). All Rights reserved.
'
'Distribution: You can freely use this code in your own
'              applications provided that this copyright
'              is left unchanged, but you may not reproduce
'              or publish this code on any web site, online
'              service, or distribute as source on any
'              media without express permission.
'
'Requirements:
'MS Access version 97 (8.0) - 2003 (11.0)
'
'
'In a Module:
Option Explicit
Option Compare Database
 
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
'
'
'*********************************************************
'
'Example usage:
'Behind a Form
'
'
Option Explicit
Option Compare Database
 
Private Sub cmdHide_Click()
    HideTable "Table1"
End Sub
 
Private Sub cmdShow_Click()
    ShowTable "Table1"
End Sub

Gangsta Yoda

[Shaggy Hiker]

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.