E

283-290 creating from Access tables, 81-85 sample output, 290 Select Contacts for Email sample form, 282-283 sending Access Products to Reorder report as email attachment, 432 sending Access shipping report as email attachment, 428, 430 sending text files exported from Access, 348-350 synchronizing addresses, 353-354 embedded macros, 232, 233 enabled XML element, 522 EOF property, 110 error handling, VBA versus VBScript, 601 error messages, add-in, 506-507 Event List form, 230-231 event...

Configuring SQL Server for Data Access

In Access 2003, you didn't need to do any special SQL Server setup before upsizing an Access database to SQL Server using the Microsoft Database Engine (MSDE), the predecessor to SQL Server 2005 Express. But the situation in Office 2007 is very different. In addition to preparing your Access database, you also have a considerable number of setup chores to do to make SQL ou can download a set of books on SQL Server 2005 from www.microsoft.com technet prodtechnol sql 2 0 05 downloads books.mspx....

Storing Data in Access

Access was designed from the start to store data, so (if you have a choice which is not always the case) it is the place where you should store your data. You may need to use that data to produce Word letters, SharePoint lists, Excel worksheets, or Outlook mail messages, but the data itself should be kept in Access tables, unless there is a very strong reason to store it elsewhere. One valid exception is storing data in SQL Server back-end databases, using Access as the front end. SQL Server is...

P rj f rj ing See Chapter for information on creating Word documents of various types filled with SMfiHiSi Access data

Bldg. 939 Please keep one copy and return one signed copy frith signed contract In order to ensure an efficient, neat and quality abatement, we ask that vou review the following Please be sure that the work area and access to the wort area is cleared of all movable objects. Please make certain that there is adequate parking for our vehicles, as close as possible to the entrance to the work area. Driveways and outside access ways leading to the work area must be...

Getting SQL Server

If you have installed Visual Studio 2005, you should already have SQL Server 2005, as it is installed by default as part of the Visual Studio installation. To check this, open the Control Panel and select the Programs and Features applet note that the Installed On date is the same for Microsoft SQL Server 2005 and Microsoft Visual Studio 2005 Professional Edition (see Figure 18.1). Microsoft SQL Server 2005 listed in Programs and Features. Microsoft SQL Server 2005 listed in Programs and...

Linking an Access front end to data in a SQL Server database

Windows security settings just to be able to connect to SQL Server before you even attempt to connect Access to SQL Server or upsize an Access database. This chapter helps you navigate through the SQL Server and Windows settings you need to do several SQL Server-related tasks Convert an Access database to SQL Server for use as a back end (this is known as upsiz-ing the database) Create a client server application with a SQL Server back end and an Access project front end Link an Access database...

The VBScript Help File

As mentioned in Chapter 9, Microsoft has provided a Help file for VBScript that is a model of good design and usefulness (unlike the Help files for Office 2007) to get this Help file, download the Microsoft Windows Script 5.6 Documentation file from the Microsoft Web site at http www 6-1C4 0 9 9D7BBB9& displaylang en. The Help file (script56.chm) is a compiled HTML Help file, which can be opened in both Windows XP and Windows Vista. Unlike the MSE's own Help file, the script56.chm file is...

Using the COM Addin Template

To make the COM add-in template available as one of the selections when creating a new VB project, copy the COM Add-in template files to the Projects folder under the VB Templates folder (usually C Program Files Microsoft Visual Studio VB98 Template Projects), as shown in Figure 13.1. When you next open VB, you will see a COM Add-In selection as one of the available project template choices, as shown in Figure 13.2. Once you've selected the COM Add-In template and have clicked OK, a new project...

Debugging the Addin

