Microsoft KB Archive/243459

= ACC2000: Error Message: "Object Invalid or no Longer Set" in Query with Linked Oracle Table =

Article ID: 243459

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q243459





Moderate: Requires basic macro, coding, and interoperability skills.

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



SYMPTOMS
When you run a query that contains a linked Oracle table, you receive the following error message:

Object Invalid or No Longer Set



CAUSE
The foreign key of the Oracle table is an integer. Microsoft Jet is converting it to a decimal, and this causes the error in the query.



RESOLUTION
To resolve this issue, follow these steps:  Open the query in Design view. On the View menu, click SQL.  In the JOIN statement, surround the foreign key field with the CInt function, which converts the field to an integer.

For example, if you have the following SELECT statement in SQL View, where tbl_Orders is a local Access table and ORA_tbl_OrderDetails is a linked Oracle table SELECT tbl_Orders.OrderID, tbl_Orders.OrderDate, ORA_tbl_OrderDetails.OrderID, ORA_tbl_OrderDetails.ItemName FROM tbl_Orders INNER JOIN ORA_tbl_OrderDetails ON tbl_Orders.OrderID = ORA_tbl_OrderDetails.OrderID; you would change the statement as follows, which places the OrderID foreign key of the Oracle table inside the CInt function. This is the only change needed: SELECT tbl_Orders.OrderID, tbl_Orders.OrderDate, ORA_tbl_OrderDetails.OrderID, ORA_tbl_OrderDetails.ItemName FROM tbl_Orders INNER JOIN ORA_tbl_OrderDetails ON tbl_Orders.OrderID = CInt(ORA_tbl_OrderDetails.OrderID); NOTE: If you include this function, you will not be able to view the query in Design view without losing the join. This is because there is no way for Access, in Design view, to represent a join to a field that is inside a function.  Save your query.</li></ol>

<div class="status_section">

STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

Steps to Reproduce the Behavior
<ol>  Run the following statement on an Oracle server: <pre class="fixed_text">CREATE TABLE ORA_tbl_OrderDetails( ID NUMBER(4) NOT NULL, OrderID NUMBER(4) NOT NULL, ItemName CHAR(30) NOT NULL ) </li>  In Access, create the following table: <pre class="fixed_text">  Table: Tbl_Orders -  Field Name: OrderID Data Type: Autonumber

Field Name: OrderDate Data Type: Date/Time

Table Properties: Tbl_Orders PrimaryKey: OrderID </li>  View the table in Datasheet view, and then enter the following data: <pre class="fixed_text">  OrderID    OrderDate 1         3/4/2000   2          3/8/2000   3          3/15/2000                    </li> Create a linked table to the Oracle table that you created in step 1. Select ID as the Unique Record Identifier.</li>  Open the linked table, and then enter the following data: <pre class="fixed_text">  ID       OrderID         ItemName -  1        1               Socks 2       1               Shoes 3       1               Shirt 4       2               Pants 5       3               Hat 6       3               Scarf </li> In the Database window, click Queries under Objects, and then click Create a Query in Design View.</li> Close the Show Table dialog box, and then click SQL on the View menu.</li>  Paste or type the following SQL statement: <pre class="fixed_text">SELECT tbl_orders.OrderID, tbl_orders.OrderDate, PSS_ORA_TBL_ORDERDETAILS.ITEMNAME FROM tbl_orders INNER JOIN PSS_ORA_TBL_ORDERDETAILS ON tbl_orders.OrderID = PSS_ORA_TBL_ORDERDETAILS.ORDERID; </li> On the Query menu, click Run. Note that you receive the following error message:

Object Invalid or No Longer Set

</li></ol>

Additional query words: pra

Keywords: kberrmsg kbbug kbnofix KB243459

-

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

© Microsoft Corporation. All rights reserved.