Software

Microsoft Access 2010 new feature overview

Derek Schauland introduces some of the new features you can expect to see in Access 2010, including a Web-ready format and an improved macro environment.

Microsoft Access is one of those applications you either love or loathe. Some of the features are quite useful for smaller databases, like address books and CD/DVD collections, but the 2GB database file size limitation is constricting. The Office 2010 release of Access offers some improvements that make the application more usable, including reliability enhancements.

This post will look at some of these new and/or improved items to bring you an overall picture of just what Access 2010 brings to the party this time around.

Web-ready database format

The Web is the new desktop in some environments. The Access team took this into consideration and provided a Web-ready format for databases. While previous versions of Access allowed publishing to the Web via scripting technologies to access the data, Access 2010 makes Web publication even easier by making use of the Web Database type.

Macros

Another change to Access 2010 is the inclusion of a better macro environment. Macros function more like SQL triggers. They can be launched conditionally based on actions taken within the database, which not only improves performance, but also can improve the user experience by allowing tasks to be automated.

Figure A

The Access 2010 Data Macro Environment

Themes

Office themes are supported in Access 2010, making customizations to the appearance of databases much easier and more uniform than in the past. When using a form in Access, you can apply a theme to it, similar to the way themes can be applied in Word and PowerPoint. This will help keep the appearance of your forms unified across the application. Themes can also be applied to reports created in Access.

Groups of fields

Access 2010 changes the way that fields are added to the database by using a better list of fields to insert. The Data Type gallery replaces the Add Field task pane and contains all of the common field types for use in a database. One handy new feature here allows you to add certain groups of fields, called Quick Start selections, as a collection rather than adding one field at a time (Figure B). For example, when you select the Address Quick Start, multiple fields are inserted:
  • Address
  • City
  • Country_Location
  • State
  • Zip

Figure B

Quick Starts allow you to add fields as a collection.
In addition to the data types already available in the Add Field fly-out, you can add your own to the list. You can save selected fields as new, user-defined data types. When saving fields for later use, you can categorize them under any section within the Add Fields fly-out, with the default being User Defined. Figure C shows the Add Field option.

Figure C

Add Fields from Data Type gallery.

Reporting

Access 2010 allows conditional formatting within reports, showing the information that meets a certain condition or set of conditions. To use or change the conditional formatting rules in a report, open the report and choose the Format tab on the Ribbon. Then, select the Conditional Formatting option.

The Conditional Formatting Rules Manager (Figure D) lists the rules will be displayed. Just choose the field for which you want to add/remove/modify formatting rules. The rules for the selected field are displayed in a box below.

Figure D

Conditional formatting

Expression and Query Builder use Intellisense

Microsoft has included Intellisense in the Expression and Query Builder portions of Access. Intellisense helps complete the field names and other items you might use in these areas. This reduces the margin of error when working with queries and expressions, as well as the time spent looking up the spelling of field names in large databases.

Conclusion

These are a few of the key features I have discovered when using Access 2010. The biggest items by far for me are the macro changes and allowing them to function similarly to SQL Triggers. Access 2010 also seems a bit friendlier than previous versions. Now if only we could get Microsoft to increase the 2GB limit on file sizes.

About

Derek Schauland has been tinkering with Windows systems since 1997. He has supported Windows NT 4, worked phone support for an ISP, and is currently the IT Manager for a manufacturing company in Wisconsin.

11 comments
steve.antonius
steve.antonius

I think it's a misleading put-down to state Access features are "...quite useful for smaller databases, like address books and CD/DVD collections..". Sheez! What with the 2 GB limit 'n all. We have an R&D database of over 110,000 records, reports, queries, etc and manage to keep the size to around 110 MB. Enough of the DB snobbery!

Saby George
Saby George

It has good features but to be included tool kit to convert Exe File.

mokokamello
mokokamello

"if only we could get Microsoft to increase the 2GB limit on file sizes" .... pleeeeeeeeeeeeese do we need to make a vote about this

kevaburg
kevaburg

This was available in Access 2003. Certainly can't be called new for 2010!!

sitebro03
sitebro03

I'm glad that Microsoft have finally seen the need to incorporate a web publishing feature into MS Access. I build databases for my small business clients and some of them have been frustrated by the fact that they couldn't publish their Access databases on the internet/intranet without the use of some server application and hardware. There are businesses out there who would welcome this development. As not all businesses can afford the expense of setting up web and/or MS Office Sharepoint services(to publish Access 2007 databases), this will be a relief.

Bob_jnr
Bob_jnr

Our organisation currently uses Access 2003 and I'm not sure how much change has taken place to Access since Access 2003. In an organisation that has over 40000 employees and hundreds of Access databases, there is as you can imagine tremendous stress on the network when you run an Access database that is stored on a network drive. Does Access 2010 offer any solution in that respect. Yes, I know SQL server is much better suited for multi-user environment, however there would be huge performance gains for many organisations if for example when querying an Access database on the network, only the results were retreived through the network connection, not the entire database.

Derek Schauland
Derek Schauland

but would they be enough for you to really dig into Microsoft Access?

kkohler
kkohler

I have had a split Access database using the _be as a data warehouse. It has over 14,000 primary records with 5 child tables, one with over 1.1 million records (8,000+ for its primary record. The _be is about 300+ MB. It has had CONSTANT partial deletes/imports for over 7 years without a single hiccup! It even is MORE trusted for its data accountability than the mainframe application that provides the warehouse data! Alas, we got an official DBA, and Access is 'going away' for our organization. . . I don't know who is going to recreate at least 15 applications in .net, Visual Studio and C# during the next 10 YEARS. . Thanks to you Tech Republic for ALL of the forums that you provide and users insight as well.

md111
md111

I understand exactly where your coming from but we have a furniture database where we include all the photos and specs to the database so salepeople can get at it. having looked at it we're up to 1GB (although not sure how) so worried we'd be at 2GB fairly quick

b.schilling
b.schilling

Bob you've already identified the answer, SQL Server. SQL Server does return only the result of a query and is built with network access in mind. That's why Microsoft doesn't add to the capability of the Jet engine. They have a product designed for upsizing. The even have a free version to get you started.

dsomerv
dsomerv

Every new version of Access disables or changes some features ... this is true in going from 2003 to 2007 and no doubt to 2010. For example, the ribbon was introduced in 2007, and custom command bars either no longer worked or would work only if created in 2003 and then imported to 2007... a real pain to get any mildly custom database converted. I'd sure be reluctant to switch from 2003 in your case, with so many users and no doubt many applications. To reduce the stress on the network, why not split the Access database into two parts (use the splitting tool that Access provides) - the application (reports, forms, etc) and the data (tables and some queries); then use SQL (SQL express is free and works great and comes with a nice management tool)to handle the data portion - easily loaded from access into sql . THen the data is separate and served out / managed by SQL and you still have the convenience of being able to create and change forms and reports on the "front ends" easily.

Editor's Picks