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.
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?
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.
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).
Click on the Build button (...) next to the RecordSource property to open the Query Builder for the record source query.
With the Query Builder open, click on the View SQL button on the toolbar or select View → SQL.
The SQL window opens, displaying the query as a SQL statement, as shown in Figure 4-2.
Highlight the entire SQL statement and press Ctrl-C or select Edit → Copy to copy it to the clipboard.
Close the SQL window.
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.
Delete the original RecordSource query from the database container.
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.
For more information on working with queries, see Chapter 1.
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?
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:
Import the form zsfrmInventory from 04-02.MDB into your own application.
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.
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.
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
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 FunctionThe 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.UpdateThe 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
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.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.
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?
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:
Import zsfrmVerifySettings from 04-03.MDB into your own database.
Load zsfrmVerifySettings in form view. As it loads, it will build the object property inventory, creating tables and queries as necessary.
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.
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.
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.
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):
Open zsfrmVerifySettings and select all the forms in your application from the list of objects.
Click on the Document Selected Items button. Go out for lunch while it does its work.
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.
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.
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.
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.
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 FunctionWhen 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 SubThe 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 SubThe 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 intIFinally, 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
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.UpdateNext, 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 prpIf 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.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.
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?
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.
To use Application.Echo to disable screen updates in your own applications, follow these steps:
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.
When you want to disable screen updates, call the acbShowUpdates subroutine, passing it a
Falsevalue. To reenable screen updates, call the subroutine again, passing it aTruevalue. In other words, your code that uses acbShowUpdates should take the following form:Call acbShowUpdates(False) ' Do your work in here... Call acbShowUpdates(True)
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 SubAs 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 SubTip
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.
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", strInfoThe 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 SubIf 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.
For more information on working with the Windows API, see Chapter 11.
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?
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.
To include this functionality in your own applications, follow these steps:
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.
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 thelngLanguagevariable, 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.
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.
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
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 FunctionOnce 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
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?
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.
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.
To include similar functionality in your own applications, follow these steps:
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).
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.
Add the necessary rows to tblMessages, filling in each column with the translated text, as shown in Figure 4-13.
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.)
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.
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 FunctionThis 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 = NullIf 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 WithIf 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 SubThe 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 SubIf 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.
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?
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.
To include this functionality in your own applications, follow these steps:
Import the module basCaption (which supplies the necessary Windows API declarations and the interface routines) from 04-07.MDB.
To retrieve the current Access caption, call the acbGetAccessCaption function. For example:
strOldCaption = acbGetAccessCaption( )
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 & "")
To set the caption of any window given its window handle, call the SetWindowText API directly:
Call SetWindowText(hWnd, "Your New Caption")
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:
It uses the built-in Space function to size a string buffer large enough to hold all the characters.
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.
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 FunctionTo 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.
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 SubTo 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 SubWhat 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.
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?
Not only can you use the common File Open/Save dialogs, but you even have three ways to do it:
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 ).
To use this functionality within your own applications, follow these steps:
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.
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 |
|---|---|---|
|
A combination of zero or more flags from Table 4-1
that control the operation of the dialog. Combine them using the
|
0 |
|
The initial directory that the dialog should use. |
“” |
|
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. |
“” |
|
The number of the filter item to use when the dialog first opens. The first filter is numbered 1. |
1 |
|
A default file extension to be appended to the filename if the user doesn’t supply one. Don’t include a period. |
“” |
|
The filename to use when the dialog is first displayed. |
“” |
|
The title for the dialog. Usually, you won’t specify this parameter. |
Open/Save As |
|
The window handle for the parent window of the dialog. This value controls where the dialog will be placed. |
Application.hWndAccessApp |
|
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.
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 FunctionYou 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
ORoperator, and send this value to the acbCommonFileOpenSave function as theFlagsargument. For example, the following statement will build up aFlagsvalue 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
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
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 IfAs 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 SubIf 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.
For more information on working with the Windows API, see Chapter 11.
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?
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:
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.
To implement this technique in your own database, follow these steps:
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 |
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 FunctionOpen 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.
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.
For more information on using DAO in Access databases, see How Do I Use Data Access Objects (DAO) in New Databases? in the Preface.
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?
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:
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 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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
Get Access Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
