O'Reilly logo

Access Cookbook, 2nd Edition by Andy Baron, Paul Litwin, Ken Getz

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 4. Applications

This chapter is a compendium of tips and suggestions for making your application development go more smoothly and your applications look more professional. You'll learn how to convert queries into embedded SQL strings providing data for forms or reports. You'll learn how to build an object inventory so you can document your applications better, how to ensure that properties for objects that should match up actually do, and how to disable screen output more effectively than the methods Access provides internally can. You'll find tips on discerning the current language version of Access and modifying text in error messages and on forms and reports to accommodate the current language. You'll see how to set and restore the Access caption and how to set startup options for your application. You'll also see how to use the Windows File Open/Save dialogs and how to clear out test data before shipping your application. The final topic explains how to implement user-level Access security.

Warning

Some of the topics in this chapter take advantage of the MicrosoftData Access Objects (DAO) library. By default, when you create a newapplication in Access 2000 or later, Access doesn't includea reference to this library. Although each of the samples for thischapter includes this reference, if you create a new application andimport modules from the samples, your code won't work. In order to be able to use imported code that uses DAO objects, you'll need to select Tools References... to display the References dialog box, and select the Microsoft DAO library.

4.1. Convert Queries into Embedded SQL Statements

Problem

Access's Query Builder makes it easy to create SQL statements as row sources for combo boxes or as record sources for forms and reports. You'd prefer to use SQL statements for row and record sources because they reduce the number of unnecessary objects in your databases. Is there an easy way to make these conversions? What's the trade-off of using embedded SQL statements instead of query objects to provide your data?

Solution

There is no automatic conversion utility to transform queries into SQL statements, but you can use the View SQL button on the Query Design toolbar to display a query's SQL statement, copy it to the Windows clipboard, and then paste it into the RecordSource or RowSource property of a form or combo box.

Open 04-01.MDB and look at the form frmCompanyInfoQuery. This form has a simple query as its record source; the combo box in its header also has a query as its row source. Neither of these queries is needed elsewhere, so they are prime candidates for conversion into SQL statements.

Take the following steps to convert a query, using the form's record source query as an example. These steps have already been taken for the form frmCompanyInfoSQL, both for the form's RecordSource property and for the combo box's RowSource property.

  1. Open the form whose record source you want to convert to a single SQL statement in design view, and make sure that the properties sheet is open (Figure 4-1).

A form's properties sheet, with a query as its RecordSource property

Figure 4-1. A form's properties sheet, with a query as its RecordSource property

  1. Click on the Build button (...) next to the RecordSource property to open the Query Builder for the record source query.

  2. With the Query Builder open, click on the View SQL button on the toolbar or select View SQL.

  3. The SQL window opens, displaying the query as a SQL statement, as shown in Figure 4-2.

The SQL window for a simple query

Figure 4-2. The SQL window for a simple query

  1. Highlight the entire SQL statement and press Ctrl-C or select Edit Copy to copy it to the clipboard.

  2. Close the SQL window.

  3. Highlight the query name in the RecordSource properties sheet and press Ctrl-V or select Edit Paste to replace the query name with the SQL statement. Figure 4-3 shows the form's RecordSource property with the SQL statement in place.

A form's properties sheet with a SQL statement as its RecordSource property

Figure 4-3. A form's properties sheet with a SQL statement as its RecordSource property

  1. Delete the original RecordSource query from the database container.

Discussion

Most Access queries can be converted back and forth between the graphical representation shown in the Query Builder window and the SQL representation of the query. The SQL window makes it easy to extract a query's SQL statement and use it directly as a record source or row source or in VBA code. Because all queries in Access can be represented as SQL statements, you have a choice—you can base a form or report on a query, or you can supply the SQL string directly in the properties sheet.

Converting row source queries into SQL statements lets you eliminate many trivial queries that have no purpose other than filling forms or combo boxes. If you have a SQL statement as a record or row source, you can open the Query Builder window to view or modify it, which makes it easy to use SQL statements in place of queries. Access always saves your SQL statements as hidden queries in the background, anyway, so you still get the slight performance benefit of having the execution plan for the query saved rather than recalculated each time the query runs.

We should mention a few caveats. First, if you use the same complex query as a row source for several different database objects, especially if you anticipate changing the query, it may be best to leave the query as a query object rather than converting it into a SQL statement. If you use one query as a record source for several forms or reports, when you change the query all the forms or reports that use it will pick up the changes. Also, there are some query properties that apply only to saved queries, such as the RunPermissions property. If you need to use these properties in a secured database, you must leave the queries as query objects.

In some cases, you may need to convert a SQL statement into a query (for example, if you need to use it as a record source for several forms or reports). In that case, simply reverse the steps given earlier: open the SQL statement in the Query Builder window and then save it as a named query, which you can use as a record source for other database objects.

In addition, you can use the Query Builder to help create a row source or control source from scratch. Simply click on the Build button and build a SQL statement as though you were building a query. Rather than saving a query object in the database container, Access will save the SQL string you've created into the appropriate property.

See Also

For more information on working with queries, see Chapter 1.

4.2. Build an Object Inventory

Problem

To document your application, you'd like to be able to create a list of all the objects in your databases, including their owners, date of creation, and date of last update. You're sure you can do it manually, but is there a better way to create a table containing all this information?

Solution

Access's Data Access Objects (DAO) can give you the information you need. By programmatically working your way through each of Access's container collections, you can add a row to an inventory table for each object in your application, storing information about that object. You should be able to use the techniques for this operation to write your own code for enumerating other collections in Access. There are a few tricks along the way, which this solution discusses, but in general this is a straightforward project.

To create an object inventory for your applications, take only two steps:

  1. Import the form zsfrmInventory from 04-02.MDB into your own application.

  2. Load and run the form. As it opens, it builds the object inventory, saving the data in zstblInventory. If you want to rebuild the inventory once the form's up, click the Rebuild Object Inventory button. This recreates the inventory table and fills it with information about all the objects in your database. Figure 4-4 shows the form once it's been run on a sample database.

The inventory-creating form once it's done its work on a sample database

Figure 4-4. The inventory-creating form once it's done its work on a sample database

Tip

This example form includes the Access system tables, which you may never have encountered. These tables are part of every Access database and are not cause for alarm. You can view them in the Database Explorer by choosing the Tools Options menu and turning on the Show System Objects option.

Discussion

How this solution works is a lot more interesting than the final product. The object inventory itself can be useful, but the steps involved in creating the inventory may be more useful to you in the long run. All the code examples used in this section come from the form module attached to zsfrmInventory (in 04-02.MDB).

