..:: Access-Freak :: Getting started with Microsoft(R) Access 2007 (Step by Step Tutorials/Samples...)

The main intent of these tutorials is to guide frequent users of Microsoft® Access through the transition of earlier versions to the new 2007 edition. The monumental changes made to the Office interface are as ground breaking as the switch from Access 1.0/2.0 to Access 95/97. There are many new key features available within Access 2007,
but one of the most obvious and most challenging is the new ribbon and navigation pane user interface. These new elements of the development environment are very powerful tools and can enrich your custom database applications in many ways. However, the learning curve to adapt to the new surroundings might seem intimidating to users who are used to the earlier database window and menu/command bars. With the help of a direct, one to one comparison between Microsoft® Access 2003 and Microsoft® Access 2007 it should be possible to adapt this powerful new edition for future development. Throughout the different tutorials we will cover topics from program specifications through features added, dropped, or unchanged as well as demonstrating implementations of subjects discussed. Depending on personal experience with importance of the subject matter some elements will be covered in more detail then others. Furthermore, we will evaluate several known developer practices in consideration of Microsoft® Access 2007.

For your convenience you can download each tutorial in PDF format zipped so you can follow along at home or on the road without being connected to the internet.

Make sure to keep checking this site as I find more time to expand the list of tutorials.

Throughout this tutorial, we will introduce some of the most important changes made to Microsoft® Access 2007. This will allow you to get comfortable with several of the new features, which we will cover in more detail later on. Furthermore, you will learn about the new product release line Microsoft® has implemented for the Office 2007 edition.

Microsoft® Office editions

Similar to earlier Office versions, Microsoft® implemented several different methods of distribution for the new Office 2007 release. According to Microsoft® there are seven editions publicly available excluding the Basic edition, which is only accessible for OEM (Original Equipment Manufacturer) allocations. As an Access developer, we will concentrate on only four of the initial seven editions. Comparable to past Office releases the Small Business, Standard, and Home and Student editions do not ship with Access. One new addition to the suites line up is the Ultimate edition. Following the example of Windows Vista, Microsoft® is now offering a complete Office edition retail package to the general public. The Enterprise and Professional Plus editions are only available to large businesses by purchasing a volume license, but it is now possible,
if desired, to purchase the very similar Ultimate edition. From Microsoft’s® standpoint this new version is supposed to combine the best of both worlds by implementing popular features from the Business Suite as well as the Home Edition. Furthermore, the more moderately priced Professional edition is available for retail as usual. A comparison of the four releases is shown in Table 1.1, which will help you identify the difference of features in Office editions.

	Professional 2007	Professional Plus 2007	Enterprise 2007	Ultimate 2007
Access	x	x	x	x
Excel	x	x	x	x
Outlook	x	x	x	x
Outlook with Business Contact Manager	x
PowerPoint	x	x	x	x
Word	x	x	x	x
InfoPath		x	x	x
Publisher	x	x	x	x
OneNote			x	x
Groove			x	x
Communicator		x	x	x
Enterprise Content Management		x	x	x
Electronic Forms		x	x	x
Windows Rights Management Services capabilities		x	x	x

You can see that the programs incorporated in the Professional edition did not change from previous releases. However, there are several additions to the other suites including the integration of OneNote, Groove, and Communicator.

As usual, it is not required to purchase a complete Office Professional or a higher suite. Access can be purchased separately as a standalone program or as an upgrade to your existing Access software.

It has been made public that the 2007 Access Run-Time as well as the Developer Extensions (ADE) are being made available free of charge. The Access team has finished all the work on these components and you can download them from the Microsoft® download page:

Installing the ADE add-in will enable further options of the Office button

pull down menu (see Picture 1.0).

The majority of changes in the new Access edition appear to gear towards an enhanced user experience with the software itself. There have been ample amounts of new features and improvements of old behaviors, but the most obvious modifications seem related to the user interface. This is no surprise when you think about the difficulties new users have with older versions of Access. Making the software more approachable yet more powerful appears to be a step in the right direction for Microsoft® and the future of Access. It is arguable that some new features might seem unattractive or even useless to current developers, but we have to keep in mind that not everyone approaching the new edition has been using past generations of Access versions or other database development tools. Access 2007 does give the impression of the most user friendly adaptation yet.

Like all other 2007 Office programs, Access has a completely renovated user interface. Visually, the most outstanding feature and perhaps the most powerful addition is the new Ribbon (see Picture 1.1).

Instead of hiding commands behind several levels of menu buttons the Ribbon directly exposes all available tools and functionalities to the user. Commands are grouped within several tabs. Another striking feature of the Ribbon is the implementation of Galleries. A Gallery displays several different options typically related to formatting choices. This eliminates the need for various dialog boxes, which obstruct the view and work flow when dealing with properties of controls, forms or reports. Additionally, these Galleries support an efficient preview capability which should eliminate the constant use of the undo button.

This approach to the development environment is very inviting to new users of Access. However, seasoned developers who have been working with Access for several years and are used to the old menu might find the Ribbon challenging. In all probability, it is necessary to completely forget old habits and relearn the location of specific tools and functionalities. Later on, we will look at the Ribbon in much more detail and dissect it systematically to raise the familiarity with the new user interface.

Another new implementation in the overhauled Access 2007 development environment is the Navigation Pane. Rather than using a separate database window to group all database objects like in previous versions the Navigation Pane is now an integrated part of the user interface. This has the advantage of never losing sight of all database objects while working on them (see Picture 1.2).

Database objects will not open on top of the navigation pane and obstruct it, but rather appear in a tabbed format right beside it. Of course, this behavior can be altered if the developer prefers overlapping windows. We will look at further options and implementations more closely later on.

Microsoft® Access continues its powerful legacy of being one of the easiest development tools for creation of full featured front end solutions. This allows the developer to effortlessly create user interfaces through forms and reports, which directly connect to the underlying data. In continuation to keep this high standard, Access will now support a so-called WYSIWYG (What You See Is What You Get) approach to form and report design. This eliminates the need to switch between design and form view while trying to implement your solution. It is now possible to design your forms and reports while being connected to live data to immediately see the affects of your alterations and ensure the results you want to achieve.

Based on this concept a further addition to the new interface in shape of a new type of form view was implemented. Access 2007 introduces the split form. This allows the developer to create a very user friendly form which combines the power of a datasheet with a regular single form for data entry or detailed record view (see Picture 1.3).

Picture 1.3: Split form view showing datasheet and single form at the same time

The datasheet can be applied at the top, bottom, right, or left in relation to the single form.

Other new visually appealing additions to forms or reports include natively supported features like alternating row colors, vertical gridlines or transparent control buttons including text.

As mentioned earlier in this tutorial some of the new integrations might not be very attractive to developers who are knowledgeable in database design procedure and best practices. However, Microsoft® wants to make its software as approachable as possible and allow everyone to benefit from it. Even someone who has never heard of relational databases can now create powerful and fully functional applications. Part of the result of this achievement is the inclusion of many different template applications. Access 2007 is bundled with several completely finished tracking applications. They are ready to use with preset tables, relationships, forms etc. It is not necessary to modify the templates if they meet the developer’s needs, but they can be adjusted as needed. The majority of templates are available online through the Microsoft® Office website; nevertheless, there are numerous samples included with Access itself (see Picture 1.4).

In combination with start up support through templates, it is now also much easier to create new tables, fields, and relationships. Based on improvements of the datasheet, Access is capable of easily creating new fields and automatically assigning correct data types based on values entered. The datasheet includes a new Add New Field column which is a placeholder for additional fields. As an example, the developer can simply enter a new text value and this will create a new field of data type text.

Similarly, the creation of relationships between tables has been simplified. It is only a matter of dragging and dropping a field from a list into a table and Access will guide the developer through the rest of the process.

Seasoned developers will most likely not take advantage of these features and prefer to create applications through conventional methods like the table design view and the relationship window. However, an additional improvement to the datasheet should be of enormous importance to developers. The datasheet now supports a totals row which can perform similar task like a totals query (sum, count, average…) (see Picture 1.5).

There have been several other great improvements to the table level of Access. One of them is the introduction of two new field data types. One of these two data types is a multi-value field. Eliminating long workarounds of the implementation of many-to-many relationships this data type easily allows the creation and usage of a multi value list, which is somewhat expected from its given name. Of course, it is not necessary to implement this new data type, but it could make development much easier. The other new data type is capable of storing any external file as an attachment within Access itself. Without the side effect of database bloat, it is now possible to attach one or many file types to a single table record. If the files are not already compressed Access will automatically do so for optimal file size management.

A further performance improvement in regards to database bloat has finally been implemented. Earlier versions of Access were not very capable of handling specific image files. Some of the formats Access 2007 supports are jpeg, gif and png. These will not only be displayed correctly (e.g. transparency), but additionally they will also not cause any negative bloat on the database file. This supports the developer in creating much neater, modern looking custom applications.

Additional support for the developer comes in the form of a new native rich text control for memo fields and a date picker control. This eliminates the need to include third party ActiveX controls and ensures fully functional and easy implementation of applications on different computer systems. The new calendar control is automatically added to controls which are bound to date/time data type fields (see Picture 1.6).

The rich text support is applied to memo fields and can be adjusted through the new TextFormat display property. This property enables the developer to easily switch between plain text or rich text formats. The rich text format is based on HTML rather then RTF commands, which supports the communication between Access and SharePoint.

Besides excellent scalability with SharePoint through easy data mapping, workflow support and offline SharePoint list support, Access 2007 integrates another new powerful feature, which has been long overdue. It is much easier for developers to implement functionalities to enable data collecting from outside users, who do not have direct access to the application. Sending InfoPath or HTML embedded forms in emails easily facilitates the retrieval and integration of outside data, which used to be a longwinded and painful scenario before Access 2007.

A further major integration in Access 2007 is the fully implemented support of PDF (Portable Document Format) and Microsoft’s® XPS (XML Paper Specification) formats. The support of these two electronic paper output formats gives the developer a choice to effortlessly share formatted data with users or clients, who do not have direct access to the Access application itself. Additionally, it eliminates the need to buy third party PDF driver licenses and use lengthy workarounds to export the data. Both the OutputTo and SendObject methods support automation variants of this new export feature. However, before being able to use either of these formats within Access it is necessary to download and install an add-in (SaveAsPDFandXPS.exe) or two seperate ones (SaveAsPDF.exe and SaveAsXPS.exe), which can all be retrieved free from the Microsoft® Download Center website (see Picture 1.7a and Picture 1.7b).

We will look at the process of activation and implementation of these features in more detail later on.

A major change regarding Access itself has been made to the security model. User Level Security (ULS) will no longer be supported for the new ACCDB file format. For some developers this will be a shock; however, many of you might not be surprised by this change. For numerous reasons it has been a common practice to implement custom security solutions rather then relying on User Level Security. This mindset is now more welcome then ever. The new Access Ribbon and Navigation Pane invite the developer for easy implementation of new custom security models. For backwards capabilities, ULS will still be supported for old file formats.

Additional updates to the Access security include new startup evaluations of secure applications, safe Macros which function even in a code disabled and secure environment and improved data encryption.

