Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006
    Tucson Arizona

    Question Unanswered: Mystery tables in relationship window

    Greetings. Just joined the forum and am humbled by the impressive knowledge base here....maybe I will be able to soak some of it up by association.
    Here is a brief about my situation:
    Our project is based on long term wildlife population and habitat monitoring and we keep track of all of the data via different databases (habitat, telemetry, trapping, reproduction, etc). Each database has forms for data entry to allow student workers or technicians to be able to enter data with minimal supervision.
    Within each database, we generally have one table being the "main" table with a unique site ID#, collection date, etc. and then related to this table are tables with specific information - for example tblCanopy contains canopy cover, tblAllTrees contains all trees and tree measurements for that site (many to one relationship to tblMain) etc.
    What I am noticing is that when I look in the relationships window, there are often these mysterious copies of tblMain (e.g. tblMain_1, tbl_Main_2, tbl_Main3) that appear to be virtual tables (i.e. they do not exist in the tables list). They also have relationships to other tables that seem to be created all by themselves. I have been having trouble with one form in particular that every time I click on a subform I get the error message: "the record cannot be deleted or changed because "tblLogs" includes related records"...for no apparent reason. I am starting to think that these virtual tables are to blame....I can't figure out what they do or where they come from. Even if I delete all relationships, they come back. Is this somehow related to the record source for the form?
    Any thoughts or ideas?
    Thanks in Advance. -Melissa

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Hi Melissa, and welcome to the forum....

    if you are seeing <tablename>_x then I'm guessing its likely that the same table has multiple keys referencing it, in some cases it could be that there is a foreign key referring to the same table.

    the access realtionship designer can't handle more than one reference between the same table(s) so to get rounf that limitation it creates a ghost table witht he extension "_x".
    say for example you have a tbl_employee and a another table tbl_batches which recorded which employee made a part, which employee inspected the part, which employee packed the part you might have a relationship from tbl_employee to tbl_batches for each realtionship access would show theese as

    tbl_employee (EmpID) ------ tb_Batches (EmpProd)
    tbl_employee_1 (EmpID) ------ tb_Batches (EmpInsp)
    tbl_employee_2 (EmpID) ------ tb_Batches (EmpPackedby)

    if you delete the relationships in the relationships display, unless you select the line linking the two tables you will not delete the realtionship, merely remove the table from the display. assuming that the person who designed the application knew what they were doing when they created the design then I'd be very carefull about deleting any realtionships unless you are absolutely certain you are aware of the consequences.

    the error message you are getting suggests that there ius a realtionship between records in tbllogs and the table you are trying to delete records from. the solution is to either delete the records in tblLogs first, or define the realtionship in the realtions window to delete cascase. (what this means is that if a parent record is deleted then any records in tables defined with a delete cascade relationship will also be deleted. again you need to think carefully about what you are doing.

    realtionships help preserve the integrity of data (both in terms of data capture but also data maintenance.

    if this error occurs in one of seemingly many similar database designs then I'd have a look and see what is different between the various db's. It may be that the delete cazscade has been iomitted in thsi sepcific db
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2006
    Tucson Arizona
    Thanks. It is good to finally figure out what is going on with the ghost tables! The relationships were a bit messy, so I rebuilt the DB and there are none of the mystery tables hanging out now. Also, the problem with the error message goes away if I uncheck "enforce referential integrity" in the relationship properties. But that is probably a bad idea? I would get the error when I wasn't trying to add or delete records, just look at them in the data entry form.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts