book

Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

Name: Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications
Author: Helen Feddema
ISBN: 9780470047026

by Helen Feddema

May 2007

Intermediate to advanced

720 pages

14h 14m

English

Wiley

Read now

Unlock full access

Copyright
About the Author
Credits
Acknowledgments
Introduction
Who Should Read This BookHow This Book Is OrganizedConventions and FeaturesWhat's on the Companion Web SiteMinimum RequirementsWhere to Go from Here
I. The Office Components and What They Do Best
1. Storing and Displaying Data in Access
1.1. A Brief History of Office Data Exchange1.2. Storing Data in Access1.3. Displaying Data in Access Forms and Reports1.3.1. Creating Access Form Letters1.3.2. Creating Worksheet-type Reports in Access1.3.2.1. Plain Datasheet Reports1.3.2.2. PivotTables1.3.2.3. PivotCharts1.4. Summary
2. Creating Word Documents from Access
2.1. Filling Word Documents with Access Data Using the TypeText Method2.2. Using Word Templates for Creating Formatted Word Documents2.2.1. Bookmarks2.2.2. Document Properties2.3. Form Field Documents2.4. Summary
3. Analyzing Data with Excel
3.1. Exporting Access Data to an Unformatted Worksheet3.2. Using Excel Templates to Create Formatted Worksheets Filled with Access Data3.3. Formatting Excel Worksheets in VBA Code3.4. Summary
4. Organizing and Communicating with Outlook
4.1. Exporting Appointments and Tasks to Outlook4.2. Exporting Journal Information to Outlook4.3. Creating Emails from an Access Table4.4. Summary

