Hiding Objects in the Database Window



Hiding your tables, queries, forms, macros, and modules from casual users who can open the Database window is one of the easiest ways to protect your database objects. I have to qualify this statement upfront, though, because the menus that you use to make an object visible take no more time than it does to hide the objects. The key difference is that once you have hidden an object, the casual user should be blissfully unaware of its existence. Now I will walk you through the manual steps required to hide an object:

  1. Open the Database window.

  2. Find the object that you want to hide.

  3. Right-click the object (shown in Figure 3-14) and choose Properties.

    click to expand
    Figure 3-14: Right-click a table, query, form, report, macro, or module and choose Properties.

  4. Select the Attributes: Hidden check box (shown in Figure 3-15).

    click to expand
    Figure 3-15: Select the Attributes ” Hidden check box to hide the object.

This procedure will work the same for all objects in the database, but modules that you hide in the Database window will be visible in the Project Explorer in Access 2000 and later. In other words, this technique is designed only for "tricking" users who are having a "casual explore" of the Database window, rather than a database-savvy person. (For more on this issue, see the next section.)

You can view all hidden objects in the Database window by choosing Tools ˜ Options and selecting the View tab. Select the Hidden Objects check box (as shown in Figure 3-16), and the hidden objects appear in the Database window in gray rather than in the solid color used for visible objects.

click to expand
Figure 3-16: The Access 2002 View tab with the most secure settings selected.

You can also view all system objects by selecting the System Objects check box. All the system tables that will be visible through this process will be read-only and generally will not be useful to normal users. You should note that system tables should be left alone because they contain information that is vital for the database to operate correctly. System tables start with the MSys or USys prefix.

Tip  

An easy way to hide your table like a system table is to give it the prefix of USys. I recommend this prefix only for tables that relate to program settings in your database.

Just so that you know the difference, hidden objects (tables, queries, forms, reports , macros, modules) are objects that the developer or the user has created. System objects generally are objects that Microsoft Access has created and that the developer or users cannot modify.

The VBA Editor Exposes a Hidden Module

Hiding objects in the database window is a neat way to protect objects from your database's users, but you need to be aware that VBA modules (and code behind forms) are more exposed because they are not hidden in Visual Basic Editor, or specifically , the Visual Basic Explorer. For example, I'll first show you how to hide a module, and then I'll show you how a casual user might find the module in a different way.

  1. Open the demonstration database.

  2. Open the Database window by pressing F11.

  3. Select the Modules tab.

  4. Create a module by clicking New.

  5. Type the following subroutine into the code window:

     Sub AussieWarning   Msgbox "Watch out for the Crocodile!" End sub 
  6. Save the module as MyHiddenModule and close it.

  7. On the Module tab, right-click MyHiddenModule and choose Properties.

  8. Select the Attribute: Hidden check box and click Apply.

  9. If you can still see the module, choose Tools ˜ Options and select the View tab, and make sure that the Hidden check boxes are not selected.

  10. Now create another new module and press F2 to open the Object Browser, as shown in Figure 3-17.

    click to expand
    Figure 3-17: The Object Browser makes hidden modules visible. Example is from Access 97.

  11. Select Hid from the drop-down list at the top of the form. Hid is the name of the current project name for this database, and this list provides all the modules and VBA code in the database.

  12. You can now double-click MyHiddenModule, and you will be in the "hidden module."

In Access 2000 or later, it is even easier to find a hidden module, as I will demonstrate in the following instructions:

  1. Create a new module as described in steps 1 through 9 of the previous procedure.

  2. Open Visual Basic Editor by pressing ALT+F11 or by opening any module in design view.

  3. On the left side of Figure 3-18, you will see a pane with a tree view of all the VBA code in the project. If you cannot see this window, choose View ˜ Project Explorer.

    click to expand
    Figure 3-18: The Project Explorer window enables you to navigate to all modules and code in your project.

  4. Now you or your users can explore down and double-click MyHiddenModule to get into the supposedly hidden module.

If you are starting to worry about the secrecy of your code, don't fret too much at this stage ”I discuss solutions later on in this book. These solutions include creating security for your code by using the MDE format (Chapter 11), special code to protect your modules by using workgroup security (Chapter 8), and using the VBA project password (Chapter 11). Now we will review how you can hide objects in your database by using VBA code.

Hiding Objects in the Database Window by using VBA Code

In the demonstration form called frmHiddenAndSystemObjects (shown in Figure 3-19), VBA code is illustrated to show you to change attributes to hide objects from users.

click to expand
Figure 3-19: This form demonstrates VBA code options for system tables, hidden objects, and temporary tables.

The first step that we will need to do in the application is to make sure that users cannot see the hidden and system objects in the Database window. This step is accomplished in VBA code by the A button (shown in Figure 3-19), as follows :

 Application.SetOption "Show Hidden Objects ", False Application.SetOption " Show System Objects ", False 

Now that you have cleared these two options, you should test that they work by choosing Tools ˜ Options and selecting the View tab. Button B on the demonstration form will also open the Options Tab form for you. Or, you can view options by using the VBA code example under Button B (Figure 3-19), as follows:

 RunCommand acCmdOptions 

Selecting or Clearing the Hidden Attribute for all Objects


In Access 97, it was not possible to hide an object in the Database window by using VBA code. In Access 2000, the GetHiddenAttribute and SetHiddenAttribute methods were added to allow you to retrieve and change the attributes of all the objects in an Access database. The syntax for these two methods follows:

 GetHiddenAttribute(  objecttype  ,  objectname  ) SetHiddenAttribute(ObjectType, ObjectName, fHidden) 

Note the following for the preceding code:

  • ObjectType requires one of the following constants for Access objects: acDataAccessPage , acDiagram , acForm , acFunction , acMacro , acModule , acQuery , acReport , acServerView , acStoredProcedure , acTable .

  • ObjectName is the name of object.

  • fHidden is True if you want to hide the object and False if you want to view it.

In the demonstration form called frmHiddenAndSystemObjects , the VBA code example under button C (shown in Figure 3-19) shows you how first to find and then to swap the current value of the hidden attribute for both a table and a form.

 Dim tableIsHidden As Boolean Dim formIsHidden As Boolean   tableIsHidden = Application.GetHiddenAttribute(acTable, "myTable")   If tableIsHidden Then     Application.SetHiddenAttribute acTable, "myTable", False   Else     Application.SetHiddenAttribute acTable, "myTable", True   End If   formIsHidden = Application.GetHiddenAttribute(acForm, "frmGotovb123")   If formIsHidden Then     Application.SetHiddenAttribute acForm, "frmGotovb123", False   Else     Application.SetHiddenAttribute acForm, "frmGotovb123", True   End If statusMsg = "The hidden attribute for the table myTable is currently set to " & _ tableIsHidden & vbCrLf & _ "The hidden attribute for the form frmGotovb123 is currently set to " & _  formIsHidden MsgBox statusMsg 

Making a Table a Read-Only System Table

By using VBA code, you can change the attributes of a table so that it becomes a system table. Once you change this property with VBA code, the table will only be visible if you choose Tools ˜ Options, select the View tab, and select the System Objects check box (shown in Figure 3-16). More important, though, is the fact that the table is read-only in the database window, just like a system table.

You can use VBA code to change Access table attributes by manipulating the TableDef object. TableDef objects have an Attributes property that specifies the characteristics of the table. This property is confusing (to me anyway) because the clever developers at Microsoft have managed to store a variety of different options in the one number, which is then stored as the attribute property value. Included in this property value are numbers to signify that the table is a system table, a linked table, or just a table.

I've found that the following VBA code gives both tables and linked tables a system table attribute. The example under the D button (shown in Figure 3-19) on the frmHiddenAndSystemObjects form reverts the setting back to its original attribute if the table is already a system table. Here's the full procedure:

 Private Sub swapSys_Click() ' To hide a table, make it a system object. If it already is a system object, ' then make it visible again by resetting its attributes to 0. Dim dbs As DAO.Database Set dbs = CurrentDb With dbs.TableDefs("World_Demo") ' System/no system attribute for normal tables.   If .Attributes = 0 Then     .Attributes = dbSystemObject   ElseIf .Attributes = dbSystemObject Then     .Attributes = 0 ' System/no system attribute for linked tables.   ElseIf (.Attributes And dbAttachedTable) Then     If (.Attributes And dbSystemObject) Then       .Attributes = 0     Else       .Attributes = dbSystemObject     End If   End If End With End sub 

Now that you have made a table a system table, if you open this table from the Database window, it will always open in read-only mode. This is a good safeguard for the database-exploring user.

