Referential Integrity that isn't

[Bob Rogers]

I seem to have gotten myself into a bit of a pickle. I am trying to
delete a record from a table attached to a form, and it complains
about not being able to delete a record in a table because "the record
cannot be deleted because table sales includes related records"
Cascade deletes and referential integrity right?

Well no. The table I am deleting out of has NO referential integrity
with ANY other table. I tried renaming and deleting the table and all
to no avail.

Here is my situation

Table1 is Sales and it has a company ID in it
Table2 is Company Table with company ID
Table3 is kind of a join with branches and headquarters of Companies.
It has ONLY the company ID of the headquarter and that of the branch

Table1 IS related to Table2 with RI (no cascade updates or deletes...)

The form for maintaining Table3 is a join on Company twice for the
purpose of displaying the names of the branch and headquarter company
for the user

Table2 Table3 Table2_1
ID-----------ID
Name SubID----------ID
Name
Then we display Table3.ID, Table2.Name, Table3.SubID, Table2_1.name as
subname

When I try to delete any record I get the can't delete record. If I
don't create the record source with the wierd 3 table join, it deletes
fine.

Ideas?

Thanks in Advance
Bob Rogers

[Dean Covey]

Re: Referential Integrity that isn't

I have seen this before. Try compacting the database then try deleting
table.

--
Dean Covey

https://www.coveyaccounting.com

MS-Office Certified:

http://www.microsoft.com/learning/mcp/officespecialist/default.asp

"Bob Rogers" <microsol@hevan et.com> wrote in message
news:91038654.0 404162201.4e2b5 34f@posting.goo gle.com...[color=blue]
> I seem to have gotten myself into a bit of a pickle. I am trying to
> delete a record from a table attached to a form, and it complains
> about not being able to delete a record in a table because "the record
> cannot be deleted because table sales includes related records"
> Cascade deletes and referential integrity right?
>
> Well no. The table I am deleting out of has NO referential integrity
> with ANY other table. I tried renaming and deleting the table and all
> to no avail.
>
> Here is my situation
>
> Table1 is Sales and it has a company ID in it
> Table2 is Company Table with company ID
> Table3 is kind of a join with branches and headquarters of Companies.
> It has ONLY the company ID of the headquarter and that of the branch
>
> Table1 IS related to Table2 with RI (no cascade updates or deletes...)
>
> The form for maintaining Table3 is a join on Company twice for the
> purpose of displaying the names of the branch and headquarter company
> for the user
>
> Table2 Table3 Table2_1
> ID-----------ID
> Name SubID----------ID
> Name
> Then we display Table3.ID, Table2.Name, Table3.SubID, Table2_1.name as
> subname
>
> When I try to delete any record I get the can't delete record. If I
> don't create the record source with the wierd 3 table join, it deletes
> fine.
>
> Ideas?
>
> Thanks in Advance
> Bob Rogers[/color]

[Bob Rogers]

Re: Referential Integrity that isn't

Well, I'll be hornswaggled!!! thank you Dean. That does in fact seem
to do it. Yet another case of Access remembering too many things for
it's own good.
[color=blue]
> I have seen this before. Try compacting the database then try deleting
> table.[/color]