B
Brian Camire
I'm evaluating the possibility of upgrading to Access 2007, but have run into
a problem. A type of query (involving joins between linked ODBC tables) that
used to be updatable in earlier versions (at least Access 2000 and XP) is no
longer updatable in Access 2007. When I try to insert or update records in
one of these queries (say, that joins a child table to a parent table named
PARENTS), Access 2007 fails with the following error:
The Microsoft Office Access database engine cannot find a record in the
table 'PARENTS' with key matching field(s) 'PARENT_ID'.
To duplicate the problem:
1. Create and populate the source tables (in Oracle 10g in my case)
something like this:
CREATE TABLE PARENTS
(
PARENT_ID NUMBER(9,0) PRIMARY KEY,
PARENT_TEXT VARCHAR2(50) NOT NULL
);
CREATE TABLE CHILDREN
(
CHILD_ID NUMBER(9,0) PRIMARY KEY,
PARENT_ID NUMBER(9,0) NOT NULL REFERENCES PARENTS (PARENT_ID)
);
INSERT INTO PARENTS VALUES (1, 'A');
INSERT INTO PARENTS VALUES (2, 'B');
COMMIT;
2. Create an MDB.
3. Link the source tables to the MDB.
4. Verify that there are pseudo indexes defined in Access on the primary
key fields (as they were in my case).
5. Create and save a new query in Access whose SQL looks like this to join
the linked tables:
SELECT
CHILDREN.*,
PARENTS.PARENT_TEXT
FROM
CHILDREN
INNER JOIN
PARENTS
ON
CHILDREN.PARENT_ID = PARENTS.PARENT_ID;
6. In Access 2007, open the query and insert a record, supplying a unique
CHILD_ID and a PARENT_ID (either 1 or 2). When you try to save the record,
Access fails with the error:
The Microsoft Office Access database engine cannot find a record in the
table 'PARENTS' with key matching field(s) 'PARENT_ID'.
7. Open the same MDB in an earlier version of Access (I've tried both
Access 2000 and XP), and repeat Step 6. Unlike with Access 2007: a) when you
supply the PARENT_ID and navigate to another field in the record, Access
automatically populates the PARENT_TEXT field in the record with the
corresponding value from the PARENTS table, and, b) Access successfully
inserts a record.
I've noticed the problem does not occur in Access 2007 (or Access 2000 or
XP) if, for the PARENT_ID fields, you use a data type that does not get
mapped to "Decimal" in Access (for example, VARCHAR2, or NUMBER with a
precision greater than 28, both of which get mapped to "Text").
Unfortunately for me, changing the data type of the columns of my real source
tables in Oracle is not a very attractive workaround -- I have about 175
tables to contend with, and they are interfaced with other things besides
Access.
Does anyone have any insight or suggestions?
a problem. A type of query (involving joins between linked ODBC tables) that
used to be updatable in earlier versions (at least Access 2000 and XP) is no
longer updatable in Access 2007. When I try to insert or update records in
one of these queries (say, that joins a child table to a parent table named
PARENTS), Access 2007 fails with the following error:
The Microsoft Office Access database engine cannot find a record in the
table 'PARENTS' with key matching field(s) 'PARENT_ID'.
To duplicate the problem:
1. Create and populate the source tables (in Oracle 10g in my case)
something like this:
CREATE TABLE PARENTS
(
PARENT_ID NUMBER(9,0) PRIMARY KEY,
PARENT_TEXT VARCHAR2(50) NOT NULL
);
CREATE TABLE CHILDREN
(
CHILD_ID NUMBER(9,0) PRIMARY KEY,
PARENT_ID NUMBER(9,0) NOT NULL REFERENCES PARENTS (PARENT_ID)
);
INSERT INTO PARENTS VALUES (1, 'A');
INSERT INTO PARENTS VALUES (2, 'B');
COMMIT;
2. Create an MDB.
3. Link the source tables to the MDB.
4. Verify that there are pseudo indexes defined in Access on the primary
key fields (as they were in my case).
5. Create and save a new query in Access whose SQL looks like this to join
the linked tables:
SELECT
CHILDREN.*,
PARENTS.PARENT_TEXT
FROM
CHILDREN
INNER JOIN
PARENTS
ON
CHILDREN.PARENT_ID = PARENTS.PARENT_ID;
6. In Access 2007, open the query and insert a record, supplying a unique
CHILD_ID and a PARENT_ID (either 1 or 2). When you try to save the record,
Access fails with the error:
The Microsoft Office Access database engine cannot find a record in the
table 'PARENTS' with key matching field(s) 'PARENT_ID'.
7. Open the same MDB in an earlier version of Access (I've tried both
Access 2000 and XP), and repeat Step 6. Unlike with Access 2007: a) when you
supply the PARENT_ID and navigate to another field in the record, Access
automatically populates the PARENT_TEXT field in the record with the
corresponding value from the PARENTS table, and, b) Access successfully
inserts a record.
I've noticed the problem does not occur in Access 2007 (or Access 2000 or
XP) if, for the PARENT_ID fields, you use a data type that does not get
mapped to "Decimal" in Access (for example, VARCHAR2, or NUMBER with a
precision greater than 28, both of which get mapped to "Text").
Unfortunately for me, changing the data type of the columns of my real source
tables in Oracle is not a very attractive workaround -- I have about 175
tables to contend with, and they are interfaced with other things besides
Access.
Does anyone have any insight or suggestions?