Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 301915 - Last Review: March 26, 2007 - Revision: 5.1
Access causes an error when you export fields with data type single or double to Oracle
This article was previously published under Q301915
Moderate: Requires basic macro, coding, and interoperability
skills.
This article applies to a Microsoft Access database (.mdb) and to a
Microsoft Access project (.adp).
When you use the Oracle ODBC driver to export an Access
table that has fields of data type Single or Double, the driver fails to export
those fields as follows.
The Microsoft Oracle driver displays the following error message:
Microsoft Access was unable to append all the data to the table. The contents of fields in <number> record(s) were
deleted, and 0 record(s) were lost due to key violations.
- If data was deleted, the data you pasted or imported
doesn't match the field data types or the FieldSize property in the destination
table.
- If records were lost either the records you pasted contain
primary key values that already exist in the destination table, or they violate
referential integrity rules for a relationship defined between tables.
Do
you want to continue anyway?
The Oracle ODBC drivers up to and including version
8.1.6 display the following error message:
ODBC - call
failed
[Oracle][ODBC][ORA] ORA-01401: Inserted value too large for column
(#1401)
In Microsoft Access 97, the export process converts the
Single and Double data type fields to VarChar2(40). However, in Microsoft
Access 2000 and later, the fields are converted to VarChar2(4), which it too
small to hold the data.
To successfully export the data to Oracle, use a query
based on the relevant tables. Use the
CStr() function to convert the data type to String.
For
example, consider the following SQL syntax:
SELECT tblExample.pkeyDataID, tblExample.dblTest
FROM tblExample;
where dblTest is a field with a data type of Double. Convert dblTest to
a String data type by making the following change to the SQL syntax:
SELECT tblExample.pkeyDataID, CStr([dblTest]) AS Expr1
FROM tblExample;
The query can now be successfully exported to Oracle.
This problem is resolved in Microsoft Jet 4.0 Database Engine Service Pack 8 (SP8).
For more information about Microsoft Jet 4.0 Database Engine SP8, click the following article number to view the article in the Microsoft Knowledge Base:
829558Â
(http://kbalertz.com/Feedback.aspx?kbNumber=829558/
)
Information about Jet 4.0 Service Pack 8
Microsoft
has confirmed that this is a problem in the Microsoft products that are listed
at the beginning of this article.
The third-party products that are
discussed in this article are manufactured by companies that are independent of
Microsoft. Microsoft makes no warranty, implied or otherwise, regarding the
performance or reliability of these products.
APPLIES TO
- Microsoft Office Access 2007
- Microsoft Office Access 2003
- Microsoft Access 2002 Standard Edition
- Microsoft Access 2000 Standard Edition
| kbexport kbbug kberrmsg kbnofix KB301915 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate