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

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.

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

1.	Create a new Access project.
2.	Create a table named AllTypes that contains all SQL server data types.
3.	Open a new Access database.
4.	On the File menu, point to Get External Data, and then click Import.
5.	Select the Access project that you created in step 1, and then click Import.
6.	In the Import Objects dialog box, click the Tables tab. Select AllTypes, and then click OK.
7.	Open the table in Design view. Note how the data types changed.
8.	Open the Access project that you created in step 1.
9.	On the File menu, point to Get External Data, and then click Import.
10.	Select the Access database that you created in step 3, and then click Import.
11.	In the Import Objects dialog box, click the Tables tab. Select AllTypes, and then click OK.
12.	Open the table in Design view. Note how the data types changed in comparison to the table that you created in step 2.