Access deleted my relationships! Now I'm lonely!! : ) 1

[JJman]

Somehow Access deleted the permanent relationships I had established between three tables for a receiving/completion process we have. I had outer joins between the receiving table and completion table (so all receiving records would show and any completion records that might exist) and an outer join between the completion table and an "Out To" table that records other teams/depts parts of a batch might need to go out to (all completion records show and any records in the "out to" table that might exist). I see nothing wrong with the table relationships, but even if there were problems I can't imagine why Access would delete them. Further, it would not allow me to re-establish the relationships until all records in the completion table and the "out to" table were deleted. Also, now there is a problem on the receiving side where, though it always goes to a new record when the receiving form is opened (and after an item is received), it is trying to re-use autonumber ids and giving people duplicate record errors. What might I have done wrong?
Thanks!

 

[RickSpr]

One possibility is that you have a corrupted database. Are there any other signs of that?

The duplicate autonumbers you mention--are they keys in the Completion and Out To tables? That would be explained if you compacted the database after you cleared these tables. When you compact, Access automatically locates the highest number in use and sets the next Autonumber to one higher. If there are no rows, it sets the next Autonumber to 1.

The fact that Access wouldn't let you define the relationships until you cleared the child tables suggests that, by that time, you already had out-of-sync keys, which would suggest that the loss of the relationships occurred at some previous time.

The most likely way the relationships were lost would be if the Completion table was dropped and recreated. Could it be that you made a duplicate table at some point, copied the records to it, renamed and deleted the original table, and renamed the new one to Completion? Perhaps you don't realize that relationships are associated with the actual table, not merely with the name. When you deleted the original table, the relationships were dropped with it. If the table has an Autonumber key, that could also explain how the Autonumbers got hosed, depending on how you copied the records to the new table. If you used an Append query to do the copying, the Autonumbers would be retained, but if you used any other method new Autonumbers would have been assigned.

If you use a MakeTable query on an existing table, the table is deleted and recreated--but the relationships, which are deleted with it, are not recreated.

In general, it's not easy to drop a table without having Access warn you that the relationships will go with it, but if SetWarnings False is in effect, you might not get the warning. SetWarnings False is often used when code is used to manipulate table design, and sometimes when manipulating records behind the scenes (especially when doing bulk updates or deletes). If the code doesn't do SetWarnings True before it exits--say, because an error aborted the routine, or the error was trapped but the code didn't clean up before exiting--it won't be obvious that the warnings are turned off. That creates the possibility that a MakeTable query could delete the relationships without your getting a warning about it. Check whether you have any SetWarnings in code, and if so, make sure they're balanced False and True regardless of any errors that might occur.

If you post again, explain how the tables are related (parent keys and foreign keys of each), and whether you are enforcing relational integrity in the relationships.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[willir]

Rick

I am giving you a star. Great discussion.

JJman

I use the "Lookup" property in the table design, and use a combo box to link my foreign keys. This gives you control over the link, and I find it also helps save time when designing the forms.

Richard

 

[JJman]

First, thanks to both of you for your help!

The autonumber is the key field in the receiving table, and this is the one that was giving some (but not all) users a duplicate value error. I didn't have to delete all the receiving records, only the completion and out to records, so this duplicate values issue is related to a table that hasn't changed. However, I did not have this issue before the relationships were lost,etc. Here is a summary of my tables/relationships;

tbl_Receiving_Record (record of batches received)
PK: Rec_batch_id (autonumber)

tbl_Completion_Record (record of batches completed)
PK:Comp_id (autonumber)
Rec_batch_id (linked to tbl_receving_record)

tbl_Outto_Record (record of where parts of batches must occasionally be routed. Subset of tbl_Completion_Record)
PK: Outto_record_id (autonumber)
Outto_id (linked to tbl_Outto below)
Comp_id (link to tbl_completion record)

tbl_Outto (table with text descriptions of the teams/depts that parts of batches may be routed to.)
PK: Outto_id (linked with tbl_Outto_Record above)
Outto_Description (text used in combo box in form)

Basically, an item is received and gets a rec_batch_id, then multiple people may complete different parts of it, creating multiple Comp_id's in the completion record for each batch. Within each Comp_id, there may or may not also be multiple Outto_id's, one for each team/dept something is routed to. I joined the receiving record and completion record by dragging the PK (rec_batch_id) from the receiving record to the same field in the completion record. I then double clicked the join, selected Enforce Referential Integrity and Cascade Delete Related Records. I then dragged the PK (Comp_id) from the completion record to the outto record, clicked Join Types and chose option 2 (all records from completion record and only records in outto record where equal. Correct me if I'm wrong, but this should enable me to see all receiving records, whether completion records exist for them or not, and all completion records whether outto records exist for them or not.

As you have probably already figured out, I'm not exactly an Access guru, so your help is appreciated! I also apologize if this is overkill, but I'd rather give too much info than not enough.

Till next time,
Joe

 

[RickSpr]

Joe, don't worry about providing too much information. It's better to have it and not need it than to need it and not have it. I compliment you on the excellent quality of your information, too.

You didn't say that you chose option 2 for the join between tbl_Receiving_Record and tbl_Completion_Record, but you said originally that you had an outer join there, so I'm going to assume your tables and relationships are set up correctly. Your understanding of how it should work is correct.

So we're back to the original question of how the relationships got deleted. Are you still in development, or had you deployed this database to the users before the problem was discovered? If in development, the most likely cause was some kind of procedure you went through to fix a table design problem. If deployed, it could be either something in your code, or tinkering by naughty users. Have you blocked their access to design mode?

In any case, is this a recurring problem? If so, we can probably narrow down on what happened.

But if the problem doesn't recur, we'll probably never know what happened, and it's not really worth spending a lot of time on it. My earlier post describes some ways the relationships could have gotten deleted without you explicitly deleting them. Just take a lesson from that and move on.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[JJman]

Hi again,

You are correct about my selecting option 2 between the receiving record and completion record. I apologize I didn't mention that.

This is a new addition to an existing database, and development is almost finished. Users have been training on it/testing it in preparation for its rollout.

I have "blocked" users from going into design mode only in that the switchboard is the only thing visible on start up, not the database window. As for those who might know about the magic F11 key, all the objects are hidden, so they would also have to change setting to view hidden objects. Regardless, I really don't feel it's anything like that.

On a positive note, I did a small-scale stress test on it earlier where about 150 batches were received in five minutes by eight different users. Although the system paused for a second or two at points, there were no duplicate value messages, so I'm hoping it is now a non-issue.

I am concerned now about possible corruption in the database, which I already know exists from previous problems. I'm somewhat frustrated because I've found a lot of information about corruption symptoms, etc., but I don't understand how to detect/fix/prevent it. The compact & repair feature doesn't seem to do much, and if the database won't even open, you can't use it anyway. I've also read that copying all the objects into a new blank database helps, but if the corruption is caused by one of the objects, you're not helping anything. I'm really looking for exact steps (as simple as possible) to follow to clean out any corruption and to prevent it as much as possible in the future. Any help/websites/articles along those lines would be a great help

Thanks!
Joe

 

[RickSpr]

As for those who might know about the magic F11 key, all the objects are hidden, so they would also have to change setting to view hidden objects.
While that's true, it fails to recognize how easy it would be to damage the relationships. As soon as they hit F11 by a finger slip, they'd have the Database toolbar, with its button for the Relationships window. (Or, they could right-click the Database Window.) However, I'll accept your assurance that your users wouldn't carelessly play around with the innards.

Corruption has never been a problem for me. I hope that's mostly because I dot all my "i"s and cross all my "t"s when I write code. But it's probably more because I've never had to support a high volume back end on a network.

For Access 97 (and maybe 2000, I can't recall), Microsoft offers a compaction utility called JetComp.exe that will compact and repair more problems than the built-in function. It also works from the outside, so you can use it on a database you can't open in Access. You can find it in the Knowledge Base. That will take care of the fixing part.

For prevention, I think an important principle is don't do design work in a database that others might have open. Although there are design locks in the software, I get the sense from other people's corruption problems that they don't always work across a network. Perhaps it's a timing issue.

Other than that, I don't know much about what causes corruption, but I believe there are some white papers on the Knowledge Base that discuss it.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein