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