Besides excluding User Level Security support for new 2007 files, the ACCDB file format also eliminates the functionality of designing Data Access Pages (DAP) and utilizing database replication. It is still possible to use Data Access Pages; however, it requires you to use an earlier version of Access to create or modify them. The tremendous SharePoint support of Access 2007 or the use of Active Server Page technology (ASP) might be good alternatives for developers who seek internet capable solutions.

These are the major changes made in the new Access version. There are a few other additions and modifications (e.g. improved mouse wheel behavior or control anchoring), but they are not as earth shattering as the earlier mentioned features. Most of the minor alterations will be addressed on an as needed basis throughout the rest of the tutorials. Furthermore, we will look at the majority of the functionalities in more detail and step through their possible implementation later on, too.

The majority of this tutorial will deal with a direct comparison of Microsoft® Access 2003 and Microsoft® Access 2007. We will look at the similarities and differences in the software’s limitations and look at a detailed comparison of Access 2003 menu options and the Access 2007 user interface. While doing so we will start to get more familiar with the new and challenging development environment, which will also provide a foundation for the next tutorial.

Access 2007 introduces a new file format named ACCDB. This file format enables the developer to use all the new features of Access 2007. Most of them were mentioned in tutorial one, but to refresh your memory, some of these are new data types, SharePoint support, or linking to other ACCDB databases. It will not be possible to utilize these features with the MDB file format; however, you can still use that format within Access 2007. The file migration process is fairly straight forward, but you should keep in mind that this will eliminate your ability to open the application with prior Access versions. Other things to consider are the dropped support of User-Level-Security (ULS) and Data Access Pages (DAP). The Convert command can be found under the main pull-down menu displayed when clicking the round Office button

next to the Quick Access toolbar (see Picture 2.1).

Before being able to utilize MDB files created in versions prior to Access 2000 it is necessary to either enable or convert these to a 2000 or later file format. The process of enabling 97 file formats or prior allows you to do data changes in Access 2007, but it is not possible to modify the design of any objects. In cases of mixed environments, where conversion of the file to a higher format is not an option, design modifications would need to be done in the older Access editions. It is not possible to up-convert MDE files so it is crucial to have a backup copy of the original MDB file in its old format.

Visual Basic for Applications (VBA) references in Access 2007 are handled similarly to the earlier versions. Design changes are required for the references to automatically update to the new libraries. As usual these new references will not be backward capable to older Access versions if the file happens to be opened in earlier Access editions.

The Save As option is still available and can be found similarly under the main Office button

pull-down menu (see Picture 2.1). However, this functionality will be disabled if the created ACCDB file includes prior unsupported, complex data like any of the new file types.

The familiarization with a new area of Office 2007 called the Trust Center might help you with the migration process within Access. The Trust Center lets you easily manage Trusted Publishers, Trusted Locations, Add-ins, ActiveX Settings, Macro Settings, Document Action Bar Settings, and Privacy Options. To access the Trust Center you can use the Access Options button of the Office button

pull-down menu (see Picture 2.1).

When opening an earlier created application with Access 2007 you will not receive many of the security prompts you might or might not have gotten used to in Access 2003. To ensure that your application functions as expected in the new environment you will need to enable it. Within the Trust Center it is possible to specify certain directory folders as secure sources of applications (see Picture 2.2).

At startup of files Access 2007 evaluates them for legitimate digital signatures and their location and either enables or disables the application. A disabled file is still opened, but will not allow any code or action queries execution. At any point while observing the opened and disabled project the user can enable it with a click of a button on a security warning Document Action Bar below the Ribbon (see Picture 2.3).

Accessing the security Options… from the Document Action Bar will reveal a security alert dialog window. Within this dialog you can completely enable the application by choosing Enable this content and pressing OK (see Picture 2.4). Keep in mind that you should never enable unfamiliar applications if you cannot guarantee that no malicious code will be executed.

If the file was opened from within a trusted folder location specified in the Trust Center or includes a valid signed VBA project it will be completely enabled without any user notifications.

Most of the specifications of Access 2003 are still current in the new 2007 version. The introduction of the new ACCDB file format did not influence the general limitations. The file size limit for ACCDB and MDB files is still 2 Giga Byte minus the space needed for internal system objects. The number of total concurrent users is still set at 255, which really does not represent the true concurrent user limit. Depending on several unpredictable factors like the design of the application or network characteristics the limit is much lower then mentioned above. From personal experience Access can run into problems with only twenty to thirty concurrent users. For a complete comparison of the limitations of Microsoft® Access 2003 and Microsoft® Access 2007 see Table 2.1.

You will recognize that there has been little to no modifications in the limitations from Access 2003 to Access 2007. The introduction of the new ACCDB file format deals primarily with the functionality of complex data created by the new data types. This is the reason ACCDB files which include these data types are not backward capable to the MDB file format. The two Giga Byte file size limit might disappoint developers who looked forward to a much larger limit. If necessary a good alternative to an Access backend could be the SQL Server Express edition. It is completely free to the general public and can handle a file size twice as much as Access.

We have already briefly talked about the obvious and major user interface changes of Microsoft® Access 2007 in previous segments of these tutorials. Let us move forward and get more familiar with the new Ribbon and Navigation Pane. With the help of a direct comparison of menu options of Access 2003 and Access 2007 we will build a blueprint for an easier transition between the two versions and establish a basis for the beginning usages of all functionalities of the software.

• File Menu Option (2003):

The majority of options of the File Menu item in Access 2003 can now be found under the large, round, and bright orange Office button

next to the Quick Access toolbar (see Picture 2.1). You will recognize the options to create a new database application (New…), open an existing one (Open…), save the current application (Save…), print the current object in focus (Print…), and close the presently opened database (Close Database). The Send To option of the 2003 edition has been renamed Email…in Access 2007.

Additionally, you will recognize two further options in the new 2007 main Office pull-down menu. We have already discussed the Save As…command in the beginning of this tutorial. In Access 2003 this functionality was meant purely for saving individual database objects. In Access 2007 this menu option also incorporates the Convert Database command which used to be under the submenu Database Utilities of the Tools menu option in earlier Access versions. The move of this command to the main Office menu seems much more logical. Save As…also includes the new functionality to save an object in PDF (Portable Document Format) or XPS (XML Paper Specification) format (see Picture 2.1). However, as earlier mentioned in tutorial one, this requires the installation of additional add-ins, which we will look at in more detail later on.

The Manage button is the other option, which also includes one functionality from the Database Utilities submenu of Access 2003. This is the new home of the Compact and Repair Database…, Back Up Database…, and Database Properties…commands (see Picture 2.5).

You might recognize that two important menu items of the old File main menu are missing from the new Access 2007 main pull down. The Get External Data and Export… options can now be found combined in one major tab on the new Ribbon. The briefly mentioned new expansion to the import functionalities with SharePoint List support as well as collecting data through emails might have triggered the move to spread out all possible options to interact with external data on a new separate tab. The External Data tab presents the developer all commands related to importing and exporting within one view rather then working through many menus, submenus, and dialogs (see Picture 2.6).

The External Data tab eliminates the need for the export or import dialogs which present the user with the choice of interacting with different external data types in Access 2003. In Access 2007 the depreciation of these behaviors results in backward capability problems for Access 2003 files utilizing them. If your Access 2003 application incorporates a custom command bar which includes the Export…/Import… command or you implemented the acCmdExport/acCmdImport from the AcComand Class of the RunCommand action anywhere within your application code you will receive an error within Access 2007. A workaround for this scenario would be to directly specify the export or import with the provided TransferSpreadSheet, TransferDatabase, TransferText, or OutputTo methods. In the case of a custom command bar approach it is possible to tailor the Ribbon in a similar manner when converting to the Access 2007 ACCDB file format. We will discuss these options further in the next tutorial.

Furthermore, you might or might not have already recognized the Access Options command at the bottom right of the Access 2007 main pull down menu. We will ignore this option for now, but return to it later on within this tutorial. The majority of the features under Access Options integrate functionalities from the Tools menu of prior Access editions.

• Edit Menu Option (2003):

The commands of the Edit Menu from Access 2003 have been split up between the new Ribbon and Navigation Pane in Access 2007. The Clipboard functionalities (Copy, Cut, Paste, accessing the Clipboard…) can now be found on the Home tab on the Ribbon (see Picture 2.7).

All group object management commands have been integrated into the new Navigation Pane. Clicking on the pull down button at the very top of the Navigation Pane allows you to easily view and manage all or some database objects either by predefined or custom groups (see Picture 2.8).

The majority of the general View options, meaning that no database object is open in Design View, refer to the display options of the Database Window in Access 2003. Similarly to some of the Edit menu commands these features now interact with the new Navigation Pane. Changing the way database objects are represented within the Navigation Pane (Details, Icons, List) can be done by selecting the View By shortcut menu option which appears when executing a right-click on the top pull down button or in any white space within the Navigation Pane (see Picture 2.9).

We will look at the Navigation Pane itself in much more detail later on and reveal some old, familiar behavior of the Database Window, but also new features and application options.

When a Form, Report, Table, Query, or Macro object is in Design View in Access 2003 the View menu will include further commands from the corresponding Design Toolbars for example, commands for the different object view options, the object’s property dialog, the Form’s tab order, the Report’s Sorting and Grouping, etc. An additional toolbar appearing when in Design View of Access 2003 Form or Report objects is the Toolbox, which holds a variety of possible controls which can be added to the object itself.

Instead of having separate toolbars and menu options, all of the related object commands are combined within one tab on the Access 2007 Ribbon (see Picture 2.10). Opening a database object in Design View in Access 2007 will reveal several new tabs on the Ribbon. For Form object’s two tabs (Design and Arrange) are grouped as Form Tools. The Design tab gives the developer easy access to the different Form views, all possible formatting options including the very powerful Conditional Formatting, creating and adding new controls, as well as property information (see Picture 2.10).

The Arrange tab groups all commands affecting the layout of the Form object. This includes options like anchoring and aligning controls, sending controls to the front or to the back, viewing the tab order etc. (see Picture 2.11).

If you are were skeptical about the new Ribbon you might now start to recognize the obvious benefits of grouping all Form design tools within one well structured area, rather then spreading and hiding them behind several menus and submenus.

You will recognize three new Ribbon tabs (Design, Arrange, and Page Setup) when viewing a Report in Design View. These three tabs are grouped within Report Tools. The Design and Arrange tabs function similarly to the same tabs of the Form Tools. The Page Setup tab should seem familiar to you (see Picture 2.12).

The commands available on this tab used to be under the File menu of earlier Access versions. These setup options have always been meant solely for Report objects, but many Access users mistakenly applied them to Form objects as well. Forms are really just meant for interacting with the data stored in the application. However, Reports give the user flexibility in formatting the data for previewing and printing purposes.

The display of a Table object in Design View reveals the Table Tools group with a Design tab on the Ribbon interface. The Design tab incorporates all commands from the old separate Table Design command bar as well as commands from the View main menu item (see Picture 2.13).

