The DAO object model was developed to work with Access data, and (despite rumors of its death, which have been heard for many versions now, and heavy promotion of the alternative ADO object model) DAO is still the best object model for working with data in Access tables. In Access 2007 instead of removing the DAO object model, Microsoft wisely chose to trim it of some rarely used components and rename it the Microsoft Office 2007 Access database engine object model. My recommendation is to use DAO for all tasks involving Access data.

When you need to work with data in other types of databases or data sources, however, ADO is the object model you need to use (no choice there — DAO only works with Access data). ADO can be used to work with Access data as well, though it has some limitations compared to DAO, so in some cases you may want (or need) to use ADO to work with Access data; I have provided information on converting DAO code to ADO for these situations.

Despite the new and improved report interactive features discussed in the "Report Layout View" sidebar, for full control over the appearance and content of documents filled with Access data, VBA Automation code working with Word documents is the best choice. This chapter discusses producing Word documents by using a Ribbon command to create simple documents, or writing VBA Automation code to create Word documents and fill them with data, using four different methods.

In contrast to Access reports (even in the new Layout view), Word documents have extensive formatting options, including tables, form fields, and other specialized controls not available in Access reports, even in Layout view. Generating Word documents from Access VBA code lets you use all of Word's formatting options and (if desired) to create a separate document for each Access record, instead of a multi-page mail merge document. And the Word documents containing merged Access data can be edited, which is not an option even for Access 2007 reports.


Office 2007 built-in Word export

Components of the Word object model used in Automation code

Creating Word documents filled with Access data, using four different methods

Report Layout View

Access 2007 reports have a new view selection, Layout View. Access 2003 had Print Preview, Layout Preview (read-only, no data), and Design View, and you could only modify the report's layout, filtering, or sorting in Design view. In Access 2007, the new Layout view replaces Layout Preview, and there is a new Report view in addition to Print Preview. The following figure shows the View selector on the Access report Ribbon.

Access 2007 report views.

The new Layout view for Access reports has much more functionality than the old Layout Preview, letting users sort and filter from a right-click menu, as shown in the next screenshot, and even resize report columns while looking at the data (a long-requested feature).

W iptContactsFoiMerge

Contact List by Company

4-Sea»ns Software, Limited ft Productions, Inc.

Regional Manager

Paste jj| Paste Formatting ['S Group On C o mpany Na in e Total Company Name Sort A to Z 5 J Sort Z to A

Clear filter from CompanyNan Text Eilters

Equals "AAA Imports, Ltd." Does Not Equal "AAA Imports, Ltd." Contains "AAA Imports, Ltd."

Aaron Buckman

Diane Hughes Joyce An?alone Peter Bartlett Noble Smith Joseph Holloway Terri Bamett

Norman Burton Allison Cormier Elizabeth Murray Leland Foster Peter Carver Ramifl TVlrmrpy_

The new Layout view for an Access report

However, even an interactive report still falls far short of the functionality of a Word document, especially if you need to distribute documents containing Access data to people who don't have Access installed.

0 0

Post a comment