Something odd has recently started happening in a database I’m messing with and I’m completely baffled as to what I’ve done to cause this change.
There’s an Access application which has both a front-end and a back-end database. I’ve recently created a separate front-end database to create some tables, queries and forms to enable me to do some checking of the data in the back-end. One thing it’s done is to create a table containing a list of records which looks potentially suspect and need to be checked manually.
I have a SELECT query which returns data that comes from this table but uses fields on two other tables in order to select the a subset of records where checking them is a priority. So, if you like, there are three tables, A, B and C. In logical, data,terms, there is a one-to-many relationship between A and B and a one-to-many relationship between B and C. The query only returns information from table C which is that table that contains the info about what needs checking. Tables A and B are in the main application’s back-end database (and do have an Access table relationship set-up), while table C is in this new front-end I’m using and doesn’t have an Access table relationship set up with any other tables. One additional pertinent fact is that table B has a delete cascade relationship with a further back-end table, D, which isn’t used in the query.
This data table C contains information about records whose data looks suspect and I’ve been using the SELECT query in datasheet mode to view it. The data might be OK, but it might be wrong, so what I’ve been doing is checking the data and where it’s wrong I’ve been updating the data in the main tables (A, B, D and others) using the main application. When I’ve done that I’ve just manually deleted the records in table C via the query in datasheet mode.
For most of the time, when I’ve done this, all it’s done is deleted the record in table C, but it’s just started doing something different. When I delete a record via the datasheet now it displays the message “Relationships that specify cascading deletes are about to cause 1 record(s) in this table, along with related records in related tables, to be deleted. Are you sure you want to delete these records?” If I click on “Yes” it not only deletes the record in table C but the corresponding record in table B which, because of the delete cascade, deletes any related records from table D.
I have double-checked and there is definitely no Access relationship between table C and any other table, so what’s causing the record in B to be deleted? Or have I fundamentally misunderstood how Access handles record deletions via SELECT queries?
Thanks in advance for any help you can give me.
