473,883 Members | 1,741 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Mar 1 '06 #1
5 2402
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" <bd****@gmail.c om> wrote in message
news:11******** **************@ t39g2000cwt.goo glegroups.com.. .
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

Mar 2 '06 #2
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

Mar 2 '06 #3
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.ForeignTabl e,
RelationAttribu tes(rel.Attribu tes)
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(str Table 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(st rTable)
For Each ind In tdf.Indexes
Debug.Print ind.Name, IndexDescrip(in d)
Debug.Print " Field(s): ";
For Each fld In ind.Fields
Debug.Print fld.Name;
Next
Debug.Print
Debug.Print " " & IndexDescrip(in d)
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" <bd****@gmail.c om> wrote in message
news:11******** **************@ u72g2000cwu.goo glegroups.com.. .
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

Mar 3 '06 #4
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.

Mar 3 '06 #5
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

Mar 3 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
5107
by: francophone77 | last post by:
I deleted a relationship in the relationship view, but when I create a query that includes those two tables, the relationship reappears. When I go back to the relationship view there is NO relationship but I can't seem to get rid of it for queries (without manually doing it for each query). Can anyone tell me whats going on??? TIA
8
2060
by: Christopher Weaver | last post by:
I'm having the hardest time doing the simplest thing. I have a DataGrid bound to a table in a master detail DataRelation within a DataSet relTaskActivities = new DataRelation("TaskActivities", parentCol, childCol); dsTaskActivities.Relations.Add(relTaskActivities); dgActivity.SetDataBinding(dsTaskActivities, "Tasks.TaskActivities"); This works fine. I have a properly behaving master detail relationship.
0
1104
by: Steve Lewin-Berlin | last post by:
For some reason (and I wish I could say specifically when) studio.net keeps dis-associating my event handlers with their events. In a recent frsutrating example, my (C#) page_load routine wasn't getting called. I used the gui in VS.net to associate the routine with the Load event, but then the code for one of my buttons wasn't running. When I re-associated the OnClick routine, vs.net deleted the page_load connection. The routine...
3
6055
by: Robert Ludig | last post by:
I am fairly new to SQL and I am currently trying to create a SQL table (using Microsoft SQL) that has a recursive relationship, let me try to explain: I have a piece of Data let's call it "Item" wich may again contain one more "Items". Now how would I design a set of SQL Tables that are capable of storing this information? I tried the following two approaches:
7
2016
by: Ron | last post by:
Hi All, Using Access2000, winXP. Table 1 = tblClients displayed on frmClients via qryClients. 2nd table = tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails shown on subform(to frmInvoices) sfrmDetails via qryDetails. Relationship built between tblClients/tblInvoices/tblDetails by ClientID. Relationship between tblInvoices/tblDetails by InvoiceID. All works fine if
1
1944
by: BuckBaxter | last post by:
Recently had trouble creating a one-to-many relationship between Customer table and Order table. After drag & dropping the field CustomerNo (key) in Customer table to CustomerNo (foreign) in Order table, the relationship wizard would begin making a one-to-one relationship. After a lot of searching, I still don't know why the wizard thinks I want a one-to-one relationship or how to change that. However, when I deleted the field CustomerNo from...
3
3466
by: Kush | last post by:
Hi. I was just wondering if someone could help me understand the relationship between the recv_hist_retention value and archive logs (and pruning of history file). My understanding is that the history file will be automatically pruned at the number of days specified in the recv_hist_retentn parameter. First of all, is that correct? Secondly, does the archive logs, that are older than the value in recv_hist_retentn value, also get...
1
2120
by: heatguru | last post by:
An Access 2000/2003 database has a form containing a memo field. Periodically the value of the memo field will show as #Deleted while the record itself is fine. The #Deleted value interferes with reports and an automated process. To correct the record, I have to recreate it and delete the old. Is there any way on the form, I can allow users to enter and edit, but not delete the value?
4
2447
by: sriram | last post by:
Hello Friends, I am new to this group so big HIIIIIIII to all :) fine i have a serious doubt about session handling in PHP. After 20 min (default time) session getting expired, session values are stored in a folder as a file, fine how php finiding that particular session expired? Because the files in session folder will not be deleted after 20 min but one session expired... Please explain me how this is happening? How php finds that...
0
9792
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11142
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10743
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10416
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7129
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5797
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5991
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3233
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.