Relationship getting deleted by itself!

This is a strange one--

I have an Access 2K database with a split front end/back end. There
are two tables in the back end - RFile and RLine. There is a one to
many relationship from Rfile.Rnumber to Rline.Rnumber. Originally I
went in with the Relationships window, drew the line, and clicked the
cascading updates and deletes. Everything was fine, for a couple of
years.

All of the sudden however, this relationship seems to delete itself.
It's quite unlikely that any user goes in and deletes the relationship.
In the last 3 months It has deleted itself 4 or 5 times. I go into
the back end, look at the child table (Rline) and I'll find 2 or 3 sets
of orphaned records where the parent record in Rfile was changed, and
the children in Rline weren't changed along with the parent thus
creating orphaned children. Of course, when I go to re-create the
relationship, it won't let me because of the orphaned records, so I
have to go and create the parent records again for these records, or
just delete them.

Help!

1) How could this relationship get deleted without someone actually
doing it.
and
2) I wrote a routine that automatically adds the records back in to
the parent table, but I am at a loss for the query that creates the one
to many relationship between the tables in the back end. If I had that
code, to create a one-to-many relationship in the back end, at least I
could have one of the users go in, click a button, and fix the database
when I'm not here.

-BrianDP

re: Relationship getting deleted by itself!

Hi Brian

When you perform a Compact/Repair, one of the things Access does is rebuild
the indexes. If an error has occurred (e.g. if there is a duplicate in the
field that is supposed to be the primary key index), Access cannot restore
the index, so it deletes it (not the field, just the index.)

If there was a relationship that depended on this index (such as
relationship to a foreign key with enforced referential integrity), Access
can no longer enfoce the relationship without the key index, so it also
deletes the relationship.

Normally this is rare, though I also struck it yesterday on An Access 2000
machine running on Windows 2000. I don't know if the Win2000 bit is
significant, but Win2000 and Win2003 currently use a different version of
msjet40.dll than what Windows Xp does.

If this is happening regularly for you and the data is coming over a
network, it might suggest that one of your machines has a flakey network
card/connection, so the writes are not always completed and the index is
being corrupted.

IME, Access is very stable when the database objects are not being altered
(e.g. in an MDE), but does not cope well at all with unstable network
connections.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BrianDP" <bdp222@gmail.com> wrote in message
news:1141241841.696803.248960@t39g2000cwt.googlegr oups.com...[color=blue]
> This is a strange one--
>
> I have an Access 2K database with a split front end/back end. There
> are two tables in the back end - RFile and RLine. There is a one to
> many relationship from Rfile.Rnumber to Rline.Rnumber. Originally I
> went in with the Relationships window, drew the line, and clicked the
> cascading updates and deletes. Everything was fine, for a couple of
> years.
>
> All of the sudden however, this relationship seems to delete itself.
> It's quite unlikely that any user goes in and deletes the relationship.
> In the last 3 months It has deleted itself 4 or 5 times. I go into
> the back end, look at the child table (Rline) and I'll find 2 or 3 sets
> of orphaned records where the parent record in Rfile was changed, and
> the children in Rline weren't changed along with the parent thus
> creating orphaned children. Of course, when I go to re-create the
> relationship, it won't let me because of the orphaned records, so I
> have to go and create the parent records again for these records, or
> just delete them.
>
> Help!
>
> 1) How could this relationship get deleted without someone actually
> doing it.
> and
> 2) I wrote a routine that automatically adds the records back in to
> the parent table, but I am at a loss for the query that creates the one
> to many relationship between the tables in the back end. If I had that
> code, to create a one-to-many relationship in the back end, at least I
> could have one of the users go in, click a button, and fix the database
> when I'm not here.
>
> -BrianDP[/color]

re: Relationship getting deleted by itself!

Compact and repair doesn't seem to have much to do with this problem.

One thing that you mentioned, which might be an issue, is fairly
recently (2 months?) about when the problems started, was when I moved
him from Win2K to WinXP with a new computer. Different versions of the
Jet hmm? Wonder what I can do about that. Is there an upgrade to fix
the latest jet? Or somehow reinstall the one he was using before this
problem started happening?

In any case, is it possible to create a relationship in that
relationship window with SQL code? If so, could you post how to post a
one to many relationship between rfile.rnmbr --> rline.rnmbr Rfile has
the one, and the rline has the many.

Many to Many thanks!

-Brian

re: Relationship getting deleted by itself!

Whether the repair was involved or not, the situation most likely represents
a corruption of the index and therefore loss of the relation. A genuine
solution must therfore identify what is causing the corruption and dealing
with, that rather writing code to create a self-closing gate after the horse
has bolted.

If you want to write it anyway, you will need to use DAO code to check for
the existence of the Index and the Relation with the correct Attributes and
Properties, and recreating them if missing or inadequate.

To get you started, the samples below show how to enumerate the Relations
for the database, and the Indexes for a table if that is what you want to
do. Again, though, I would consider this approach to be a genuine solution
to the problem.

Public Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable,
RelationAttributes(rel.Attributes)
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

Function ShowIndexes(strTable As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim ind As DAO.Index
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdf = db.TableDefs(strTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(ind)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(ind)
Debug.Print
Next

Set ind = Nothing
Set tdf = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BrianDP" <bdp222@gmail.com> wrote in message
news:1141324148.332357.217360@u72g2000cwu.googlegr oups.com...[color=blue]
> Compact and repair doesn't seem to have much to do with this problem.
>
> One thing that you mentioned, which might be an issue, is fairly
> recently (2 months?) about when the problems started, was when I moved
> him from Win2K to WinXP with a new computer. Different versions of the
> Jet hmm? Wonder what I can do about that. Is there an upgrade to fix
> the latest jet? Or somehow reinstall the one he was using before this
> problem started happening?
>
> In any case, is it possible to create a relationship in that
> relationship window with SQL code? If so, could you post how to post a
> one to many relationship between rfile.rnmbr --> rline.rnmbr Rfile has
> the one, and the rline has the many.
>
> Many to Many thanks!
>
> -Brian[/color]

re: Relationship getting deleted by itself!

Could you verify, please, that you created the relationships in the
backend? You do mention the backend and it seems you have created the
relationships there but I did not find words where you stated
explicitly that you created the relationship in the backend, that is
with the backend db and its relationship window open, and not in the
front end, (preferably with the front end closed).
Creating a relationship in the front end could explain what you
describe. It's a common error. Another front end is opened; the
relationship is not available to that front end nor to the backend.
Regerential Integrity is compromised. Possibly it's too basic for you,
but we should rule out the obvious before we tackle the arcane.

re: Relationship getting deleted by itself!

Yes, I created the relationship in the back end, with the relationship
window open. I added the two tables, RFILE, and RLINE, and I drew a
line between the two. It seemed to know that it was supposed to be a
one-to-many relationship, and created it correctly. I cliked the boxes
to make referential integrity and cascading updates take place.

That's about it! I'm still thinking Allen may have hit on something
with a different version of the Jet. This database worked flawlessly
(well, okay, it worked very well, without this particular problem) for
years and years (6 years) before this all the sudden started happening.
The only change was the the client machine that constantly bangs away
at this database was changed from a 2000 to an Xp machine. It was also
changed from a 98 to a 2000 in this time as well, but that didn't cause
this problem. The Relationship Deletion problem as I've come to call
it started when I changed his computer out for a windows XP OS.

-Brian