Upgrading and Converting to Access 2010 : INSTALLING MULTIPLE VERSIONS OF ACCESS ON ONE PC, CHANGING FILE FORMATS

1. INSTALLING MULTIPLE VERSIONS OF ACCESS ON ONE PC

As a developer, you are likely to need to work with multiple versions of Access at the same time and even for the same application. In the past, it was typical to use a different PC for each version. Although this avoided conflicts with Dynamic Linked Libraries (DLLs), it took a toll on resources and space. Thankfully, reliable options are now more affordable. Two of the popular options are to use virtualization software or to have side-by-side installations of selected programs.

LIBRARIES (DLL) Complex applications and operating systems usually employ libraries, or files that contain common functions that are shared among the software. A Dynamic Linked Library (DLL) is a library that software loads on the fly. This helps reduce the application's install size and may use less system resources when it shares the libraries, but this comes at the price of being dependent on a specific version, which may not be available on other systems; and only one version can be registered at a time, which is why developers need to handle the dependency carefully.

With the new processors and hard drives, many machines have the space and capacity to run multiple versions of software. And, there are a growing number of options for creating virtual machines so that each version of software is running in its own operating environment. This can become resource intense, and each configuration requires individual maintenance but there are great benefits. For example, having virtual machines might allow you to design and test an application to run under multiple configurations, and it can allow you to emulate a client's environment to replicate their activities.

There are some general guidelines for installing multiple versions of Access directly onto one computer, also known as running side-by-side. First, be sure to install in order from oldest version to newest version. Second, if you are installing from an Office Suite instead of a standalone copy of Access, select a custom Office installation and install only Access — and while you're at it, install all of the features that might be used. It can be rather frustrating to have to stop in the middle of a process to get the CD and install more features.

NOTE

Be aware that because Access 2010 is brand new, there may be some challenges when in running it side-by-side with previous Access versions.

After installing the versions of Access that you need, you may want to set up shortcuts with icons that clearly denote which version it opens. Be aware that installing side-by-side comes with a price: Every time we switch the version, Office has to perform a reconfiguration to update the Registry to use the appropriate references for the version being opened. This occurs only the first time you open a certain version and doesn't happen again until you switch to the other version.

2. CHANGING FILE FORMATS

Before actually converting or enabling older files, it would be good to know how to work with the various file types in Access 2010. A good place to start is to specify the default file format. And, because we all agree that the data should not be in the front end or application file, we'll also tell you how to split the database. Given that legacy files could be MDE versions or runtime applications, we will also review the steps for creating these file types in the Access ACCDB format.

2.1. Selecting the Default File Format

For Access 2010, the default file format is ACCDB. But, if most of the files will be used by prior versions of Access, it might be best to specify a 2000 or 2002−2003 MDB as the default file format. It is easy to set the default file format, and it does not lock you into that selection. If you need to specify a different file type, it only takes a couple of extra clicks to override the default selection.

Setting the default file format is accomplished in a few easy steps:

1. Open Access 2010, but don't open a file.

2. If Backstage is not shown, click the File on the Ribbon. 

3. Click Options, a button on the left pane of the Backstage.

4. In the left pane of the Access Options dialog box, click General.

5. Under Creating Databases, in the Default File Format box, select the preferred format.

6. Click OK.

You can quickly confirm that the settings were saved as expected by initiating the process for creating a new database. That will open a pane on the right and provide a default name for the file. Then click the folder to the right of the filename to open the New Database File dialog box. The field "Save As Type" in the dialog box will display the default file format, including the version and extension, such as 2002−2003 format (*.mdb).

2.2. Overriding the Default File Format

As with other Office programs, you can easily save a file to a format other than the default format. However, changing the format of a database file may interfere with existing code and features, so it is best to select the desired format when you are first creating the database.

To override the default file format when creating a new database, choose New in the Backstage. The Backstage will then show a text box to enter a filename on the right.

1. Type a name for the new database in the File Name box.

2. Click the yellow folder next to the File Name box to open the New Database File dialog box.

3. Accept or select a different folder location and filename.

4. Select the file format that you want in the Save As Type drop-down list. You can specify 2000 MDB, 2002−2003 MDB, 2007 ACCDB, or ADP.

5. Click OK.

That's essentially all it takes to save a file in both the ACCDB and MDB file formats. Of course, when going from ACCDB to MDB, some features will not be available. And if the file has multivalue fields, SharePoint offline data, attachments, data macros, and/or Web objects, Access will either silently discard the unsupported features or provide an error message and not convert the file.

2.3. ACCDE and MDE Files

Access 2010 will create either a MDE or an ACCDE file, depending on which file type is open. Both files compile and lock down the code, so it cannot be viewed or modified. Any future changes have to be made to the originating MDB or ACCDB file and then a new ACCDE or MDE file will need to be created. Because the steps are essentially the same, this section will only provide the steps for creating an ACCDE file.

It takes just six steps to create an ACCDE file in Office Access 2010.

1. Use Access 2010 to open the database that you want to save as an ACCDE file.

2. Click File to access the Backstage.

3. Click Save & Publish.

4. On the middle pane, choose Save Database As.

5. On the right pane, choose Make ACCDE.

6. A Save As dialog box will appear. Type in the filename and click Save.

2.4. Steps for Converting or Enabling

For the most part, this section will focus on converting existing Access applications to Access 2010. However, if an Access 2010 application will be used by prior versions of Access, it will also be important to know how to create a compatible file. Access 2010 makes this relatively easy and straightforward. Note the qualifier, relatively, which is based on the inclination to include a caveat with regard to references and VBA.

The basic steps are typically adequate for simple files rather than complex applications. They do not check or fix broken references, test and debug code, replace custom menus and toolbars, or do a lot of other things that you will need to manage when converting your Access solutions. However, they do provide important guidance about the issues to consider, and they guide you through the steps for converting a database.

2.5. File Conversion Using Access 2010: A One-Stop Shop

Access 2010 has essentially one process to manage database conversion. You can quickly create a copy of a database so that it can be compatible with and used by multiple versions of Access. This is definitely a time to appreciate simplicity because the process is as easy as changing a picture from a 5MB BMP to a 300KB JPG.

In addition to converting to and from the ACCDB file format, Access 2007 will convert to and from MDB files with 2000 and 2002−2003 formats. As we previously mentioned, it requires extra steps and considerations to work with 95 and 97 file formats. Because Access creates and converts a copy of the original file, you will be ready to work with both the original and converted applications. An important, and seemingly obvious, item to note is that all database objects need to be closed when you are converting a file.

As always, we recommend that you compile the database and resolve issues related to deprecated features, references, and custom UI before you convert it. The investment at this point will quickly be rewarded by the reduced potential for errors and undue challenges during conversion. Keep in mind that conversion is a relatively low-risk process because Access creates a copy of the file. So the original is preserved, and you can quickly start again.

If you have an existing database that you want to convert to a different format, you can choose a format under the Save Database As command. Just follow these steps:

1. Use Access 2010 to open the database that you want to convert.

2. Click the File button to access backstage.

3. Click Save & Publish.

4. On the right pane, choose the desired format.

5. The Save As dialog box will appear. Type in the new filename and click Save. Depending on the size, this can take a few minutes.

6. When the conversion is completed, a dialog box advises you that the file cannot be shared with prior versions of Access. Click OK.

7. The new file opens in Access 2010.

2.6. Other Considerations When Converting

Keep in mind that saving a file to a different file format is only a small part of the process. As already discussed, there may be issues involving code, references, macros, security, and integration with other applications. For the most part, moving to newer versions is easier than moving backward. When converting to a prior version, some newer features may be lost or have only part of their functionality, and custom features may not be recognized or implemented as expected. Despite those concerns, it is certainly handy to have the ability to save a file in an older format when you want to.

But what about times when only some of objects are needed? Instead of converting an entire database, there is also the option to import database objects into an Access 2010 file, whether you need an MDB or ACCDB format. Importing objects does not automatically import or set the necessary references. So if you import VBA objects that depend on specific references, you may need to manually add the same references to the new file.

NOTE

When converting a database that contains linked tables, it is a good practice to ensure that the linked tables are still in the location specified in the Connect property. Using the Linked Table Manager to relink to the current tables is a fast, easy way to refresh or update the links. After the database has been converted, the tables can be moved and the Linked Table Manager can be used to relink to the tables in their new location.

To convert a database, it must be closed, meaning that no users can be accessing the database, and you essentially need to have the equivalent of Administrator permissions for the database. Fortunately, the default mode for an unsecured database is for all users to have Admin permissions. 

2.7. Converting to Access 97 or Earlier Is a Two-Version Process

Rather than converting a 2010 file to work with Access 97, consider converting all Access 97 applications to the 2000 (or newer) file format if at all possible. If the situation demands that the files be converted to Access 97, keep in mind two important factors:

• Microsoft no longer supports Access 97.

• Access 97 does not support Unicode, so there will be issues if the databases contain Unicode data, including Asian and Complex Script languages.

To convert an Access 2007 database to Access 97, you first need to convert it to an intermediate version (2000 or 2002−2003) and then use that file and version to convert to an Access 97 file. Access 2010 will be able to open the new Access 97 database and users may enter data. However, you will have to have an installed copy of Access 97 in order to make any design changes. Additionally, the data file will also need to be converted to Access 95 (or newer) to be accessible to Access 2010.