PRB: Error Importing an Oracle Table in Access 2000 When Tablename Contains Lower Case Letters in USER_OBJECTS System Catalog (247253)


The information in this article applies to:

  • Microsoft ODBC for Oracle version 2.5 Build 2.573.4403
  • Microsoft ODBC for Oracle version 2.5 Build 2.573.6526
  • Microsoft ODBC for Oracle version 2.5 Build 2.573.7713.2
  • Microsoft Data Access Components 2.5
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.7
This article was previously published under Q247253

SYMPTOMS

The error message:
ORA-00942: table or view does not exist
will be seen when trying to access a table which was exported from Access 2000 using MSORCL32.DLL and the destination table is not specified using upper case letters.

CAUSE

By Default Oracle stores the names of database objects and other schema information in upper case in its system catalogs. When an Access 2000 table is exported using Msorlc32.dll, the table is created on the Oracle server and the table name is stored in exactly the same way as specified during the export process. Specifying table names using lower case letters results in problems because in this case the table name is stored differently compared to the default storage pattern.

RESOLUTION

Specify the name of the destination table using upper case letters when exporting a table from an Access 2000 database to an Oracle Server.

STATUS

This behavior is by design.

MORE INFORMATION

This problem is seen because of the way in which Oracle internally stores schema information.

Steps to Reproduce Behavior

Create a system DSN using Msorcl32.dll version 2.573.4202 which connects successfully to an Oracle 8.0.5 server.
Create a table in Access 2000 and try to export this table to the Oracle 8.0.5 server using the system DSN created in step 1. When asked to specify the destination table name use a combination of lower and upper case letters or just lower case letters. Connect using SQL*PLUS or any other oracle client utility and then run the following query "Select object_name from user_objects". It is seen that the table name in the system catalog appears similar to the name specified for the destination table during the export process. All the other object names appear in upper case. Try importing the table from Access 2000 using the system DSN created in step 1.Errors are seen indicating that such a table doesn't exist in the schema.
Modification Type:MajorLast Reviewed:12/5/2003
Keywords:kbDatabase kbDriver kbOracle kbprb KB247253
©2003 Microsoft Corporation. All rights reserved.