Hide / Unhide Database Objects With VBA

Did you know that you can display the Database window in a "Detail" view? Then you can sort by LastModified date, and the most recent object floats to the top of the list, making it easy to find among all the other objects.

jacksonmacd, yes I did know that.  But what I need to do is hide the objects from the user and then be able to unhide them when I need to work on it.

I know about all of the security stuff (tho I really don't want to use that for this one) and the AutoExec, and the StarUp Features.

I just need to be able to Hide and Unhide the objects.

Thanks
:>)

WonHop

Create an Autoexec Macro (if you don't all ready have one).

On the first row of Autoexec select RunCommand - and below in the Item box select WindowHide. This will hide your main database window.

On the next row in Autoexec either RunCode or OpenForm.

Make sure to go to the Startup and take out any form name in the Display Form: box.

Hello rauerd,  Thanks for your response.

I have this code below to hide the tables.  I am looking for this type of code to hide the other objects.

Function HideTables()

 Dim tdf As QueryDef
   
   For Each tdf In CurrentDb.QueryDefs
       If tdf.Name Like "msys*" Then
       Else
           tdf.Attributes = dbHiddenObject
       End If    
   Next tdf
   Set tdf = Nothing

End Function

For this reason, I will have to reject your answer.

Also as a matter of courtesy, we prefer that no one locks a question.  It takes it away from the other experts and the asker does not get as many responses.  We prefer to have the asker accept a comment as an answer.

Thanks
WonHop

WonHop,

I see - you don't want the entire DB window hidden. Is this for security reasons to keep certain individuals from seeing/changing objects? If so, how about a logon to dissallow certain users from viewing any objects.

Yes - I learned a bit ago about the Comment vs Answer thing. I certainly appreciate your tactfullness - Thank You.

You are welcome rauerd.  Yes that is what I want.  But I do not want to do a mdw file.  I have code already the stops the ShiftKey, give them a Custom Menu Bar, I did that StartUp Menu to hide the DB Window and the other toolbars.  I have code that will keep them from opening the DB past a certain date.
I am looking for code to hide the other objects so that I don't have to do it by hand.

This is just a demo database for now and I don't want them to see the objects if they try to import the info into another DB.

WonHop

OK..I found an article that lets me hide it if I put USYS in front of the Object Name.

You can hide an object by causing Microsoft Access to treat the object as a system object. Note that the Show System Objects option must be set to No in the Options dialog box in order for this method to work.

To hide an object, preface the object name with "USYS" (without quotation marks). For example, to hide the Employees table in the sample database NWIND.MDB, change its name to USYSEmployees. Tables, queries, forms, macros, reports, and modules can all be hidden in this manner.

I will leave this question open for a couple of days in hopes of finding a way to do it without changing the names.

WonHop

hi WonHop,

here is an example to hide or unhide tables:
(in my example i look for 2 tables to hide or unhide : tblMail and tblItems)

Private Sub HideTables()
Dim tdf As TableDef
Dim prpLus As Property

For Each tdf In CurrentDb.TableDefs
    If (tdf.Name = "tblMail") Or (tdf.Name = "tblItems") Then
            tdf.Attributes = dbHiddenObject
    End If
Next tdf
End Sub

Private Sub UnhideTables()
Dim tdf As TableDef
Dim prpLus As Property

For Each tdf In CurrentDb.TableDefs
    If (tdf.Name = "tblMail") Or (tdf.Name = "tblItems") Then
        tdf.Attributes = 0
    End If
Next tdf
End Sub


cheers
Ricky

btw, the tables will truly be unvisible, no matter what u select in the option box (tools --> options ...)

Hello Paurths.  Thanks for your response.  I have the code to hide the tables.  I got that from the Help File.  What I am looking for is how to hide all of the other objects with code.  I know about the Tools - Options.  I would like to hide and unhide the Querys, Forms, Reports, Macros, and Modules too.

Is that possible with code.  Also since I posted this question, I have been upgraded to Access 2000.

Thanks
WonHop

hi Wonhop,

the code i showed will hide or unhide, in this case, tables.

What i meant about the tools --> options  --> was this:
U can select a table, right click on it and set it to hide. But when u go to tools --> options u can set 'show hidden objects'.

The code i showed will hide objects in a manner that the tools --> options wont work to show the table again.
It will seem as if the table is actually not in the database...

Hi Paurths.  I did understannd what you meant.  But I need the code to hide everything.  Not just the tables.  I would like to hide and unhide the Querys, Forms, Reports, Macros, and Modules too using VBA code.  I cannot seem to find an example of that anywhere.  All I see are examples of how to hide the tables only.

Thanks
WonHop

WonHop:

What would you like to do with this question? Please address it.

thanks!
amp
community support moderator

It's time to clean up this topic area and that means taking care of this question. Your options at this point are:

1. Award points to the Expert who provided an answer, or who helped you most. Do this by clicking on the "Accept Comment as Answer" button that lies above and to the right of the appropriate expert's name.

2. PAQ the question because the information might be useful to others, but was not useful to you. To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

3.  Ask Community Support to help split points between participating experts.  Just comment here with details.

4.  Delete the question because it is of no value to you or to anyone else.  To use this option, you must state why the question is no longer useful to you, and the experts need to let me know if they feel that you're being unfair.

If you elect for option 2, 3 or 4, just post comment with details here and I'll take it from there. We also request that you review any other open questions you might have and update/close them.  Display all your question history from your Member Profile to view details.

PLEASE DO NOT AWARD THE POINTS TO ME.
____________________________________________

Hi Experts:

In the event that the Asker does not respond, I would very much appreciate your opinions as to which Expert ought to receive points (if any) as a result of this question.  Likewise, you can also suggest that I PAQ or delete the question.

Experts, please do not add further "answer" information to this question.  I will be back in about one week to finalize this question.

Thank you everyone.

amp
community support moderator

My comment was rejected because it wasn't what he really wanted.

Hello everyone.  Sorry for the late response.  

Let me shift the focus just a little.

Without creating a MDE file or using the MDW file, is there anyway to hide the Database Objects (MDB) so the cannot be imported, linked or viewed either manually or with VBA?

WonHop

Sorry...forgot part of the question.  

Without creating a MDE file or using the MDW file, is there anyway to hide the Database Objects (MDB)
so the cannot be imported, linked or viewed either manually or with VBA  "From another Database"

Thanks Ricky.

:0)

Wonhop

the key here is to use the application.SetHiddenAttribute method.... below is shown for queries

Private Sub MakeQryHidden()

    Dim qry As QueryDef
    Dim x As Integer
   
    For Each qry In CurrentDb.QueryDefs
   
        If Left(qry.Name, 1) <> "~" Then
            If MsgBox("Make hidden?  " & qry.Name, vbYesNo) = vbYes Then
                Call Application.SetHiddenAttribute(acQuery, qry.Name, True)
            Else
                Call Application.SetHiddenAttribute(acQuery, qry.Name, False)
            End If
        End If
    Next


End Sub