Even though Microsoft® Access 2007 makes it easier then ever to modify a table structure at runtime with the help of the new Add New Field column visible in Datasheet View, seasoned developers however, will most likely still utilize the Table Design View and build Tables and create new fields within these Tables from scratch. The Design tab of the Table Tools group should be a straight forward and a somewhat familiar Ribbon component which helps you achieve this.

Opening a Query object in Design View exposes the Query Tools with the corresponding Design tab on the Access Ribbon (see Picture 2.14).

You can see that all Query options including the Query Types are laid out nicely for the developer’s easy usage. The group Macro Tools appears similarly to Query Tools of a Query object in when viewing a Macro design (see Picture 2.15).

You might be surprised to hear that Microsoft® actually improved the behavior of Macros. The majority of developers use Visual Basic for Applications (VBA) code because of its powerful flexibility and the ability to incorporate error-handling within the application. Macros will still not replace VBA code and that is probably a good idea, but they have become an excellent alternative for users who are unfamiliar with VBA code. Besides the possibility to create so-called safe, embedded Macros which can be executed even in a disabled environment and follow the assigned database object when exported, it is now also possible to integrate error handling and use debugging techniques.

While looking at all the database object tools groups you might have recognized that each individual object’s tools group and relating Ribbon tabs are color coded in a unique manner. Displaying a Form object in Design View reveals a purple shaded Form Tools group with its corresponding purple Design and Arrange tabs. The Report Tools and tabs appearing in Report Design View are tinted in green. The Table Tools group is distinguished with a light yellow tone and Query Tools highlights with the default Access application blue color. Macro Tools and its associated Design tab are colored yellow as well. This visually appealing distinction between Design Views of database objects supports the general theme of improving the development environment esthetics, but might also be helpful when creating and interacting with many database objects.

The Toolbars command of the Access 2003 View menu does not appear anywhere in Access 2007 anymore. As earlier mentioned it is possible to customize the Ribbon for a user specific experience and we will look at the details later on. Using MDB files which have been created in prior Access editions and utilize custom Toolbars will still function in Access 2007 as expected, but editing these elements will need to be done in an earlier version.

Furthermore, if necessary it is possible to customize the Quick Access toolbar with build in Access commands. You can find this feature under the Access Options which can be accessed through the main pull down menu appearing when clicking on the Office button

in the top-left corner next to the Quick Access toolbar (see Picture 2.1). In the left navigation pane of the Access Options dialog window click the Customization option and use the Add button to assign any commands displayed based on the selection made in the Choose commands from drop down control (see Picture 2.16).

The dialog also includes the option to display the Quick Access toolbar below or above (default) the Ribbon. The shortcut to view the Quick Access toolbar customization dialog is right-clicking on the toolbar itself. Further options of this short-cut menu are the just mentioned placement of the Quick Access toolbar as well as the minimization of the Ribbon, which can also be achieved by double left-clicking on any Ribbon tab.

The tailoring of the this toolbar can be very helpful for developers who repeatedly use the same Access commands from the main Ribbon, but should probably not be used as a replacement for custom command bars of earlier Access editions. The Quick Access toolbar is Access specific and is not tied to the application itself. Customizing the Ribbon is a much more appropriate implementation for this scenario.

In Access 2003 the Insert menu varies similarly to the View menu depending on a general view with no database objects open, an open object view, or an object in design view. The general options of the Insert menu are available on the Create tab of the Access 2007 Ribbon. For example the command to insert a new Module or Class Module can be found in the Macros pull down button of the Create tab.

Furthermore, the Create tab does not only consist of the common commands to create new database objects from the Insert menu option of earlier Access editions, but it also integrates the commands to start the corresponding object wizards (see Picture 2.17).

Picture 2.17: Create Tab with Options to Create New Objects or Open Object Wizards

The database object wizard options used to be part of the old database window which we now know has been replaced by the new Navigation Pane.

Developers should appreciate the Create tab for being one point of contact for creating new database objects.

The specific menu commands of the Access 2003 View menu when viewing an object in design view can be found on the Design tab under the appearing object tools group on the Ribbon in the 2007 version (see Picture 2.10 to 2.15).

The majority of commands found under the Tools menu in Access 2003 have been moved to the Database Tools tab on the Ribbon in Access 2007 (see Picture 2.18).

The Database Tools tab includes the option to view the Relationship window, all commands of the Analyze sub menu option (Analyze Tables/Performance and the Documenter), as well as several features of the old Database Utilities sub menu (Linked Table Manager, Database Splitter, Switchboard Manager, Upsizing Wizard, and Make ACCDE).

Clicking on the Relationships button within the Database Tools Ribbon tab will reveal the Relationship Tools group which includes a Design tab holding commands related to relationship functionalities (see Picture 2.19).

As earlier mentioned the Convert Database and Compact and Repair Database options have been moved to the Manage and Save As options of the main pull down menu behind the round Office button

in the top left corner of the application window (see Picture 2.1 and Picture 2.5).

Macro options like converting a macro to Visual Basic code or to a shortcut menu are further options on the Database Tools Ribbon tab which used to be under the Tools main menu item in Access 2003.

Furthermore, the Database Tools Ribbon tab also integrates the option to show the Object Dependency feature, which was introduced in Access 2003 and used to be housed under the View main menu item. Assuming that the Name AutoCorrect feature is turned on under the Current Database page of the Access Options dialog (see Picture 2.22) clicking on the Object Dependencies option within the Database Tools tab will open up the Object Dependencies pane including all information regarding objects and the relations between them (see Picture 2.20).

Two further options of the Database Tools tab are password encryption of the application and configuration of add-ins through the Add-In manager.

Other important commands like the startup settings or main options of the 2003 tools menu have been moved to the Access Options dialog, which can be accessed through the Office button

pull down next to the Quick Access toolbar (see Picture 2.1 or Picture 2.5).

The Popular page of the Access Options dialog lets you specify global settings associated with your Access installation. Microsoft® might have dubbed this area of the Access Options dialog “popular” because it includes the most basic but also important settings users might use frequently. Most of these settings used to be under the Advanced or General tab of the Options submenu in Access 2003 (see Picture 2.21).

As expected you can see that the default file format is the 2007 ACCDB file format. This can be changed to the MDB file format, but keep in mind that this would prevent you from using any 2007 features like integrating complex data.

The Current Database page reveals many options of the old startup properties dialog which was evoked through the Startup command of the tools main menu item (see Picture 2.22).

You can see the options to specify settings like the Application Title or Icon, the first form to display at application start, hiding the Navigation Pane, disabling Access special keys, compacting on close, as well as Name AutoCorrect. The Current Database page integrates further settings from the old Options submenu of earlier Access editions which should look familiar as well as new settings which allow you to modify object views in the new development environment (tabbed or overlapping documents).

The Toolbar option group includes the implementation of custom Ribbons, which replaces the custom command bar options of the old startup dialog. You might not know exactly what this means. Do not worry about this right now; we will look at this feature in later tutorials and explain exactly how to create and implement a custom Ribbon.

The Navigation Options of the navigation group brings up the Navigation Pane dialog (see Picture 2.23).

The shortcut to access the same dialog would be to right-click within white space in the Navigation Pane itself or right-click on the top Navigation Pane bar (see Picture 2.9).

The Navigation Options dialog lets you easily group objects within the Navigation Pane as well as specify general view settings like revealing hidden or system objects, which used to be in the view tab of the options dialog in prior Access editions.

The Datasheet page of the Access Options dialog holds all display preferences for the datasheet view (see Picture 2.24). This is an exact replacement of the datasheet tab from the options dialog of Access 2003.

Right-clicking within a Form object in datasheet view reveals a shortcut to access the datasheet formatting dialog which allows you to modify display properties of the datasheet at runtime.

The Object Designer page of the Access Options dialog combines all property settings of Table, Query, Form, and Report objects (see Picture 2.25). The Options dialog of Access 2003 or prior editions housed these settings on two separate tabs called Tables/Queries and Forms/Reports.

The Proofing page of the Access Options dialog represents the Spelling tab of the Options dialog of prior Access editions. This includes language, dictionary and AutoCorrect options (see Picture 2.26).

As in previous versions of Access the AutoCorrect options might not be too useful in a database application. It definitely has its place and benefits in word processing, but can create unexpected results when used in a data entry environment within Access. Besides running into the risk of storing values which were not the initial intent of the user the feature is known to cause some performance issues. The decision to implement it or leave it out is up to you, the developer. You might be familiar with habits of potential users of the application or can experiment with different settings in test environments. Keep in mind that AutoCorrect is an Office wide shared feature and modifying settings within Access will also result in the same behavior within other Microsoft® Office programs like Word or Excel.

The Advanced page of the Access Options dialog combines many settings of the Options dialog of Access 2003. It is here you can specify keyboard behavior, default Find and Replace behavior, confirmation warnings, and international settings like reading direction. Furthermore, the Advanced page includes preferences from the old General and Advanced tabs of the Options dialog for example using four-digit year formatting, globally predefined printer margins, or record locking (see Picture 2.27).

We have already looked at the Customization page of the Access Options dialog while talking about the Quick Access toolbar (see Picture 2.16), so let us move along and glance at the last few pages of the Access Options dialog.

The Add-ins page functions similarly to the Add-in manager. However, the layout easily reveals active, inactive, or disabled application level add-ins as well as document related add-ins (see Picture 2.28). This enables the developer to inspect all add-in related settings within one view, rather then browsing through several menus and dialogs.

In the beginning of this tutorial while discussing the differences in the MDB and ACCDB file formats we were partly speaking about the new Office wide Trust Center.

When clicking on the Trust Center button of the Access Options dialog you will be presented with a separate dialog dedicated purely to the new Trust Center area of Microsoft® Office (see Picture 2.2). As earlier mentioned one of the newly implemented security features within the Trust Center is the capability to assign trusted locations. In Access 2003 one of the only ways to get around the wealth of security warnings when opening an unsigned application was to modify the Macro Security Level. This was never an advised approach to the problem because of the negative effect of creating a security breach for users. In Access 2007 it is possible to set up trusted locations which can hold these applications. This will enable the launch of custom solutions without any security prompts and allow the applications to run fully functional without any interventions. Keep in mind that digitally signing your projects is still the preferred method of distributing a trusted and secured application. This can either be done through the creation of self-signed certificates or by purchasing a certificate from a trusted third-party commercial certificate authority.

The Macro Settings page of the Trust Center dialog allows you to specify what actions to take for unsigned documents which are not housed within any trusted locations (see Picture 2.29).

Since Access 2007 allows the user to interact with disabled applications it might be a good decision to keep the default setting to disable all macros with notification. If you decide that the database is secure after inspecting its content it is always possible to easily enable all functionalities through the Document Action Bar underneath the Ribbon (see Picture 2.3 and Picture 2.4).

The Message Bar settings within the Trust Center dialog (see Picture 2.30) refer to the Document Action Bar which appears at startup of untrusted applications in Office 2007 and allows the user to either disable the content within the application or enable it (see Picture 2.3 and Picture 2.4).

The Privacy Options page within the Trust Center dialog bundles all options related to your privacy and the connectivity to the internet (see Picture 2.31).

