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.