ACC2000: Data Types Change Importing from ADP to MDB and Back

ID: Q224529

The information in this article applies to:

Microsoft Access 2000

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).

SYMPTOMS

The following data types change when you import a table from a Microsoft Access project into a Microsoft Access database, and then import that table from the Access database into an Access project.

binary
char
nchar
numeric
smalldatetime
smallmoney
text
timestamp
varchar

MORE INFORMATION

The first column in the following table shows all the data types of an Access project. The second column shows what the data type is converted to when you import a table into an Access database. The third column shows what the data type is converted to when you import the table back into an Access project.


   Project              Database       Project
   --------------------------------------------------
   binary               binary         varbinary
   bit                  yes/no         bit
   char                 text           nvarchar
   datetime             date/time      datetime
   decimal              number         decimal
   float                number         float
   image                ole object     image
   int                  number         int
   money                currency       money
   nchar                text           nvarchar
   ntext                memo           ntext
   numeric              number         decimal
   nvarchar             text           nvarchar
   real                 number         real
   smalldatetime        date/time      datetime
   smallint             number         smallint
   smallmoney           currency       money
   text                 memo           ntext
   timestamp            binary         varbinary
   tinyint              number         tinyint
   uniqueidentifier     number         uniqueidentifier
   varbinary            binary         varbinary
   varchar              text           nvarchar

Steps to Reproduce Behavior

Create a new Access project.

Create a table named AllTypes that contains all SQL server data types.

Open a new Access database.

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

Select the Access project that you created in step 1, and then click Import.

In the Import Objects dialog box, click the Tables tab. Select AllTypes, and then click OK.

Open the table in Design view. Note how the data types changed.

Open the Access project that you created in step 1.

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

Select the Access database that you created in step 3, and then click Import.

In the Import Objects dialog box, click the Tables tab. Select AllTypes, and then click OK.

Open the table in Design view. Note how the data types changed in comparison to the table that you created in step 2.

Additional query words: pra mismatch sql server datatypes data types

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