Office Access 2007

Migration considerations for Access 2007

Updated: 2007-03-08

With Microsoft Office Access 2007, you can open and use databases created in Access 2000, Access 2002, and Access 2003. Using the changes and improvements in Office Access 2007, you can also convert databases created with previous versions of Access into the new Office Access 2007 file format. Databases created with Access 97 or earlier must be enabled or converted for use with Office Access 2007. This topic discusses database migration considerations, including:

Migration considerations for Access 2000, Access 2002, and Access 2003
Migration considerations for Access 97 and earlier
Office Access 2007 in mixed environments
Office Access 2007 and SQL Server
Tools to help with your conversion project

Migration considerations for Access 2000, Access 2002, and Access 2003

Databases created using Access 2000, Access 2002, and Access 2003 do not need to be converted for use with Office Access 2007. You can open the databases and modify data and object design in Office Access 2007. You can convert databases from MDB file format to ACCDB file format to enable new functionality.

Most functionality in previous versions of Access is available in Office Access 2007, with some exceptions.

Features available only in the new file format in Office Access 2007

The following features are available only with databases that are in Office Access 2007 ACCDB file format. To use these features with existing databases, you must first convert the databases to Office Access 2007 ACCDB file format.

Complex data (multi-valued data types)
Attachment Date type
Append Only Memo fields
Compressed image storage for any Picture property
E-mail database as attachment
Publish database to a Document Library in Microsoft Office SharePoint Server 2007
Full support for Linked Tables to Office SharePoint Server 2007
Offline support for Linked Tables to Office SharePoint Server 2007
Linked Tables to files in ACCDB format
Encrypt with database password

Features available only in MDB file format

The following features are available only with databases that are in Access 2003 or earlier MDB file format. They are not available with Office Access 2007 ACCDB file format.

Ability to open the database with previous versions of Access
Object Level Security (also known as Workgroup Security)
Database replication
Encode database (replaced with Encrypt with database password)

Features no longer available in Office Access 2007

The following features are no longer available in Office Access 2007:

Data Access Pages (DAPs) cannot be opened using Office Access 2007.You must use Access 2003 or earlier to create or make design changes to DAPs. To browse DAPs, you must use Internet Explorer. To browse Access 2000 DAPs, you must install Microsoft Office 2000 Web Components, which installs with Access 2000. To browse Access 2002 and Access 2003 DAPs, you must install Microsoft Office XP Web Components, available at Office XP Tool: Web Components (http://go.microsoft.com/fwlink/?LinkId=36954).
Microsoft Office XP Web Components is not installed with Office Access 2007. Forms in PivotTable or PivotChart view still function correctly. Databases with references to OWC10.DLL point to the new OFFOWC.DLL. The new OFFOWC.DLL does not support all of the functionality in OWC10.DLL. In some cases, you might need to download and install the Microsoft Office XP Web Components.
Toolbars used in previous Access versions are not used by default. They are used only if the following Startup options are configured:
- The Allow Built-in Toolbars option is disabled
- A default menu bar is specified.
The user interface for toolbar and menu customizations is removed and replaced by the new ribbon. The toolbars and menus can be modified in previous versions of Access or by using the VBA object model or macros.
The user interface for some early import and export formats is removed. There is no user interface to export to ASP or IDC/HTX. There is no user interface to import files from Lotus 1-2-3/DOS (*.wj*) or Exchange. Code and macros created to work with these formats continue to work.

For more information about features that have changed in Office Access 2007, see Changes in Access 2007.

Migration considerations for Access 97 and earlier

When you upgrade from Access 97 and earlier, you must either enable or convert your database files (in MDB format) to open in Office Access 2007. When you open an Access 97 format MDB file for the first time, you can enable or convert the database.

Enabling a database

By enabling a database, you make it compatible with Office Access 2007. You can open objects and edit data, but all object definitions are read-only. You can open enabled databases in Access 97 or Office Access 2007, but you can only make design changes in Access 97. You can make data changes in either Access 97 or Office Access 2007. This option is useful in mixed environments where a database must open in both Access 97 and Office Access 2007.

Converting a database

Access 97 or earlier format databases are converted into Access 2002-2003 format by default. If a database is converted to Access 2002 or Access 2003, the database can only be opened by Access 2002 or Access 2003. To convert an Access 97 or earlier database to Office Access 2007 ACCDB file format, you must first convert it to Access 2002, Access 2003, or Access 2000. You can then convert the database to Office Access 2007.

MDE file limitations

MDE files are MDB files with VBA source code compiled into computer code and VBA source code removed. Office Access 2007 cannot convert or enable an MDE file. To upgrade an MDE file, you must find the original MDB file and convert that file.

Access 2007 in mixed environments

You can use Office Access 2007 databases with previous versions of Access if you save the database in MDB file format. Previous versions of Access cannot open databases in the ACCDB file format. You can change the default file format for databases created in Office Access 2007. The new database template feature requires the ACCDB file format.

Previous versions of Access do not recognize new Office Access 2007 features. In general, previous versions of Access ignore new properties set in Office Access 2007, but these property values will appear again when the properties re-open in Office Access 2007.

The following table shows new features in Office Access 2007 and how the features behave in previous versions of Access.

New Feature in Office Access 2007	Behavior in Access 2000 and Access 2003
ACCDB file format	Cannot be opened.
Complex data	Only available in ACCDB file format.
Attachments	Only available in ACCDB file format.
Append-only memo fields	Only available in ACCDB file format.
Offline support for linked tables to Windows SharePoint Services	Only available in ACCDB file format.
Linked tables to ACCDB database	Only available in ACCDB file format.
Encrypt with database password	Only available in ACCDB file format.
Linked Tables to Windows SharePoint Services V3	Not all data types are fully supported. Some columns may be read-only or might not appear.
Rich text	Appears as plain text with HTML tags.
Date picker	Does not appear.
Gridlines on layouts	No gridlines appear.
Control layouts (stacked and tabular)	Behave like independent controls.
Linked tables to Excel12 files	Linked tables cannot be opened.
Macros embedded in event properties	Event properties appear to be blank.
Control auto-resize and anchoring	Controls do not automatically resize or move.
Tabbed document mode (SDI)	Multiple windows (MDI).
Navigation pane	Database container.
Custom groups in the navigation pane	Does not appear.
Tables and Views mode	Does not appear.
Ribbon	Command bars.
Ribbon customizations	Does not appear.
Saved imports and exports	Does not appear.
Create data collection e-mail	Does not appear.
Manage data collection replies	Does not appear.
Alternating row color (alternate back color property)	All rows appear the same color as the first row. The Alternate Back Color property is ignored.
Filtering and sorting improvements	Previous filtering and sorting user interface.
Report browse mode	Print Preview only.
Design in browse mode for forms and reports	Only design via the property sheet.
Save Database As	Does not appear.
Share database on SharePoint	Does not appear.
Upsize database to SharePoint	Does not appear.
Access security and the Trust Center	Prompts with security warnings and does not have the ability to trust a file based on its location.
Improved accessibility	Datasheet, forms, and reports do not have the same support for accessibility aides.
New Sorting and Grouping task pane	Sorting and grouping dialog box.
Property Sheet task pane	Property sheet floating dialog box.
Data Source task pane	Field list floating dialog box.
Creating schema in the datasheet	Schema must be created in table design.
Getting Started experience	Getting Started task pane.
Database templates	Cannot be opened.
Office Center for Options	Separate dialog boxes for Options, Startup, and AutoCorrect.
Editable value lists	Value Lists do not have a user interface for editing and are not automatically inherited from the table.
Edit list items command for combo boxes and list boxes	Does not appear.
SharePoint Site Manager	Does not appear.
Slit views	Appears as a single item form.
Datasheet user interface enhancements	Record selectors and selection.
Search box in record navigation user interface	Does not appear.
Customizable caption for the record navigation user interface	Always appears as Record.

Handling VBA references

VBA references are handled the same way in Office Access 2007 as in previous releases. References to other applications in 2007 Microsoft Office system, such as Microsoft Office Word 2007, Microsoft Office Excel 2007, or Microsoft Office Outlook 2007, reference their new type libraries in 2007 Office system when you make design changes in Office Access 2007. If you do not make design changes, the references are not automatically modified by Office Access 2007.

If the references are upgraded and the database opens on a computer that does not have 2007 Office system installed, the database has a broken VBA reference. This can cause error messages. VBA references are compatible with previous versions of a type library, but they are not guaranteed to work with future versions of a type library. To fix these databases, you must manually fix VBA references to point to the version of the Office applications installed on that computer.

In general, when you are working with multiple versions of Office, the best practice is to test the database on the oldest version of Office and the oldest version of Microsoft Windows that you plan to support. Make sure all of the references are fixed. When the database is opened using a newer version of Office or Windows, the references still work.

Access 2007 and SQL Server

Access creates front-end applications that leverage SQL Server as a backend data source. Access forms and reports can be optimized as efficiently as Visual Basic front-end for SQL Server. Office Access 2007 offers two ways to connect to SQL Server data: linking to SQL Server and Access Data Projects (ADPs).

The preferred way to connect to SQL Server is MDB file format or ACCDB file format. This enables you to use the full flexibility of local tables and local queries, while leveraging the full power of SQL Server. In addition, MDB and ACCDB files link to multiple SQL Servers and a wide variety of other data sources. Office Access 2007 contains many new features available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs.

Linking to SQL Server

Access leverages the flexibility of the Jet desktop database engine to link to SQL Server. Jet provides extensibility to connect to a variety of different data sources. Previous versions of Access used the version of Jet included with Microsoft Windows. Office Access 2007 uses its own version of Jet.

From MDB or ACCDB files, Office Access 2007 enables you to create read/write Linked Tables to SQL Server tables or views. Jet also supports SQL Pass-Through Queries, which can send SQL commands directly to the SQL Server.

This linking ability enables you to:

Link to multiple SQL Servers or other data sources.
Include local tables.
Include ad hoc or local queries instead of putting the queries on the server. Jet optimizes the local queries to send as much of the query to the SQL Server as possible to minimize client-side query processing.

You cannot directly modify the design of Linked Tables. You must use an ADP file or Enterprise Manager included in SQL Server to make schema changes or design changes.

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, like the .xls or.doc file formats. It contains forms, reports, macros, VBA modules, and a connection string. All tables and queries are stored in SQL Server. The ADP architecture was designed to create client-server applications. Because of this, there is a limit to the number of records that Access returns in any recordset. This limit is configurable, but you typically must build enough filtering into your application so that you do not reach the limit.

Access uses OLEDB to communicate with SQL Server. To provide the Jet-like cursor behavior desired for desktop applications, Access implements the Client Data Manager (CDM) as an additional layer between Access and OLEDB.

Because of the layers required to get from Access to SQL Server in the ADP architecture, it is often easier to optimize MDB/ACCDB file solutions. However, there are some scenarios where a report might be generated significantly faster in an ADP file. To add these performance improvements and retain the flexibility of SQL Server, you can build the majority of the application in an MDB or ACCDB file and have the file load reports from a referenced ADP file.

One advantage that ADP files have over files in MDB or ACCDB format is the ability to make design changes to SQL Server objects. ADP files include graphical designers for tables, views, stored procedures, functions, and database diagrams.

Access 2003 Conversion Toolkit

You can use the Access 2003 Conversion Tool, available at Access 2003 Conversion Tool (http://go.microsoft.com/fwlink/?LinkId=49681), to analyze databases for upgrade and conversion to Access 2007.

Note:
This tool does not convert your databases. It only helps you with scoping and identifying known issues that have an impact on the conversion process.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

Planning and architecture for the 2007 Office release

See the full list of available books at Office Resource Kit information.

Product Families

Resources

About Microsoft

Popular Places

Popular Searches

Popular Downloads