ACC2000: Upsizing Report Incorrectly Shows Field as Varchar (206135)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q206135
This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

Advanced: Requires expert coding, interoperability, and multiuser skills.

SYMPTOMS

When you use the Upsizing Wizard to upsize a table that contains a field with an Access data type of Text, the resulting upsizing report indicates that the field was upsized to a data type of varchar, when actually the field was upsized to a data type of nvarchar.

RESOLUTION

If you do not plan to use the data in other language versions of Microsoft SQL Server, you can change the Unicode data type of nvarchar to varchar. By using varchar, you can reduce disk space requirements. (See the "More Information" section later in this article.) To change the data type, follow these steps:
  1. Open either an existing Microsoft Access project or create a new Access project that is connected to the SQL Server database in question.
  2. Click Tables under Objects, and then click the table that contains the field that you want to change.
  3. Open the table in Design view.
  4. In Design view, click the data type for the field and change the data type to varchar.
  5. Close and save the table.

STATUS

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

MORE INFORMATION

The nvarchar data type is a variable-length Unicode character data type. The length must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered.

The varchar data type is a variable-length non-Unicode character data type. The length must be a value from 1 through 8,000. Storage size is the actual length of the data entered.

Steps to Reproduce Behavior

  1. Create a new Access database named TestDB.
  2. In the new database, create a new table called Test1 with the following characteristics: 
       Table: Test1
   ----------------------------
   Field Name: CategoryID
   Data Type: Counter
   Indexed: Yes (No Duplicates)

   Field Name: CategoryName
   Data Type: Text
    Save the table and open it in Datasheet view.
  3. Enter the following sample data.

    CategoryIDCategoryName
    1Drinks
    2Meats
    3Vegetables
  4. Close the table.
  5. On the Tools menu, point to Database Utilities, and then click Upsizing Wizard.
  6. Upsize the Test1 table to a new database.
  7. Click Next for each step in the wizard until you see the screen that asks What application changes do you want to make?
  8. Click Create a new Access client/server application, and then click Finish.
  9. In the resulting report, note that the data type of CategoryID is varchar.
  10. Close the report.
  11. In the new TestDB.adp, open the Test1 table in Design view. Note that the data type of CategoryID is nvarchar.

REFERENCES

For more information about the Upsizing Wizard, click Microsoft Access Help on the Help menu, type use the upsizing wizard in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MajorLast Reviewed:6/24/2004
Keywords:kbbug kbnofix KB206135
©2004 Microsoft Corporation. All rights reserved.