-
2 Attachment(s)
[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:
http://www.vbforums.com/attachment.p...chmentid=38722
After:
http://www.vbforums.com/attachment.p...chmentid=38723
VB Code:
'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 http://www.vbforums.com/attachment.p...chmentid=38679
-
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. ;)
-
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.
-
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. :D
VB Code:
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
-
1 Attachment(s)
Re: [MS Access] - Hide Database Objects
It also will not show up in the Database properties dialog Contents tab window. :D
No "Table1" displayed in the listing.
-
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. ;)
-
Re: [MS Access] - Hide Database Objects
Ok, ok. Here is the VB6 way but now the thread may be moved to CodeBank - Classic VB6 :D
Or maybe create a duplicate thread for VB6.
VB Code:
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
-
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:
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
:(
-
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:
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
-
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?
-
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:
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
-
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! :thumb:
-
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. ;)
-
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. :eek2: This is because dbhiddenobjects are considered to be Temp Tables.
:afrog:
-
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. :)
-
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
-
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.
-
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
-
1 Attachment(s)
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
-
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
-
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.
-
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
-
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?
-
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
-
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.
-
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
-
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.
-
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
-
Re: [MS Access] - Hide Database Objects
Good Day RobDog888! How about queries? Is there any way of hiding them
programmatically? Thanks
-
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
-
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
-
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.
-
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
-
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.
-
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?
-
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 :D
-
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
Quote:
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.
-
Re: [MS Access] - Hide Database Objects
Seems it only applies to Tables and Queries for now.
-
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
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:
http://www.vbforums.com/attachment.p...chmentid=38722
After:
http://www.vbforums.com/attachment.p...chmentid=38723
VB Code:
'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 http://www.vbforums.com/attachment.p...chmentid=38679
-
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.