“Ready to consider upgrading to Microsoft Access 2010 or Microsoft Access 2007?…It may well be worth it!…”
If you have been using Microsoft Access 2007 for a while now, moving to Access 2010 will be seamless and all you need to do is understand what new features have been introduced and what it has also replaced (where applicable).
However, a lot more users haven’t yet upgraded to Microsoft Access 2007 and are still using Access 2003 (or even an earlier version). So I’ve decided to include and combine the latest two versions and their new features to help with the transition and migration.
I will clearly point out where a feature is exclusive to either version so that you have the heads up and understanding of what you will expect to see and more importantly how to access the feature due to new GUI (graphical user interface).
Microsoft Access 2010 (& 2007) offer plenty of templates that allow users to create databases, tables, queries, forms and reports with a simple one click action. There are new layout views for the form and report objects and design tools to assist in managing data.
In previous versions, wizards were provided when creating a database or building new tables. Now templates make it easier to start the process taking you straight into the application’s environment (and in most cases a help window is displayed).
Microsoft also provides a wealth of free downloadable templates and could save a lot of planning designing time.
The obvious aspect when creating a new database is the default new file extension .ACCDB replacing the older .MDB format. This must be used in order to take advantage of the newer features on offer but if you really insist on staying with the previous file format then not a problem, just don’t expect to use any of the richer tools that are exclusive to .ACCDB.
Note: If you are upgrading an existing .MDB file to .ACCDB be careful as some of the features from Access of old is no longer available. Continue reading this article to know a little more about your options.
Tables and Fields
In Access 2007, table (and now field) templates makes your planning and decision process even easier as it now provides pre-defined fields names with preset properties.
In Access 2010, these similar features appear in a different way via the Fields – Ribbon Bar.
In addition, Access 2010 exclusively introduces the ability to add Application Parts to an existing database saving a lot of design time of tables, forms and reports.
You simply choose the template theme for your additional objects and the system generates its for you.
The data sheet view for a table has changed too. You can add new fields on the fly and set their data types and formatting. Copy and paste from Excel is now more consistent as Microsoft Access will fit to size and automatically change the data types.
New from Access 2007 is the Field List pane which lets you add existing fields from other tables (not necessarily related ones) and embed them into your new table. If there is a potential relationship between the two tables, a prompt will appear to help you link them together.
There is a new data type and control types for fields in a table including Attachment, Multi-Values, Enhanced Memo and a built in Calendar control.
The Attachment data type allows you to store external documents as pointers to the source file without impeding database file sizes and it is not restricted to only a single document (as OLE Object is) it can handle multiple files too.
The Multi-Values control can store more than one value for a list or combo box control creating a multi value store. Handy for capturing multiple category types without using separate fields or records.
The Enhanced Memo control is enabled with the Rich Text property setting to the Memo data type and opens up character formatting.
The Calendar Control is utilised as an option (by default) to display a date picker control when the cursor is placed in the field.
Exclusive to Access 2010 is the ability to now calculate directly into a table with fields of that table only using the Expression Builder tool when adding it as a new field.
General table functionality introduced from Access 2007 includes the ability to change alternating colour backgrounds, adding total rows with basic aggregate functions (sum, average, count etc.) and enhanced sorting and filtering tools.
New Look Interface
One of the obvious change users will see is the new look and feel of the Access GUI (Graphical User Interface) which includes the Ribbon Bar (Microsoft Office Fluent user interface) replacing Command Bars (Menu and Toolbars).
Other tabs (Contextual) dynamically appear and disappear based on the task and object in use. It makes Access more accessible without the need to return to a central point and is deemed more intuitive to the user (especially if you have never used an earlier version). Added galleries now show a preview before it is applied.
Existing users may feel a little dis-orientated at first but it won’t take you too long to adapt and you’ll look back and wondered what the fuss was all about.
Access of old had a Database Window which is where you managed all the objects. Now we have the Navigation Pane which is positioned to the left edge of the application window.
Use the Navigation Pane to manage and organise all the objects by dates, types, dependencies or even a custom group of your own design. There is an easy way to collapse and expand this view using the double chevron icon (top right hand corner).
Also new for Microsoft Access 2010 & 2007, is the Tabbed Objects feature which is the default and is displayed along the top of working area which is in essence a way to lock and maximise the view of each open object. This setting can be changed back to the independent window (as in the previous versions).
Alongside the traditional Design and Open view, there is a new view called Layout View which applies to forms and reports.
It is a combination of both the design-time and run-time views for a form or a report which allows you to preview the data and carry out some basic grouping and formatting. It has been extended in Access 2010 and must be the view used when designing interfaces for the web.
Exclusively new to Access 2010 is the Backstage View which is where you manage the database system including Compact and Repair, creating new databases, setting Access options and publishing your database.
In Access 2007, to manage similar functionality, you use the Office button located in the top left corner of the Application Window.
Now it is far easier and more intuitive to create new objects for forms and reports with a single click action. In addition to the traditional Wizards (which are still present), by picking a table or query first, pre-defined template forms and reports are created with the formatting and layouts all in place (even logos) giving you a head start to the design process.
Note: Pages have been dropped from Access 2007 and you can still use this object if working with .MDB file formats. In Access 2007 and 2010, you now have better web compatibility and Pages has now been integrated in other layout objects.
Reports have a layout view which is now more interactive and further enhanced in Access 2010. This view is where you make layout and formatting changes whilst in the browsing view and therefore no need to switch between the design view and print preview mode. The print preview is now a better tool too with extra functionality and better rendering.
Report designs provide better grouping and sorting functionality with an easy and more intuitive utility of a sort and grouping pane (bottom of the design view) – Group, sort and totals are managed here.
Forms and reports have better control layout options to help manage tabular and column groups of natural data sets. Grouping blocks of fields together (Stacking) keeps design controls under control when moving, resizing and formatting.
Split forms are new to Access where a form in the upper half is a Form view and the lower half is a Datasheet view and can be positioned to the top, left and right of a form.
Conditional Formatting has been improved further with ability to add Data Bars (Access 2010 only) that calculate against other values with a gradient indicator making numbers for legible.
There are now more (and better) themes and professional designs for forms and reports allowing you to customise your own colours and fonts styles.
Importing and exporting Access objects is more intuitive and the task can be saved for re-use at a later point. This also extends to importing and exporting data to and other formats. The Import Spreadsheet Wizard lets you override data types that Access chooses for you.
From Access 2007, you now have the capability to export a report as PDF file (and XPS) – great news indeed!
From Access 2010, the macro designer tool is completely new offering IntelliSense and a far user-friendlier interface with inline arguments.
More keyword commands are available making a good case to handle most the standard procedures instead of having the need to use VBA (Visual Basic for Applications).
Data Macros are also a new feature where you can now code events to data changes in a table and trigger certain actions. For example, you may want to set a status for a limit of credit to a customer and flag it as ‘Authorised’ or ‘Pending Authorisation’.
From Access 2007, macros used in forms and reports now have an Embedded feature which is stored with the form or report and not as a separate macro.
Using the .ACCDB file format, you no longer have multi-user level (workgroup) security (you must keep the file as an .MDB). You still have Encrypt with Password option and the file must first be opened in Exclusive mode before you can apply it.
Access files should be trusted (by location) which is managed in the Access options tool and now security is enhanced further with Windows SharePoint Services.
Enhanced Expression Builder – using IntelliSense so you can see what you are typing with display prompts.
Improved proofing tools – better spell checker tool.
Better functionality with external data especially the web.
Integrating the Windows SharePoint Services offering better collaboration and permission setting.
Having migrated to Microsoft Access 2007 and now working with Access 2010, I’m a convert. I still use Access 2003 for some of my clients and realise how much users are missing when trying to design standard forms and reports with added macros.
The difference between Access 2010 and 2007 is not major unless you are going to use the following two features – Macros and Web Services.
Access 2010? Access 2007? or staying put?
Written by me, Ben Beitler