Note  

The dbSystemObject table attribute makes a table hidden and read-only if you open it from the Database window. If you open the table through a form or a query, you can make changes and add records. The read-only attribute does not apply to linked tables (discussed in Chapter 4).

Temporary Tables and Hidden Tables

While searching the help files and the Internet for information on hidden tables and objects, I was misled by a table attribute called dbHiddenObject . After some research and a lot of reading, I found that Access seems to use a table's dbHidden attribute to define a temporary table. After looking through the relevant Access newsgroups, I found that many developers were making the mistake of using this attribute to hide a file, which in fact deletes the table when the database was compacted . Thankfully, there were a number of warnings on most of these postings about the problems associated with this attribute.

Caution  

To hide a table, do not use the hidden attribute in VBA code because Access uses this attribute to flag a temporary table. When you compact the database, Access uses the hidden attribute as a signal to delete all records from the table. You can use the hidden attribute when you want to save information, however, as for a temporary table. The following code is an example:

 ' Please do not use this code to hide a table. Use it make a temporary table. CurrentDb.TableDefs("Table1").Attributes = dbHiddenObject 

A note on Access 97: After much investigation, I have concluded that there is no supported way in Access 97to hide an object in the database window by using VBA code. Access handles the hidden objects by making changes to the flag field in the MSysObjects system table, which is not a table that you can manipulate by using VBA code. If you want to give an object a hidden property, you must do it manually as I described previously in this chapter.

Now we will take a look at how we can document the system and temporary tables in your database.

Listing the System and Temporary Tables in Your Database

The final example on the frmHiddenAndSystemObjects demonstration form shows how you can loop through all the tables in your database and display only those that are system tables or temporary (dbhiddenobject) tables. If you look carefully at the code, you will see that system tables that have the "MSys" prefix are excluded from the list. To view the code, have a look at button E's onClick event, as follows:

 Private Sub viewSystemTables_Click() ' Showing system and temporary tables requires a reference to the DAO library. Dim dbs As DAO.Database, i As Integer, tablesStatus As String Dim bolTempTbl As Boolean, bolSystemTbl As Boolean, bolHiddenTbl As Boolean Dim prop As DAO.Property, tabDef As DAO.TableDef tablesStatus = "" Set dbs = CurrentDb For i = 0 To dbs.TableDefs.Count - 1   If Left(dbs.TableDefs(i).[Name], 4) <> "msys" Then '   Test for temporary and system tables using table attributes.     bolTempTbl = dbs.TableDefs(i).Properties!Attributes And dbHiddenObject     bolSystemTbl = dbs.TableDefs(i).Properties!Attributes And dbSystemObject '   Test if table is hidden.   bolHiddenTbl = GetHiddenAttribute(acTable, dbs.TableDefs(i).Name)     If (bolTempTbl = True) Or (bolSystemTbl = True) Or (bolHiddenTbl = True) Then       tablesStatus = tablesStatus & (dbs.TableDefs(i).[Name]) & _        IIf(bolTempTbl, " - Temporary table ", "") & _        IIf(bolSystemTbl, " - System table ", "") & _        IIf(bolHiddenTbl, " - Hidden table ", "") & vbCrLf     Else     End If   End If Next If Len(tablesStatus) = 0 Then   MsgBox "No hidden, temporary or system tables were found" Else   MsgBox tablesStatus, vbInformation, "Hidden, System and Temporary tables" End If End Sub 

The manually hidden tables do not appear in Access 97 because this information is stored separately in the MSysObjects table. Now I will show you how you can be a little more specific in what you hide.

Hiding Columns From Users

For another way to hide information, you can choose the Hide Columns command in a table or a query. To hide a column in a table, you can right-click the top of a column and choose Hide Columns (shown in Figure 3-20).

click to expand
Figure 3-20: Hiding a column in a table or a query can help protect your information.

To make the column reappear, choose Format ˜ Unhide Columns. To program this procedure by using VBA code, search help for the ColumnHidden property.

That concludes the detail on using options and attributes to protect your database. Now I will describe where you can find additional resources on topics related to the discussions in this chapter.




Real World Microsoft Access Database Protection and Security
Real World Microsoft Access Database Protection and Security
ISBN: 1590591267
EAN: 2147483647
Year: 2003
Pages: 176

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net