Converting to Access

You can convert a Microsoft Access database from 2.0 or later to the Access 2000 or Access 2003 file format. You can also convert an Access Project (connected to SQL Server) from Access 2000 to the Access 2003 file format. Keep in mind that converting a database affects only the file that is being converted; it does not affect linked tables.

Before you convert any database, always make a backup. OK, you probably already planned on doing that, but it can't be overemphasized. A handy way to make a backup is to put all associated files into a clearly named zip file. Using a zip file or putting the backup in a different folder provides just a little extra insurance in case something goes awry during a conversion process. With a split database, it is important that all linked tables are in the locations specified in the path in the Linked Table properties. And, if at all possible, compile the database before converting it. This extra step is certainly worth the time because it reduces the possibility of errors during conversion. Fortunately, Access 2003 will open and compile an Access 2000 file. However, databases created in Access 97 or before must be opened by their "original" version of Access to be compiled.

When converting a database that contains linked tables, be sure that the linked tables are still in the location specified in the Table properties. After the database has been converted, the tables can be moved and the Linked Table Manger can be used to relink to the tables in their new location.

The steps to compile a database depend on the version of Access. First, open a module in Design view (note: if there is not a module, create and save one) then:

□ For Access 2002 or 2003, click Debug on the menu bar and then click Compile<ProjectName>. Notice that the ProjectName is listed.

□ For Access 95 or 97, click Debug on the menu bar and then click Compile All Modules.

□ For Access 2.0, click Run on the menu bar and then click Compile Loaded Modules.

This seems so self-explanatory that we'll skip including a screen shot.

The project name in the IDE window may not be the same as the database file name. This is often the case if a database has been renamed, because renaming the database does not rename the VBA project. You can change the name of the project by clicking Tools on the menu bar and then clicking <ProjectName>Properties. This will open the Project Properties window, as displayed in Figure A-2.

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. Handily, the default mode for an unsecured database to open gives the user these permissions. There will be more about permissions in the section on converting a secured database, which is discussed later in this appendix.

The following steps walk you through converting an Access 2.0, 95, or 97 databases to Access 2003:

□ Open Access 2003 without specifying a database.

□ On the File menu, click Open, (or select the Open Folder icon on the toolbar) and browse to the folder containing the database file to be converted.

□ Click (or double-click) File to open the database. The Convert/Open Database window, as shown in Figure A-3, will appear.

Figure A-2

□ Select Convert Database and click OK. This will open the Convert Database Into explorer window, which will require the database name and location. Access will not allow the converted database to be saved with the same name in the same location. Figure A-4 shows the error message from attempting to give the database the same name in the original folder. Again, this is a handy insurance that the original file is not inadvertently overwritten.

□ Finally, click Save. Voila! Access will do the work.

The conversion process may produce some error messages about compile errors. This could likely be due to some of the Visual Basic commands no longer being valid. The code can be corrected after the database is converted. This book has several chapters that may help resolve the errors, such as "VBA Basics," "Executing VBA," and "VBA Error Handling."

It is worth noting that converting Access 2.0 and Access 95 to 2003 automatically converts the built-in and custom toolbars to the new toolbar styles. However, Access 95 custom menu bars are interpreted but not automatically converted, so they cannot be modified in the Customize dialog box. The underlying macros can be used to create new menus, toolbars, and shortcut menus. Click Tools on the menu bar, then click Macro, and then select the desired action: Create a menu, toolbar, or shortcut menu from a macro.


Figure A-3


Figure A-3

Figure A-4

Was this article helpful?

0 0

Post a comment