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 1. Storing and Displaying Data in Access

IN THIS CHAPTER

A brief history of Office data exchange
Storing data in Access
Displaying Access data in forms and reports
Working with rich text in Memo fields
New report interactivity
Using Access as a control center for working with Office documents

Since its earliest days—about 14 years ago—Access has been a relational database program, storing data in tables and using its own queries, forms, and reports to sort, filter, display, and print data. With successive Office versions, moving data among Office components (especially Word, Excel, and Outlook) has become so much easier that it is now often more efficient to use another Office component rather than an Access report for a task such as printing letters or analyzing numeric data.

Additionally, using other Office components to display or print data from Access makes the data stored in Access tables more widely accessible. Many Office users have an edition of Office that doesn't include Access—but they all have Word and Excel, and many also have Outlook, so they can easily work with Word documents, Outlook messages or appointments, and Excel worksheets, filled with data from Access tables.

Whether you plan to present your data as an Access report, PivotChart, or PivotTable; or a Word document or Excel worksheet, the data is stored in Access tables, and entered and edited in Access forms.

A Brief History of Office Data Exchange

As the Windows operating system has progressed from Windows ...

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 1. Storing and Displaying Data in Access

A Brief History of Office Data Exchange

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.