Troubleshoot converting an Access file

When you convert a Microsoft Access version 2.0 database to Access 2000 or Access 2002 - 2003 file format, the conversion utility converts Access Basic code to Visual Basic for Applications (VBA) code. If any code doesn't successfully compile, you might receive an error message after you have converted the Access database.

In the converted Access database, open a module in Design view and click Compile <Project Name> on the Debug menu. As Access compiles, it stops at any line of code that contains an error. You can then modify the syntax to resolve the compile error.

• In rare instances, the conversion utility may fail to convert some syntax. For example, it may fail to modify a DoCmd statement into a DoCmd method. You may also receive compile error messages if your Microsoft Access version 2.0 database contains syntax errors. For this reason, it is a good idea to compile all of your previous-version database modules before you convert them.

• Microsoft Access 2000 or later does not support the DAO 2.5/3.x compatibility library. If you attempt to convert a database in which the code contains older versions of DAO objects, methods, and properties that depend on the DAO 2.5/3.x compatibility library, you receive a message that there were compilation errors during the conversion of the database. Before you convert the database, update the code so that it does not depend on the DAO 2.5/3.x compatibility library. If you still receive a message that there were compilation errors during conversion, use the following procedure to correct the references.

  1. Open the converted Access database in Access 2002 or later.
  2. Open a module in the database.
  3. On the Tools menu, click References.
  4. In the Available References box, clear the check boxes next to the missing references to the DAO 2.5/3.x compatibility library.
  5. Select the check box next to the Microsoft DAO 3.6 Object Library.
• Microsoft Access 97 or later supports some new Visual Basic keywords, so you can no longer use these keywords as identifiers. These keywords are: AddressOf, Decimal, DefDec, Enum, Event, Friend, Implements, RaiseEvent, WithEvents. When you convert a previous-version Access database to Access 2000 or Access 2002 - 2003 file format, existing identifiers that are the same as a new Visual Basic keyword will cause a compile error. To fix this problem, change existing identifiers so they are not keywords.
• In Access version 2.0, you could specify a name for a Visual Basic procedure that was the same as a module name. However, in Microsoft Access 95 or later, a procedure and a module can't have the same name. To fix this problem, rename the procedure or rename the module. Alternatively, you can avoid this error by using a fully qualified name for the procedure in your Visual Basic for Applications (VBA) code.
• When you convert an Access version 2.0 database to Access 2000 later, the code in the new file is contained in a Visual Basic for Applications project. By default, the name of the VBA project is the same as the name of the Access file. The name of the VBA project is also a reserved word in Visual Basic. If a module or a procedure has the same name as the VBA project, you receive a message that there were compilation errors during conversion.

A file in Microsoft Access 2000 or Access 2002 - 2003 file format has a limit of 1000 modules, while an Access 97 database has a limit of 1024 modules. The number of modules in a Microsoft Access file includes forms and reports with the HasModule property set to Yes. To fix this memory problem, reduce the number of objects in your Access database. Consider dividing your application into multiple databases. If you have modules with a large amount of code, consider using library databases to store the code.

If Microsoft Access encounters errors while converting an Access file, you can view a summary of these errors by opening the Conversion Errors table in the new Access file. The Conversion Errors table contains the following columns:

Object Type    The type of database object in which Access encountered an error, or "Database" if Access encountered an error that is not specific to a particular type of object.

Object Name    The name of the object in which Access encountered an error. If Access encounters a compilation error during conversion, however, the name of the module that contains the error is not specified.

Error Description    If necessary, you can press SHIFT+F2 to view the entire description of the error.

If you can't successfully convert your Microsoft Access file by pointing to Database Utilities on the Tools menu and then clicking the Convert Database/Project command, use the following procedure.

1. In Access 2002 or later, click New on the File menu and create a new Access database or Access project.

2. On the File menu, point to Get External Data and click Import.

3. In the Import dialog box, select the Access file you want to convert.

4. In the Import Objects dialog box, select the database objects you want to import. If the new Access file is an Access project that you have connected to a Microsoft SQL Server database, import only the forms, reports, pages, macros, and modules.