It is up to you to decide if and when you want your Office installation to connect to content on Microsoft® Office Online. One of the advantages of connecting to Microsoft® Office Online is the exposure of extensive support material like help files or templates. We will look at the new support system in more detail when comparing the help main menu item of prior Access editions.

For the same reasons we will not yet look at the Recourse page of the Access Options dialog. All options within that page are also related to the Help main menu item of past Access releases so we will come back to it at the end of this tutorial.

The User Level Security and Replication menu options of the old tools main menu will not exist when using the new ACCDB file format; however, opening a MDB file with Access 2007 or creating a new database when the MDB file format is set as the default format under the Popular page of the Access Options dialog (see Picture 2.21) will reveal a new group called Administer on the right of the Database Tools Ribbon tab (see Picture 2.32).

For backward compatibility this group will allow the developer to implement or modify User Level Security and Replication options very similar to the Access 2003 edition (see Picture 2.33 and Picture 2.34).

Nevertheless, as earlier mentioned, the MDB format will not allow you to integrate new Access 2007 features like complex data. Because of the wealth of new features integrated in Access 2007 it might be beneficial to use the ACCDB file format to take advantage of them. As mentioned earlier, customizing the Ribbon and Navigation Pane are good alternatives to using User Level Security.

The settings of the window menu option of prior Access editions do not appear anywhere with Access 2007 by default. The reasoning is the default view setting of database objects. We had already revealed earlier in tutorial one that the new development environment supports a tabbed style view of opened database objects (see Picture 1.2). If you prefer separate overlapping windows as you might be used to from all earlier Access versions you can modify the default Tabbed Documents setting through the Current Database page of the Access Options dialog (see Picture 2.22). When using the overlapping windows option the Home tab of the Ribbon will house a new Window group (see Picture 2.35).

All commands of the window menu option of prior Access versions can be found under the Window group (see Picture 2.36).

The majority of commands of the Access 2003 help menu can now be found under the Recourses page of the Access Options dialog. As illustrated before the Access Options dialog can be activated through a selection on the main Office pull down behind the Office button

(see Picture 2.1 or Picture 2.5).

The Recourses page allows you to check for Microsoft® Office updates, provide customer feedback, activate as well as detect and repair the Office installation, register for certain online services or learn more about the installation itself (see Picture 2.37).

The Microsoft® Office Help files can be accessed directly through the familiar round and blue question mark button

in the top right corner of the Ribbon or by pressing the F1 keyboard shortcut key. The question mark is always visible at all times no matter what Ribbon tab selection the user makes.

The Access Help dialog might remind you of a web browser with its included navigation buttons to go back, forward, home, refresh or stop a page, or print the current content (see Picture 2.38).

Besides easier user navigation the reasoning behind the help user interface might be the fact that extensive parts of the Office 2007 help system are offered as online content. Office 2007 still ships with local help file content but a vast amount of support and templates can be retrieved from Microsoft® Office Online.

You can specify what content you want to search through by switching the Connection Status in the pull down next to the Search button (see Picture 2.38) or by accessing the status through the options on the right of the Access Help dialog status bar (see Picture 2.39).

The Format menu option in Access 2003 appears when viewing a form or report object in design view. As earlier mentioned the commands behind this menu item (Conditional Formatting, Align, Bring to Front/Send to back etc.) have been moved to the Design, Formatting, and Arrange tabs of the specific Form or Report Group, which appear on the Ribbon when viewing a form or report in design or layout view (see Picture 2.10 to Picture 2.12).

The appearance and its included commands of the Visual Basic Editor in Access 2007 are unchanged from prior Access editions. Therefore, you should not have any problems with that aspect of Access 2007 and we will not dissect it as we did with the new main user interface.

We have now finished our detailed review of a comparison in specifications, features, and menu items of Access 2003 and Access 2007. Do not worry if you have already forgotten where certain things are in the new user interface. It will come back to you when you are using the software extensively. I have personally found that performing the method of “learning by doing” almost always positively effects the outcome of getting familiar with new software or software environments. This tutorial is meant to extensively reveal a comparison between Access features and options of prior editions with the Access 2007 version. However, you can use this tutorial as a quick reference in case you need to look up a specific command you are familiar with from Access 2003 or if you are not certain about specific Access limitations.

Tutorial three is a slight continuation of tutorial two. We should now be somewhat familiar with the new user interface and start to recognize old features and options in their new places. In this tutorial we will extend our comparison of Microsoft® Access 2003 and Access 2007 in terms of functionality rather than the scalabilities, specifications and visual comparisons we looked at in tutorial two. We will quickly look at some of the most important keyboard shortcut keys and test coding and development practices from Access 2003 in the new 2007 edition.

There are several reasons for me to include this section in this tutorial. The first one is that I am hoping that there other “keyboard lovers” left out there, like me, who will appreciate yet another attempt to persuade users to give up their mouse. Keyboard shortcuts are probably the most efficient way to get your work done within Microsoft® Office no matter if you are a developer or the user of a finished system. A list of most commonly used shortcuts will serve for those as a refresher or reference if you have a hard time remembering the many combinations. This reveals yet another reason for this section. Even though Office 2007 took over all earlier known shortcut keys and integrated some new user interface specific combinations, it is now easier then ever to keep your hands on the keyboard. There is no longer a need anymore to remember combinations like Ctrl + S to access menu features and commands with the new interactive shortcut key smart tags. We will look at this in more detail soon. For now let us refresh our memory of some of the most useful shortcut keys carried over from earlier Access editions (see Table 3.1).

The above list represents only a small amount of some of my favorite global Office and Access keyboard shortcuts from a vast amount of useful options.
A complete list can be found in the Access help files

under the “Keyboard Shortcuts” topic. Most of them should be familiar to frequent users of Microsoft products.

In addition to the complete continuation of all shortcuts from prior Office editions there are several shortcut combinations in Office 2007 relevant to the navigation of the new Ribbon user interface (see Table 3.2).

The majority of people using the mouse to navigate around the Office workspace might do so because of the overwhelming amount of key combinations which require a fairly good memory. Office 2007 introduces a new and easier then ever method to keep the hands off the mouse and on the keyboard. Pressing the ALT key will reveal key tips for all possible shortcut options within the Ribbon, main Office button

and the Quick Access toolbar (see Picture 3.1).

This new feature eliminates the need to remember several key strokes in a row and allows you to easily navigate through all available commands and options with the keyboard. As an example, pressing ALT-C-T-N would activate the Create tab of the Ribbon and then create a new blank table (see Picture 3.2).

Another quick example would be the equivalent of the earlier mentioned and well known CTRL+S combination to invoke the saving of an object. Using the new Key Tips feature activated by the ALT key the user is guided directly to the save command after pressing the F and then the S key (ALT-F-S) (see Picture 3.3).

Throughout the years, developers have implemented several custom procedures or utilized specific application properties to achieve certain behavior within their applications.

The vast amount of new features in Access 2007 eliminates the need for most of these practices. For example, as earlier mentioned, it is not necessary to implement a third party Rich Text control or PDF export solution anymore with the new natively implemented support.

However, there are still a few well known coding “tricks” that you might be questioning if they are still applicable to the Access 2007 edition. In the coming pages we will look at and test some of these practices, which you might frequently use in your applications.

In earlier Access editions, it was possible to implement specific start up options for your application under the Startup sub menu command of the Tools main menu option. This includes the ability to open a specific form at application start, specify a custom application title and icon or disable certain menu bars, toolbars, shortcut menus or special Access keys. Another method of achieving similar settings would be to execute a public function, which programmatically sets these startup options, with the RunCode action of a macro named AutoExec.

In Access 2007 you will find similar startup options under the Current Database page of the Access Options dialog (see Picture 2.22).

Using an AutoExec macro to execute specific actions or sequences of actions at database startup works similarly as in earlier Access versions.

The build in shift bypass key mechanism avoids the execution of both the set startup options and an AutoExec macro to ensure that an application is not completely disabled in case the developer needs to make future modifications. However, this also enables users of finished databases to get back into the design level of the application and make vital and unauthorized changes or see data that is not permitted to them.

A way to disable the shift bypass key is to utilize the AllowBypassKey property of the CreateProperty method of the Microsoft® DAO object library. It is generally known that it is possible to reset the AllowBypassKey property and therefore it will not completely secure your application, but it is one of many features you can implement to protect against potential misusage of your application.

Access 2007 continues to use the DAO object library which is set by default as the Microsoft® Office 2007 Access database engine Object Library in the new ACCDB file format. If you utilize the MDB file format it will be set by default as the well known Microsoft® DAO 3.6 Object Library. Either library still supports the CreateProperty method which allows us to set the Boolean type AllowBypassKey property.

A quick sample illustrating the disabling of the shift bypass key can be seen in code example 3.1:

Public Function SetAllowBypassKeyFalse()

'----- Dimension (Variable Declaration)
Dim db As DAO.Database, prp As DAO.Property

'----- Set AllowBypassKey property if it exists
Set db = CurrentDb

'----- Property not found error
'----- Create property if it does not yet exist
If Err = 3270 Then

Set prp = db.CreateProperty("AllowBypassKey", dbBoolean, False)
db.Properties.Append prp

Else

'----- some unspecified error occurred
MsgBox "SetAllowBypassKeyFalse", Err.Number, Err.Description

The above code needs to be executed only once within the application to set the property. Keep in mind that you should implement a method to turn the AllowBypassKey property back to True to ensure that you can get back into the application yourself.

This topic might be closely related to the sample we have just covered. Sometimes you might want to give users with appropriate rights in a secured environment the possibility to work with the Navigation Pane.

The startup options under the Current Page allow you to hide the Navigation Pane similarly as you might have been doing with the Database Window in earlier Access versions.

To programmatically show or hide the Database Window or Navigation Pane you can run the sample code shown in the code example 3.2:

'----- show the Database Window or Navigation Pane
DoCmd.SelectObject acTable, “YourTable”, True

'----- hide the Database Window or Navigation Pane
DoCmd.SelectObject acTable, “YourTable”, True
DoCmd.RunCommand acCmdWindowHide

When utilizing the above code to show or hide the Database Window it is not necessary to actually specify a true existing database object name in the ObjectName argument of the SelectObject method of the DoCmd object. However, when trying to show or hide the Navigation Pane in Access 2007 it is required to pass along an existing object name for the code to function correctly.

Though you might not even want to completely hide the Navigation Pane or reveal it to certain users in Access 2007 anymore. The Navigation Pane can be an integrated part of your application and serve as a very interactive switchboard replacement.

Access 2007 introduces three specific macro actions which will help developers with the customization of the Navigation Pane. We will look at this topic later on when discussing the implementation of these new Access features. For now let us mention the three macro actions without going into too much reasoning and detail about their functionalities. The SetDisplayCategories action allows the developer to show or hide specific categories to the user. The NavigateTo macro action enables us to organize categories and database objects within the Navigation Pane and the LockNavigationPane action prevents anyone from mistakenly cutting or deleting database objects from within the pane.