When the form loads, or when you click the Rebuild Object Inventory button on zsfrmInventory, you execute the following code. (The "zs" prefix, by the way, reminds you that zsfrmInventory is a "system" form, used only by your application. The z forces this form to sort to the bottom of the database container so you won't get it confused with your "real" forms.)

Private Sub RebuildInventory( )
    On Error GoTo HandleErr
    DoCmd.Hourglass True

    Me.lstInventory.RowSource = ""
    Call CreateInventory
    Me.lstInventory.RowSource = "SELECT ID, Container, Name, " & _
     "Format([DateCreated],'mm/dd/yy (h:nn am/pm)') AS [Creation Date], " & _
     "Format([lastUpdated],'mm/dd/yy (h:nn am/pm)') AS [Last Updated], " & _
     "Owner FROM zstblInventory ORDER BY Container, Name;"

ExitHere:
    DoCmd.Hourglass False
    Exit Sub

HandleErr:
    Resume ExitHere
End Sub

This code turns on the hourglass cursor and sets the main list box's RowSource property to Null. (It must do this because it's about to call the CreateInventory procedure, which attempts to delete the table holding the data. If the list box were still bound to that table, the code couldn't delete the table—it would be locked!) It then calls the CreateInventory subroutine. This procedure fills zstblInventory with the object inventory, and it can take a few seconds to run. When it's done, the code resets the list box's RowSource property, resets the cursor, and exits.

Documenting all the containers

The CreateInventory subroutine first creates the zstblInventory table. If CreateTable succeeds, CreateInventory then calls the AddInventory procedure for each of the useful Access containers (Tables, Relationships, Forms, Reports, Scripts, and Modules) that represent user objects. (Tables and queries are lumped together in one container. As you'll see, it will take a bit of extra effort to distinguish them.) Because each of the AddInventory procedure calls writes to the status bar, CreateInventory clears out the status bar once it's done, using the Access SysCmd function. The following code fragment shows the CreateInventory subroutine:

Private Sub CreateInventory( )
    If (CreateTable( )) Then
        ' These routines use the status line,
        ' so clear it once everyone's done.
        Call AddInventory("Tables")
        Call AddInventory("Forms")
        Call AddInventory("Reports")
        Call AddInventory("Scripts")
        Call AddInventory("Modules")
        Call AddInventory("Relationships")

        ' Clear out the status bar.
        Call SysCmd(acSysCmdClearStatus)
    Else
        MsgBox "Unable to create zstblInventory."
    End If
End Sub

Creating the inventory table

The CreateTable function prepares the zstblInventory table to hold the current database's inventory. The code in CreateTable first attempts to delete zstblInventory (using the Drop Table SQL statement). If the table exists, the code will succeed. If it doesn't exist, the code will trigger a runtime error, but the error-handling code will allow the procedure to continue anyway. CreateTable then recreates the table from scratch by using a data definition language (DDL) query to create the table. (See the Solution in Recipe 1.15 for more information on DDL queries.) CreateTable returns True if it succeeds or False if it fails. The following is the complete source code for the CreateTable function:

Private Function CreateTable( ) As Boolean
    ' Return True on success, False otherwise.
    Dim qdf As DAO.QueryDef
    Dim db As DAO.Database
    Dim strSQL As String
    
    On Error GoTo HandleErr
    Set db = CurrentDb( )

    db.Execute "DROP TABLE zstblInventory"
    
    ' Create zstblInventory.
    strSQL = "CREATE TABLE zstblInventory (Name Text (255), " & _
     "Container Text (50), DateCreated DateTime, " & _
     "LastUpdated DateTime, Owner Text (50), " & _
     "ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' If you got here, you succeeded!
    db.TableDefs.Refresh
    CreateTable = True
    
ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err
        Case 3376, 3011 ' Table or Object not found
            Resume Next
        Case Else
            CreateTable = False
    End Select
    Resume ExitHere
End Function

Documenting each container

The AddInventory subroutine is the heart of the inventory-creating operation. In Access, each database maintains a group of container objects, each of which contains a number of documents. These documents are the saved objects of the container's type, such as tables, relationships, forms, reports, scripts (macros), or modules. AddInventory looks at each document in each container, adds a new row to zstblInventory for each document, and copies the information contained in the document into the new row of the table. (All the code examples in this section come from AddInventory in zsfrmInventory's module.)

The first step AddInventory performs is to set up the necessary DAO object variables:

Set db = CurrentDb
Set con = db.Containers(strContainer)
Set rst = db.OpenRecordset("zstblInventory")

The code then loops through each document in the given container, gathering information about the documents:

For Each doc In con.Documents
...
Next doc

For each document in the Tables container, the code must first determine whether the given document is a table or query. To do this, it calls the IsTable function, which attempts to retrieve a reference to the requested object from the database's TableDefs collection. If this doesn't trigger a runtime error, that table must exist. Because attempting to retrieve a query's name from the TableDefs collection will certainly fail, you can use IsTable to determine if an element of the Tables container (which contains both tables and queries) is a table. The isTable function appears as follows:

Private Function IsTable(ByVal strName As String) As Boolean
   Dim db As DAO.Database
   Dim tdf As DAO.TableDef

   On Error Resume Next

   Set db = CurrentDb( )

   ' See the following note for information on why this
   ' is commented out.
   ' db.Tabledefs.Refresh

   Set tdf = db.TableDefs(strName)
   IsTable = (Err.Number = 0)
   Err.Clear
End Function

Tip

Normally, before retrieving information about any Access persistent object collection (TableDefs, QueryDefs, etc.), you must refresh the collection. Because Access doesn't keep these collections up to date unless necessary, it's possible that a table recently added by a user in the user interface might not yet have been added to the TableDefs collection. In this case, you'll be calling IsTable repeatedly. To speed the operation of zsfrmInventory, the IsTable function used here does not use the Refresh method each time it's called; it counts on the caller to have refreshed the collection. In almost any other use than this one, you'd want to uncomment the call to the Refresh method in the previous code example and allow the code to refresh the collection before checking for the existence of a particular table.

This code fragment fills a string variable, strType, with the type of the current document. The type is one of Tables, Relationships, Queries, Forms, Reports, Scripts, or Modules.

If strContainer = "Tables" Then
    If IsTable(doc.Name) Then
        strType = "Tables"
    Else
        strType = "Queries"
    End If
Else
    strType = strContainer
End If

The value of strType will be written to zstblInventory along with the document information.

Once AddInventory has determined the correct value for strType, it can add the information to zstblInventory. AddInventory retrieves the various properties of the document referred to by doc and copies them to the current row in zstblInventory, referred to by rst. Once it's done, it uses the recordset's Update method to commit the new row. This process is illustrated in the following code fragment from the AddInventory procedure:

rst.AddNew
    rst("Container") = strType
    rst("Owner") = doc.Owner
    rst("Name") = doc.Name
    rst("DateCreated") = doc.DateCreated
    rst("LastUpdated") = doc.LastUpdated
rst.Update

Avoiding errors

The list box on zsfrmInventory has the following expression as its RowSource property:

SELECT ID, Container, Name, 
 Format([DateCreated],"mm/dd/yy (h:nn am/pm)") AS [Creation Date],
 Format([lastUpdated],"mm/dd/yy (h:nn am/pm)") AS [Last Updated],
 Owner FROM zstblInventory ORDER BY Container, Name;"

There are two issues to consider here. First, the SQL string used as the RowSource pulls data from zstblInventory. It's quite possible, though, that when you load the form, zstblInventory doesn't exist. To avoid this problem, we saved the form with the list box's RowSource set to a null value. When the form loads, it doesn't attempt to retrieve the data until the code has had time to create the table, as you can see in the RebuildInventory procedure shown earlier.

The second thing to bear in mind is that Access doesn't always keep the collections completely up-to-date: you may find deleted objects in the collections. (These deleted objects have names starting with "~TMPCLP".) You probably won't want to include these objects in the inventory, so the code that loops through the collections specifically excludes objects with names that start with "~TMPCLP". To determine which objects are deleted, the code calls the IsTemp function, as shown in the following code fragment:

For Each doc In con.Documents
   If Not IsTemp(doc.Name) Then
   ...
   End If
Next doc

Private Function IsTemp(ByVal strName As String)
   IsTemp = Left(strName, 7) = "~TMPCLP"
End Function

Comments

If you want to remove system objects from your inventory, you'll need to check each object and, if it's a system object, skip it in the display. You can use an object's Attributes property to see if it's a system object. See Access's online help for more information.

You might wonder why this application uses the Access containers to retrieve information about tables and queries, since this requires more effort than if the code had just used the TableDefs and QueryDefs collections. It makes sense to use the containers because the TableDefs/QueryDefs collections don't contain information about the owners of the objects, one of the items of information this application is attempting to track.

You can also use the collections provided by Access, such as AllForms, AllReports, AllTables, which can be useful for gathering information on your objects. But these too lack ownership information, which is part of the Jet database engine's security system and therefore must be accessed using the Jet Containers and Documents collections. The AllForms and AllReports collections do contain additional useful information, however, including an IsLoaded property for each of the AccessObjects in the collections.

See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.

4.3. Verify That Objects Use Consistent Settings

Problem

You've finished your application and you're ready to deliver it, but you notice that your use of color, fonts, alignment, and other layout properties isn't consistent across all your forms or reports. You know you can manually check the values of all the properties of all the controls on your forms and reports, but there's got to be a faster way. Is there some method you can use to compare similar properties for all the objects in your application?

Solution

Access doesn't provide a "cross-section" of your properties, which is really what you need—some way to look at properties not listed by item, but by property name, across all objects. Building on the technology introduced in the Solution in Recipe 4.2, this solution creates a group of tables containing information about all the properties on any forms or reports you select. Once it builds those tables, it constructs a query that will allow you, using the Quick Sort menu items, to view all the property settings for various objects, sorted any way you'd like. Once you've sorted the output by property name, for example, you'll quickly be able to see which objects have incorrect settings for that particular property.

The 04-03.MDB sample database includes a single form, zsfrmVerifySettings. Figure 4-5 shows the form after it has done its cataloging in Northwind.MDB, ready to present property information on three different forms. Figure 4-6 shows the output data, sorted by property name, showing that several controls have different background colors.

To use zsfrmVerifySettings to catalog properties in your own applications, follow these steps:

  1. Import zsfrmVerifySettings from 04-03.MDB into your own database.

  2. Load zsfrmVerifySettings in form view. As it loads, it will build the object property inventory, creating tables and queries as necessary.

  3. Once the form has presented the list of forms and reports, click on the items you want documented. Click again on an item to remove it from the list of selected items. In Figure 4-5, for example, three items are to be documented. You can also use the Select All, Select All Forms, and Select All Reports buttons to select groups of items.

zsfrmVerifySettings is ready to catalog all controls on three selected forms

Figure 4-5. zsfrmVerifySettings is ready to catalog all controls on three selected forms

  1. When you've selected all the forms or reports you'd like to manipulate, click the Document Selected Items button. This will work its way through the list of selected items and document all the properties of each control on each of those items.

  2. When the documentation process is finished (it may take some time to work through all the items you've selected), click the View Results button. This will open zsqryProperties, which is shown in Figure 4-6. It lists all the properties of all the objects and the sections and controls on those objects.

zsqryProperties allows you to sort by any categories to view your property settings

Figure 4-6. zsqryProperties allows you to sort by any categories to view your property settings

  1. Use the toolbar buttons to control sorting and filtering so that you can view only the properties you want for the objects in which you're interested.

For example, you might want to ensure that all command buttons on all your forms have their ControlTipText properties set. To do that, follow these steps (assuming you've followed the previous steps):

  1. Open zsfrmVerifySettings and select all the forms in your application from the list of objects.

  2. Click on the Document Selected Items button. Go out for lunch while it does its work.

  3. Once it's finished, click on the View Results button, which brings up zsqryProperties, showing one row for each property of each object you selected. For a large set of forms or reports, this query could return tens of thousands of rows.

  4. Choose Records Filter Advanced Filter/Sort and build a filter that sorts on Parent and limits the output to rows with "ControlTipText" in the PropName field and "Command Button" in the ObjectType field. Figure 4-7 shows this filter.

This filter limits rows to the ControlTipText property of command buttons

Figure 4-7. This filter limits rows to the ControlTipText property of command buttons

  1. Apply the filter by clicking on the funnel button on the toolbar or by right-clicking on the filter design area and choosing Apply Filter/Sort. You will see only the rows for the command buttons' ControlTipText properties. Look for the rows in which there's no value in the PropValue column. Those are the buttons that don't yet have a value set. Figure 4-8 shows the output of the sample query. It's quite clear which buttons don't yet have their ControlTipText properties set.

The result query shows which buttons don't have their ControlTipText properties set

Figure 4-8. The result query shows which buttons don't have their ControlTipText properties set

Discussion

To build the list of forms and reports, zsfrmVerifySettings borrows code from the example in the Solution in Recipe 4.2. Instead of looping through all the collections, however, it works only with the Forms and Reports collections. Otherwise, the mechanics of creating the list of objects are the same as in the Solution in Recipe 4.2; investigate that topic if you'd like more information on building the object inventory.

Creating the temporary tables and query

The Solution in Recipe 4.2 created a single table, zstblInventory, to hold the list of objects. In this case, however, you need three tables (zstblInventory for main objects, zstblSubObjects for objects on those forms or reports, and zstblProperties for property information). You also need a query (zsqryProperties) to join the three tables and display the output. The CreateTables function, shown here, uses DDL queries to create each of the necessary tables (see the Solution in Recipe 1.15 for more information on DDL queries) and DAO to create the query (see Chapter 6 for more information on using DAO):

Private Function CreateTables( ) As Boolean

    ' Return True on success, False otherwise.
    
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim strSQL As String
    
    On Error GoTo HandleErr
    
    Set db = CurrentDb
    
    db.Execute "DROP TABLE zstblInventory"
    db.Execute "DROP TABLE zstblSubObjects"
    db.Execute "DROP TABLE zstblProperties"
    
    ' Create zstblInventory.
    strSQL = "CREATE TABLE zstblInventory (Name Text (255), " & _
     "Container Text (50), DateCreated DateTime, " & _
     "LastUpdated DateTime, Owner Text (50), " & _
     "ID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' Create zstblSubObjects.
    strSQL = "CREATE TABLE zstblSubObjects (ParentID Long, " & _
     "ObjectName Text (50), ObjectType Text (50), " & _
     "ObjectID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL

    ' Create zstblProperties.
    strSQL = "CREATE TABLE zstblProperties (ObjectID Long, " & _
     "PropName Text (50), PropType Short, " & "PropValue Text (255), " & _
     "PropertyID AutoIncrement Constraint PrimaryKey PRIMARY KEY)"
    db.Execute strSQL
    
    ' Create zsqryProperties.
    strSQL = "SELECT zstblInventory.Name AS Parent, " & _
     "zstblInventory.Container, zstblSubObjects.ObjectName, " & _
     "zstblSubObjects.ObjectType, zstblProperties.PropName, " & _
     "zstblProperties.PropValue FROM zstblInventory " & _
     "INNER JOIN (zstblSubObjects INNER JOIN zstblProperties " & _
     "ON zstblSubObjects.ObjectID = zstblProperties.ObjectID) " & _
     "ON zstblInventory.ID = zstblSubObjects.ParentID;"
    
    db.CreateQueryDef ("zsqryProperties")
    Set qdf = db.QueryDefs("zsqryProperties")
    qdf.SQL = strSQL

    ' If you got here, you succeeded!
    CurrentDb.TableDefs.Refresh
    CreateTables = True
    
ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err
        Case acbErrTableNotFound, acbErrObjectNotFound, _
          acbErrAlreadyExists
            Resume Next
        Case Else
            CreateTables = False
    End Select
    Resume ExitHere
End Function

Getting ready to document items

When you click on the Document Selected Items button, the form walks through the list of selected items and then documents the object. The code in cmdDocumentSelected_Click does the work: it looks through the ItemsSelected collection of the list box and, for each selected item, calls either DocumentForm or DocumentReport, depending on the value in the second column of the list box. Each of those procedures requires the ID of the parent object (the form or report in question) and the name of the object. The source code for the cmdDocumentSelected_Click event procedure is:

Private Sub cmdDocumentSelected_Click( )

    ' In the list box:
    ' ParentID == Column(0)
    ' Container == Column(1)
    ' Name == Column(2)
    
    Static fInHere As Boolean
    Dim varItem As Variant
    Dim strName As String
    Dim lngParentID As Long
    
    On Error GoTo HandleErr
    ' Don't allow recursive entry. If this routine is doing
    ' its thing, don't allow more button clicks to get you
    ' in again, until the first pass has finished its work.
    If fInHere Then Exit Sub
    fInHere = True
    
    With Me.lstInventory
        For Each varItem In .ItemsSelected
            strName = .Column(2, varItem)
            lngParentID = .Column(0, varItem)
            Select Case .Column(1, varItem)
                ' This will handle only forms and reports.
                Case "Forms"
                    Call DocumentForm(strName, lngParentID)
                Case "Reports"
                    Call DocumentReport(strName, lngParentID)
            End Select
        Next varItem
    End With
    
    Call SysCmd(acSysCmdClearStatus)
    Me.cmdViewResults.Enabled = True
    
ExitHere:
        fInHere = False
    Exit Sub

HandleErr:
    MsgBox Err.Number & ": " & Err.Description, , "DocumentSelected"
    Resume ExitHere
End Sub

Visiting all the objects

The DocumentForm and DocumentReport procedures do the same things, though in slightly different ways. They both document the properties of the main object itself, followed by the properties of each of the sections (forms can have up to 5 sections, reports up to 25). Finally, both procedures walk through the collection of controls on the main object, documenting all the properties of each control. The following code shows DocumentForm, but DocumentReport is almost identical:

Private Sub DocumentForm( _
  ByVal strName As String, ByVal lngParentID As Long)
    ' You must first open the form in design mode, and then
    ' retrieve the information. With forms, you can open the
    ' form in hidden mode, at least.

    Dim db As Database
    Dim rstObj As DAO.Recordset
    Dim rstProps As DAO.Recordset
    Dim lngObjectID As Long
    Dim frm As Form
    Dim ctl As Control
    Dim intI As Integer
    Dim obj As Object
    
    On Error GoTo HandleErr
    Call SysCmd(acSysCmdSetStatus, "Getting information on form " & _
     strName & ".")
    
    Set db = CurrentDb( )
     ' No need to open the form if it's THIS form.
    If strName <> Me.Name Then
        DoCmd.OpenForm strName, View:=acDesign, WindowMode:=acHidden
    End If
    Set rstObj = db.OpenRecordset("zstblSubObjects", _ 
     dbOpenTable, dbAppendOnly)
    Set rstProps = db.OpenRecordset("zstblProperties", _
     dbOpenTable, dbAppendOnly)

    ' Handle the form properties first.
    Set frm = Forms(strName)
    AddProps rstObj, rstProps, frm, "Form", lngParentID
    
    ' Handle the five possible form sections.
    For intI = 0 To 4
        Set obj = frm.Section(intI)
        AddProps rstObj, rstProps, obj, "Section", lngParentID
Form_Next_Section:
    Next intI
    
    ' Handle all the controls.
    For Each ctl In frm.Controls
        AddProps rstObj, rstProps, ctl, GetControlType(ctl), lngParentID
    Next ctl
    
    ' Don't close the form that's running all this.
    If Me.Name <> strName Then
        DoCmd.Close acForm, strName
    End If

ExitHere:
    Exit Sub

HandleErr:
    Select Case Err
        Case acbErrInvalidSection
            Resume Form_Next_Section
        Case Else
            MsgBox Err & ": " & Err.Description, , "DocumentForm"
    End Select
    Resume ExitHere
End Sub

The procedure starts by opening the requested object in design mode so it can get the information it needs. It cannot open the objects in normal view mode, because that would run the objects' event procedures, which might have unpleasant side effects.

Starting with Access 2002, you can specify a WindowMode when you use DoCmd.OpenReport. This allows you to hide a report when you open it, which is nice when you are opening it in design view.

As shown in our example, if the code tries to open the current form, it simply skips the open step. (This means, of course, that your documentation on the current form will be different than that of other forms: it's already open in form view, and the rest will be opened in design view.) Skipping the current form isn't an issue if you're documenting reports. When it's complete, DocumentForm/Report also closes the object (as long as it wasn't the current form). This is shown in the following code fragment from the DocumentForm procedure:

' No need to open the form if it's THIS form.
If strName <> Me.Name Then
   DoCmd.OpenForm strName, View:=acDesign, WindowMode:=acHidden
End If
.
. ' All the real work happens here...
.
' Don't close the form that's running all this.
If Me.Name <> strName Then
   DoCmd.Close acForm, strName
End If

DocumentForm next opens two recordsets, to which it adds rows as it documents your objects. These are specified as append-only recordsets in order to speed up the processing. The relevant code is:

Set rstObj = db.OpenRecordset("zstblSubObjects", _
 dbOpenTable, dbAppendOnly)
Set rstProps = db.OpenRecordset("zstblProperties", _
 dbOpenTable, dbAppendOnly)

Next, the procedure documents all the properties of the main object itself. As it will do when documenting all the objects, it calls the AddProps procedure. AddProps expects to receive references to the two recordsets, a reference to the object to be documented, the text to appear in the list box for the object's type, and the ID value for the main, parent object. The code fragment that calls AddProps appears as follows:

' Handle the form properties first.
Set frm = Forms(strName)
AddProps rstObj, rstProps, frm, "Form", lngParentID

The procedure then documents the properties of the sections. For forms, there can be at most five sections (detail, form header/footer, page header/footer). For reports, there can be up to 25: the same 5 as for forms, plus a header and footer for up to 10 report grouping sections. Note that any section may or may not exist. Therefore, the code traps for this error and jumps on to the next numbered section if the current one doesn't exist. The portion of the code that documents section properties is:

   ' Handle the five possible form sections.
   For intI = 0 To 4
      Set obj = frm.Section(intI)
      AddProps rstObj, rstProps, obj, "Section", lngParentID
Form_Next_Section:
   Next intI

Finally, DocumentForm/Report visits each of the controls on the form or report, calling AddProps with information about each control:

' Handle all the controls.
For Each ctl In frm.Controls
   AddProps rstObj, rstProps, ctl, GetControlType(ctl), lngParentID
Next ctl

Recording property information

The AddProps procedure, shown here, does the work of recording information about the selected object into zstblSubObject and about all its properties into zstblProperties. Note the large error-handling section; several properties of forms, reports, sections, and controls are not available in design mode, and attempting to retrieve those property values triggers various error messages.

Private Sub AddProps(rstObj As DAO.Recordset, _
  rstProps As DAO.Recordset, obj As Object, _
  ByVal strType As String, ByVal lngParentID As Long)
  
    Dim lngObjectID As Long
    Dim prp As Property
    
    On Error GoTo HandleErr

    rstObj.AddNew
        rstObj("ParentID") = lngParentID
        rstObj("ObjectName") = obj.Name
        rstObj("ObjectType") = strType
        ' Get the new ID
        lngObjectID = rstObj("ObjectID")
    rstObj.Update
    For Each prp In obj.Properties
        rstProps.AddNew
            rstProps("ObjectID") = lngObjectID
            rstProps("PropName") = prp.Name
            rstProps("PropType") = prp.Type
            ' Store the first 255 bytes of the
            ' property value, converted to text.
            rstProps("PropValue") = Left(prp.Value & "", 255)
        rstProps.Update
    Next prp

ExitHere:
    Exit Sub
    
HandleErr:
    Select Case Err.Number
        ' Some property values just aren't available in the design view.
        Case acbErrInvalidView, acbErrNotInThisView, _
         acbErrCantRetrieveProp, acbErrCantGetProp
            Resume Next
        Case Else
            MsgBox Err.Number & ": " & Err.Description, , "AddProps"
    End Select
    Resume ExitHere
End Sub

To add a row about the object to zstblSubObjects, AddProps uses the AddNew method of the recordset and then fills in the appropriate fields. Just like on an Access form, when you add a new row to a recordset, Access fills in any autonumber values as soon as you begin editing the row. Here, we grab that new ObjectID value and store it in the variable lngObjectID, for use later as the object ID in the related properties table:

rstObj.AddNew
    rstObj("ParentID") = lngParentID
    rstObj("ObjectName") = obj.Name
    rstObj("ObjectType") = strType
    ' Get the new ID
    lngObjectID = rstObj("ObjectID")
rstObj.Update

Next, AddProps loops through all the properties in the object's Properties collection, adding a row for each to zstblProperties. Note that because tables don't support Variant fields, we've set the PropValue field to be a 255-character text field; the code converts the property value to text and truncates it to no more than 255 characters. Few properties require more text than that, but some, such as the row sources of combo boxes, could. You might want to use a memo field for these properties instead. Memo fields are somewhat less efficient, but they are more efficient starting with Jet 4.0 (Access 2000 or later) than they were in previous versions.

For Each prp In obj.Properties
    rstProps.AddNew
        rstProps("ObjectID") = lngObjectID
        rstProps("PropName") = prp.Name
        rstProps("PropType") = prp.Type
        ' Store the first 255 bytes of the
        ' property value, converted to text.
        rstProps("PropValue") = Left(prp.Value & "", 255)
    rstProps.Update
Next prp

Tip

The rest of the code in zsfrmVerifySettings's module deals with selecting items in the list box. You're welcome to peruse that code, but it's not crucial to understanding the object/property inventory.

Comments

If you're interested in working with multiselect list boxes in your applications, take the time to work through the code that manipulates the list box in this example. The code uses the Selected property of the list box, setting various rows to be selected or not by setting the value of the property. It also makes heavy use of the Column property, allowing random access to any item stored in the list box.

More than for most of the solutions in this book, effective use of the techniques covered here requires some of your own imagination. Not only are the techniques for providing the object and property inventory interesting, but the output itself can be useful as well. Since we developed this example, we've used it in several applications to verify that all the controls used the same fonts, that all the command buttons had their ControlTipText properties set, and that all the detail sections used the same background color. You should strive for design consistency in your applications, and this tool can help you achieve it.

See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.

4.4. Hide Access Screen Activity

Problem

You can use a form's Painting property to disable updates to that form, but some activities still seem to show through or cause flashing on the screen. Is there any way to hide screen activity?

Solution

Sometimes you need more control over screen repainting than you get with either Form.Painting. You may also need to investigate the Application.Echo method. By passing this method a True or a False value, you can indicate whether you want to display updating within the main Access window. You can also optionally pass the method a second parameter—a string indicating text to be displayed within the status bar while screen updating is disabled.

Load and run frmLockScreen (Figure 4-9) from 04-04.MDB. This sample form simply opens three reports in design mode and then closes them. The form includes a checkbox that allows you to run the test with screen updates enabled or disabled. Try it both ways; you should see a clear difference between the two ways of running the test. With the checkbox set, the underlying code disables screen updates, so you shouldn't see the reports' icons pop up. Without the checkbox set, you will see the reports open and minimize, in design view.

The sample form, frmLockScreen, ready to run its tests

Figure 4-9. The sample form, frmLockScreen, ready to run its tests

To use Application.Echo to disable screen updates in your own applications, follow these steps:

  1. Import the module basLockScreen from 04-04.MDB. This module includes the simple code that's required in order to disable updates to the Access main window.

  2. When you want to disable screen updates, call the acbShowUpdates subroutine, passing it a False value. To reenable screen updates, call the subroutine again, passing it a True value. In other words, your code that uses acbShowUpdates should take the following form:

    Call acbShowUpdates(False)
    ' Do your work in here...
    Call acbShowUpdates(True)

Discussion

The Application.Echo method is simple to use, but many developers miss it, allowing their applications to appear somewhat dizzying as objects appear and disappear from the screen. The acbShowUpdates method really doesn't do much besides what a direct call to Application.Echo does:

Sub acbShowUpdates(blnShow As Boolean)
   If blnShow Then
      Application.Echo True
   Else
      Application.Echo False
   End If
End Sub

As a matter of fact, the reason this procedure exists at all is because the techniques used in this topic work great in Access 2002 and later, but may not work correctly in earlier versions—it may be that if you're running Access 2000 or earlier, using Application.Echo to turn off screen updating while opening a report in design view may not hide screen updates. In that case, you may want to try an alternate technique, calling the parallel acbShowUpdatesAPI method.

The acbShowUpdatesAPI subroutine (in basLockScreen) does its work by calling the Windows API function LockWindowUpdate. This function takes as its only parameter a window handle. If that handle is nonzero, Windows simply stops updating the contents of that window on screen. If the handle is 0, Windows reenables screen updates to the locked window.

Because the only window you care about locking in Access is the main Access window itself, the acbShowUpdatesAPI routine shields you from any of the details. If you pass it a False value, it blocks window updates. If you pass it a True value, it reenables updates. It finds the Access window handle for you, if necessary, and then calls LockWindowUpdate. Its source code is simple:

Sub acbShowUpdatesAPI (blnShow As Integer)
   If blnShow Then
      acb_apiLockWindowUpdate 0
   Else
      acb_apiLockWindowUpdate Application.hWndAccessApp
   End If
End Sub

Tip

In Access 2.0, finding the window handle (the unique integer that identifies every window) for the main Access window was difficult. It required a good deal of work with multiple Windows API functions. In later versions, the Application object exposes the hWndAccessApp property, which returns the window handle of the main Access window.

You may find, depending on the version of Access you're using, that t his method of disabling screen updates isn't perfect. Because Access has no idea that you've turned them off, Access itself occasionally turns on screen updates. For example, depending on how you open forms and reports in design mode, completely hiding the properties sheet may be difficult. In the sample application, 04-04.MDB, the reports' properties sheet isn't showing. If you open one of the reports, open the properties sheet, and then save the report, no combination of Application.Echo and calls to LockWindowUpdate will completely remove that properties sheet from the screen when you open the report in design view.

Hiding reports in design view

In older versions of Access, you had to resort to hacks to hide reports in design view. Fortunately, that is no longer necessary in Access 2002 and later, because Microsoft has finally supplied a WindowMode parameter that can be used to hide a report when you open it, even if it's opened in design view. Also, many of the printer settings that made it necessary to open reports in design view are no longer necessary starting in Access 2002 because of the Printer object (see Chapter 5 for several examples).

If you are working in Access 97, you can take advantage of an undocumented but effective technique for hiding the hard-to-hide properties windows of reports that are open in design view. Be warned, however, that this method is totally undocumented, is unsupported by Microsoft, and doesn't work in Access 2000 or later.

The Application object in Access supports the GetOption and SetOption methods, which allow you to get and set global options. Most of these options are documented (see the online help topics for GetOption/SetOption), while a few items are not documented but do useful work. One such option allows you to retrieve and set the coordinates for the form or report properties sheet (in versions of Access prior to Access 2000) and to set whether or not you want the properties sheet to be visible when you open a form or report in design view.

To retrieve the information about the report properties sheet in Access 97 or 95, use a call like this:

strInfo = Application.GetOption("_26")

This will retrieve a string containing information on the report properties sheet's location and whether or not to display it when you open a report in design view. The string will be in this format:

open?;left;top;width;height;

For example, it might look like this:

1;510;433;835;683;

indicating that the properties sheet will be visible when you load a report and that when it does show up it will be at 510, 433 with a width of 835 and a height of 683.

To make sure that your application doesn't show the properties sheet while it does its work, you can retrieve this property, set the first character to 0, and then save it. The code might look like this:

Dim strInfo As String
strInfo = Application.GetOption("_26")
strInfo = "0" & Mid(strInfo, 2)
Application.SetOption "_26", strInfo

The only way this will have any influence is if you call this code before you've loaded any reports in design mode. Access looks at this information only once, when it loads the properties sheet for the first time. Once it has loaded the properties sheet, it doesn't look at these values again. Every time you leave design mode Access stores information about the properties sheet, so if you're going to try to set these values for the next time you start Access, make sure you do it when there's no report open in design mode. Otherwise, Access will override your settings when it saves them itself.

To use this technique for forms, use option "_24" instead. It's not nearly as useful with forms as it is with reports, however, because in older versions of Access you can open hidden forms but not hidden reports.

As an example of an error handler that resets screen updates, the code executed by frmLockScreen handles errors by using the normal exit route from the routine:

Private Sub cmdOpenReports_Click( )
    Dim intI As Integer
    Dim intSuccess As Integer

    On Error GoTo HandleErr

    If Me.chkHideUpdates Then
        If Me.chkUseAPI Then
            Call acbShowUpdatesAPI(False)
        Else
            Call acbShowUpdates(False)
        End If
    End If
    For intI = 1 To 3
        Call acbOpenReport("rptReport" & intI, acDesign)
    Next intI
    For intI = 1 To 3
        DoCmd.Close acReport, "rptReport" & intI
    Next intI

ExitHere:
    If Me.chkHideUpdates Then
        If Me.chkUseAPI Then
            Call acbShowUpdatesAPI(True)
        Else
            Call acbShowUpdates(True)
        End If
    End If
Exit Sub

HandleErr:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitHere
End Sub

If an error occurs while this subroutine is active, the code will jump to the HandleErr label and from there will resume at the ExitHere label. The code will re-enable screen updates and then exit the routine. Your own code may not look exactly like this, but you must handle errors so that the screen never remains locked up when an error occurs.

See Also

For more information on working with the Windows API, see Chapter 11.

4.5. Find out What Language Version of Access Is Installed

Problem

You distribute your applications in several countries, and your users have different internationalized versions of Access installed. You'd like your applications to be able to make decisions based on the installed version of Access. How can you find out which language version of Access is currently running?

Solution

In older versions of Access, you had to use an API call to get this information. However, starting with Access 2000, it is possible to retrieve language information using the Microsoft Office Object Library. This solution demonstrates how you can gather the language information you need.

Load and run the form frmLanguage in 04-05.MDB. As it loads, it calls the necessary functions to determine the currently running language version of Access. Figure 4-10 shows the form after it's been loaded into a retail U.S. English version of Access.

frmLanguage indicates the language version of Access that's running

Figure 4-10. frmLanguage indicates the language version of Access that's running

To include this functionality in your own applications, follow these steps:

  1. Import the module basFileLanguage from 04-05.MDB into your own application. This module includes constants representing the seven most commonly used languages and their related intrinsic constants and values.

  2. Declare a long integer variable, lngLanguage. When your application starts up, make a call to acbAccessLanguage, which will return a number representing the current running language version of Access. You can assign this return value to the lngLanguage variable, as follows:

    lngLanguage = acbAccessLanguage( )

    You can then pass that variable to procedures in your application that make decisions based on the current language version of Access.

In the example application, the language ID is stored in an option group, which will work only if you are supporting a known, limited set of languages. The example also includes code that detects the version of Access in use and whether it is a runtime version.

Discussion

Retrieving language information requires setting a reference to the Microsoft Office Object Library. You can then refer to the Application object's LanguageSettings property to retrieve the language being used. Each language has its own LanguageID property, which is an integer value. These language IDs are represented by enumerated constants. When you set a reference to the Microsoft Office Object Library, you can see a complete list of constants by examining the msoLanguageID enumeration, as shown in Figure 4-11.

Each language value has a corresponding constant

Figure 4-11. Each language value has a corresponding constant

The call to acbAccessLanguage requires a simple variable:

lngRetval = acb_apiGetLanguage( )

Or you can use a control, as we have in the example:

Me.grpLanguage = acbAccessLanguage( )

The function returns a single value, which tells you which language version the function found. Table 4-1 lists only a few of the Windows languages and the ID values associated with them, along with the corresponding constants. You can see a complete list by using the Object Browser, as shown in Figure 4-11.

Table 4-1. Windows languages and ID values

Language

Constant

ID

American English

 msoLanguageIDEnglishUS

1033

French

 msoLanguageIDFrench

1036

German

 msoLanguageIDGerman

1031

Italian

 msoLanguageIDItalian

1040

Russian

 msoLanguageIDRussian

1049

Spanish

 msoLanguageIDSpanish

1034

Portuguese

 msoLanguageIDPortuguese

2070

Swedish

 msoLanguageIDSwedish

1053

Zulu

 msoLanguageIDZulu

1077

The simple function in basFileLanguage, acbAccessLanguage, returns only the national language ID number (from Table 4-1) for the installed version of Access:

Function acbAccessLanguage( ) As Long
    acbAccessLanguage = _
     Application.LanguageSettings.LanguageID(msoLanguageIDUI)
End Function

Once you know the ID for the national language, you can make choices in your application. For example, as shown in the next two solutions, you can modify labels on forms and reports and modify the error messages that you display.

The example form also uses two functions from basAccessInfo in 04-05.MDB, acbGetVersion and acbIsRuntime. Both are quite simple, comprising only calls to the built-in SysCmd function. The first, acbGetVersion, returns the version number of the currently running copy of Access. The second, acbIsRuntime, returns True if your application is running in the runtime version of Access or False if it's in the retail version. You may find these functions useful if your application needs to react differently to different environments.

Public Function acbGetVersion( ) As String
   ' Retrieve the Access version for places
   ' that can't use symbolic constants.

   acbGetVersion = SysCmd(acSysCmdAccessVer)
End Function

Public Function acbIsRuntime( ) As Boolean
   ' Use SysCmd( ) to gather the information.

   acbIsRuntime = SysCmd(acSysCmdRuntime)
End Function

4.6. Internationalize Text in Your Applications

Problem

You'd like to be able to pop up translated error messages in your applications, based on the currently running language version of Access. You'd also like other text on your forms and reports to adjust automatically based on the current language version. You know there are a number of ways to do this, but you can't decide which is best. How should you store and retrieve messages in multiple languages?

Solution

The translated version of Access handles its own error messages (in the German version, for example, the Access error messages appear in German). But you do need to translate your own messages if you want your application to run smoothly in other languages. Though there are several methods of handling text, the most generic solution uses a table of messages, which you can look up by ID number.

Load and run the form frmTestMessage from 04-06.MDB. This form, shown in Figure 4-12, allows you to choose from three different languages (English, French, and Spanish) in an option group. As you choose each language, code attached to the option group's AfterUpdate event changes accordingly the captions for labels on the form and the status-bar text for text boxes. To try a sample error message in the chosen language, click the Test Message button.

The sample form, frmTestMessage, showing the French test error message

Figure 4-12. The sample form, frmTestMessage, showing the French test error message

In each case, the messages are coming from the table tblMessages. This table includes a column for the message identifier (the primary key) and one column for each of the languages your application supports. Figure 4-13 shows the table, filled in for the sample application.

The message table, tblMessages, filled in for the sample application 04-06.MDB

Figure 4-13. The message table, tblMessages, filled in for the sample application 04-06.MDB

To include similar functionality in your own applications, follow these steps:

  1. From 04-06.MDB, import the modules basFileLanguage (which includes the procedures from the Solution in Recipe 4.5 for obtaining the current language version of Access) and basGetMessages (which looks up particular messages in tblMessages).

  2. From 04-06.MDB, import the table tblMessages. This is the table you'll use to hold your messages. Delete the existing rows, if you like. Also, you can modify the structure and add more languages if necessary.

  3. Add the necessary rows to tblMessages, filling in each column with the translated text, as shown in Figure 4-13.

  4. On any form for which you'd like to have language-sensitive captions and status-bar text, place the message ID (the MsgNum column from tblMessages) in the Tag property for the control whose text you'd like to change. For labels, the code you'll call is set up to change the Caption property; for text boxes, the code is set up to change the StatusBarText property. (If you want to include other control types, you can modify the code in the subroutine GetInfo, as described in Recipe 4.6.3.)

  5. To set the captions for labels and the status-bar text for text boxes when your form loads, place the following code in the Open event procedure for your form:

    Private Sub grpLanguage_AfterUpdate( )
       acbSetText Me, Me.grpLanguage
    End Sub

    The acbSetText subroutine walks through all the controls on your form, searching for ones with a numeric value in the Tag property. For any such controls, it looks up the appropriate message and assigns it to the Caption or StatusBarText property.

Discussion

The technique presented in this solution includes two basic pieces of functionality: retrieving the correct messages from the table of messages and replacing all the required property values on your form or report. Together, these two operations accomplish the goals of changing labels and status bar text and providing translated error messages.

The acbGetMessage function retrieves the messages you need from tblMessages. You pass to it, as parameters, a long integer specifying the message number you want and an integer specifying the correct language.

Public Function acbGetMessage( _
  ByVal lngMessage As Long, _
  ByVal lngLanguage As Long) As Variant

    ' Retrieve a message from tblMessages, given a message
    ' ID and a language.
    
    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varLanguage As Variant
    Dim varResult As Variant
    
    On Error GoTo HandleErr
    
    varResult = Null
    Set db = CurrentDb( )
    Set rst = db.OpenRecordset("tblMessages", dbOpenTable)
    With rst
        If Not .EOF Then
            ' Set the index, which is the message number
            .Index = "PrimaryKey"
            .Seek "=", lngMessage
            If .NoMatch Then
                ' You could raise an error here,
                '  but we're just returning a null value.
                varResult = Null
            Else
                varLanguage = GetLanguageName(lngLanguage)
                If Not IsNull(varLanguage) Then
                    varResult = rst(varLanguage)
                Else
                    varResult = Null
                End If
            End If
        End If
    End With
ExitHere:
    If Not rst Is Nothing Then
         rst.Close
         Set rst = Nothing
    End If
    acbGetMessage = varResult
    Exit Function

HandleErr:
   varResult = Null
   MsgBox Err.Number & ": " & Err.Description, , "acbGetMessage"
   Resume ExitHere
End Function

This function starts by creating a table-type recordset based on tblMessages:

Set rst = db.OpenRecordset(acbcMsgTable, dbOpenTable)

If there are any rows in tblMessages, the function looks for the row you've requested. If it doesn't find a match, you must have requested a message number that's not in the table, so the function returns Null:

With rst
   If Not .EOF Then
      ' Set the index, which is the message number.
      .Index = "PrimaryKey"
      .Seek "=", lngMessage
      If .NoMatch Then
         varResult = Null

If it does find a match, it converts the language number into the table's column name for the language (using the GetLanguageName function). If it finds a language name, it retrieves the appropriate message from tblMessages:

      Else
         varLanguage = GetLanguageName(intLanguage)
         If Not IsNull(varLanguage) Then
            varResult = rst(varLanguage)
         Else
            varResult = Null
         End If
      End If
End With

If any error occurs along the way, acbGetMessage returns Null. If things work out, it returns the message it found in tblMessages.

You can call acbGetMessage directly (e.g., to fill the text for a message box or to build up a more complex error string). In addition, the acbSetText subroutine—which does the work of replacing text when you load a form or report—calls acbGetMessage multiple times, once for each message.

The acbSetText procedure takes two parameters: an object containing a reference to the form or report, and the language ID. The procedure walks through all the controls on the requested form or report, calling the GetInfo function for each. The complete text of the acbSetText procedure is:

Public Sub acbSetText(obj As Object, ByVal lngLanguage As Long)
   
    ' Set text for labels (caption) and text boxes (status-bar
    ' text) on the specified report or form.
    
    Dim ctl As Control

    For Each ctl In obj.Controls
         Call GetInfo(ctl, lngLanguage)
    Next ctl
End Sub

The GetInfo subroutine does the actual work; this is the procedure you'll need to change if you want to handle more than just labels' Caption properties and text boxes' StatusBarText properties. It checks the Tag property and, if it's numeric, looks up the associated text string in the appropriate language. Once it has the string, it checks the control type and places the string in the correct property for the given control type. The complete source code for the GetInfo subroutine is:

Private Sub GetInfo(ctl As Control, lngLanguage As Long)
    ' Given a control and a language, look up the label
    ' or status-bar text for it.
   
   Dim varCaption As Variant

    With ctl
        If IsNumeric(.Tag) Then
            varCaption = acbGetMessage(.Tag, lngLanguage)
            If Not IsNull(varCaption) Then
                Select Case .ControlType
                    Case acLabel
                        .Caption = varCaption
                    Case acTextBox
                        .StatusBarText = varCaption
                End Select
            End If
        End If
    End With
End Sub

If you want to support more languages than just the three used in this example, you'll need to modify the structure of tblMessages (adding a new column for each new language) and modify the GetLanguageName procedure in the basGetMessage module. As it is now, GetLanguageName looks like this:

Private Function GetLanguageName( _
  ByVal lngLanguage As Long) As Variant
   ' Make sure to set a reference to the Office Library.
   ' Given a language identifier, get the column name in
   ' tblMessages that corresponds to it. This function
   ' expects, for lngLanguage:
   '   msoLanguageIDEnglishUS (1033),
   '   msoLanguageIDSpanish (1034), or
   '   msoLanguageIDFrench (1036).

   Dim varLang As Variant

   Select Case lngLanguage
      Case msoLanguageIDEnglishUS
         varLang = "English"
      Case msoLanguageIDFrench
         varLang = "French"
      Case msoLanguageIDSpanish
         varLang = "Spanish"
   End Select
   GetLanguageName = varLang
End Function

Add more cases to the Select Case statement, matching the new columns in your messages table. The constants come from the Office Library, a reference to which you must add to your project. An alternative approach is to use the language IDs themselves as the column headings—that way you won't need the extra step of translating the IDs to names. You could also redesign the solution to use three columns—MsgNum, LanguageID, and ErrorMessage—which would make adding a language a matter of adding records rather than modifying code.

The sample form contains only a few controls. Attempting to modify the properties of several hundred controls would noticeably increase load time for a form. For forms that contain many controls, you might be better off creating one version of the form per language and distributing translated versions of your application. Alternatively, you could preload the form invisibly when your application starts up so that it appears instantly when made visible.

Another problem you should consider when attempting to modify captions on the fly is that many non-English languages take more space to present the same information. You'll find that some languages require twice as much space (or more) for a given text string. This may mean that dynamic translation isn't feasible, due to real-estate problems. Again, the best solution is to plan the translated versions carefully and prepare a different set of forms and reports for each language, or to leave enough space for the most verbose language you need to support. You could also include width values for each language and adjust the controls as needed, but this would get complicated because you would also need to adjust their positions and perhaps even the size of the form. A comprehensive solution would require you to store many property values for each control and for each form and report.

Message boxes don't present such a problem, of course, because Access automatically resizes them to fit the data you send to them. The same goes for ControlTipText. Call the acbGetMessage function to provide the text for any message box you wish to fill, as in this example:

Call MsgBox(acbGetText(intLanguage, 1), vbExclamation, acbGetText(intLanguage, 2))

You can use this technique to alter any messages within your application at runtime. For example, if you want to provide different levels of help for different users, you can keep all your messages in a table and retrieve the correct help messages depending on who the current user is. In this case, rather than looking up language names, you'd be looking up user or group names.

4.7. Change and Reset the Access Caption Bar

Problem

You'd like to be able to change the caption of the main Access window as part of your application. Of course, you need to be able to reset it back to its original value when you're done. You've found the AppTitle property in Access, but you just can't get it to work. Is there some simple way to retrieve and set the Access caption, as you can with any of the windows within Access?

Solution

This is one situation where it's simpler to use the Windows API than it is to use the built-in functionality. Although Access does support a property of the current database, AppTitle, that you can use to set and retrieve the Access titlebar, it's clumsy to use because AppTitle is a user-defined property. If the property doesn't yet exist in a database, you must create it. With the Windows API, retrieving and setting the Access caption both require just a few predictable steps, and neither process is terribly difficult. This solution demonstrates the steps to set and retrieve the Access caption with the Windows API. The AppTitle property is discussed in Recipe 4.7.3.

To try changing the Access caption, load and run frmSetTitleBarCaptionAPI from 04-07.MDB. The form displays the current Access caption. By filling in a new value in the New Access Caption text box and pressing the Set New Caption button, you can change the caption on the main Access window. Figure 4-14 shows the form once it's already done its work. Press the Reset Caption button when you're done to reset the Access caption.

frmSetTitleBarCaptionAPI after it has set the new Access caption

Figure 4-14. frmSetTitleBarCaptionAPI after it has set the new Access caption

To include this functionality in your own applications, follow these steps:

  1. Import the module basCaption (which supplies the necessary Windows API declarations and the interface routines) from 04-07.MDB.

  2. To retrieve the current Access caption, call the acbGetAccessCaption function. For example:

    strOldCaption = acbGetAccessCaption( )
  3. To set a new Access caption, call the acbSetAccessCaption subroutine, passing to it a string that holds your new caption, as follows (by appending an empty string to the contents of the text box, you guarantee that the value you pass to acbSetAccessCaption is indeed a string, even if the text box's content is empty):

    Call acbSetAccessCaption(Me.txtOldCaption & "")
  4. To set the caption of any window given its window handle, call the SetWindowText API directly:

    Call SetWindowText(hWnd, "Your New Caption")

Discussion

To retrieve the Access window caption, call the acbGetAccessCaption function, which passes the Access window handle (Application.hWndAccessApp) to the more generalized acbGetWindowCaption function, which does its work in the following three steps:

  1. It uses the built-in Space function to size a string buffer large enough to hold all the characters.

  2. It calls the Windows API function GetWindowText to fill the buffer with the actual window caption. GetWindowText returns the number of characters it filled in.

  3. It uses the built-in Left function to remove extra characters.

The code for the acbGetWindowCaption function is as follows:

Private Function acbGetWindowCaption(ByVal hWnd As Long) As Variant

   ' Get any window's caption, given its hWnd.

   Dim intLen As Integer
   Dim strBuffer As String

   Const acbcMaxLen = 255

   If hWnd <> 0 Then
      strBuffer = Space(acbcMaxLen)
      intLen = GetWindowText(hWnd, strBuffer, acbcMaxLen)
      acbGetWindowCaption = Left(strBuffer, intLen)
   End If
End Function

To set the Access caption, call the acbSetAccessCaption subroutine, passing to it the new caption you'd like to use. This procedure is much simpler than the previous one: it passes the Access window handle and the caption to the SetWindowText API procedure. The code for the acbSetAccessCaption subroutine is as follows:

Public Sub acbSetAccessCaption(ByVal strCaption As String)
   
   ' Set the Access caption to be the value in strCaption.
   Call SetWindowText(Application.hWndAccessApp, strCaption)
End Sub

Access does provide a built-in mechanism for setting the caption to be used while a specific database is loaded: the Tools Startup dialog, shown in Figure 4-15. Using this dialog, you can set many of the startup options you'll need to deliver any application: the startup form, titlebar, icon, shortcut menu bar, and global menu bar. You can control other Access behavior as well, such as displaying the database window at startup, displaying the status bar, using built-in toolbars, or allowing toolbar changes.

Use the Tools → Startup dialog to set application startup options

Figure 4-15. Use the Tools Startup dialog to set application startup options

The AppTitle property allows you to set the database's titlebar, and the AppIcon property allows you to set an icon for the application. Both are usually set using the Startup dialog, but you can also modify them programmatically, as long as you remember that they're not built-in properties of the database. You must first create the properties and append them to the collection of properties; then you'll be able to use them.

The example database includes a form called frmSetTitleBarCaptionProperty that uses the AppTitle database property, creating the property on the fly if necessary. Here's the code that sets a new titlebar caption:

Private Sub cmdNewCaption_Click( )
    Dim prp As DAO.Property
    On Error GoTo HandleErr
    CurrentDb.Properties("AppTitle") = Me.txtNewCaption & ""
    
ExitHere:
    Application.RefreshTitleBar
    Exit Sub

HandleErr:
    Select Case Err.Number
        Case 3270 'Property not found
            Set prp = CurrentDb.CreateProperty( _
             "AppTitle", dbText, Me.txtNewCaption)
            CurrentDb.Properties.Append prp
        Case Else
            MsgBox _
             Err.Number & ": " & Err.Description, , "cmdNewCaption"
    End Select
    Resume ExitHere
End Sub

To retrieve the titlebar caption when the form opens, we used error handling that assumes the caption is "Microsoft Access" if the AppTitle property hasn't been used to change it:

Private Sub Form_Open(Cancel As Integer)
    On Error Resume Next
    Me.txtOldCaption = CurrentDb.Properties("AppTitle")
    If Err.Number <> 0 Then
        Me.txtOldCaption = "Microsoft Access"
    End If
End Sub

What are the trade-offs? The Windows API requires less code, runs faster, and works with applications other than Access (if you can get a window handle, you can set the caption). However, the AppTitle property actually persistently sets the database's property, so the next time you load the database, the title is set for you. It takes a bit more work to use the non-API Access method, but it does allow you to preserve the setting for your next session.

One final note: the Windows API allows you to set the caption to be an empty string. You cannot set the Access AppTitle property to be an empty string; Access will reject it. If you want to remove the text from the titlebar altogether, use the API method.

4.8. Use the Windows File Open/Save Common Dialogs

Problem

You need to allow users to choose filenames for opening and saving files. You know that Windows supports a common way to get these names. How can you use this mechanism from within Access?

Solution

Not only can you use the common File Open/Save dialogs, but you even have three ways to do it:

  • You can use the ActiveX control, COMMDLG.OCX, that ships with the some versions of the developer version of Office, and with Visual Basic.

  • In Access 2002 and later, you can use the FileDialog object.

  • You can call the Windows API directly.

If you don't have the developer version of Office, or Visual Basic, the first suggestion won't help. In addition, distribution of applications that use the common dialog ActiveX can get complex, because of ActiveX versioning issues. The FileDialog object added in Access 2002 makes it easier to select files, but it's not available in earlier versions. Therefore, this solution shows how to call the Windows API directly and lists all the options you have when using these common dialogs.

Open and run the form frmTestOpenSave from 04-08.MDB. This sample form allows you to set various flags (described later in this solution) and to see the results. You can try both the File Save and File Open common dialogs. Try changing some of the settings and see what happens. Figure 4-16 shows the File Open dialog—with the Read Only checkbox hidden and allowing for multiple selections—displayed in explorer mode (as opposed to the older Program Manager look, which is what Windows will use if you specify the multiselect option by itself ).

The sample form, frmTestOpenSave, showing the File Open dialog in use

Figure 4-16. The sample form, frmTestOpenSave, showing the File Open dialog in use

To use this functionality within your own applications, follow these steps:

  1. Import the module basCommonFile from 04-08.MDB into your own application. This module provides the type and API function declarations you'll need and the wrapper functions that make it easy for you to use the common dialogs.

  2. To use the File Open or File Save dialogs, call the acbCommonFileOpenSave function, passing to it information indicating what you want it to do. Table 4-2 lists the options available when you call the function. None of the parameters is required; the table lists the default values the function will use if you leave off each of the parameters. As a simple example, the following function call will ask for the name of the file to which you'd like to save, suggesting FOO.TXT and returning the full path of the file you choose:

    varFileName = acbCommonFileOpenSave(FileName:="FOO.TXT", OpenFile:=False)

Table 4-2. Parameters for the acbCommonFileOpenSave function (all optional)

Parameter name

Description

Default value

                              Flags

A combination of zero or more flags from Table 4-1 that control the operation of the dialog. Combine them using the OR operator.

0

                              InitialDir

The initial directory that the dialog should use.

""

                              Filter 

A string listing the available file filters. Use acbAddFilter, as shown in the examples, to build this parameter. The format of this item is important, so make sure to use the function rather than just setting the value by hand.

""

                              FilterIndex

The number of the filter item to use when the dialog first opens. The first filter is numbered 1.

1

                              DefaultExt

A default file extension to be appended to the filename if the user doesn't supply one. Don't include a period.

""

                              FileName

The filename to use when the dialog is first displayed.

""

                              DialogTitle

The title for the dialog. Usually, you won't specify this parameter.

Open/Save As

                              hWnd

The window handle for the parent window of the dialog. This value controls where the dialog will be placed.

Application.hWndAccessApp

                              OpenFile

Whether it's the Open or Save dialog. (True = Open, False = Save).

True

Tip

Because the acbCommonFileOpenSave function accepts so many optional parameters, and you'll generally want to set only a few of them, you may find VBA's support for named parameters useful. That is, rather than depending on the exact order of the parameters you send to acbCommonFileOpenSave, use the name of the parameter, a :=, and then the value, as we've done in this example. This will make your code easier to read and far less error-prone.

  1. If you also want to specify filter choices that show up in the "Files of type:" combo box on the dialog, call the acbAddFilterItem function. This function accepts three parameters: the string of filters to which you want to add items; the description for your filter ("Databases (*.mdb, *.mda)", for example); and the actual filter file specifications, delimited with a semicolon ("*.mda;*.mda", to match the previous example). The function returns the new filter string. You can call acbAddFilterItem as many times as you need to build up your list of filters. For example, the following example (similar to the example in basCommonFile) sets up four filter expressions. You can call TestIt from the debug window in Access to test the filters:

    Function TestIt( )
        Dim strFilter As String
    
        strFilter = acbAddFilterItem(strFilter, "Access Files (*.mda, *.mdb)", _
         "*.MDA;*.MDB")
        strFilter = acbAddFilterItem(strFilter, "dBASE Files (*.dbf)", "*.DBF")
        strFilter = acbAddFilterItem(strFilter, "Text Files (*.txt)", "*.TXT")
        strFilter = acbAddFilterItem(strFilter, "All Files (*.*)", "*.*")
    
        MsgBox "You selected: " & acbCommonFileOpenSave(InitialDir:="C:\", _
         Filter:=strFilter, FilterIndex:=3, DialogTitle:="Hello! Open Me.")
    End Function
  2. You may want to specify some of the available options for controlling the common dialogs, as shown in frmTestOpenSave. You can specify any of the options shown there, and more, when you call the function. To specify your selected options, choose values from the items in Table 4-2, combine them together with the OR operator, and send this value to the acbCommonFileOpenSave function as the Flags argument. For example, the following statement will build up a Flags value that tells Windows to hide the Read Only checkbox and the Network button, and that the output path must already exist:

    lngFlags = acbOFN_HIDEREADONLY Or acbOFN_NONETWORKBUTTON Or _
     acbOFN_PATHMUSTEXIST

Discussion

When you call acbCommonFileOpenSave, you're actually calling the GetOpenFileName or GetSaveFileName Windows API functions. The acbCommonFileOpenSave function takes only the parameters you send it, replacing missing ones with the default values shown in Table 4-2, and fills in a user-defined data structure that both API functions expect to receive. The API functions actually do the work, and acbCommonFileOpenSave returns to you the chosen filename. Although you may find it interesting to dig into the details of calling the API functions directly, that's beyond the scope of this solution. The wrapper function, acbCommonFileOpenSave, handles a large majority of the cases in which you'll need to use common File Open/Save dialogs.

Table 4-3 lists all the values you can use in the Flags parameter of the call to acbCommonFileOpenSave. You can skip the parameter altogether, or you can use one or more of these values, combined with the OR operator. For example, to hide the Read Only checkbox and allow multiple files to be selected, use this code:

lngFlags = acbOFN_HIDEREADONLY Or acbOFN_ALLOWMULTISELECT

Table 4-3. Values to be combined in acbCommonFileOpenSave's Flags parameter

Constant name

On input

On output

 acbOFN_ALLOWMULTISELECT

Allows you to select more than one filename (File Open only). Unless you also select the acbOFN_EXPLORER flag, you'll get an old-style dialog box.

The strFile member will contain the chosen path, followed by all the files within that path that were chosen, separated with spaces, as in C:\ResultFolder File1.TXT File2.TXT.

 acbOFN_CREATEPROMPT

Prompts you if the selected file doesn't exist, allowing you to go on or make a different choice.

 acbOFN_EXPLORER

Creates an Open or Save As dialog that uses user-interface features similar to the Windows Explorer. If you've specified the acbOFN_ALLOWMULTISELECT flag, you'll generally also want to include this flag.

 acbOFN_EXTENSIONDIFFERENT

Set if the chosen filename has a different extension than that supplied in the DefaultExt parameter.

 acbOFN_FILEMUSTEXIST

Forces you to supply only existing filenames.

 acbOFN_HIDEREADONLY

Hides the Read Only checkbox.

 acbOFN_LONGNAMES

Causes the Open or Save As dialog to display long filenames. If this flag is not specified, the dialog displays filenames in 8.3 format. This value is ignored if acbOFN_EXPLORER is set.

 acbOFN_NOCHANGEDIR

Restores the current directory to its original value if the user changed the directory while searching for files.

 acbOFN_NODEREFERENCELINKS

Returns the path and filename of the selected shortcut (.LNK) file. If you don't use this flag, the dialog returns the path and filename of the file referenced by the shortcut.

 acbOFN_NOLONGNAMES

Specifies that long filenames are not displayed in the File Name list box. This value is ignored if acbOFN_EXPLORER is set.

 acbOFN_NONETWORKBUTTON

Hides the Network button.

 acbOFN_NOREADONLYRETURN

Specifies that the returned file does not have the Read Only checkbox checked and is not in a write-protected directory.

 acbOFN_NOTESTFILECREATE

Normally, COMMDLG.DLL tests to make sure that you'll be able to create the file when you choose a filename for saving. If set, it doesn't test, providing no protection against common disk errors.

 acbOFN_NOVALIDATE

Disables filename validation. Normally, Windows checks the chosen filename to make sure it's a valid name.

 acbOFN_OVERWRITEPROMPT

Issues a warning if you select an existing file for a File Save As operation.

 acbOFN_PATHMUSTEXIST

Forces you to supply only valid pathnames.

 acbOFN_READONLY

Forces the Read Only checkbox to be checked.

Set if the user checked the Read Only checkbox.

 acbOFN_SHAREAWARE

Ignores sharing violations. Because Access code cannot handle the errors that occur when sharing violations occur in this code, you should not set this flag.

 acbOFN_SHOWHELP

Shows a Help button on the dialog. Though this option works, the button will not, so its use in Access is limited.

Not all of the flags make sense for both File Open and File Save operations, of course. Your best bet is to experiment with the flags, either in your own code or using the sample form frmTestOpenSave from 04-08.MDB.

Some of the flags are useful only on return from the function call. For example, if you select the Read Only checkbox on the common dialog, Windows passes that fact back to you in the Flags parameter. To retrieve that information from your call to acbCommonFileOpenSave, pass the Flags argument in a variable, not directly as a literal value. Because acbCommonFileOpenSave accepts the Flags argument by reference, it can return the value to your calling procedure after you've selected a filename. To check if a particular flag value was set during the call to acbCommonFileOpenSave, use the AND operator with the return value, as in this example fragment (see the Solution in Recipe 11.1 for more information on using the AND and OR operators):

Dim lngFlags As Long
Dim varFileName As Variant

lngFlags = 0
varFileName = antCommonFileOpenSave(Flags:=lngFlags)
If lngFlags AND acbOFN_READONLY <> 0 Then
   ' The user checked the Read Only checkbox.
End if

If you pass a variable to acbCommonFileOpenSave containing the Flags information (rather than not sending the parameter, or sending a literal value), the function will return to the caller information about what happened while the dialog was in use. Several of the flags listed in Table 4-3 provide information on output. That is, you can check the state of the Flags variable, and if it contains the flags from Table 4-3, you know that the tested condition was true. For example, to open a file and then check to see if the selected file is to be opened read-only, you could use code like this:

Dim lngFlags As Long
Dim varRetval As Variant

varRetval = acbCommonFileOpenSave(Flags:=lngFlags)
If Not IsNull(varRetval) Then
   If lngFlags AND acbOFN_READONLY Then
      MsgBox "You opened the file read-only!"
   End If
End If

As you can see in this example, you can use the AND operator to see if Flags contains the specific flag in which you're interested.

The file filter (the Filter parameter to acbCommonFileOpenSave) has a unique format: it consists of pairs of strings. Each item is terminated with vbNullChar (Chr$(0)). The first item in the pair supplies the text portion, which appears in the combo box in the lower-left corner of the dialog. The second item supplies the file specifications that Windows uses to filter the list of files. Though it doesn't matter what you use in the first item, by convention, most applications use something like this:

Oogly Files (*.oog)

listing the file description. The conventional file specification looks something like this:

*.oog

To simplify building these filter strings, use the acbAddFilter function from basCommonFile. See Step 3 for an example.

If you select the acbOFN_AllowMultiSelect flag, the result value may contain a null-delimited list of files, starting with the folder containing the files. For example, if you navigated to C:\AccessCookbook, and selected 04-04.mdb and 04-06.mdb, the return value from acbCommonFileOpenSave would contain the following text (we've used the vertical pipe symbol here to represent Chr(0) within the text):

 C:\AccessCookbook|04-04.mdb|04-06.mdb

The sample form replaces the Chr(0) with a space character for you:

Private Sub cmdFileOpen_Click( )
    Dim varResult As Variant
    varResult = FileOpenSave(True)
    
    Me.txtFileOpen = Replace(varResult, vbNullChar, " ")
End Sub

If you allow multiple file selection, it's up to you to parse the various the file path and names yourself.

Take the time to study all the parameters in Table 4-2 and all the options in Table 4-3. There's not room here to go into detail for each one, so your best bet is to try out all of them. You can play with frmTestOpenSave to test the effects of some of the flag values. See what happens when you place a value into one of them, and then experiment from there.

Although you have no direct control over the placement of the common dialogs when they pop up, the choice of the parent window can affect the location. If you pass 0, Application.hWndAccessApp, or a normal form's hWnd property for the hWnd argument to acbCommonFileOpenSave (or just don't send a value, so it uses the default value), the dialog will appear in the upper-left corner of the Access MDI client window. If, on the other hand, you pass it the hWnd property of a pop-up form, Windows will place the dialog in the upper-left corner of that pop-up form even if the form is not visible. Therefore, for complete control over the placement of the dialog, create a form, set its PopUp property to True, and use that form to place the dialog.

Finally, remember that these dialogs don't actually do anything—they just supply you with the names of files. It's up to your application code to open or save the requested files.

See Also

For more information on working with the Windows API, see Chapter 11.

4.9. Clean Test Data out of a Database When You're Ready to Ship It

Problem

You're finished designing and building a database; it's ready to ship to your client. Before they can use it, you need to remove the artificial data you've entered, without destroying permanent lookup tables. Is there a simple way to do this without running into referential-integrity problems?

Solution

One solution is to open every data table in datasheet view, select all the records, press the Delete key, and confirm the deletion. However, there are three problems with this simple method:

  • You have to open tables in a particular order (i.e., tables on the many side of a many-to-one relationship before their related one-side tables).

  • You have to remember which tables contain test data and which ones contain production data.

  • The task is tedious and repetitive.

Instead of clearing out your test data by hand, you can write a general-purpose routine that uses a table of tables and a simple SQL statement to remove only the test data, in the correct order.

Open 04-09.MDB and view the tables in the database container. Open the tblFood table and try to delete some records. You'll get a referential-integrity error, because there are related records in txrefFoodRestaurant. Figure 4-17 shows the relationships set up for the sample database. Now open frmDemo and click on the Clear button to remove all the test data from the database without any manual intervention.

Relationships in the sample database

Figure 4-17. Relationships in the sample database

To implement this technique in your own database, follow these steps:

  1. Import the table zstblDeleteOrder (structure only, without data) into your own database, or create a new table with the fields shown in Table 4-4.

Table 4-4. Structure of zstblDeleteOrder

Field name

Data type

Field size

Properties

Order

Number

Integer

PrimaryKey

TableName

Text

  1. Import the module zsbasMaintain into your database, or create a new module with the single function shown here:

    Public Function acbClearData( ) As Boolean
        ' Remove all data from tables specified in zstblDeleteOrder.
        ' Data is removed in the order specified to avoid
        ' referential-integrity violations.
      
        On Error GoTo HandleErr
        
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        
        Set db = CurrentDb( )
        Set rst = db.OpenRecordset("zstblDeleteOrder", dbOpenSnapshot)
    
        Do Until rst.EOF
            db.Execute "DELETE * FROM " & rst("TableName")
            rst.MoveNext
        Loop
        
        rst.Close
        Set rst = Nothing
        acbClearData = True
        
    ExitHere:
        Exit Function
    
    HandleErr:
        acbClearData = False
        MsgBox "Error " & Err & ": " & Err.Description, , "acbClearData( )"
        Resume ExitHere
    End Function
  2. Open zstblDeleteOrder in datasheet view and add one record for each table you want to clear out before shipping. These tables must be listed in the order in which you want them cleared. Assign each table a unique order number, with the lowest number belonging to the first table to be cleared. Tables on the many side of a one-to-many relationship should be listed before tables on the one side of the relationship. Tables that you don't want to clear (including zstblDeleteOrder) should not be entered at all. Figure 4-18 shows the sample version of zstblDeleteOrder.

Sample zstblDeleteOrder

Figure 4-18. Sample zstblDeleteOrder

  1. If you'd like a form to control the deletion process, create a new, blank form. Place one command button on the form and modify the command button's Click event handler to call acbClearData:

    Private Sub cmdClear_Click( )
        Call acbClearData
    End Sub

Discussion

The acbClearData function automates the task of selecting the order of your tables and then deleting the data table by table. You select the order when you build the zstblDeleteOrder table. The function works by opening a snapshot of this table and looping through the snapshot one line at a time. The line in the function that does the actual work is:

db.Execute "DELETE * FROM " & rst("TableName")

This line concatenates the table name found in rstTables, using SQL keywords to form a complete SQL statement. For example, if you specify tblFood as one of the tables to delete, Access builds the following SQL statement:

DELETE * FROM tblFood;

This is the SQL equivalent of a delete query that selects all rows from the table and deletes them. The db.Execute statement turns this query over to the Jet engine for execution.

The sample database has a second button, Restock, on the demo form. This button runs a procedure that in turn runs four append queries to take backup copies of the data and return them to the main data tables. This lets you test the function in the sample database more than once.

When you use this technique in your own database, be sure to compact the database before you distribute it to your users. To do this, select Tools Database Utilities Compact and Repair Database. There are two reasons to compact your database at this point:

  • Until you compact, the Access file won't shrink at all. When you delete data from tables, Access marks the data pages as empty, but it doesn't give them back to your hard drive as free space. This occurs only when you compact the database.

  • When you compact a database, Access resets the next counter values for all incrementing autonumber fields. If you remove all the data from a table with an autonumber in it and compact the database, the next record added will have an autonumber value of 1.

See Also

For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.

4.10. Secure Your Access Database

Problem

You've created an Access database that you'd like to secure. The database contains some sensitive data to which you wish to limit access. You'd like to be able to create different classes of users, so that some users have no access to this data, others can read the data but can't change it, and still others can modify the data. How can you accomplish this?

Solution

The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model that allows you to secure your Access databases, assigning permissions to users and groups. Access supports two mechanisms for securing your database: the database password feature and user-level security. The database password feature is an all-or-nothing proposition—users who know the password aren't restricted in any way once they're in the database. If you want to assign varying permissions to different users, you'll need user-level security. User-level security is fairly complex—it doesn't work if you leave out a step. It consists of creating a new workgroup file (which holds user, group, and password information) and then using this new workgroup file to secure the database. There is a Security Wizard built into Access that will help you secure your database, but you can also manually perform the process, which will help you understand what's happening.

User-level security relies on a special database, called a workgroup file, to store users, the groups to which they belong, and their passwords. When you install Access, you are automatically hooked up to a default workgroup file called System.mdw. To secure your database, you will need to create your own unique workgroup file.

Every Access workgroup file includes two built-in groups: the Users group, which contains every user; and the Admins group, the members of which automatically get permission to administer security. There is also one built-in user, Admin. The Admin user starts out in the Admins group, but don't let the name Admin confuse you. You can remove Admin from the Admins group and take away all its administrative privileges, as long as some other user is left in Admins to act as the administrator. The Admin user has the same identity in every Access workgroup file, so any privileges that you give Admin will be available to anyone.

Securing a database involves adding a new member to the Admins group, removing the Admin user from that group, removing permissions from the Admin user and from the Users group, and assigning permissions to the custom groups that you define. The steps that follow show you how to implement user-level security in your Access database:

  1. Create a new, unique workgroup file. In Access 2002 and later, this capability is built into the product, but in older versions you must run a separate utility called the Workgroup Administrator (Wrkgadm.exe). Write down the Name, Organization, and Workgroup ID strings that will be requested when you create your new workgroup file, and store them in a safe place. These strings will be encrypted to form the unique identity of your new workgroup file—if the original ever becomes lost or corrupted, it can be reconstructed as long as you input the identical strings. Each database "knows" the workgroup file it was secured with by this unique token (the Workgroup ID, or WID) and will not recognize a workgroup file that has a different WID. This means that you'll be permanently locked out of your database if you lose these strings. Also, upgrading a secured Access database to a newer version of Access is almost impossible if you don't have this information, because the recommended upgrade path is to recreate the workgroup file in the new version of Access and then upgrade the secured database. Figure 4-19 shows the Workgroup Administrator dialog with the new workgroup information. You can try this solution with any of the MDB files used in this chapter, such as 04-09.MDB.

The Workgroup Administrator dialog

Figure 4-19. The Workgroup Administrator dialog

  1. The Workgroup Administrator automatically switches you to the new workgroup file, so you can simply close when you're finished. The Workgroup Administrator will create the necessary entries in the registry, making the new workgroup file the default. Start Access and load your database.

  2. You will be logged on as a user named Admin. Use the Security menu options to set a password for the Admin user. This causes Access to prompt for a logon name and password the next time you try to open a database using this workgroup file.

  3. Create a new user, which is the account you will use to secure the database. Add this new user to the Admins group, to make it the administrator. None of the user accounts has any built-in capabilities. You also need to write down the strings used for the Name and Personal Identifier (PID). Part of recreating a workgroup file is recreating the key accounts stored in it. The PID is not a password—it is encrypted along with the name string to create a System Identifier, or SID. The SID is the token used when assigning permissions and when distinguishing users from each other. The name alone isn't secure, although Access won't let you have duplicate names in the same workgroup file.

  4. Quit Access entirely and restart, logging on as the new user account that you created in Step 4. Don't type anything in the Password dialog—you haven't set one for this account yet.

  5. Remove the Admin user from the Admins group so that Admin is a member of only the Users group. Every user is automatically added to the Users group, which is similar to Everyone in Windows. You can't delete any of the built-in users or groups (Admin, Admins, and Users), but you can move users in and out of various groups. Access requires that there always be one member of the Admins group (that would be you). Later you'll create additional groups, assigning permissions to the groups for various database objects. Users then inherit permissions from their group membership. You'll probably want to remove all permissions from the Users group, since permissions granted to Users are granted to all.

  6. At this point you'll want to secure the database. You can either run the Security Wizard or manually secure it. If you manually secure it, you'll create a new database (this is how you transfer ownership of the database) and then import all of the objects. Next, remove all permissions for the Users group and the Admin user. The Admins group has full permissions by default—only the Admins group can work with users and groups and has irrevocable administrative permissions on the database. If you use the Security Wizard, it will also remove all permissions from the Admin user and the Users group and encrypt the new database (you can do this manually if you choose).

  7. You need to create your own custom groups and assign the desired level of permissions to these groups. Every user is required to be a member of the Users group (otherwise, a user would not be able to start Access), so grant to Users only those permissions that you want everyone to have. Members of the Admins group have irrevocable power to administer database objects, so make sure to limit membership in the Admins group to only those users who are administrators.

  8. Create your own users and assign them to the groups that reflect the level of permissions you want them to have. Do not assign permissions directly to users, because that is extremely difficult to administer; users inherit permissions from the groups of which they are members, and keeping track of the permissions assigned to a group is much easier than keeping track of the separate permissions of individuals. If a user is a member of multiple groups, that user will have all the permissions granted to any of those groups plus any permissions assigned specifically to the user (this is known as the "least-restrictive" rule). There is no way to deny permissions to a user if that user is a member of a group that has been granted those permissions. If you need to create specific permissions for only a single user, create a group for that user and assign the permissions to the group; then add the user to the group. The reason for this becomes clear when you consider that the user may leave unexpectedly, and you may have to set up permissions for the replacement on short notice.

  9. Test security by logging on as users with varying levels of permissions. Try to do things that a user at that level shouldn't be able to do. The only way you'll be able to see if your database security is working is to bang on it and try to break it.

Discussion

The Microsoft Jet database engine, which Access uses to store and retrieve its objects and data, employs a workgroup-based security model. Every time the Jet database engine runs, it looks for a workgroup file, which holds information about the users and groups of users who can open databases during that session. The default workgroup file, System.mdw, is identical across all installations of Access. That's why it's important not to skip the first step of creating a new workgroup file.

The workgroup file contains the names and security IDs of all the groups and users in that workgroup, including passwords. Each workgroup file contains built-in groups (Admins and Users) and a generic user account (Admin). You can't delete any of the built-in accounts, but you can add your own group and user accounts.

The built-in accounts each have their own characteristics and properties:

  • The built-in Admins group is always present, and its users have administration rights that cannot be revoked. You can remove rights from the Admins group through the menus or through code, but any member of Admins can assign them right back. Access ensures that there is always at least one member in the Admins group to administer the database. The Admins group is the only built-in account that has any special properties.

  • The default user account, Admin, is a member of the Admins group in an unsecured database and is the only user account present in the default System.mdw workgroup file. It has no special properties of its own; all of its power is inherited through membership in the Admins group.

  • The Users group is a generic group to which all users belong. You can create users in code and not add them to the Users group, but they won't be able to start Access—internal tables and system objects are mapped to the permissions of the Users group. Other than the fact that all users must belong to the Users group, it has no special properties.

Permissions to various Access objects can be assigned directly to users (explicit permissions) or to groups. Users inherit permissions from the groups to which they belong (implicit permissions). It's always a good idea from an administrative point of view to assign permissions only to groups, and not to users, which could become endlessly complicated.

Access employs the least-restrictive rule: users have the sum total of their explicit and implicit permissions. In other words, if a user belongs to a group that has full permissions and you make that user a member of a group that has restricted permissions, the user will still have full permissions because he is a member of the unrestricted group.

User and group information, including passwords, is saved in the workgroup file, or System.mda/mdw, which validates user logons at startup. Permissions to individual objects are saved in the database itself. You can give the groups and users within a workgroup various levels of permission to view, modify, create, and delete the objects and data in a database. For example, the users of a particular group might be permitted to read only certain tables in a database and not others, or you could permit a group to use certain forms but not to modify the design of those forms.

Most Access database applications consist of a frontend with linked tables against a backend database. You need to secure both the frontend and the backend using the same workgroup file.

Access user-level security works best when securing data—if you want to secure your code, the best solution is to compile your application as an MDE. This prevents anyone from viewing or altering the design of forms, reports, or module code. It also prevents users from creating new Access objects, but it has no effect on data objects (tables and queries). You'll need to save a backup copy of the original .mdb file if you want to make alterations later—there's no way to decompile an MDE to recover the source code and source objects.

Also bear in mind that security in an Access database is mainly good for deterrence only. In any situation in which the physical files are exposed, it is impossible to guard against determined hackers. An additional weakness is that the network share where the Access .mdb and .mdw files are located also needs to have read, write, and delete permissions, which means you can't prevent users from deleting or copying the .mdb and .mdw files. The only alternative is to create an n-tier application where the middle-tier objects alone have access to the physical files. However, this means that you need to write the application "unbound," since the users will no longer be directly connected to the database. When you get to that point, you'll probably be considering SQL Server or another database platform that is capable of scaling to support more users and larger volumes of data.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required