II. Writing VBA Code to Exchange Data between Office Components
5. Working with Access Data
5.1. Working with Older Format Databases in Access 20075.1.1. Disambiguating References to Object Model Components5.1.2. The DAO Object Model (Old and New)5.1.2.1. New Objects in the Access 2007 DAO Object Model5.1.2.1.1. ComplexType5.1.2.1.2. Field25.1.2.1.3. Recordset25.1.2.2. Hidden Objects in the Access 2007 DAO Object Model5.1.2.3. Databases5.1.2.4. Recordsets5.1.2.4.1. Table5.1.2.4.2. Dynaset5.1.2.4.3. Snapshot5.1.2.4.4. Forward-only5.1.2.4.5. Dynamic5.1.2.5. QueryDefs5.1.2.6. TableDefs and Fields5.1.3. The ADO Object Model5.1.3.1. Connection5.1.3.2. Command5.1.3.3. Recordset5.1.3.3.1. Dynamic5.1.3.3.2. Keyset5.1.3.3.3. Static5.1.3.3.4. Forward-only5.1.3.4. Record5.1.3.5. Stream5.2. Converting DAO Code to ADO Code5.3. Summary
6. Working with Word Documents and Templates
6.1. Built-in Word Export in Office 20076.2. Exporting Access Data to Word Using Automation Code6.2.1. The Word Object Model6.2.1.1. Creating a New, Blank Word Document6.2.1.2. Creating a Word Document Based on a Template6.2.1.3. Using a Query to Concatenate Data for Export6.2.2. Choosing a Method for Merging Access Data to Word6.2.2.1. Working with Word Document Properties6.2.2.1.1. Sending a Word Letter to a Single Access Contact6.2.2.1.2. Sending a Word Letter to Multiple Access Contacts6.2.2.2. Word Bookmarks6.2.2.3. The TypeText Method6.2.2.4. Word Mail Merge6.2.2.4.1. Mailing Labels6.2.2.4.2. Lists6.2.2.4.3. Documents6.3. Summary
7. Working with Excel Worksheets
7.1. Simply Exporting Access Data to Excel7.2. The Excel Object Model7.3. Minimally Formatted Worksheets7.4. Tabular Worksheets Formatted from Code7.5. Timesheets7.6. Summary
8. Working with Outlook Items
8.1. Exporting Access Data to Outlook Items8.1.1. Using the Collect Data Group8.1.2. Using the Import Group to Import or Link to Outlook Data8.1.3. Using Sample Databases and Forms8.2. The Outlook Object Model8.2.1. Explorer, Inspector, and other Outlook Objects8.2.2. Syntax for Referencing Outlook Objects8.2.3. Referencing Outlook Items in VBA Code8.3. Working with Outlook Appointments8.4. Working with Outlook Tasks8.5. Working with Outlook Contacts8.6. Summary
9. Working with Files and Folders
9.1. Working with Windows Explorer Folders9.1.1. The Office FileDialog Object9.1.2. The FileSystemObject9.2. Backing up Your Database9.3. Working with Text Files9.3.1. Writing Data to Text Files9.3.1.1. ADO9.3.1.2. FileSystemObject9.3.1.3. VB9.3.2. Reading Data from Text Files9.3.2.1. ADO9.3.2.2. FSO9.3.2.3. VB9.4. Working with Attachment Fields9.4.1. Loading Files into Attachment Fields9.4.2. Saving Attachments to Files9.5. Summary
10. Working with External Data
10.1. Working with Text Files Using the TransferText Method10.1.1. Creating an Import or Export Specification10.1.2. Importing and Exporting Text File Data in VBA Code10.1.2.1. Importing Text Data10.1.2.2. Exporting Text Data10.2. Working with Legacy Database and Spreadsheet Files10.2.1. Importing Database Files10.2.2. Importing Spreadsheet Files10.2.3. Exporting Database and Spreadsheet Files10.3. Working with XML and HTML Files10.3.1. Importing HTML and XML Files10.3.2. Exporting HTML and XML Files10.4. Emailing Exported Text Files10.5. Summary
11. Synchronizing Access and Outlook Contacts
11.1. Creating a Denormalized Table from a Set of Linked Tables11.2. Comparing Outlook and Access Contacts11.2.1. Re-creating the Flat-file Tables of Access and Outlook Data11.2.2. Copying Contact Data from Access to Outlook (or Vice Versa)11.3. Working with Attachments11.4. Summary
12. Going Beyond the Basics
12.1. Creating Fancy Word Shipping Labels12.2. Creating Excel PivotCharts from Access Queries12.3. Emailing Shipping and Reordering Reports12.4. Summary
III. Adding More Functionality to Office
13. Creating COM Add-ins with Visual Basic 6
13.1. Creating a COM Add-in Using Visual Basic 6.013.1.1. Using the COM Add-in Template13.1.2. Creating the LNC Control Renaming COM Add-in13.1.2.1. The SharedCode Module13.1.2.2. The AccessDesigner Module13.1.2.3. Creating the DLL13.1.3. Installing a COM Add-in13.1.4. Troubleshooting a COM Add-in13.2. Using a COM Add-in13.3. Comparing COM Add-ins with Access Add-ins13.4. Summary
14. Creating Access Add-ins
14.1. The Purpose of Access Add-ins14.2. Add-in Types14.3. Creating a Library Database14.3.1. Menu Add-ins14.3.2. Wizards14.3.3. Property Builders14.4. Things You Need to Know When Writing Add-ins14.4.1. Special Requirements for Add-in Code14.4.2. Tips on Add-in Construction14.5. The Extras Add-in Code14.5.1. Extras Options14.5.2. basExtras Module14.5.3. Back up Database14.5.4. Back up Back End Database14.5.5. List Query Fields14.5.6. List Table Fields14.5.7. Other Procedures14.5.8. Finalizing the Add-in14.6. Troubleshooting Add-ins14.6.1. Interpreting Add-in Error Messages14.7. Installing an Add-in14.8. Using the Extras 2007 Add-in14.8.1. Extras Options14.8.2. Back up Database14.8.3. Back up Back End Database14.8.4. List Query Fields14.8.5. List Table Fields14.9. Summary
15. Customizing the Ribbon with XML in Access Databases and Add-ins
15.1. Useful Tools for Creating and Editing XML Code15.1.1. XML Notepad 200715.1.2. VB 2005 XML Editor15.1.3. Office 2007 Custom UI Editor15.2. Customizing the Ribbon in an Access Database15.2.1. Creating the XML Code15.2.1.1. Adding a New Tab, Group, and Controls to the Ribbon15.2.1.2. Removing a Tab or Group from the Ribbon15.2.2. VBA Code15.2.3. Form Ribbons15.3. Customizing the Ribbon with an Access Add-in15.4. Summary
16. Customizing the Access Ribbon with a Visual Studio 2005 Shared Add-in
16.1. Preparing to Write a Visual Studio Add-in16.1.1. Adding .NET Support to Office16.1.2. Modifying the Connect Class Module Code16.1.3. Adding Functionality to the Shared Add-in16.1.4. Debugging the Add-in16.1.5. Building and Installing the Add-in16.1.6. Using the Add-in16.2. Summary
17. Creating Standalone Scripts with Windows Script Host
17.1. Tools for Working with Windows Script Host Scripts17.1.1. The Microsoft Script Editor17.1.2. The VBScript Help File17.2. Differences between VBA and VBScript Code17.3. Useful Scripts17.3.1. Setup Scripts17.3.2. Office Scripts17.3.3. Miscellaneous Scripts17.4. Scheduling a Backup Script with the Windows Vista Task Scheduler17.5. Summary
18. Working with SQL Server Data
18.1. Getting SQL Server 200518.2. Preparing an Access Database for Upsizing to SQL Server18.3. Configuring SQL Server 2005 for Data Access18.3.1. Getting through the Firewall18.3.1.1. Windows XP18.3.1.2. Windows Vista18.3.2. Other Security Roadblocks18.4. Using the Upsizing Wizard18.4.1. Converting Access Tables to SQL Server Tables18.4.2. Creating a Client/Server Application18.5. Linking to Data in SQL Server Tables18.6. The SQL Server Migration Assistant for Access18.7. Summary

Content preview from Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

Chapter 3. Analyzing Data with Excel

IN THIS CHAPTER

Exporting Access data to Excel using a Ribbon command
Creating new Excel worksheets from templates
Formatting Access data exported to an Excel worksheet

You can print Access data directly, using Access reports, as described in Chapter 1, or you can present the data interactively in PivotTables or PivotCharts. But, as mentioned in Chapter 1, these options have some limitations, because you can only work interactively with PivotTables and PivotCharts within an Access database. If you (or others) want to be able to manipulate and analyze Access data without the need to have Access installed, Excel worksheets are an excellent choice.

Rather than preparing Access reports, PivotTables, or PivotCharts (or in addition to them), you can export data to Excel, and allow users to analyze the data with Excel's tools. This option is available to everybody who has Office installed, because even the most basic editions of Office include Excel, whereas Access is only included in some editions (the more expensive ones).

You can export the Access data to an unformatted worksheet and let the users work with it as they want (this works fine if they just need the data and don't require fancy formatting), or you can create Excel templates and export the data to a new worksheet made from a template; this technique allows you to do some of the formatting in advance.

A third alternative is to export the Access data into either a default worksheet or a worksheet ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470047026Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Access™ 2007 VBA Bible: For Data-Centric Microsoft® Office Applications

by Helen Feddema

Chapter 3. Analyzing Data with Excel

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.