Microsoft KB Archive/241470

= ACC2000: Exporting Currency to Visual FoxPro Produces Double Data Type =

Article ID: 241470

Article Last Modified on 11/29/2000

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q241470





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 that this is a bug in the Microsoft products that are listed at the beginning of this article.



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 </li> On the View menu, click Datasheet View.</li> Type the following three records as test data: </li> Close the table.</li> In the Database window, select the TestFox table.</li> On the File menu, click Export.</li> Under Save as type, click ODBC Databases at the bottom of the list. Leave the name as Testfox.</li> In the Select Data Source Dialog Box, click the Machine Data Source tab.</li> Click New.</li> Click User Data Source, and then click Next.</li> Click Microsoft Visual FoxPro driver. Click Next, and then click Finish. The ODBC Visual FoxPro Setup dialog box appears.</li> Name the new data source TestFox1.</li> Click Free Table directory.</li> 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 "".

</li> Click OK twice. The file is exported.</li> On the File menu, point to Get External Data, and then click Link Tables.</li> Under Files of type, click ODBC Databases at the bottom of the list.</li> <li>Click the Machine Data Source tab, and select the Testfox1 data source that you created earlier. Click OK.</li> <li>Select the Testfox table that you exported. Click OK. The table appears in Access as Testfox1.</li> <li>Open the table. Note that the numbers no longer have dollar signs ($).</li> <li>On the View menu, click Design View. Note the data type for the Price field is Number, and that the field size is Double.</li></ol>

Additional query words: pra

Keywords: kbbug kbnofix KB241470

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.