And Spreadsheet Files

Since the earliest days of Access (when dBASE and Paradox were major forces in the database world, and Lotus 1-2-3 was the leading spreadsheet application) Access could import from or export to these formats. Some people are still using these programs, or at least have old files created by them in past years, so you still might need to import data from a dBASE, Paradox, or Lotus file or (though it's much less likely) export to one of those formats. Access still supports importing from these...

Working with Older Format Databases in Access

Even if you are currently using Access 2007, you may still be working with Access 2002 2003 format databases (or even Access 2000 format databases) for a while. Access 2007 supports working with these older database formats in read write mode, and you may need to do this for example, if you are working on a database for a client running an older version of Office. So long as you don't need to use any of the new features introduced in Access 2007 (such as multi-valued lookup fields, or rich text...

A

.accda file format, 472, 473, 505, 511. See also add- ins, Access .accdb file format, 471, 473, 482, 511, 671 Access backing up databases, 277-282 calendar pop-up, 52, 53, 320 comparing add-ins with COM add-ins, 469 comparing contact data with Outlook contact data, 359-390 concatenating data for export, 143-144 CopyAccessAttsToAccess procedure, 394-395 CopyAccessContactsToOutlook procedure, 390 procedure, 390 CopyOutlookAttsToAccess procedure, CreateProjectAppts procedure, 245-247...

Access Queries

You can create great interactive charts and tables using Access's own tools (PivotCharts and PivotTables), as noted in Chapter 1. However, there is a drawback to using Access PivotCharts and PivotTables they are only interactive while working in Access. If you save a PivotChart or PivotTable as a PDF (if you have installed the Save as PDF utility) or Snapshot file, and send it to someone else, it is just an image, not an interactive chart or table. If you need to put Access data into an...

Importing Spreadsheet Files

If you have old Lotus 1-2-3 spreadsheet files, you can import data from them into Access tables using the TransferSpreadsheet method, which works much like the TransferText method, importing all the data from a worksheet. Unlike database files, you can use named argument values from the AcSpreadSheetType enum for spreadsheets of various versions these values are listed in Table 10.2. SpreadsheetType Named Constants for Lotus 1-2-3 When you inspect new job data imported from a Lotus spreadsheet...

M

Macro recorder, 202 Mail Merge method advantages and disadvantages, 146 for documents, 176-182 for lists, 176 for mailing labels, 175-176 MailMergeTextFile procedure, 177, 178-182 as method for merging Access data to Word, 146,175-182 mailing addresses code for updating in Access, 385-387 creating shipping labels in Word, 397-415 labels for Mail Merge, 175-176 synchronizing, 353-354 as user information field, 31, 32, 33 MailMerge object, 139, 140 MailMergeTextFile procedure, 177, 178-182...

Exporting Access Data to an Unformatted Worksheet

If you just need to move a chunk of data from Access to Excel, and you don't need fancy formatting, you can use the Excel command in the Export group on the External Data tab of the Ribbon to export the Access data to a plain, unformatted worksheet. The sample database, based on the Northwind sample database, has a query that links all the data tables, qryNorthwindAll. A query of this type is very useful for doing data exports, because it contains all the data you might want to export. (Figure...

Chapter Creating Standalone Scripts with Windows Script Host

Tools for Working with Windows Script Host The Microsoft Script Editor The VBScript Help Differences between VBA and VBScript Useful Setup Scripts Office Scripts Miscellaneous Scheduling a Backup Script with the Windows Vista Task Chapter 18 Working with SQL Server Data 627 Getting SQL Server 2005 Preparing an Access Database for Upsizing to SQL Server Configuring SQL Server 2005 for Data Access Getting through the Windows Windows Vista Other Security Using the Upsizing Converting Access Tables...

Formatting Excel Worksheets in VBA Code

If you need to sort, group, indent, or otherwise format exported data in an Excel worksheet, or create a total under the last row of data, you can write VBA code to use Excel commands to do the work in code. You can apply formatting to a worksheet created by the TransferSpreadsheet method, or one created from the Ribbon command, or a worksheet created programmatically from a template. -77777-pr- See Chapter 7 for examples of creating worksheets using the TransferSpreadsheet I U& SOgMyJA...

The File SystemObject

The FileSystemObject library provides another way to work with Windows folders (and files). To use the components of the object library in a database, you need to set a reference, in this case to the Scripting Runtime library, as shown in Figure 9.5. If you don't see the Microsoft Scripting Runtime selection in the References dialog, you can get this library by downloading Microsoft Windows Script 5.6 (or whatever is the current version), plus the Microsoft Windows Script 5.6 Documentation...

Adding Functionality to the Shared Addin

For the Visual Studio add-in I used components of the Access object model to create a table, form, or report programmatically, adding fields to the table and controls to the form or report. To implement this functionality, I needed to write the custom Ribbon's XML code, three button functions, and some supporting code. To create the Ribbon XML and embed it within the project, first create an XML File item by selecting Project C Add New Item, and then the XML File item in the Add New Item dialog...

Using Automation Code

Automation code is the tool you need to use when creating or working with Word documents in Access VBA. Automation code is not a special programming language, just a set of functions used in VBA code to work with the object models of other applications. All Automation code starts with one of the two functions described as follows, either of which sets a reference to a high-level Automation object. When working with Word, this is generally the Word Application object. The CreateObject function...

Working with Outlook Appointments

You may have calendar appointment data stored in an Access table, perhaps dating back to before Outlook became a part of Office. Because Outlook has a much richer interface for working with calendars than Access, I recommend exporting the Access calendar data to Outlook and working with it in Outlook calendars in the future. To export data from an Access appointments table such as the table from the sample Events database, called tblEvents to Outlook appointments, use the function listed next...

Creating a Database from a Template

O create a database from one of the new templates, first select New from the Access File menu, as shown in the following figure. 5 Basic Northwind Copy 3, 25-Nov- 7 V Database converted to 2007,accd 8 Documents AMadified Northwind. a ccdb 9 Modified Northwind - Copy p ,accdb The New item on the Access File menu. If the template you want to use is displayed in the next screen, select it directly. Assets and Inventory Office Management Sales The new Access 2007 conta you quickly track, report, ai...

Importing and Exporting Text File Data in VBA Code

The main menu of the sample application, External Data.accdb shown in Figure 10.10 , has buttons for opening various forms that import and export data in a variety of file formats. Writing VBA code to do the imports and exports gives you one-click convenience, particularly useful if you have to do an import or export task frequently, for example importing weekly Jobs data downloaded from a mainframe computer or emailed as a fixed-width or comma-delimited text file. The main menu of the External...

Sending a Word Letter to a Single Access Contact

Application Letter Envelope Format

You may have a Contacts or Customers form in an Access database, and it would be convenient to have a quick way to create a letter to the current contact, using a command button on the form. The sample Word Export database has a form for browsing contacts, frmContacts, shown in Figure 6.15. If you click the Word button in this form's header, a letter to the selected contact is created, filling Word document properties with data from that record. Opening the old Paragraph dialog box from the new...

S

Sample Code.accdb sample database, 90 sample databases Basic Northwind CS.adp sample database, 627 Basic Northwind v 1 linked SQL Server tables .accdb, 627 Basic Northwind.accdb, 627, 631, 648, 656 DAO and ADO Sample Code.accdb, 90 Excel Export.accdb, 187 Excel.accdb, 49 External Data.accdb, 312 Files and Folders.accdb, 264, 302 Folders.accdb, 264 Modified Northwind.accdb, 11 Northwind Plus.accdb, 397, 398, 403 Northwind 2007.accdb, 90 Northwind.accdb, 611, 612 Outlook Export.accdb, 230, 231...

Backing up Your Database

Everybody knows that data should be backed up frequently, and I like to make it as convenient as possible to back up a database. My standard database main menu features a Backup button, which calls the BackupDB procedure listed next. I created the Backup code and menu button in an earlier version of Access, when there was no way of backing up a database without closing it down. Since that time, Microsoft has added a backup command that doesn't require closing down the database, though it's...

The Office File Dialog Object

To allow the maximum amount of user choice, combined with convenience, I like to put one or two folder selection command buttons on a database's main menu, for selecting folders that will be used throughout the database. In the sample database for this chapter, Files and Folders.accdb, for example, the main menu has a section with two sets of controls for selecting a folder one has a command button that pops up a Folder Picker dialog for selecting the Input Documents folder used for storing...

Working with Outlook Tasks

As with appointments, if you have an Access table of tasks created many Office versions ago, I recommend exporting the task data to Outlook, so it can be maintained in the Task List in Outlook 2007, renamed the To Do List for future use. The table that I imported from the sample Tasks database tblTasks to Outlook tasks can be used as an example of how to export Access task data to Outlook. The following function does the export it can also be run from the mcrExportTasksToOutlook macro Public...

Access Addin

Because Access add-ins in previous versions of Access didn't add buttons to command bars menus or toolbars , you can't replace old code referencing CommandBars with new code referencing the Ribbon. However, you can replace a set of menu add-ins with a single menu add-in to load a custom Ribbon, and place the rest of your add-in's commands on the Ribbon. As an example, I made a version of my Extras 2007.accda add-in the sample database for Chapter 14 and modified it to load a custom Ribbon. The...

Creating Emails from an Access Table

If you have an Access table say, of customer, client, or contact information with email addresses, you can create emails to people in the table directly from an Access form, so you don't need to open Outlook to create an email, which can save time. tblContacts in the sample database has an Email field with the contacts email address, and the form frmEMail Figure 4.7 lets you send emails to contacts selected from a multi-select ListBox. A form for selecting contacts as email recipients. A form...

Creating Fancy Word Shipping Labels

In Chapter 6 you learned how to create basic mailing labels, with name and address information pulled from a table or query, using either the TypeText method or mail merge. A name and address is all you need to print a set of labels for a monthly mailing to a list of club members, or to a list of people who receive a regularly scheduled product shipment. But in the real world, often there are much more complex requirements for printing labels. Before shipping a product, you might also need to...

Tabular Worksheets Formatted from Code

Many companies store data on customer or client accounts in an Access database and need to export that data to Excel for further analysis or distribution. For example, an insurance company might need to export data on the companies it insures, including the account number, account type, policyholder, and account executive for use by their employees in the field. The ExportAccountSummary procedure listed as follows exports this data, using a different approach than the previous procedure....

Using Excel Templates to Create Formatted Worksheets Filled with Access Data

If you want to produce a more formatted worksheet, you can prepare an Excel template and format it as needed for example, adding a large, centered title and column headings with appropriate text, perhaps in a larger or bolder font than the data area. Then, instead of using the Excel command on the Ribbon, use VBA code to export the Access data row by row to the data area of a new worksheet created from the template. I created a set of queries for archiving data, again using the sample Northwind...

Creating the XML Code

Table 15.1 lists some of the most commonly used XML elements for customizing an Access 2007 Ribbon. XML Elements for Use in Customizing the Ribbon customUI The top-level element for a custom Ribbon ribbon The Ribbon definition Set the startFromScratch attribute to true to create a new, blank Ribbon. The quotes are needed, a difference from VBA code with its True and False keywords. If set to false or omitted, the customizations are applied to the standard Ribbon. Creates or references a group...

The Excel Object Model

The two export options described in the previous section are fine for creating a simple, minimally formatted or unformatted worksheet filled with data from an Access table or query, but if you need to create fully formatted worksheets, such as personnel forms, timesheets, sales reports, factory production data reports, and so forth, you will need to work with the Excel object model to create worksheets using Automation code, fill them with Access data, and then apply formatting, using...

Table Defs and Fields

TableDefs correspond to tables in the interface. Although it is much more common to need to create a query programmatically, sometimes you may need to create a table in code. When you create a new table, you also need to create fields for it. The following code creates a new table in an external database, with several fields of different data types. Each field is created and its default value set, for two of them , and is then appended to the new table. An error handler returns the user to the...

Creating an Import or Export Specification

The process of creating an import specification for importing a fixed-width text file is described next you create specifications for importing a comma-delimited text file, or exporting either of those file types, in a similar manner, with different options depending on the file type. You might want to use an import specification, for example, if you receive a text file of comma-delimited data downloaded from a mainframe every week, and you need to import the weekly data into an Access table....

Q

QryContactIDsPhones query, 368 qryContacts query, 191 qryContactsForMerge query, 144 qryNeedTimesheets query, 202 creating PivotCharts from, 22-25 creating PivotTables from, 19-21 creating plain datasheet report from, 11-19 as data source, 50, 51, 52 qryOrdersAndDetails query, 62, 63 qryThisWeeksTimesheets query, 202 queries, Access calculated fields, 370-371 complex, 352 creating Excel PivotCharts from, 415-426 creating PivotCharts from, 22-25 creating PivotTables from, 19-21 creating plain...

The SQL Server Migration Assistant for Access

There is an alternative to using the Upsizing Wizard the SQL Server Migration Assistant for Access SSMA , a free Microsoft download, at access default.mspx. Compared with the Upsizing Wizard, SSMA has a more modern look and includes a number of extra features, but as far as the basics are concerned, it should accomplish the same task converting Access tables to SQL Server tables. However, at this time the SSMA does not support the new Access 2007 .accdb database format, so if you attempt to use...

Exporting Access Data to Outlook Items

Apart from exporting whole contact, task, or appointment records to Outlook, you may need to create new Outlook items on the fly, as the data in your Access tables changes, using code running from event procedures or macros. For example, if you have a database of project-related information, you can create project task reminders in the form of email messages filled with data from an Access table, or Outlook tasks or appointments triggered by changes in data stored in the Access tables. You can...

Finding Control Names for Use in XML Code

If you want to assign a familiar Access image to a button on the Ribbon, you need to know the name of the standard Access control that uses this image, so you can set the imageMso argument for the button with that name. You can download an Excel worksheet AccessRibbonControls.xls with this information from 432 9d9e9-4d11-46a5-8 98d-23e4f331e9ae amp displaylang en on the Microsoft web site. Even though Office 2007 has been released, the latest worksheets of control names were prepared during the...

The Type Text Method

For simple documents such as mailing labels, where you just need to insert a block of text from Access, without fancy formatting, the TypeText method of the Word Selection object can be useful. If you select the Avery 5160 TypeText selection from the Select Document combo box on frmMergeToWord, you will get a Word document in the form of a table with cells of the right size to print on the label sheets, as shown in Figure 6.21. An Avery 5160 labels document filled with data from Access. An...

Office Custom UI Editor

From http openxmldeveloper.org archive 2 006 05 2 6 CustomUIeditor.aspx you can download the Custom UI Editor Tool, for use in writing XML code to customize the Office 2007 Ribbon. I installed this utility, attempted to open a saved XML file in it one that opened fine in the VB 2005 Express XML editor and XML Notepad 2007 , and got a message that the file contains corrupted data. However, I was able to copy XML code to the clipboard and paste it into the editor window. The Custom UI Editor see...

Property Builders

My LNC Rename add-in originally created in Access 97 and updated for Access 2000 is still useful in Access 2007 running on Windows XP, because Microsoft has not yet implemented automatic object and control naming according to a naming convention. This add-in lets you automatically rename database objects and form and report controls according to the Leszynski Naming Convention. It includes several menu add-ins and two property builders, which run from the Name property of a control for renaming...

Working with Attachments

Outlook has had attachments for many versions now Access 2007 introduced the Attachment data type for Access tables. In Outlook, attachments are a collection belonging to various item types, primarily mail messages Access 2007 attachments are a recordset belonging to a field of the Attachment data type. Because both an Outlook contact item and an Access table may have attachments, I needed to be able to handle copying attachments from an Outlook contact item to an Access table and vice versa....

Simply Exporting Access Data to Excel

Just as in earlier Office versions, Access offers two ways to do a quick-and-dirty export of table or query data to an Excel worksheet. You can use the Excel button in the Export group of the External Data tab of the Ribbon to export Access data without worrying about formatting, for an Office 2007 user who just wants the data. If you need to create worksheets that can be opened and edited by users running older versions of Office, or using a handheld device such as a BlackBerry, you can use...

Reading Data from Text Files

Just as you can write data to text files, you can use legacy VB statements, FileSystemObject code, or ADO code to read data from text files. The Import Data from Text File form frmTextlmport has a command button for selecting a text file to import Figure 9.13 shows the form, and Figure 9.14 shows the File Picker dialog opened by this button , and an option group with a choice of ADO, FSO FileSystemObject , and VB-type text imports the imported data is written to the large Imported Text textbox...

The Microsoft Script Editor

The Microsoft Script Editor MSE has been part of Office for several versions now, but you might not be aware of its existence. It doesn't appear in either the Microsoft Office or Microsoft Office Tools program group, nor is it one of the Open With selections on the right-click menu of a VBS file. The MSE executable is located in the following path for Office 2007 C Program Files Common Files Microsoft Shared OFFICE12 MSE7.EXE, as shown in Figure 17.1. The Microsoft Script Editor executable...

Using Word Templates for Creating Formatted Word Documents

Generate Reports Word Macro Vba

The TypeText method used in the previous section is only suitable for creating very simple Word documents. If you need to produce fully formatted Word documents, with headers, footers, and sections with different margins, or if you need to place Access data at various points within blocks of text, it's best to prepare one or more Word templates in advance, formatting them as needed. Then you can create new documents from the templates and fill them with Access data as needed, using either...

XML Notepad

One such tool is XML Notepad 2007, available as a free download from the Downloads page of the Microsoft web site, at the following link. 9 -7 87d-4118-ba5f-4f3 0fe913 62 8 amp DisplayLang en Figure 15.1 shows the TreeView tab of the XML Notepad editor, with the List Fields XML code. This view shows each attribute on a separate line of the tree in the left pane, with its value displayed in the right pane. The XML Notepad 2007 utility was updated towards the end of the beta of Office 2007...

Pivot Charts

Vba Chart Axis

Making a PivotChart is even easier select the data source query or table and click the PivotChart button in the Forms group in the Create tab of the Ribbon. Figure 1.27 shows the new, blank PivotChart with drop zones at the top and right sides of the form. As with a PivotTable, you simply drag fields from the field list to the appropriate drop zones. I dragged the OrderQuarter field to the Category field drop zone, the Supplier field to the Series drop zone, the CategoryName to the Filter drop...

Converting DAO Code to ADO Code

If you want to convert your old DAO code to new ADO code perhaps for consistency with ADO code working with other types of data, or out of concern that DAO will no longer be supported in future versions of Access you can use Table 5.7 as a guideline. Bear in mind that some types of DAO code can't be converted to ADO, because they have no equivalent in the ADO object model, so you will still need to use DAO for Access form recordsets, or creating tables and their fields pro-grammatically. You...

Modifying the Connect Class Module Code

If you were for example creating an Excel 2007 add-in using VSTO 2005 with the 2007 upgrade, you could add Ribbon support to your add-in by simply adding a Ribbon Support item to your project. Visual Studio 2005 lacks a Ribbon Support item, and VSTO doesn't support creating Access add-ins, so this step requires extensive manual modification of the Connect class module, to support working with Access and the Ribbon. In the OnConnection method, modify the rows that set the application Access and...

Mailing Labels

Setting Mailing Labels Word

Sheets of mailing labels are very suitable for mail merge you can select three types of Avery mailing labels Mail Merge type from the Select Document combo box on frmMergeToWord. If you select the Avery 5160 Labels Mail Merge document, you will get a Word document like the one shown in Figure 6.23. The Avery labels documents look and work exactly the same whether produced by mail merge or the TypeText method, so I prefer to use the TypeText method, because it is much simpler to set up a plain...