ACC2000: Upsizing Report Incorrectly Shows Field as Varchar

• Microsoft Access 2000
• Microsoft Office 2000

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 this to be a problem in the Microsoft products 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.
   
   

   CategoryID	CategoryName
   1	Drinks
   2	Meats
   3	Vegetables




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.

Additional query words: OFF2000 pra

Keywords : kbdta
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbbug