Microsoft Knowledge Base Email Alertz
All KB/RSS Links  |  Hot Kb!  |  New Kb!  |  Scott Cate  |  FAQ / KB  |  Web Hosting  |  Unsubscribe  

(295172) - 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...

Search KbAlertz

Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.
Click here to create a
FREE account
Already have an account?
[Click here to Login]











Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks
Article ID: 295172 - Last Review: February 20, 2004 - Revision: 1.0

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

View products that this article applies to.
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).

On This Page

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.
Back to the top

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.
Back to the top

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.
Back to the top

STATUS

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

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.
Back to the top
APPLIES TO
  • Microsoft Access 2000 Standard Edition
  • Microsoft Data Access Components 2.1 Service Pack 2
  • Microsoft Data Access Components 2.6
  • Microsoft Data Access Components 2.6 Service Pack 1
Back to the top
Keywords: 
kbbug KB295172
Back to the top
        

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

Alex Zorach Report As Irrelevant  
Written: 1/6/2005 3:10 PM
Does anyone know a workaround? We have a proprietary database designed in Oracle that uses lowercase names for some of the tables, and we cannot change the table names. However we want to link to them in Access 2000 via ODBC. How can we work around this problem? This is a big issue for us and we would be quite interested in a way to solve it, even if it is ugly.



Copyright © 2002-2007 KBALERTZ.COM.  All Rights Reserved. Terms / Privacy. Discount ASP.NET Hosting