Migration considerations for Access 2007

Updated: March 5, 2009

Applies To: Office Resource Kit

This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.

 

Topic Last Modified: 2016-11-14

In this article:

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

  • Migration considerations for Access 97 and earlier

  • Office Access 2007 in mixed environments

  • Download this book

With Microsoft Office Access 2007, you can open and use databases created in Microsoft Access 2000, Access 2002, and Access 2003. By 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 article discusses database migration considerations, including the following:

  • 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 Microsoft SQL Server

  • Tools to help with your conversion project

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

Databases created by 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.

Keep in mind that ACCDR is a new file name extension (earlier version was an *.mdb file format) that lets you open a database in runtime mode. By changing a database file name extension from .accdb to .accdr, you can create a “lockdown” version of the Office Access 2007 database. You can change it back to restore full functionality.

If you are working in an Access 2003 database but using Office Access 2007, when the Access 2003 database has workgroup security enabled, run as an Administrator. This is no longer on the Ribbon but details can be found in What happened to user-level security? (https://go.microsoft.com/fwlink/?LinkId=143688).

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 by using previous versions of Access

  • Object Level Security (also known as Workgroup Security)

  • Database replication

  • Encode database (replaced with Encrypt with database password)

Note

To understand the changes that were made in the new Access file format, see the Introduction to the Access 2007 file format.

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 by 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 (https://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.

  • By default, toolbars used in previous Access versions are not used in Office Access 2007. 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 Office 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

By default, Access 97 or earlier format databases are converted into Access 2002-2003 format. 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 that have 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.

Office 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 3.0

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 improvements

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 by using a newer version of Office or Windows, the references still work.

Office Access 2007 and SQL Server

Access creates front-end applications that use SQL Server as a back-end data source. Access forms and reports can be optimized as efficiently as Microsoft 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 Server computers and a wide variety of other data sources. Office Access 2007 contains many new features that are available in both MDB and ACCDB file formats, but only a subset of those features are available in ADPs.

Linking to SQL Server

Access uses 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 Server computers 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.

Access Data Projects (ADPs)

An Access Data Project is an OLE document file, such as 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.

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.

Note

For more information about ADPs, see Create an Access project (https://go.microsoft.com/fwlink/?LinkId=143703).

Access 2003 Conversion Toolkit

You can use the Access 2003 Conversion Tool, available at Access 2003 Conversion Tool (https://go.microsoft.com/fwlink/?LinkId=49681), to analyze databases for upgrade and conversion to Office 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 article is included in the following downloadable book for easier reading and printing:

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