Solved

Hide / Unhide Database Objects With VBA

Posted on 2001-06-14
20
1,835 Views
Last Modified: 2008-02-26
Hello all
Is it possible to hide and unhide Queries, Forms, Macros and Modules using VBA?  I would like to be able to work with one Object set at a time.  I would like one functions just for the queries, one for the forms and so on.

Thanks
WonHop
0
Comment
Question by:WonHop
  • 9
  • 4
  • 3
  • +3
20 Comments
 

Expert Comment

by:jacksonmacd
ID: 6192123
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.
0
 
LVL 2

Author Comment

by:WonHop
ID: 6192143
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
0
 

Expert Comment

by:rauerd
ID: 6192326
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.

0
 
LVL 2

Author Comment

by:WonHop
ID: 6192462
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
0
 

Expert Comment

by:rauerd
ID: 6192550
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.
0
 
LVL 2

Author Comment

by:WonHop
ID: 6192705
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
0
 
LVL 2

Author Comment

by:WonHop
ID: 6193475
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
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6329978
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
0
 
LVL 12

Expert Comment

by:Paurths
ID: 6329979
btw, the tables will truly be unvisible, no matter what u select in the option box (tools --> options ...)
0
 
LVL 2

Author Comment

by:WonHop
ID: 6335000
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 12

Expert Comment

by:Paurths
ID: 6335404
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...


0
 
LVL 2

Author Comment

by:WonHop
ID: 6340480
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
0
 

Expert Comment

by:amp072397
ID: 6742516
WonHop:

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

thanks!
amp
community support moderator
0
 

Expert Comment

by:amp072397
ID: 6758944
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
0
 

Expert Comment

by:rauerd
ID: 6760160
My comment was rejected because it wasn't what he really wanted.

0
 
LVL 2

Author Comment

by:WonHop
ID: 6761914
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

0
 
LVL 2

Author Comment

by:WonHop
ID: 6761918
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"
0
 
LVL 12

Accepted Solution

by:
Paurths earned 200 total points
ID: 6762180
bad news,

even if u hide all the database objects,
it is still possible to read all the objects, their structures, field values, etc. from another db.

the example of 'for each tdf in db.Tabledefs' was shown for working inside the currentdb, but could easily be adjusted to read another db.

adjustment of the path, e.g. "c:\anyfolder\yourdb.mdb" instead of 'set db = currentdb'

cheers
Ricky
0
 
LVL 2

Author Comment

by:WonHop
ID: 6762234
Thanks Ricky.

:0)

Wonhop
0
 
LVL 5

Expert Comment

by:PatOBrien
ID: 7194480
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
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now