The Error List pane (mentioned in the previous section) helps you to locate any errors in your code you can double-click an item to go to the line of code that caused the error. When working with an add-in, you can use the Start Debugging selection on the Debug menu to step through the code but first you have to select Access as the application to start when debugging. To do this, select CreateObjects Properties from the bottom of the Project menu (if you are working with a different project,...

Filling Word Documents with Access Data Using the Type Text Method

You can create a blank Word document (based on the default Word template) with two lines of code Set appWord GetObject(, Word.Application) Set doc appWord.Documents.Add Working with Word document properties Working with form fields Working with tables lr T r '- ' v 'n most of my Automation code working with other Office applications (Word, Excel, and Outlook), I use the GetObject function in the body of a procedure, to set a reference to the running instance of the application, if there is one...

Importing Database Files

The form for importing data from legacy application files is similar to the form for importing from text files it differs in offering a selection of three legacy application types dBASE, Paradox, and Lotus. The following steps describe importing Jobs data from a dBASE file 1. To open the Import Job Data from Application File (frmlmportAppData) form, select the Import Data from App Files option on the main menu and click the button to its left (see Figure 10.10). The Job Data from Application...

Installing a COM Addin

If you copy the DLL file created by a COM add-in to the default Add-ins folder (usually C Documents and SettingsWser Name Application Data Microsoft AddIns), its button(s) should automatically appear in the Toolbar Commands group of the Add-Ins tab of the Ribbon (as shown in Figure 13.8) at least if you are running Windows XP A COM add-in button on the Add-Ins tab of the Ribbon in Access 2007. A COM add-in button on the Add-Ins tab of the Ribbon in Access 2007. If you don't see your COM...

See the SQL Server Bible Wiley for more information about working with SQL Server features such as stored procedures

Figure 18.45 shows the four queries in the original Basic Northwind.mdb database that were successfully upgraded to SQL Server. The first two queries were converted to views and the third and fourth to user-defined functions, as indicated by their distinctive icons. Figure 18.46 shows the qryOrderSubtotals view in design view, and Figure 18.47 shows the qryCurrentProductList user-defined function in SQL view. You now have a client server application consisting of an Access project front end and...

Summary

This chapter covered three methods that you can use to write Access data to Word documents (TypeText, bookmarks, and doc properties). With the aid of these techniques, and a set of preformatted Word templates, you can make use of the superior data storage and editing capabilities of Access and the superior document formatting properties of Word. You can print Access data directly, using Access reports, as described in Chapter 1, or you can present the data interactively in PivotTables or...

Documents

You can use mail merge to create a merge document, where each page displays data from one contact record. I prefer to use the document properties or bookmarks method, in order to have a separate document for each contact, but if you have many hundreds (or thousands) of documents to generate, this is not practical. The Contact Letters (Mail Merge) selection in the Select Document combo box creates a mail merge document with a letter on each page Figure 6.25 shows one page of this merge document....

Menu Addins

A menu add-in needs three rows in the USysReglnfo table, each with an appropriate value in the Type field, and some with values in the ValName or Value field as well, as described in detail next In the first row of the add-in, the Subkey field (which is the same for all the add-in's rows) has the Registry key information, referencing the Menu Add-Ins section under the HKEY_CURRENT_ACCESS_PROFILE key (which references the currently running version of Access), and ending with the command name to...

Adding More Functionality to Office

Creating COM Add-ins with Visual Basic 6 Customizing the Ribbon with XML in Access Databases and Add-ins Customizing the Access Ribbon with a Visual Studio 2005 Shared Add-in Creating Standalone Scripts with Windows Script Host Office 2000 introduced COM add-ins as a new development tool, an alternative to creating VBA add-ins for Access, Excel, Outlook, and Word. A COM add-in is created as a Dynamic Link Library (DLL) that is registered to work with Office applications. COM add-ins (at least...

Sending a Word Letter to Multiple Access Contacts

When you need to select a group of recipients for a Word letter, set of labels, or another document, you need a different interface. The form frmMergeToWord has a combo box for selecting a Word template, and a multi-select ListBox for selecting one or more contacts as recipients (see Figure 6.17). A form for selecting a document and recipients for creating Word documents filled with Access data from multiple contact records. The Select Document combo box list shows the merge type in the second...

Using the Addin

To test the add-in, open any Access 2007 database (or create a new database). As in Figure 16.32, you should see a new Visual Studio Add-in tab on the Ribbon with a group called Create Access Objects containing three buttons. There is no need to manually add the Ribbon, or even to close and reopen the database the Ribbon appears immediately (a refreshing change from the struggle you have to go through in order to get a custom Ribbon to appear from an Access add-in). A new tab and group created...

Minimally Formatted Worksheets

If you need to create a simple tabular worksheet listing the contacts in qryContacts, with minimal formatting, you can create a new workbook in VBA code, from a saved workbook template with a title, correctly sized columns, and the font and other layout of your choice, and fill it with Access data. The ExportContactsToExcel procedure creates a recordset based on qryContacts, and exports selected fields from each record in that query to a workbook created from a template, with a title, column...

Building and Installing the Addin

After finding and correcting any errors in the add-in's code, you are ready to build the add-in to do this, click the Save All button, and then select Build from the context menu of the Create ObjectsSetup item in the Solution Explorer (the name is your add-in's name plus Setup), as shown in Figure 16.25. When Build succeeded appears in the lower-left corner of the status bar, you can then install the solution by selecting Install from the same context menu. If the Install selection is disabled...

List Query Fields

This command fills a table with the names of select queries (omitting those whose prefixes are on the Exclude list), and their field names, and offers to print a report based on that table. The table is shown in Figure 14.23. Only select queries will be listed. Although queries of other types (action queries) are listed by name in the QueryDefs collection, they have no fields to list. A table filled with query names and fields. A table filled with query names and fields. Note the Expr1008 field...

Linking to Data in SQL Server Tables

You may need to connect to SQL Server databases for a company, university, or other organization that stores its data in SQL Server. If you need to link an Access database to data in existing SQL Server tables, or create a new Access front end for SQL Server tables, the process is different. sample databases you can download from the SQL Server 2005 Samples and Sample Databases (February 2007) page on the Microsoft web site, at this link www.microsoft.com DisplayLang en filelist (or possibly a...

Emailing Shipping and Reordering Reports

After you ship out orders, using the shipping labels described in an earlier section, you might need to produce reports detailing the amounts of different products that were shipped and the amounts that need to be ordered to replenish stock, and then email these reports to various persons. The Shipping Reports and Reordering form, shown in Figure 12.25, lets you see the inventory for all products where the amount in stock plus the amount on order is at or under the reorder level for that...

Displaying Data in Access Forms and Reports

Sometimes you don't need to go outside of Access to present your data if you are designing an Access application, displaying data in forms and printing it in reports may be all you need. t *r j r Over the years, Access forms and reports have been significantly upgraded. In Access 2007, one long-requested feature has finally arrived in a workable form (I recall an early and unreliable implementation that made a brief appearance in Access 95). Memo fields can now store and display rich text,...

Creating the LNC Control Renaming COM Addin

The procedures that do the renaming of form and report controls in the LNC Control Renaming COM add-in are basically the same as the LNC Rename Access add-in, which was covered in detail in my earlier book Expert One-on-One Microsoft Access Application Development this chapter concentrates on the differences needed to make the code work in a COM add-in. For more details on the LNC Rename add-in, see my book Expert One-on-One Microsoft Access Application Development (ISBN 0764559044). Starting...

C

Calculated fields, in queries, 370-371 calendar, Outlook importing appointments from Access, 72-78 linking folder to Access tables, 224 working with appointment data, 241-247 calendar pop-up, 52, 53, 320 Callback function, 263, 264 certificates, digital creating for signing VBA code, 398-402 why not to create for Access add-ins, 511 Chart templates, 423 charts. See PivotCharts, Access PivotCharts, Excel circular references, 11 Class property, 240 client server applications, 657-662 Clipboard, 3...

Adding NET Support to Office

Since your installation of Office 2007 may not have .NET support enabled, you need to check that this feature has been selected it is required in order to create Shared add-ins. To check whether.NET support is enabled, you need to run Office install. In Windows Vista, first select Programs in the Control Panel, then Programs and Features, then select the Microsoft Office item, as shown in Figure 16.1. If you are running Windows XP, start by selecting the Add or Remove Programs applet in the...

Timesheets

Almost any type of business (other than a one-person operation) needs a form for recording employees' work hours and a way to print or electronically distribute the timesheet data. Often a company has used a paper form to record work hours for many years, and the electronic form needs to replicate the paper form. In some cases, there are specific government or industry standard formats that must be used, or the data must be produced in a format that can be imported by a mainframe computer. You...

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...

Tools for Working with Windows Script Host Scripts

Though you can create and edit WSH scripts with Notepad, it is a lot easier to work with them in the Microsoft Script Editor MSE , using the VBScript downloadable Help file for reference. Curiously, neither the MSE nor the VBScript Help file appears as part of the interface when you work with a WSH script you have to locate and possibly download these files and set them up manually to provide a more functional working environment. The next sections tell you how to obtain and use these tools for...

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...

Ing with Access

In older versions of Microsoft Office, there were two choices for working with data stored in Access tables. One was the Data Access Objects DAO object model, which was developed specifically to work with Access data in recordsets including form recordsets and to work with table structure using the Tables and subsidiary Fields collections. Because of these customized features, DAO was the best object model for working with Access data. The other choice for working with Access data was and is...

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...

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...

Working with Attachment Fields

, f y. , .jw-f rir ' The Attachment data type discussed in this section is new to Access 2007. Previous versions of Access had an OLE Object field data type, which only supported certain types of objects and was quite cumbersome to use not to mention causing terrible database bloat . By contrast, in Access 2007, it is quite easy to store files of any type in a field of the new Attachment data type, and the attachments are automatically compressed to save database space. The Contacts form,...

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...

Recordset

ADO recordsets represent sets of records in a database, much like DAO recordsets, though their attributes are more generic. An ADO recordset can be based on a table, query, SQL statement, or Command object. The TestForwardReadOnly procedure listed here uses a saved select query as the recordset source Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Create a connection to the current database. Set cnn CurrentProject.Connection Set rst New ADODB.Recordset Create a recordset based on a...

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....

Syntax for Referencing Outlook Objects

The Items collection for a Folder represents all the separate Outlook items in that folder, which may be of different types. There is no such thing as a singular Item object in Outlook a pitfall that has caught many beginning Outlook programmers. Thus when you need to work with items in a folder, you also need to declare a variable as Object. This is because a variable of the Object type may represent items of different types each item can be inspected, and if it is of the appropriate type,...

Working with Word Document Properties

In previous versions of Word, document properties were accessed in a straightforward manner, through the Properties dialog, opened from the File menu. The process is now more complicated in Word 2007 you click the Office button, select Prepare, and then Properties see Figure 6.9 . Word templates and documents in Word 2002 2003 and Word 2007 formats. Computet gt Data ID I Templates gt Contact Templates Contact Letters fl Documents Ijj Contact Templates 3 Office Book Office Book Copy . Vista Bug...

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...

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...

Ribbon checkbox on the Personalize page of the Word Options dialog Figure

To add the choices for a drop-down or combo box list, switch to Design mode by clicking the Design button in the Controls group, select the control, and then select Properties from the Controls group on the Developer tab, as depicted in Figure 2.15. Turning on the Developer tab on the Word Ribbon. I1 Change the most popular options in Word. ScreenTip Scheme Show enhanced ScreenTips j j E l Show Developer tab in the Ribbon 0 WfiJpen e-mail attachments in Full Screen Reading view Color Scheme...

The Outlook Object Model

Because of the limited options for exporting Access data to Outlook, I prefer to use VBA Automation code to export Access data to Outlook objects. To export Access data to Outlook, you need to understand the Outlook object model. The Outlook 2007 object model has a number of new components they are listed in the MSDN article What's New for Developers in Microsoft Office Outlook 2007 Part 1 of 2 , which you can download from the following link ms772422.aspx officeoutlook2 The Outlook object...

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...

Form Field Documents

Sometimes you need to create documents that display some data from Access and also allow users to enter more data in a controlled fashion. This can be done using either bookmarks or doc properties to display the Access data, and Word content controls called form fields in earlier versions of Word for the user-entered data. The third button on frmContacts creates a letter filled with data from Access, using doc properties additionally, the template has two form fields, to be filled in by the...

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...

Patrick Schmid on Access Ribbon Customizability

He best discussion of Access 2007 Ribbon customizability or the lack thereof is from the October 18, 2006 entry on MVP Patrick Schmid's blog If you ask me about the customizability of the new Ribbon UI in Office 2007, my answer would be too little, too difficult. Compared to previous Office versions, especially Office 2003, 2007 simply has a serious customization deficiency. In fact, most users will probably conclude that the Ribbon cannot be customized at all. In contrast, Office 2003 is the...

Using Word Templates for Creating Formatted Word Documents

Vba Custom Document Property Boxes

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...

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...

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...