Failure to Correctly Add A Record (Joined Tables) (Access 2000)

Topic

New Reply

Situation:
I had working VBA code in Access 97 that opened a Recordset object using records from a query that drew fields from two related/referential integrity linked one-to-one tables via indexed AutoNumber field (in the primary table). I could then use the “recOBJECT.AddNew” method that would happily add a record to each of the underlying tables.
Problem:
I “updated” to Acess 2000. Now my code is broken. The AddNew method still works but the second I try to put data in the new record, Access crashes with the “creating log file” idiot message (where IS this mythical log file anyway…) It doesn’t work to add a record directly to the Query Dataview screen either, but I CAN add individual records to.each table. Why did my referential integrity go away and how can I get it back? Any help out there? (Thanks in advance)!
– DB

Reply | Quote

Replies

There is an essential difference between Access 97 and 2000 in the way the query engine behaves. In 97 and prior versions, you only added the key fields to the query grid for tables that you wanted to append to. In 2000, you must add *all* the key fields of all the tables in the query in order to append anything at all. On the other hand, prior versions would automatically try to add records to all tables in the query that had key fields in the grid, while 2000 only adds records to those tables that you’re trying to write to.

Your referential integrity is still there, but you may not be able to use queries the way you did before. At least, I’ve had to change the way I handle appending records, especially if I want to append to a query to take advantage of referential integrity. For one thing, the parent table won’t automatically insert a key into the child table until you start a record in the child table by entering data into it.

Reply | Quote

After further experimentation with this problem, I found something curious: ACCESS 2000 appears to refuse to permit referential integrity between tables in the current .MDB file and items which are LINKED from elsewhere (a change from ACCESS 97). It does not help to set exclusive use (Tools | Options | Advanced). Anybody know how to re-establish referential integrity? (The two tables I’m using are linked by a field called “ID” which is an indexed autonumber field in one table and an indexed integer field in the other.)

Reply | Quote

Actually, it is *NOT* a change from 97. No version of Access has implemented referential integrity between tables in different databases, no matter what it may have looked like.

Reply | Quote