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

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

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.

To resolve this issue, follow these steps:

1.	Open the query in Design view.
2.	On the View menu, click SQL.
3.	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.
4.	Save your query.

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

1.	Run the following statement on an Oracle server: CREATE TABLE ORA_tbl_OrderDetails( ID NUMBER(4) NOT NULL, OrderID NUMBER(4) NOT NULL, ItemName CHAR(30) NOT NULL )
2.	In Access, create the following table: Table: Tbl_Orders --------------------- Field Name: OrderID Data Type: Autonumber Field Name: OrderDate Data Type: Date/Time Table Properties: Tbl_Orders ---------------------------- PrimaryKey: OrderID
3.	View the table in Datasheet view, and then enter the following data: OrderID OrderDate -------------------- 1 3/4/2000 2 3/8/2000 3 3/15/2000
4.	Create a linked table to the Oracle table that you created in step 1. Select ID as the Unique Record Identifier.
5.	Open the linked table, and then enter the following data: ID OrderID ItemName --------------------------------- 1 1 Socks 2 1 Shoes 3 1 Shirt 4 2 Pants 5 3 Hat 6 3 Scarf
6.	In the Database window, click Queries under Objects, and then click Create a Query in Design View.
7.	Close the Show Table dialog box, and then click SQL on the View menu.
8.	Paste or type the following SQL statement: 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;
9.	On the Query menu, click Run. Note that you receive the following error message: Object Invalid or No Longer Set