The Oracle table name must be in uppercase characters when you link to the Oracle table in Access 2000 (295172)


The information in this article applies to:

  • Microsoft Access 2000
  • Microsoft Data Access Components 2.1 SP2
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 SP1
This article was previously published under Q295172
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

You can link a table from an Oracle 8 relational database management system (RDBMS) to a Microsoft Access 2000 database. However, if the Oracle table name is not in uppercase characters, you receive the following error message:

The Microsoft Jet database engine could not find the object '<database name>.<table name>'. Make sure that object exists and that you spell its name and the path name correctly.

CAUSE

Oracle 8 is a case-sensitive RDBMS. The Oracle 8 RDBMS interprets uppercase characters (SHIPPERS), mixed-case characters (Shippers), and lowercase characters (shippers) in a table name as different table names. Access, used with the Jet database engine, interprets uppercase characters (SHIPPERS), mixed-case characters (Shippers), and lowercase characters (shippers) in a table name as the same table name. The error message that is mentioned in the "Symptoms" section occurs partly because of the difference in case sensitivity between the Oracle 8 RDBMS and the Access 2000 database.

The Oracle 8 RDBMS uses the following mechanisms to differentiate among table names:
  • The table ID
  • The friendly name
The table ID is the exact table name for a specific table. You specify the table ID by enclosing the table name with double quotation marks. For example, "SHIPPERS" refers to the table with the table ID of SHIPPERS.

The friendly name is the name of the table that is enclosed by single quotation marks. Therefore, "SHIPPERS" refers to the table ID and 'Shippers' is one possible friendly name for the "SHIPPERS" table.

When you provide the Oracle 8 RDBMS with the table name that you want to link to, only single quotation marks enclose the table name. Therefore, you link to the friendly name of an Oracle table. For example, assume that you have both SHIPPERS and Shippers in the Oracle 8 RDBMS. Also, assume that you have successfully linked to Shippers. Access displays the linked table name as Shippers (mixed-case characters). However, you are actually linked to the uppercase-character version of the table (SHIPPERS) because you used single quotation marks to enclose the table name. The Oracle 8 RDBMS uses the friendly name of the table instead of the table ID.

In another example, assume that you have only Shippers in the Oracle 8 RDBMS. SHIPPERS does not exist in the Oracle 8 RDBMS. When you try to link to Shippers and SHIPPERS does not exist, you receive the error message that is mentioned in the "Symptoms" section.

RESOLUTION

To resolve this problem, use all uppercase-character table names in the Oracle 8 RDBMS. If you export a table from Access to the Oracle 8 RDBMS, specify the table name as uppercase.

STATUS

Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section of this article.

MORE INFORMATION

Steps to reproduce the problem

  1. On a computer that is running Access 2000, Microsoft Data Access Components (MDAC) 2.1 Service Pack 2 (SP2), MDAC 2.6, or MDAC 2.6 Service Pack 1 (SP1), install Oracle version 8 client utilities.
  2. Create a new system data source name (DSN). In the Name box, type Oracle8.

    Note You must use the Microsoft ODBC driver for Oracle.
  3. Open the Northwind sample database, and then select the Shippers table.
  4. On the File menu, click Export.
  5. In the Files of Type dialog box, click ODBC Databases.
  6. Click the Oracle8 DSN that you created in step 2.
  7. Click OK to export the "Shippers" table to the Oracle 8 RDBMS.
  8. On the File menu, point to Get External Data, and then click Link Tables.
  9. In the Files of Type dialog box, click ODBC Databases.
  10. Click the Oracle8 DSN that you created in step 2.
  11. In the Login dialog box, type the user name and the password for the Oracle server, and then click OK.
  12. In the Available Tables list for the Oracle 8 RDBMS, click logon name.Shippers.
  13. Click OK to try to link the table to the Access 2000 database.

    You receive the error message that is mentioned in the "Symptoms" section.
Modification Type:MajorLast Reviewed:2/20/2004
Keywords:kbbug KB295172 kbAudITPRO kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.