Problem

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

Solution

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

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

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

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

Discussion

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

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

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

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

ExitHere:
    DoCmd.Hourglass False
    Exit Sub

HandleErr:
    Resume ExitHere
End Sub

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

Documenting all the containers

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

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

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

Creating the inventory table

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

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

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

Documenting each container

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

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

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

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

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

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

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

   On Error Resume Next

   Set db = CurrentDb( )

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

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

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

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

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

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

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

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

Avoiding errors

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

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

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

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

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

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

Comments

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

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

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

See Also

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

Recipe 12.4: Perform a Mail Merge from Access to Word

Problem

You'd like to be able to do a mail merge to Word using Access data, without having to launch the mail merge from Word using its mail merge features.

Solution

Access allows you to output data directly to any format using the DoCmd.OutputTo functionality. You can then run a mail merge from Word to a predefined Word template that contains the merge codes.

First you must create the Word template that holds your merge codes; then you can write the code in Access that performs the merge. The sample application 12-04.MDB contains a table and a query that retrieves the data to be sent to Word.

To perform a mail merge from Access to Word, follow these steps:

1. In Access, create the query that you will use for your data. Copy the rows from the datasheet view of the query and paste them into a Word document.

2. Save the Word document in the same folder as the Access database. The sample application uses the name qryCustomers.doc.

3. In Word, create a template by choosing File New Template from the menu. Fill in the plain text for your main merge document.

4. Choose Tools → Mail Merge from the menu to add the merge fields to the template. Use the Active Document option and select the Word document you created in Step 2. This will add the merge toolbar to your application.

5. Insert the merge codes for the fields in your template, then save the template in the same folder as qryCustomers.doc and the Access database.

6. In Access, write the code to perform the mail merge. Declare two module-level constants for the name of the template and the name of the query:

   Private Const conTemplate As String = "acbMailMerge.dot"
   Private Const conQuery As String = "qryCustomers"

1. Create a procedure to perform the mail merge. Here's the complete listing:

   Public Sub MailMerge( )
       Dim strPath As String
       Dim strDataSource As String
       
       Dim doc As Word.Document
       Dim wrdApp As Word.Application
           
       On Error GoTo HandleErrors
       ' Delete the rtf file, if it already exists.
       strPath = FixPath(CurrentProject.Path)
       strDataSource = strPath & conQuery & ".doc"
       Kill strDataSource
       
       ' Export the data to rtf format.
       DoCmd.OutputTo acOutputQuery, conQuery, _
        acFormatRTF, strDataSource, False
   
       ' Start Word using the mail merge template.
       Set wrdApp = New Word.Application
       Set doc = wrdApp.Documents.Add(strPath & conTemplate)
           
       ' Do the mail merge to a new document.
       With doc.MailMerge
           .OpenDataSource Name:=strDataSource
           .Destination = wdSendToNewDocument
           .SuppressBlankLines = True
           With .DataSource
               .FirstRecord = wdDefaultFirstRecord
               .LastRecord = wdDefaultLastRecord
           End With
           If .State = wdMainAndDataSource Then 
               .Execute
           End If
       End With
       
       ' Display the mail merge document.
       wrdApp.Visible = True
       
   ExitHere:
       Set doc = Nothing
       Set wrdApp = Nothing
       Exit Sub
       
   HandleErrors:
       Select Case Err.Number
           Case 53         ' File not found.
               Resume Next
           Case Else
               MsgBox Err.Number & ": " & Err.Description
               Resume ExitHere
       End Select
   End Sub

2. Create the FixPath procedure to handle any backslashes in the pathname:

   Private Function FixPath(strPath As String) As String
       If Right(strPath, 1) = "\" Then
           FixPath = strPath
       Else
           FixPath = strPath & "\"
       End If
   End Function

3. Test the procedure by positioning your cursor anywhere in the MailMerge procedure and pressing the F5 key.

Discussion

Microsoft Word exposes an Application object, which you can use to launch Word, and a Document object, which you can use to open a new Word document. Once you've launched Word, you can use all its capabilities from your Access application. The following sections outline the steps involved in communicating with Word via Automation.

Starting the connection with Word for Windows

To be able to work with Word from Access, you must create an object variable to refer to the Word Application object. You also need a Document variable to work with a specific Word document. The following code fragment defines these variables:

Dim doc As Word.Document
Dim wrdApp As Word.Application

The next step is to delete any previously existing data source documents:

strPath = FixPath(CurrentProject.Path)
Kill strPath & conQuery & ".doc"

