Editor's note: The authors of Access Cookbook, 2nd Edition have selected four recipes offering practical solutions to common problems Access users face. Read on and learn how to perform a mail merge from Access to Word, create a web front end to an Access table, build an object inventory, and export columns to an HTML table.
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.
Figure 4-4. The inventory-creating form once it's done its work on a sample database
TIP: This example form includes the Access system tables, which you may never have encountered. These tables are part of every Access database and are not cause for alarm. You can view them in the Database Explorer by choosing the Tools → Options menu and turning on the Show System Objects option.
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
code turns on the hourglass cursor and sets the main list
box's RowSource property to
(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,
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
function prepares the zstblInventory table to hold the current
database's inventory. The code in
CreateTable first attempts to delete
zstblInventory (using the
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
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
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.
Caching Object References
Note that the previous code sample uses an object variable,
doc, to refer to the current document. The
Because later code will refer to this particular document a number of
times, it's more efficient to set up this direct
reference than to ask Access to parse the general reference,
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
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
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'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.
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:
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.
Save the Word document in the same folder as the Access database. The sample application uses the name qryCustomers.doc.
In Word, create a template by choosing File New Template from the menu. Fill in the plain text for your main merge document.
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.
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.
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"
Figure 12-6. Set a reference to the Word library
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
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
Test the procedure by positioning your cursor anywhere in the MailMerge procedure and pressing the F5 key.
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.
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
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.
display the Word documents, set the Application
object's Visible property to
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.
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?
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.
Startup FrontPage 2003.
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.
At the Web Site Templates dialog box click on Empty Web Site and enter the following location for the Web site:
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.
Select File → Import. Click on Add File... from the Import dialog box.
Navigate to the 15-06.MDB sample database and click Open. Click OK to add the database to the site.
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.
Enter "15-06" for the database connection name and click Yes to import the database and create the database connection.
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.
Select File → New... to create a new web site. On the New task pane, click on "One page Web site..." under New Web site.
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.
Figure 15-18. The first page of the FrontPage Database Interface Wizard
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.
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.
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.
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.
Figure 15-19. On the fourth page of the Database Interface Wizard you can select which pages you wish for the wizard to create
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.
Click Finish to complete the wizard.
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.
Figure 15-20. The database editor page created by the FrontPage Database Interface Wizard
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.)
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).
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 Database Power with Microsoft FrontPage Version 2002. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfp2k2/html/fp_dbpower.asp
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.
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:
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.
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.
Figure 18-15. Selecting the output location and filename
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.
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.
Figure 18-16. The output generated by the XSL transform when viewed in a browser
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.
The following W3C page contains links to many resources on XSLT:
View catalog information for Access Cookbook, 2nd Edition
Return to Windows DevCenter.
Copyright © 2009 O'Reilly Media, Inc.