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


The information in this article applies to:

  • Microsoft Access 2000
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:
  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.

STATUS

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

MORE INFORMATION

Steps to Reproduce the Behavior

  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
Modification Type:MinorLast Reviewed:7/13/2004
Keywords:kbbug kberrmsg kbnofix KB243459
©2004 Microsoft Corporation. All rights reserved.