One major annoyance developers had to deal with in prior Access editions was the control of the mouse scroll wheel and avoiding the saving of empty records in single form view.

Throughout time many different workarounds to fix this issue have been implemented. Some utilized different external DLL (Dynamically Linked Library) files to actually disable the mouse wheel. Others implemented a lot of VBA (Visual Basic for Applications) code to trap the record advancement with the mouse wheel in certain conditions.

In the end none of the solutions were extremely desirable because of flexibility or performance issues as well as the hassle of implementing them.

To the delight of probably every Access developer Access 2007 improved the mouse wheel behavior. The mouse wheel will not scroll through records in the single form view anymore. If you happen to actually desire this behavior then you can utilize the “On Mouse Wheel” form event (see Picture 3.4) as described in code example 3.3.

The mouse wheel still works as expected in continuous and datasheet form view. When utilizing the new split form view the mouse scroll wheel will function in the datasheet portion of the form, but not in the single form segment.

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)

If Count > 0 Then

'----- check if current record is total records plus new one
If Me.CurrentRecord = Me.RecordsetClone.RecordCount + 1 Then

'----- already at the end - stop scrolling
MsgBox ("You are at the end!")

Else

'----- go to the next available record
DoCmd.GoToRecord , , acNext

End If

'----- check if current record is first record
ElseIf Me.CurrentRecord - 1 = 0 Then

'----- already at the beginning – stop scrolling
MsgBox ("You are at the beginning!")

Else

'----- go to the previous available record
DoCmd.GoToRecord , , acPrevious

End If

End Sub

Some developers like to utilize the status bar at the bottom of the Access application window. Some possible usage scenarios would be the implementation of a progress meter for longer lasting processes or maybe tips to help the user of the application with navigating through the application.

The status bar in Access 2007 functions identically to earlier Access editions. It can be hidden permanently by un-checking the “Display Status Bar” checkbox in the Current Database page of the Access Options dialog (see Picture 2.22).

Alternatively you can work with the status bar with Visual Basic for Applications code (see Code Example 3.4).

'----- show text on status bar
SysCmd acSysCmdSetStatus, "Here is your text on the status bar!"

In some circumstances it is advised to split an Access application into two separate files. Examples of such circumstances would be the usage of a database in a multi-user environment, organizing large amounts of data effectively or running into problems with the 2 gigabyte file size limit.

Splitting the application will result in a frontend file housing Form, Query, Report objects as well as Visual Basic for Applications code and one backend file holding all the data tables. The frontend file uses table links to communicate with the backend file.

The build in Database Splitter can establish this whole setup within seconds and make the splitting process a fairly simple affair. In Access 2007 the Database Splitter can be found as an Access Back-End button under the Move Data group on the Database Tools Ribbon tab (see Picture 2.18).

Another method of splitting an application would be the manual process of creating two Access files and importing and deleting certain database objects within the two files. The importing process can be achieved through the External Data tab on the Ribbon (see Picture 2.6). However, keep in mind that it is not possible to link to tables housed in the new ACCDB file format from within a MDB file.

A very helpful tool in organizing table links is the known Linked Table Manager, which can also be found on the Database Tools Ribbon tab (see Picture 2.18) or by right clicking on an existing linked table in the Navigation Pane. Access 2003 houses this option under the Database Utilities sub-menu of the Tools main-menu option.

If the application files are moved around, the table links would need to be refreshed to reflect the new directory location of the backend file. Sometimes it is nice not to expose the user to this complete process and programmatically execute a procedure which mostly takes care of this issue for them. There are many different ways to achieve the refreshing of table links with Visual Basic for Applications code. One method utilizing the Connect property of TableDef DAO (Data Access Objects) objects is demonstrated in code example 3.5:

'----- Dimension (Variable Declaration)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef

'----- check if provided backend file exists
If Len(Dir(strNewLoc, vbDirectory)) = 0 Then

MsgBox "Backend file or location not correct!", vbCritical, "Cannot Link!"

'----- check if trying to link to ACCDB from MDB
ElseIf Right(strNewLoc, 5) = "ACCDB" And Right(CurrentProject.Name, 3) = "MDB" Then

MsgBox "Cannot link to ACCDB file format from MDB file!", vbCritical, "Cannot Link!"

'----- location/file exists and is in right format…try to link
Else

Set dbs = CurrentDb

'----- loop through all tables in the database
For Each tdf In dbs.TableDefs

'----- check if linked table (has connection string?)
If Len(tdf.Connect) > 0 Then

'----- update connection string to new location
tdf.Connect = ";DATABASE=" & strNewLoc

'----- refresh table links/permanently update
tdf.RefreshLink

End If

Next tdf

End If

'----- Exit Label
Exit_refreshTblLinks:

'-----Skip errors if objects not set
On Error Resume Next

'----- Release Objects
Set tdf = Nothing
Set dbs = Nothing

Exit Function

'----- Error Handler
Err_refreshTblLinks:

'----- errors when trying to refresh
If Err <> 0 Then

Msgbox("Failed!")
Resume Exit_refreshTblLinks

End If

End Function

The refreshTblLinks function shown in code example 3.5 could be called from anywhere within a frontend application; for example, utilizing the RunCode action of an AutoExec macro or behind the On Load event of a start up form. A call to the function requires the full directory and file name string value of the Access backend file to be passed along.

As earlier mentioned you will not be able to link to Table objects which are stored in the ACCDB file format if you execute this code within a MDB file. You will receive a Runtime Error 3845 if you try to do so (see Picture 3.5).

Code example 3.5 includes an integrated method of preventing this error. It is possible to link to tables within a MDB file from the ACCDB format, but not the other way around.

A very effective method of quickly securing your database is to convert the ACCDB file to the ACCDE format. The “Make ACCDE” option button is located on the Database Tools Ribbon Tab (see Picture 2.18).

Similar to the MDE file format the conversion locks down your Forms, Reports and code. To prevent errors in the conversion process of your original database file to the ACCDE file format try to manually compile your VBA code first. You can do this by opening the VBA Editor Window (keyboard shortcut CTRL+G) and selecting the Compile command off the Debug main menu item.

This does not cover all the security options you have within Access but the ACCDE or MDE file formats are a very popular choice when deploying your application to customers. We will look into more customizable security approaches in later tutorials.

After finishing the process of building a complete user interface through the help of Form objects, a switchboard form can help tie everything together to an easy navigatable menu for your users.

Access 2007 continues to include the Switchboard Manager, which is positioned on the Database Tools Ribbon Tab, too (see Picture 2.18).

However, the wizard’s functionality has not been altered and you might be better off by creating a more flexible and custom approach using more Form objects.

As earlier mentioned the new Navigation Pane could actually be utilized as a very good alternative to a switchboard form and we will have a more detailed look to this approach later on.

Many developers like to customize the start up of their application by implementing a splash screen. This simulates the feel of a professionally created application. The most commonly used method to realize a custom splash screen is to hide the default Access splash screen and start the database with a custom form.

Setting specific properties within a Form will make it appear less like an Access Form object and more like a regular splash screen. Some of these settings might be the alteration of the Scroll Bars, Navigation Buttons, Record Selectors, Border Style, and Min/Max/Close Buttons properties.

The purpose of this Form should be to stay visible on the screen for a short amount of time and then redirect to a main menu or switchboard page. This effect can be achieved by adding two lines of code (see code example 3.6) to the Form’s On Timer event and setting its Timer Interval property to an appropriate quantity (see Picture 3.6).

Keep in mind that the Timer Interval is evaluated in milliseconds. As picture 3.4 illustrates the amount of 5000 milliseconds in the Timer Interval property would execute the code behind the Form’s On Timer event every five seconds.

'----- redirecting to the Main Menu/Switchbaord
DoCmd.OpenForm “MainMenuFormName”

'----- closing the splash screen Form
DoCmd.Close acForm, Me.Name

Now that we have created a custom splash screen we can define it to open on database startup automatically as the first form by adjusting the Display Form property of the Current Database page within the Access Options dialog (see Picture 3.7).

The only thing left to do is to hide the default Microsoft® Access splash screen. Similarly, as in earlier Access editions, it is possible to create a small 1x1 pixel BMP (Bitmap) file which is named identically (besides its extension) to the ACCDB/MDB database file and located in the exact same directory (see Picture 3.8).

This finishes tutorial three. We raised our confidence to navigate through the majority of Access with the keyboard, through the help of keyboard shortcut combinations as well as the newly implemented Key Tips feature. Additionally, we tested some common development practices in Access 2007 and compared functionalities to Access 2003.

In earlier tutorials we looked at the Office suite lineup and started to ease our way into Microsoft® Access 2007 by revealing some of the key features of this new release.

To increase our awareness of the Access 2007 development environment we went through a detailed comparison of the 2003 and 2007 versions. This included the scalability and specifications of the software as well as a side by side menu assessment.

We should now be able to find our way around the new application’s user interface and can start to extensively use it by having a detailed look at all the new features we browsed through earlier. By using the software you will become more and more comfortable and soon be as proficient in it as you were earlier editions. Further tutorials will help you do just that.

As mentioned in tutorial one a new and very useful feature within Access 2007 is the implementation of the PDF (Portable Document Format) and Microsoft’s® XPS (XML Paper Specification) format support. In this tutorial we will closely look at all the details of this new attribute including the activation as well as the manually and programmatically usages.

When freshly installing a new Microsoft® Office 2007 suite (see tutorial one for suites options) and opening an Office application like Access 2007 the PDF and XPS features will not be directly available for usage. When navigating the main pull down menu of the Office button

you might recognize that the Save As command does not yet include any options to export database objects to PDF or XPS format. Instead you will find a button linked to a specific help file topic informing you about possible add-ins for other file formats (see Picture 4.1).

The help topic displayed after pressing the “Find add-ins for other file formats” option will inform you about the Publish as PDF or XPS add-in which you can activate by downloading a 934 KB (kilobyte) executable file from the Microsoft® Download Center. To navigate to the add-in installation file (SaveAsPDFandXPS.exe) either follow the link included in the help file content or directly search for it on the Download Center website. If you do not already have the Office Genuine Advantage Internet Explorer Active X control installed you will need to go ahead and do so to be able to continue with the download of the SaveAsPDFandXPS add-in. The Internet Explorer Active-X control ensures that your Windows and Office installation are legitimate and genuine. After downloading the Add-in file open it and follow the download instructions. The installation should take effect immediately and you have now enabled the Publish as PDF or XPS feature within your Office applications (see Picture 4.2).

You can now select any Table, Query, Form, or Report database objects within the Navigation Pane and export the content to an external PDF or XPS file. After selecting the PDF or XPS option of the Save As Office menu option you will be presented with a Publish as PDF or XPS dialog which houses several different options for your export (see Picture 4.3).

After publishing the selected database object to and external file you will be confronted with a further dialog which enables you to save the steps taken to export the internal content (see Picture 4.4). This nice feature will set up a shortcut for you the next time you repeat the export functionality.

Keep in mind that you or your users need to have the Adobe® Reader to view PDF files as well as the Microsoft® .NET Framework installed to view XPS content. Both can be obtained on the internet free of charge.

Two other methods of manually exporting database objects to PDF or XPS format can be achieved through the PDF or XPS command of the Export group on the External Data Ribbon Tab or by directly right clicking on the database object in the Navigation Pane and selecting the PDF or XPS option of the Export shortcut command (see Picture 4.5 and 4.6).

Two methods important for developers to automate the publishing process of PDF or XPS files are the OutputTo and SendObject methods. Both methods have been part of the DoCmd object for a long time and besides supporting the usual export formats (XLS, TXT, RTF…) the OutputFormat now also supports the PDF and XPS formats. It is not necessary anymore to implement long winded coding workarounds or third party solutions to achieve the same results. Code sample 4.1 illustrated two ways of implementing the automation methods of publishing database objects to the PDF and XPS format.

'----- OutputTo method utilizing the acFormatPDF in the OutputFormat argument
DoCmd.OutputTo acOutputReport, "YourReportName", acFormatPDF, _
"c:\YourReportName.pdf", True, , , acExportQualityPrint

'----- SendObject method utilizing the acFormatXPS in the OutputFormat argument
DoCmd.SendObject acSendReport, "YourReportName", acFormatXPS, _
"YourEmail@address.com", , , "Your Subject", "Your Body", True

The OutputTo method is especially useful for developers who do not like to employ the somewhat limited SendObject method. The SendObject method only allows the attachment of one internal database object at a time to a plain text email message. Sometimes this does not suffice the needs of developers or clients and more advanced automation code is implemented. A PDF or XPS file saved externally with the OutputTo method will enable the developer to use them as attachments in automated email messages.

Looking at code example 4.1 you might have recognized a further new addition to the OutputTo method. The new acExportQuality argument at the end of the OutputTo method can have a value of acExportQualityPrint (0) as well as acExportQualityScreen (1).

This tutorial will primarily deal with the usage of the new and major interface features of Access 2007. We had quickly browsed through most of them in earlier tutorials and will now have a detailed look at how to customize them.

The most intriguing and probably most complicated component of Access 2007 is the new user interface. However, it is very customizable and presents developers with a vast amount of options for their applications.

The best approach to customizing the new user interface is XML. This might be somewhat intimidating if you have never worked with Extensible Markup Language before but using this tutorial you should be able to get a head start and take it wherever you want afterwards.

Create a Ribbon table which will hold our XML and a unique name assigned to the XML code. Name this table USysRibbons. This ensures that the table will be hidden in the Navigation Pane unless you have the “Show System Objects” option checked under the Navigation Options dialog.

To open the Navigation Options dialog right click on the Navigation Pane’s selection bar at the top and select Navigation Options…or open the Access Options dialog
(at the bottom right of the Office menu pull down behind the big round Office button in the top left corner of the Access shell). Than choose Current Database and press the Navigation Options… button displayed there.

Open the table in datasheet view...we are ready to add some XML. The majority of questions I have seen deal with the disabling/hiding of certain commands or the Ribbon all together. So we will have a look at that. Add a new record in your table with a RibbonName value of CommandsDisabled and the following associated XML in the RibbonXML field:

For the Ribbon to be recognized you need to close and reopen the application. So close the table/application and reopen it. After that go to the Current Database tab of the Access Options dialog (described earlier). Under Ribbon and Toolbar Options select CommandsDisabled in the Ribbon Name drop down. After pressing OK Access should notify you that you need to close and reopen the application for the change to take effect. So close and reopen the application. Afterwards go back to the round Office button

in the top left corner and check the New, Close Database, Access Options, and Exit Access commands. They should all be grayed out and inaccessible.

Congratulations…you have just customized the new user interface. The shift bypass key affects the user customization similarly to other options you might have used before. So to temporarily return to the design state you can just close the application and reopen it while holding down the shift key.

Again open the USysRibbons table and add a new record with a RibbonName value of HideRibbon and the following XML for RibbonXML:

Setting the startFromScratch attribute to true will completely hide the Ribbon, remove certain commands from the Office pull down as well as the quick launch bar if you do not specify any further actions.

As described in step three close and reopen the application and set the new Ribbon Name in the Access Options dialog. Then close and reopen the application for the XML to take effect.

This short tutorial should have helped you get started. The Ribbon exposes many cool user interface features which have not been possible up till now. You can display galleries, drop downs, split buttons…and construct a really rich and friendly interface for your users. As a little hint I will show you one more step to creating a custom Ribbon button which executes a specified action when clicked. Add the following XML to a new record in your Ribbon table:

Ribbon commands act through the help of callbacks which are little subs you write in VBA code. In this example the onAction of the SampleButton will correspond to a callback named OnButtonPress. After closing your Ribbon table create a new module and paste the following code in it:

'our callback for the SampleButton
Public Sub OnButtonPress(ctl As IRibbonControl)

MsgBox ("You have just executed the OnButtonPress callback when clicking" _
& vbCrLf & "the Ribbon SampleButton!")

Save the module as whatever you want as long as it doesn’t have a name of a procedure/function it houses. Follow the instructions in step three to assign this new Ribbon XML to the application. After closing and reopening the application you should see one tab named My Tab holding one group named My Group which houses one button named Click Me. If you press the button you should receive the message box we defined in the callback procedure.

By the way...if you are using an existing MDB file with custom menu/command bars and you want to continue to only display those in Access 2007 than follow these detailed instructions:

http://msdn2.microsoft.com/en-us/library/bb258174.aspx

Another quick trick to manipulate the Access interface is to change your application's ACCDB extension to ACCDR. This results in a locked version of your application which effects the user interface as well.

In this tutorial we will look at the new Navigation Pane introduced in Access 2007. Next to the new Ribbon interface discussed in earlier tutorials the Navigation Pane adds a lot to the overall new interface of the development environment. This should be a stepping stone to navigating and customizing the Navigation Pane as we will have a detailed look at it.

General Information about the Navigation Pane

The Navigation Pane is the replacement of the Database Window of earlier Access editions. It houses all application objects in a vertical manner. Unfortunately the Navigation Pane is not self-collapsible nor can it be a separate floating container as the Database Window used to be. This might take some time to get used to as a developer because the development/design space for objects might seem drastically smaller between an expanded Navigation Pane and maybe an open property sheet (see Picture 6.1).

A further restriction of the Navigation Pane is its vertical layout. The Database Window used to display object properties (title, description, date creation/modified) in a columnar style and allowed easy sorting/filtering on the specific property columns. The Navigation Pane switches this view to a vertical representation which could mean that an object could use up to 4 lines of space (see Picture 6.2).

If you consider a large application with many objects you might be doing a lot of vertical scrolling in the Navigation Pane. However, if you know exactly what you are looking for a neat search feature provided up top might be very beneficial in such a scenario. We will look at this in more detail in the next section.

Navigation Pane features

You can manually collapse and expand the Navigation Pane by clicking on the double arrow at the top of the pane (see Picture 6.3).

For shortcut key lovers you can still press F11 to show/hide the Navigation Pane as it was possible in earlier Access versions with the Database Window. Since we are discussing the showing and hiding of the pane we can also still hide it at application startup by un-checking the Display Navigation Pane option in the Current Database tab of the Access Options dialog (see Picture 6.4).

If you save and restart the application the Navigation Pane should be unavailable. However as with other interface customizations holding down the Shift key at application startup will bypass these options. The Common developer practices section of tutorial three shows a method to disable the Shift bypass key but keep in mind that it can be easily re-enabled through code, too. The same section of that tutorial also illustrates a code example to programmatically show/hide the Navigation Pane.

As mentioned earlier there is a very useful search feature implemented at the top of the pane. If you do not see it by default then right click on the Navigation Pane header and select Search Bar (see Picture 6.5).

The Search Bar acts as a drill down feature which will filter the complete Navigation Pane. This makes it very easy to find specific database objects within seconds.

Objects within the Navigation Pane are grouped either in specific predefined categories or custom groups. Using predefined categories you can easily switch between displaying only your Tables, Forms, Queries, Reports, Modules as well as viewing a sorted break down of objects by their created or modified date (see Picture 6.6).

As mentioned in tutorial two further display options of the Navigation Pane can be access through the Navigation Options choice which becomes accessible by right clicking on the top bar of the Navigation Pane or by going through the Access Options dialog’s Current Database tab. Right clicking the top bar of the Navigation Pane also exposes other filtering and sorting options for your database objects.

Customizing the Navigation Pane

One of the strong points of the Navigation Pane is the ability to extensively customize it. This means that it can actually be included in your application as a switchboard type navigation bar. Exposing the Database Window to users in earlier versions of Access was never a recommended practice so being able to customize and lock down the main point of object navigation within your application can be of great benefit.

We will start by simply creating a couple of custom groups in some application. Open the Navigation Options dialog through one of the earlier specified methods. You should see something similar as shown in Picture 6.7:

Add a new Item and name it. For example you might want to establish a grouping specific for user forms (e.g. Supervisor Objects). Within this new Item add a couple different groups (e.g. Supervisor A, Supervisor B, Supervisor C). You might now have something as shown in Picture 6.8:

Close the Navigation Options dialog by pressing OK and change the Navigation Pane display to your new custom group (see Picture 6.9).

After applying all these settings your Navigation Pane should now look something like shown in Picture 6.10:

At this point you are ready to click and drag specific database objects into your custom groups. In our example you could drag specific Form or Report objects which only apply to your certain supervisor groups. Once you have dragged a couple of objects into your groups you might recognize that you aren’t really moving the objects, but rather create shortcut links to them. This is the appropriate way to handle the customization and actually allows you lock down the Forms. Shortcuts have an additional Disable Design View Shortcuts property that you can apply to not allow any users to switch into design view when launching your database objects through the supplied shortcut. You can set this property by right clicking on the shortcut to open up its property dialog. After you are done creating all your shortcuts in your custom groups you can completely remove the Unassigned Objects group through the Navigation Options dialog.

Now all that is left is to further lock down and customize the Navigation Pane at runtime. Access 2007 includes three new methods of the DoCmd object which will prove helpful with that. The NavigateTo method can modify the groups and category with which your objects are displayed. The LockNavigationPane method will convert the Navigation Pane to a read only element of your application. Last but not least you can utilize the SetDisplayedCategories to adjust the categories displayed when clicking the top Navigation Pane bar. All three methods can be set as actions within a Macro or directly within VBA.

The LockNavigationPane method only has one argument which takes a boolean value.
If you run the method with the boolean value set to false once, it will lock the Navigation Pane and prevent deleting of objects/shortcuts e.g.:

DoCmd.LockNavigationPane True

The NavigateTo method takes two arguments. One string value for the category and one for the group you want to navigate the Navigation Pane to. This action could be beneficial if you have some sort of evaluation process build into your application and you directly want to present certain user groups with their objects. In our little example you could run something like the following:

DoCmd.NavigateTo "Supervisor Objects", "SuperVisor A"

This would immediately display the SuperVisor A group objects when executed.

The SetDisplayedCategories method can show and hide groups from the top selection bar of the Navigation Pane. Sticking to our small sample you could run this code:

DoCmd.SetDisplayedCategories False, "Supervisor Objects"

If you executed that line and look under the pull down of the top bar of the Navigation Pane you should recognize that our Supervisor Objects group is no missing. Similar to the NavigateTo method this can be very useful in a customized environment.

This finishes our little tutorial of an introduction of the new Navigation Pane. We have learned basic usage and customization in a manual and coding manner. Just as the new Ribbon interface the Navigation Pane takes some time getting used to. However reading through this tutorial you might have recognized its powerful and useful features that you can take advantage of as a developer. It is a good alternative to the limited, build in Switchboard Manager and can be a tremendous user experience in combination with Ribbon customization.

Microsoft® Access 2007 Working with the Attachment DataType

Download Tutorial 07 (423 kb zipped)
(Click link or right click--Save Target As...)

In this tutorial we will look in detail at the new Attachment DataType introduced in Access 2007. We will go through examples demonstrating the usage of the new DataType and illustrate why it might benefit developers. Furthermore, we will work with manual as well as programmatical implementations of the Attachment DataType.

General Information about the Attachment DataType:

As discussed in some of the earlier tutorials Access 2007 implements a new, very useful DataType. The Attachment DataType is a multi-valued field that replaces the OLE Object DataType in the new ACCDB file format. Keep in mind that such complex data is only available in the ACCDB file format and not in the prior MDB formats.

The Attachment DataType works as a multi-valued field which you might think breaks normalization rules. Microsoft® ensures us that the data is stored in a relational manner at the most basic level within Access itself.

The main purpose of the new DataType is to eliminate the bloating issues the OLE Object DataType exposed when embedding external files within your application. In all versions prior to Access 2007 it was almost always suggested to link to external files with a Text DataType. Using a Text DataType field which holds the full string directory path and file name of the external file at the Table level would enable you to work around the bloating issue and still utilize external files within your application. However, one of the drawbacks of this set up is to always depend on an external folder holding your files which itself always needs to be moved along side the application.

The Attachment DataType can eliminate this set up by allowing you to directly embed files within your application. If the files are not already compressed Access will store them in a compressed manner for you to keep the ACCDB file size as small as possible.

Though the 2GB file size limit still might stir you in a direction of a set up mentioned earlier with storing the files externally and linking to them. Another alternative would be to create an ACCDB file solely for holding your files in a table utilizing the Attachment DataType and then linking to that ACCDB file from your application file. One scenario where either of those methods might be more suitable could be the plan to utilize an extremely large amount of files within your application or the file size of individual files being somewhat large.

Manual usage of the Attachment DataType:

Let’s start using the Attachment DataType. Create a new Table in your ACCDB file and switch to Design View. Add an Attachment DataType field to the design. Your Table might look similar to what is shown in Picture 7.1:

We can now switch to Datasheet View and just add a couple of records including several attachments. I will just use sample images provided by Windows in the My Pictures/Sample Pictures directory. Double left clicking on the Attachment field will invoke the Attachments dialog which will allow you to Add… images to the multi-valued field.

You should also see further options to individually save the attachments back to a file or do so with all of them as well as opening and removing them from the field (see Picture 7.2).

The Attachments dialog will also be available at the Form/Report level if you want to allow the user to interact with the field values.

Now that we have set up a sample Table holding sample data we can keep moving. Let’s create a form based on this Table to display the images we stored internally. If you just click the Form Ribbon button of the Forms group behind the Create Ribbon tab while having the newly created Table selected in the Navigation Pane you might see something similar as shown in Picture 7.3:

If you double left click on the image displayed you should receive the earlier discussed Attachment dialog which allows you to interact with the attachments. Single left clicking on the image invokes a little navigation bar at the top of the control which allows you to navigate through the attachment values or launch the Attachment dialog (see Picture 7.4).

So what if you wanted to actually display all attachments that one particular record holds on your Report. We can achieve that as well. First let’s create a Query. Click the Query Design button in the Create Ribbon Tab (under the Other group). In the Show Table dialog pick the Table you have created that holds your attachments. Now you should see in the Field selection area of the Table you picked that the Attachment DataType is actually split up in three different parts (see Picture 7.5).

It consists of the FileData, FileName and FileType. The FileData is the actual binary data that Access stores internally. The FileName is the name of the original document and the FileType holds the extension of the original document. In my case the FileType is JPG since I’ve loaded specific Windows sample images into the attachment field. Select the ID, Attachment FileData and any other Field you might want to display on your Form/Report. If you now run the Query you should see a separate Record for each attachment value the multi-valued field holds (see Picture 7.6).

You can now create a Report with specific grouping which will show you all values returned by the FileData field per record.

In case you have decided to not utilize the Attachment DataType field (for one of the reasons mentioned earlier) and to use a Text DataType field instead to store the full paths/file names to external images. You might find it interesting that the Image Control now supports a Control Source property like any other control. This means you can bind it at the Form/Report level to your Text DataType Table Field and it should display the external images correctly without storing them internally and without one single line of code.

Programmatical usage of the Attachment DataType:

Now that we have looked at some manual usages of the new DataType let’s go through some programmatical samples to interact with the Attachment DataType. You might not want your user to rely on the Attachment dialog that appears after double left clicking on an attachment control at the Form/Report level. There are several methods of the Data Access Objects library (DAO) that you can use to interact with your attachments in the multi-valued field.

The LoadFromFile method can import a file from you local hard-drive into an attachment field within your application. The SaveToFile method does the complete opposite by allowing you to save an internally stored attachment to a file on your hard-drive. Let’s first look at an example utilizing the LoadFromFile method.

To stick with out prior example let’s assume we have several images in an attachment field which you display on a Form. You might want to allow the users to import additional images to the record field. For simplicity I will hard-code the path to the external file being imported. If you want to implement a browse feature then check the code provided at: http://www.mvps.org/access/api/api0001.htm.

The function wrapped around the API call utilized in that code will return the full string path and file name to the file selected. You can store that in a variable and pass it along to the LoadFromFile method instead of using a hard-coded value as described in Code Example 7.1:

On Error GoTo Err_AddImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.Edit

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.AddNew
rsChild.Fields("FileData").LoadFromFile ("c:\Sunset.jpg")

rsChild.Update
rsParent.Update

Exit_AddImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_AddImage:

If Err = 3820 Then
MsgBox ("File already part of the multi-valued field!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_AddImage

End If

You can add the code to the on click event of a command button which is on the same Form that houses the attachment control that is bound to your Table attachment field. The only requirement is that the file does not already exist in current record of the multi-valued field. If it does and you try to execute the code you will receive a Runtime Error 3820. The error handling code included in Code Example 7.1 should eliminate this problem.

Now to reverse the action we just took we can re-save our internal file back to some local directory. If you want to include the ability for the user to browse to a directory and specify a new file name you can implement the earlier mentioned API call. In this small sample I will again just save the file to a predefined hard-coded location on my local drive (see Code Example 7.2).

*** Note: Programmatically interacting with the root drive/top directory under Windows Vista might require special permissions/settings! ***

On Error GoTo Err_SaveImage

Dim db As DAO.Database
Dim rsParent As DAO.Recordset2
Dim rsChild As DAO.Recordset2

Set db = CurrentDb
Set rsParent = Me.Recordset

rsParent.OpenRecordset

Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.OpenRecordset
rsChild.Fields("FileData").SaveToFile ("c:\")

Exit_SaveImage:

Set rsChild = Nothing
Set rsParent = Nothing
Exit Sub

Err_SaveImage:

If Err = 3839 Then
MsgBox ("File Already Exists in the Directory!")
Resume Next

Else
MsgBox "Some Other Error occured!", Err.Number, Err.Description
Resume Exit_SaveImage

End If

If the file already exists in the specified directory you will receive a Runtime Error 3839 which the error handling code should trap. The file will be saved to the exact same file name it used to have when it was embedded into the attachment field.

Removing an attachment from your attachment field with code can be as simple as utilizing the DAO Delete method with the RecordSet you have opened based on the multi-valued field. You can easily adjust the Code Examples 7.2 or 7.3 to implement a delete feature:

…
Set rsChild = rsParent.Fields("AttachmentTest").Value

rsChild.Delete
Me.Requery
…

Requiring the current Form object ensures that the changes are being immediately reflected on the Form.

We can now further utilize the code example which saves an attachment back to a file. What if you wanted to email and internal attachment to someone from within Access?

Unfortunately the build in SendObject method does not support any way to interact with the new DataType. Additionally since the SendObject method only allows internal database objects to be attached to an email we will have to use a more flexible method of sending an email. If Microsoft® Outlook is your default email client you can use the automation code shown in Code Example 7.3:

Sub SendEmail(Optional AttachmentPath)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment

Set objOutlook = CreateObject("Outlook.Application")
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

With objOutlookMsg

Set objOutlookRecip = .recipients.Add("YourRecipient")
objOutlookRecip.Type = olTo

Code Example 7.3 uses early binding so you would be required to set a reference (VBA Editor--Tools--References…) to the appropriate Microsoft Outlook 12.0 Object Library (12.0 if you are using Outlook 2007).

Using Code Example 7.2 in combination with Code Example 7.3 would allow you to create an email using an attachment displayed on your current form. You could execute the procedures behind the on click event of a button as shown in Code Example 7.4:

Call SaveAttachToFile
Call SendEmail("c:\" & Me.AttachmentTest.FileName)

As you can see we are utilizing the FileName property of the attachment control to refer to the name of the file we saved with the SaveAttachToFile procedure. After the email has been created through automation we can go ahead and delete the file from your local directory.

Further examples of programmatically interacting with the Attachment DataType can bee seen in my recent blog posts here and here. They deal with moving files from one Attachment DataType field to another one in a different table as well as retrieving the actual file size of an attachment stored in the multi valued field.

This finishes our little introduction to the new Attachment DataType. We have looked at manual as well as programmatical examples of how to interact with the DataType. Hopefully this tutorial will enable you to fully use all the capabilities of this new feature and spice up your applications a little.

Microsoft® Access 2007 Working with the Rich Text Feature

Download Tutorial 08 (494 kb zipped)
(Click link or right click--Save Target As...)

In this tutorial we will have a closer look at the new Rich Text feature introduced in Access 2007. I had made brief comments about it in prior tutorials so it is time to introduce more details. This tutorial will be a little shorter just based on the content amount but we will go through some general information about the Rich Text feature as well as manual and programmatical usage samples.

General Information about the Rich Text Feature:

Microsoft® Access 2007 introduces a further very useful feature. The only way to format certain data for display purposes in all versions prior to Access 2007 was to either split up the content to be held in several different controls and format each control appropriately or to use a long winded workaround implementing third-party solution. No matter if you work in the MDB or ACCDB file format you can now utilize a build in solution for such scenarios. Let your users individually format the content of controls without much effort on your part.

The Rich Text feature in Access 2007 actually utilizes HTML tags rather then Rich Text Format encoding. This has several benefits if you work with SharePoint or HTML files from within Access. If you happen to use a prior non-native Rich Text control that utilizes RTF encoding and you want to convert to the internal new feature you might need to remove the encoding and reapply it in HTML tags.

The property we will be working with is called the Text Format property which can either have a value of Plain Text or Rich Text. You can set this property either at the Table or Form level. Let’s begin by creating a small new sample Table as shown in Picture 8.1:

As you can see in Picture 8.1 a Memo DataType field has the new additional Text Format property that you can set to Plain or Rich Text. If you switch to the Rich Text Format you will receive a warning message informing you that all the data the field contains will be HTML encoded (see Picture 8.2).

Now that we have created our small sample Table we can go ahead and create a Form based on it. Selecting the Table in the Navigation Pane, going to the Create Ribbon Tab and pressing the Form button in the Forms group should auto generate something similar to shown in Picture 8.3.

If you switch the Form to Form View and start entering some text in the control bound to your Memo Table field and then highlight certain content you should see the Quick Format Toolbar appear above your selection (see Picture 8.4).

Switching the Form to Design View and opening the property sheet of the bound control reveals the same Text Format property at the Form level, too. Since we auto-generated our form the property value was inherited from the underlying Table Memo field (see Picture 8.5).

Since the data of a Memo field with the Text Format property set to Rich Text is stored in HTML tags we can easily interact with it in a programmatic manner, too. To actually see how Access stores the content internally you could create a new Query based on your test Table and set the Text Format property of the Query Field to Plain Text. You can see the comparison of the formats and how Access actually stores them in Picture 8.6.

If you are not familiar with HTML here are a couple common Tags that you might find useful in interacting with the Rich Text feature programmatically (see Table 8.1).

There are much more HTML Tags you might find helpful. If you are interested in a more complete list then check the www.w3.org website.

So let’s work with a couple basic HTML tags and assign a value programmatically to a control that has its Text Format property set to Rich Text.

Utilizing the on click event of a button you could assign the following value to your control:

Me.YourControl = "This is a" & "" & " Test " & "" & _
" you can use " & " " & "" & " HTML Tags " & "" & _
" for your " & " " & _
"" & _
" Formatting!" & ""

If you switch to Form View and press the command button you should see something similar as shown in Picture 8.7:

One good usage of interacting with the internal values stored in a Memo field, that has its Text Format property set to Rich Text, might be to send actual formatted emails to your users, clients, etc.

This is now easier then ever. Let’s say you have a Form control that is bound to an underlying Memo Table field and you want to include the value the control holds in your email formatted the exact same way it is within Access. If your default email client is Microsoft® Outlook we can use automation code to automate an actual HTML email and include the value since it is stored internally with HTML Tags.

Code Example 8.1 uses early binding so you would be required to set a reference (VBA Editor--Tools--References…) to the appropriate Microsoft Outlook 12.0 Object Library (12.0 if you are using Outlook 2007). Utilizing this method obviously requires the recipient of your emails to allow the HTML format.

Sub SendHTMLEmail(Optional ctlBody)

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)

With objMail
.To = "Your@Sample.com"
.CC = "Your@Sample01.com"
.Subject = "Sample Subject"
.BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & ctlBody & "</BODY></HTML>"
.Display
End With

Set objMail = Nothing
Set olApp = Nothing

End Sub

Code Example 8.1: Creating an HTML Email through Outlook Automation

You can call this procedure from the on click event of a button on your Form and pass along the name of your control holding the formatted content e.g.:

If you press the button in Form View you should see something similar as shown in Picture 8.8.

With that we wrap up the tutorial on how to interact with the Rich Text feature. We have gone through some manual as well as programmatical samples of how to implement the new feature in your applications. We have also talked about some general information concerning the Rich Text element and you hopefully will appreciate this native support as much as I do.

Today we will have a look at yet another great new feature introduced in Access 2007. Have you ever been in the situation of needing to send or retrieve information into your Access application from clients who are infrequent users of your application and not directly connected to it at any point in time? I have been there and done that and can say from experience that your options as a developer are pretty limited. The simplest but yet still long winded workaround was normally to export internal application data to an Excel workbook or text file and sending that with emails as attachments. After the external user reviews, edits, updates, or adds data they would reattach the file to a new email and send it back, which started a whole new problem of importing everything back into the application. As with many other things Access 2007 makes our life so much easier with an internal feature for data collection through emails.

The new email data collection feature supports two main approaches for working with you application data in email forms. You can create an HTML or InfoPath email form which specifies some limitations on your recipients. Your external users either need to allow HTML content in their default email client or they would need to have Microsoft® InfoPath installed. InfoPath is part of the Office Professional suite and higher (see Table 1.1 in Tutorial 01).

Besides being able to collect new information for your application through these email form types you can also send data for review, edits and updates. If you are utilizing an InfoPath form your users will also be able to add new data while reviewing existing information.

The data collection wizard is pretty straight forward and will walk you through all possible options including the feature to automatically import updated and new data back into your application as soon as the returned email arrives in your inbox. We will look at a detailed step by step sample in the next section.

Unfortunately the Access team did not implement any object model to work with new feature in a programmatical fashion. So we will restrict this tutorial on manual usage only. However, we will discuss this “short coming” a bit more later on.

Let’s walk through the new data collection feature by demonstrating it with a step by step example. To make things easy I will use data from the well known Northwind sample that has been shipped with Access for a long time.

Since we should familiarize ourselves with Office templates we will quickly go through the process of downloading and creating a new application based on an online template. If you are not connected to the world wide web you can also find the Northwind sample as a local template under the Local Templates category in the Getting Started screen.

You can create a new application based on the online template by downloading it in the Sample category on the Getting Started screen when you start up Microsoft® Access (see Picture 9.1).

Since Microsoft® templates require a genuine validation process you might receive a dialog informing you about it taking place when you try to download the sample.
You can select to not show this dialog anymore if you want (see Picture 9.2).

After the template is downloaded Access will create a new ACCDB file based on it and save it in the location specified. Access will give you feedback of the creation process (see Picture 9.3).

Now that we have created a new ACCDB file filled with sample data we can go ahead and explore the new Data Collection feature. Let’s assume your application fulfills similar tasks as the Northwind sample and that you keep track of employee information. Your company just hired a new employee and you want to update the application so it reflects the new employment changes. Let’s also assume that you are a lazy developer like I am and that you don’t want to continuously interact with clients/users to get the data you need. This is where the great new Data Collection feature comes in very handy.

There are several ways you can use to start the Data Collection Wizard. One would be to select the Table or Query Object you want to use as the source in the Navigation Pane and then press the Create E-Mail button on the Collect Data group of the External Data Ribbon Tab (see Picture 9.4).

Another approach would be to right click on the Table or Query in the Navigation Pane and selecting Collect and Update Data via E-mail (see Picture 9.5).

The first page of the Data Collection Wizard just informs you about what features you could possibly use with the Wizard. After clicking Next you will be given the choice to either use an InfoPath or HTML form. For now we will stick with an HTML form as I assume it will be more widespread usable. As a reminder your recipients will need to have HTML email content enabled or Microsoft® InfoPath installed if you would be utilizing an InfoPath form. On the next screen you can choose to either just collect new data or update, review existing data. Since we only want to retrieve new data from our new employee we can go ahead and select the Collect new information only option and click Next again. The following screen will allow you to select what fields you want to include in the email form. You might pick the fields as shown in Picture 9.6:

If you only want your users to review certain data you could specify them as Read-only on this page, too.

On the next page you will have to decide how you want the email forms to be processed after you receive a reply. One of the nice features is that you can automatically process the data if you receive a reply without any sort of code or manual interaction. All you will have to do is select the Automatically process replies and add data to YourTable option.

If you decide to automatically process replies you can set further options within the Data Collection wizard as shown in Picture 9.7.

We will only allow a single reply from our new employee as well as setting a time line when to stop processing replies. Everyone knows that new employees don’t have any real work to do yet so they should have plenty of time to respond to your request ;)

A further option on the next page is to either enter the new employee’s email address manually or retrieve it from a table field. Since we are trying to retrieve new information from a new employee we might not yet have any data stored internally so we will just go ahead and enter it manually within Microsoft® Outlook after the email has been created for us.

The second last page of the wizard will let you enter a custom subject for the email as well as instructions for your users to follow when filling out the email form.

The last screen allows us to press the Create button to initialize the email. In case you created a new application and did not yet close and reopen it you will receive a warning about not being able to utilize the automatic processing feature because of an exclusive lock being active on the database (see Picture 9.8).

An application will be open in exclusive mode if it has just been created. Once you close and reopen the application the exclusive lock should be released automatically and you should be good to go with the auto processing feature.

After pressing the Create button on the last Wizard page you should see a new email coming up on your screen as shown in Picture 9.9.

We can now manually enter the recipients email and send it off. Before the recipient will be able to fill in the email form fields they will have to hit replay in their email client to enable data entry for the form fields. Since we selected the option to automatically process replies when they arrive in our inbox you should see a new subfolder under your inbox. Replies to your email forms will be automatically moved to it when they arrive (see Picture 9.10).

As soon as the email arrives you can check back your table in your Access application and you should have the user’s replied information as a new record (see Picture 9.11).

While you are waiting for replies you can also continue to manage the email forms you have already created or send. The Manage Replies button in the Collect Data group on the External Data Ribbon tab invokes a dialog that lets you work with your earlier emailed requests (see Picture 9.12).

If you decided to not automatically process the replies you will receive, you can use the Export to Access button directly within Microsoft® Outlook 2007 (see Picture 9.13).

Utilizing that feature will launch a dialog that will allow you to review the values returned and add them to your application table (see Picture 9.14).

You will receive a confirmation message if the data has been successfully exported to your Access table.

Picture 9.15 illustrates an email sent with an InfoPath form. This allows your recipients not only to review or update data but also add new data records within the same email. As the sender of the email you can also disable this feature if you only want your users to review or update data.

Picture 9.15: InfoPath Email with Existing Data and an Option to Add New Records

As earlier mentioned there is no Object Model exposed which would allow us to interact with this new feature through code. The closest you will probably get to automating the process would be to use the new acCmdCollectDataViaEmail constant of the RunCommand method in combination with some SendKeys commands. However, for known reasons I would not suggest falling back on using SendKeys.

A further limitation you might experience with the new feature is mentioned in the following article:

<Quote:>
You cannot process a data collection e-mail reply when the query that the e-mail was based on contains an Access 2007-defined function.

You cannot process a data collection e-mail reply when the query that the e-mail was based on contains a Microsoft Office Access 2007-defined function, such as the PlainText function in Access 2007. The e-mail reply does not process automatically into Access 2007, even if you select this option. You may notice that the data collection status in Microsoft Office Outlook 2007 displays the following information:

Failure: Undefined function 'FunctionName' in expression.
Note FunctionName represents the name of the function that you use in the query.

Additionally, you receive the following error message if you manually process the data collection:
Cannot export data due to errors. Failure: Undefined function 'FunctionName' in expression.
…
< /Quote>

With that we finish up this tutorial. We went through a detailed introduction to the new Email Data Collection feature. Furthermore, we looked at a detailed step by step illustration of implementing it in one of your applications. Hopefully you can find many good uses of this great new Access element in your future Access application development.