If the document doesn't exist, the error handler will simply resume on the next statement and create a new document containing the data from the query using the OutputTo method of the DoCmd object:

DoCmd.OutputTo acOutputQuery, conQuery, _
 acFormatRTF, strPath & conQuery & ".doc", False

Performing the mail merge

To launch Word and create a new document based on the mail merge template, set the Application object to a new instance of Word.Application. Set the Document object to create a new document using the Application's Add method, basing it on your template:

Set wrdApp = New Word.Application
Set doc = wrdApp.Documents.Add(strPath & conTemplate)

Once the document is open, use the Document object's MailMerge method to merge the data to a new document:

With doc.MailMerge
    .OpenDataSource Name:=strDataSource
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
    End With
    If .State = wdMainAndDataSource Then 
        .Execute
    End If
End With

In Access 2002 and later you must use the .OpenDataSource method in your code, but this isn't required in Access 2000.

Finishing the mail merge

To display the Word documents, set the Application object's Visible property to True:

wrdApp.Visible = True

Once the Word document is displayed, clean up by setting the Word object variables to Nothing. This frees up the memory and system resources:

Set doc = Nothing
Set wrdApp = Nothing

You'll see both the new document, named Document1 (based on the template), and the actual merge documents. You can save the merge documents or print them from Word.

Recipe 15.6: Create a Web Frontend to an Access Table Using FrontPage

Problem

While SharePoint is amazing, it has several limitations. First, it requires a server running Windows 2003 server and Windows SharePoint Services. In addition, the Data View Web Part is read-only and provides no mechanism for updating the data. Is there a way to use FrontPage to create a web frontend to your Access data without employing SharePoint?

Solution

You can use the FrontPage Database Interface Wizard to create a complete web site that lets you view and edit data from a database table. The trick in this solution is to create the database connection before running the wizard. Follow these steps to create a web site based on the tblAlbums table from the 15-06.MDB database:

WARNING This solution will not work with a SharePoint-enabled web site.

1. Startup FrontPage 2003.

2. Select Select File → New... to create a new web site. On the New task pane, click on "One page Web site..." under New Web site.

3. At the Web Site Templates dialog box click on Empty Web Site and enter the following location for the Web site:

   http://localhost/15-06

   FrontPage creates a new empty Web site on the current machine. If you do not have a Microsoft web server running on the current machine, you will need to change localhost to the name or address of a Microsoft web server for which you have site creation privileges.

4. Select File → Import. Click on Add File... from the Import dialog box.

5. Navigate to the 15-06.MDB sample database and click Open. Click OK to add the database to the site.

6. When you click OK, FrontPage recognizes that you are importing a database and asks you if you wish to create a database connection for the database.

7. Enter "15-06" for the database connection name and click Yes to import the database and create the database connection.

8. FrontPage displays an additional dialog box suggesting that the database be moved to the fpdb folder. This is a good practice so you should click Yes.

9. Select File → New... to create a new web site. On the New task pane, click on "One page Web site..." under New Web site.

10. At the Web Site Templates dialog box, select Database Interface Wizard, making sure to check the "Add to current Web site" checkbox before clicking on the OK button.

1. On the first page of the wizard, under "FrontPage has detected that your page will display best using:", select "ASP.NET" unless you'd prefer to use the older ASP technology.

TIP: You will need to choose ASP instead of ASP.NET if your web server is not configured to support ASP.NET.

1. Under "This wizard will connect your site to a database. What would you like to do?", select "Use an existing database connection", select "15-06" from the dropdown control, and click Next.

2. On the second page of the wizard, under "Select the table or view you would like to use for this database connection", select the "tblAlbums" table. Accept the default location for the new files and click Next.

3. On the third page of the wizard you are given the opportunity to modify the columns to be displayed. There's no need to modify the columns, so click Next.

1. On the fifth wizard page, you are asked to supply a user name and password to protect the database editor. Enter a user name and password or check the "Don't protect my submission page or my database editor with a username and password" checkbox.

2. Click Finish to complete the wizard.

3. The wizard creates a number of pages. Under the 15-06_interface\tblAlbums folder you should find the results_page.aspx page. Select File‡Preview in Browser to display this page in your browser.

Discussion

You aren't limited to one database interface per web site. You can rerun the FrontPage Database Interface Wizard as many times as you like, creating set of pages for either different tables within the same Access database or different databases. Just remember to check the "Add to current Web site" checkbox before clicking on the OK button when selecting the Database Interface Wizard template.

The steps in this solution apply when using FrontPage 2003. However, except for a few differences, the steps are virtually identical when using FrontPage 2002. (One big difference: FrontPage 2002 doesn't give you the choice of creating the site using ASP or ASP.NET; it always uses the older ASP technology.)

Alternatives to FrontPage

There are a number of technologies you can use to create a web frontend to an Access database, including: Data Access Pages, SharePoint, ASP.NET, Active Server Pages (ASP), Cold Fusion, PHP, and Java Server Pages (JSP). Data Access Pages are discussed in detail in Chapter 13. In addition, an ASP.NET example is included in Chapter 17.

With a wealth of options, you may be wondering which solution will work best for you. Ultimately, a number of factors will help you arrive at a decision. Do you want a solution that you can create quickly with little or no programming? If so, then you probably will want to use the FrontPage Database Interface Wizard (as demonstrated in this solution), Data Access Pages, or SharePoint. Use SharePoint (along with FrontPage) if you need to create a workgroup-based portal. One disadvantage of the SharePoint approach is that you cannot modify the Access data. You can use the FrontPage Database Interface Wizard, as demonstrated in this solution, to quickly create an ASP- or ASP.NET-based frontend to Access that you can then modify and extend using Visual InterDev (if you are using ASP), or Visual Studio .NET (if you are using ASP.NET). Finally, you can also use Data Access Pages, but only for smaller intranet-based sites.

If you're not averse to programming, you may want to use Visual Studio .NET to create an ASP.NET-based web site, or Visual InterDev to create an ASP-based site. In most cases, ASP.NET is the better choice because it provides a programming object model that is more similar to Access than ASP. Other, non-Microsoft options include Cold Fusion, PHP, and Java Server Pages (JSP). You can also use Macromedia's Dreamweaver MX to create Cold Fusion, PHP, and JSP web sites (as well as ASP- and ASP.NET-based sites).

When Access won't do the job

Keep in mind that Access might not be the most appropriate database to use in many Internet-based scenarios. As mentioned in the Solution in Recipe 15.2, Access works well in small workgroup settings, but if you expect a moderate number (over a dozen or so) of simultaneous users, you'd be better served with a server-based database such as SQL Server.

See Also

Problem

You'd like to export data from a table as XML and display it in an HTML table. However, you only want to display selected columns from the table, not the entire table.

Solution

If you wish to export data using only selected columns, you can do so with a query, but you can also accomplish this by using an XSLT transform. Using a transform has the added benefit of allowing you to format the data as HTML. Follow these steps to export only the Make and Model data from the Car table and to format the data as an HTML table:

1. Select the Car table in the database window, right-click and select Export, and choose XML in the Save as type drop-down list at the bottom of the dialog box.

2. Type a name for the XML file ending with an htm suffix and click the Export button. This example assumes that the output file is named Cars.htm.

1. Click the Transforms button. If the transform doesn't show up in the list, click the Add button to browse to it. This example uses a transform named 18-05.xsl. Click OK and OK again. Access will create a Car.htm and a Car.xsd file in the destination directory.

Discussion

The 18-05.xsl file used to transform the data contains two templates. The dataroot template contains code for creating an HTML document with an HTML table. The Car template creates the rows in the HTML table and cells containing only the Make and Model data. This transform works against a hidden XML document that is created from all the data in the table:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet 
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" >

<xsl:output method="html" version="4.0" indent="yes" />

<xsl:template match="dataroot">
   <html>
      <body>
        <table>
            <xsl:apply-templates select="Car" />

        </table>
      </body>
   </html>
</xsl:template>

<xsl:template match="Car">
   <tr>

      <td><xsl:value-of select="Make" /></td>
       <td><xsl:value-of select="Model" /></td>
   </tr>

</xsl:template>

</xsl:stylesheet>

When you view Car.htm in a browser, you can see that the data is displayed in an HTML table, as shown in Figure 18-16.

Choose View > Source from the menu and you'll see the following HTML:

<html>

    <body>
        <table>
            <tr>
                <td>Mini Cooper</td>
                <td>S</td>

            </tr>
            <tr>
                <td>Lexus</td>
                <td>LS430</td>
            </tr>

            <tr>
                <td>Porsche</td>
                <td>Boxter</td>
            </tr>
            <tr>

                <td>Ford</td>
                <td>Mustang</td>
            </tr>
            <tr>
                <td>Toyota</td>

                <td>Camry</td>
            </tr>
        </table>
    </body>
</html>

This example is very simple and creates just a bare-bones table. You can modify the HTML sections of the XSLT to specify colors, borders, fonts, and so on to create whatever custom formatting you need.

See Also