ACC2000: Exporting Currency to Visual FoxPro Produces Double Data Type

ID: Q241470

The information in this article applies to:

Microsoft Access 2000

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

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

SYMPTOMS

When you use the Microsoft Visual FoxPro ODBC driver in Microsoft Access 2000 to export a table that contains a field with the data type of Currency, the Currency data type is created as a Double data type in the exported Visual FoxPro table.

RESOLUTION

You can use an SQL pass-through query to change the column in the table. The following statement changes the data type of the column to Currency. The letter "Y" in the following syntax is used to denote the Currency data type.


ALTER TABLE tbTestFox  ALTER COLUMN Price Y

NOTE: When you view the table structure in Visual FoxPro, note that the data type is Currency. However, when you link the table in Access, the column data type is Number and the field size is Decimal.

STATUS

Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

MORE INFORMATION

Steps to Reproduce Behavior

Create a new database in Microsoft Access.

On the Tools menu, click Options.

Click the General tab, and then write down the path that is displayed in the Default database folder box. (You need it in a later step.)

Create a table in Microsoft Access with the following characteristics:


   Table Name: TestFox
   ----------------------------
   Field Name: ItemID
   Data Type: AutoNumber
   Indexed: Yes (No Duplicates)

   Field Name: ItemName
   Data Type: Text

   Field Name: Price
   Date Type: Currency

   Table Properties: TestFox
   -------------------------
   PrimaryKey: ItemID

On the View menu, click Datasheet View.

Type the following three records as test data:

ItemID ItemName Price

1 Hammer 4.19

2 Crow Bar 12.00

3 Drill 55.50

Close the table.

In the Database window, select the TestFox table.

On the File menu, click Export.

Under Save as type, click ODBC Databases at the bottom of the list. Leave the name as Testfox.

In the Select Data Source Dialog Box, click the Machine Data Source tab.

Click New.

Click User Data Source, and then click Next.

Click Microsoft Visual FoxPro driver. Click Next, and then click Finish. The ODBC Visual FoxPro Setup dialog box appears.

Name the new data source TestFox1.

Click Free Table directory.

For the path, browse to the default database folder that you noted in step 3.

NOTE: If you do not browse to the correct default database folder, the data source generates the following error:

The Microsoft Jet Database Engine could not find the object "".

Click OK twice. The file is exported.

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

Under Files of type, click ODBC Databases at the bottom of the list.

Click the Machine Data Source tab, and select the Testfox1 data source that you created earlier. Click OK.

Select the Testfox table that you exported. Click OK. The table appears in Access as Testfox1.

Open the table. Note that the numbers no longer have dollar signs ($).

On the View menu, click Design View. Note the data type for the Price field is Number, and that the field size is Double.

Additional query words: pra

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