Ready to consider upgrading to Access 2010 or Access 2007?

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

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

Access 2007 - Field Template List

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.

Access 2010 - Application Parts

The datasheet 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.Access 2007 - Field List - New FieldThere 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.

Access 2007 & 2010 - Date Picker ControlExclusive 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).

Access 2007 & Access 2010 - Ribbon BarsOther 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.

Access 2010 - Navigation PaneUse 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).

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

Access 2010 - Backstage ViewIn Access 2007, to manage similar functionality, you use the Office button located in the top left corner of the Application Window.

Access 2007 - Office OptionsCreating Objects
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.

Access 2007 - Report Group PaneForms 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.

Access 2010 - Macro Designer

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.

Other Improvements
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 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

This entry was posted in Forms, Macros, MS Access, Queries, Reports, Tables, Utilities, VBA and tagged , , , , , , , . Bookmark the permalink.

11 Responses to Ready to consider upgrading to Access 2010 or Access 2007?

  1. Ben says:

    Please outline the steps you took to upgrade and then we can see what other checks you could also apply.

  2. Anna Abduel says:



  3. Ben says:

    Hi Peter,
    Good question, never had to do this (yet).
    If the tablet software has a library file (API, DLL or similar) that connects to VBA (in Access) then yes it can be done.
    Otherwise, where is the data going to be stored which maybe the alternative way to collect data (via web, Access services).
    The first place I would enquire if the third party vendor to the tablet device and see if they have a SDK (Software developers kit).

  4. Ben,

    Can Access2010 take a signature directly off a tablet into a control box? My company uses paper and pen to record deliveries, but we are switching to collecting data in the field with a tablet. I’m told 2010 can accept a signature off the tablet. We have used Access2003 for many years now with few complaints (so the GUI is not in color), but getting it to accept a signature from a tablet has me puzzled. 2003 can reference an image file that is already saved. But how can I go directly off the tablet into a field on the database?

  5. Ben says:

    Yes, if you mean linking or importing data between the two applications.

  6. Mary says:

    Hi. It is possible to impact data from Excel 2007 into Access 2010?

  7. Janaye says:

    Wham bam thank you, ma’am, my questions are anserewd!

  8. benb says:

    Thanks – What is it about the site you would like to know?

  9. sales tax says:

    If you could e-mail me with a few suggestions on just how you made your blog look this excellent, I would be grateful.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Confirm you are human.