Note  When you convert an Access file by using this method, you might have to set references by opening a module in Design view, clicking References on the Tools menu, and selecting the check boxes next to the references that were set in the previous-version Access file.

Name AutoCorrect is on by default in databases that were created with Microsoft Access 2000 or later. To enable Name AutoCorrect in a converted Microsoft Access database, do the following:

1. On the Tools menu, click Options, and then click the General tab.
2. Select the Track name AutoCorrect info and Perform name AutoCorrect check boxes.

   If you want a log of the changes that Access makes to the database when it repairs naming errors, select the Log name AutoCorrect changes check box as well.

3. Open each table, query, form, and report in Design view, save it, and close it.

When you convert a Microsoft Access database from Access 97 or earlier to Access 2000 or Access 2002 - 2003 file format, Access uses the sort order to determine which code page to use when converting the data to Unicode. Access 2000 or later associates the General sort order with the Western European code page. Therefore, if you used the previous-version Access database with a non-English operating system and saved it with the General sort order, use one of the following procedures.

• If your operating system is in the same language as the database or supports that language, use the following procedure.

  1. Open the database in the previous non-English version of Access.

  2. Change the sort order to match the language of the operating system. For information on how to change the sort order in that version of Access, search Access Help.

  3. Compact the database.

  4. In Access 2002 or later, convert the database.

• If your operating system isn't in the same language as the database or doesn't support that language, or if the language had no sort order in the previous version of Access, use the following procedure.

  You might need to use this procedure if the language of the Access database you want to convert was created with an operating system in Vietnamese, Farsi, or a Baltic language (Estonian, Latvian, or Lithuanian).

  1. Do one of the following:

     • If you are converting an Access 2.0 database, locate the following subkey in the Windows Registry:

       HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 2.x

     • If you are converting an Access 95 or 97 database, locate the following subkey in the Windows Registry:

       HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 3.x

  2. In the Jet 2.x or Jet 3.x subkey, create a new entry named ForceCP and do one of the following:

     • Set the value of the entry "ANSI" to specify the computer's default code page.

     • Specify the code page number for the language you want. For example, specify "1257" for Windows Baltic Rim.

  3. Start Access 2002 or later and convert the database.

  4. Delete the ForceCP registry entry so that Access reverts to using the sort order of the database to determine the code page.

When you convert a Microsoft Access version 2.0 database to Access 2000 or Access 2002 - 2003 file format, queries that contain criteria based on specific time values in Date/Time fields may return different results than they do in earlier versions. This behavior may also occur if you link tables from a Microsoft Access version 2.0 database to a Access 2000 or later database. Only the time portion of Date/Time fields is affected.

Queries that contain criteria specifying dates between 1900 and 1929 may also return different results. For example, a date criteria of #01/01/15# in Microsoft Access 2.0 and 95 represents January 1, 1915; the same date criteria in Microsoft Access 97 or later represents January 1, 2015. To work around this difference, modify the data in your criteria to specify the century— for example, #01/01/1915#.

In a Microsoft Access database that you've converted from Access 2.0 to Access 2000 or Access 2002 - 2003 file format, you may encounter problems when trying to print or preview a report that has some margins set to 0. When you convert a version 2.0 report, margins are not set to 0; they are instead set to the minimum margin that is valid for the default printer. This is to prevent the report from printing data in the nonprinting region for the printer.

To resolve this problem, reduce the column width, column spacing, or number of columns in the report, so the width of the columns plus the width of the default margins is less than the width of your paper.

If a Microsoft Access version or 2.0 module contains 16-bit API calls, you may receive an error message when you convert the database to Access 2000 or Access 2002 2003 file format. You must change the API Declare statements in the converted database to their 32-bit equivalents.

The following list provides some tips for converting your code.

• Check any Declare statements to ensure that they refer to the correct dynamic-link libraries (DLLs). The following table shows the new names for the 32-bit Windows DLLs.

  16-bit Windows DLL	32-bit Windows DLL
  User.dll	User32.dll
  Kernel.dll	Kernel32.dll
  GDI.dll	GDI32.dll

• The names of some functions in the 32-bit Windows API have changed. Additionally, functions in the 32-bit Windows API are case-sensitive. Check to make sure that you've entered the procedure name and alias name correctly.
• Some functions have new parameter data types in the 32-bit Windows API.
• If a 16-bit version of a DLL with the same name as a 32-bit DLL exists on your computer, Access may try to call a function in that DLL if the directory in which it resides precedes the directory that contains the new version in your path.
• Some 32-bit DLLs contain functions with slightly different versions to accommodate both Unicode and ANSI strings. An A at the end of the function name specifies the ANSI version. A W at the end of the function name specifies the Unicode version. If the function takes string-type arguments, try appending an A to the function name.

• If your database application calls procedures in other DLLs, you must create or obtain 32-bit versions of those DLLs and make any necessary modifications to your code when you convert your database to the current version of Access. If you can't obtain a 32-bit version of a DLL, then you'll need an intermediary DLL that can convert 32-bit calls to 16-bit calls.

When you convert a Microsoft Access database containing an ActiveX control to Access 2000 or Access 2002 - 2003 file format, you may receive an error. Access version 2.0 supports 16-bit ActiveX controls, whereas Access 95 or later supports 32-bit ActiveX controls. Access can automatically update a 16-bit control to its 32-bit version only if a 32-bit version exists and is registered on your computer.

If your Microsoft Access version 2.0 database contains a table with 32 or more indexes and relationships combined, you may receive an error when you convert to Access 2000 or Access 2002 -2003 file format. To resolve this error, open your database in Access version 2.0 and modify your table design to reduce the number of relationships for the primary key table, or remove some indexes from the foreign key table. Then try to convert again.

The message "ODBC – connection to datasourcename failed" occurs when a table in your converted Microsoft Access version 1.x or 2.0 database is linked to an ODBC data source that uses a 16-bit driver manager and driver. Microsoft Access 2002 or later can open only linked ODBC data sources that use the 32-bit versions of the ODBC Driver Manager (Odbc32.dll) and the appropriate ODBC driver— for example, the 32-bit version of Microsoft SQL Server ODBC Driver (Sqlsrv32.dll).

To correct this problem, you must create a new, identically-named 32-bit data source name (DSN) for each ODBC data source that is linked to the original Access 1.x or 2.0 database.

1. In Microsoft Windows Control Panel, double-click Administrative Tools, and then double-click Data Sources (ODBC).
2. Create the new DSN by entering values identical to the original DSN. For example, for Microsoft SQL Server, you need to define at least Data Source Name and Server, but you may also need to define additional values such as the Database Name.

If you are uncertain what the original name of the DSN was, open the original database in the version of Microsoft Access that it was created with, open the linked table in Design view, and then display the Table Properties sheet. The table's Description property contains the definition for the ODBC connection string. The parameter following DSN= in the connection string is the name of the DSN.

You can convert code that uses Data Access Objects (DAO) back to Microsoft Access 97, but you may receive a message that your computer is missing at least one of the Access 97 object libraries. Use this procedure to fix the missing references.

1. Convert the Access 2000 or later database to Microsoft Access 97. When you receive a message that your computer is missing at least one of the Access 97 object libraries, click OK.
2. Open the converted Access database in Microsoft Access 97.
3. Open a module in the database.
4. On the Tools menu, click References.
5. In the Available References box, clear the check boxes next to any missing references.
6. To set the correct reference, select the check box next to the Microsoft DAO 3.51 Object Library.

Note  Unlike Access 2000 or later, Access 97 is not designed to work with ActiveX Data Objects (ADO).

When you convert a Microsoft Access database in Access 2000 or Access 2002 - 2003 file format back to Access 97, permissions in the new Access 97 database are reset to their defaults because Access 97 can't use a workgroup information file in Access 2000 or Access 2002 - 2003 file format. You must help protect the new database in Access 97.

Import specifications created and saved in a Microsoft Access file in Access2000 or Access 2002 - 2003 file format are not created in the Access 97 file. You must recreate the specifications.