Upsizing Wizard fails to upsize data in large tables (295231)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q295231
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

IMPORTANT: This article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

SYMPTOMS

When you try to upsize a Microsoft Access database (.mdb) to Microsoft SQL Server, the Upsizing Wizard does not export data for large tables.

CAUSE

The ODBC pass-through query that Microsoft Access uses to export the data is timing out.

RESOLUTION

The following section describes a resolution to the issue and involves changing the registry. If you do not want to make changes to the registry, there is also a workaround for this issue, which is described after the resolution.

To resolve this issue, set the QueryTimeout value in the registry to 0. This keeps the query from timing out. To set the QueryTimeout value to 0, follow these steps:
  1. Quit any running instances of Microsoft Access.
  2. Click Start, and then click Run.
  3. In the Run dialog box, type Regedit.exe, and then click OK. This starts Registry Editor.
  4. Click the QueryTimeout value under the following subkey in the registry:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC

  5. On the Edit menu, click Modify.
  6. In the Edit DWORD Value dialog box, click Decimal under Base.
  7. Enter 0 in the Value Data box, and then click OK.
  8. Quit Registry Editor.
  9. Start Microsoft Access, open your database, and then try to upsize it.

Workaround

To work around this issue, follow these steps:
  1. Upsize the table structure only.
  2. Use Data Transformation Services (DTS) to import the data from the Access table, and then append to the upsized structure.
This will allow you to upsize the database structure and therefore create the desired relationships and triggers/DRI without having to change the registry settings.

For additional information about how to use DTS, click the following article number to view the article in the Microsoft Knowledge Base:

285829 How to Use Data Transformation Services (DTS) to Export Data from a Microsoft Access Database to an SQL Server Database

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When it upsizes a Jet database to Microsoft SQL Server, the Upsizing Wizard uses SQL ODBC pass-through queries executed through the Jet database engine. The QueryTimeout value in the registry specifies how long (in seconds) ODBC queries may execute before timing out. By default, the QueryTimeout value is set to 60 seconds. Therefore any ODBC pass-through query that takes more than 60 seconds times out, and the Upsizing Wizard skips the operation.

Steps to Reproduce the Behavior

NOTE: The following steps simulate the behavior upsizing the Northwind sample database by setting the QueryTimeout to 1 second. This causes any ODBC pass-through queries to timeout after 1 second.
  1. Quit any running instances of Microsoft Access.
  2. Click Start, and then click Run.
  3. In the Run dialog box, type Regedit.exe, and then click OK. This starts Registry Editor.
  4. Click the QueryTimeout value under the following subkey in the registry:

    HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\ODBC

  5. On the Edit menu, click Modify.
  6. In the Edit DWORD Value dialog box, click Decimal under Base.
  7. Enter 1 in the Value Data box, and then click OK.
  8. Quit Registry Editor.
  9. Start Microsoft Access.
  10. Open the sample database Northwind.mdb.
  11. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
  12. Click Create new database, and then click Next.
  13. Select the SQL Server that you want to upsize to, and then enter your authentication information to log on to the SQL Server.
  14. Enter a name for the new SQL Server database, and then click Next.
  15. Select all tables to upsize, and then click Next.
  16. Keep all defaults on the next page, and then click Next.
  17. Select the option to create a new Microsoft Access client/server application.
  18. Enter a path and file name in the ADP Name box, and then click Finish.
  19. The Upsizing Report may show that all tables were upsized successfully, or you may see the following message under some tables:

    Table was skipped.

  20. Close the Upsizing Report. This automatically opens the new Access project (.adp) file connected to the new SQL Server database.
  21. Open the Order Details table.
Note that the structure of the table was upsized, but that the table contains no data.
Modification Type:MinorLast Reviewed:5/28/2004
Keywords:kbExport kbbug KbClientServer kbnofix KB295231 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.