UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Table Loses Pk And Relationship, Access 2010    
 
   
ngins
post Mar 20 2017, 04:06 AM
Post#1



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


I have a client with a database that was created a long time ago by someone else. Recently, one of the tables has started at random times to lose its primary key and relationship to another table.

The table is called Owners, and has a autonumber ID field which is the PK.

There is another table, called Properties, which also has an autonumber ID field as the PK, and which has OwnerID as the foreign key.

The relationship between Owners and Properties is on the OwnerID field, and referential integrity is enforced.

Now, the way this was set up is there is an Owners form (with a Properties subform) which is read-only. The user clicks a button to open the "Edit Owner" form, which allows them to edit the current owner record.

Several times now the user has gone into the Edit Owner form, and when they close it, they see "#Deleted" in the fields of the owner record they were just in. (There is nothing in the code that deletes a record.)

But that's not even the strangest part. After this happens, the Owners table will have lost its primary key. The ID field is still there; but it's not longer the primary key.

Plus, how, you may ask, can the Owners record be deleted if it has child records, and Enforce Referential Integrity is in place? Well, the Owners->Properties relationship is gone too, meaning those Property records are orphans -- something that Enforce Referential Integrity is supposed to prevent. (Owners has another relationship to another table, and that other table relationship is still in tact.)

I've searched high and low through the code, and I can't find anything that would be causing this -- certainly not anything that would remove a PK and a relationship!

Oh, and also, one time when this happened, in addition to the PK and relationship being lost, the autonumber counter on the Owner ID field was set back about 20 numbers, resulting in duplicate numbers for the next 20 owners that were entered (PK was off, so duplicates were allowed).

The first thought in that case is that someone ran an append query which contained an ID value. But I checked, and no one was running one.

Also, this is a small company, and there are only about 3-4 people at most who would be using the database at any given time. Each of the users "own" their own Owner records, so another user wouldn't be editing an Owner record that someone else was working with, since they each manage their own Owner records.

My thought is that this is a back end issue, not a code issue.

I created a new back end ACCDB file and imported all tables into it. I also recreated the Owners and Properties tables (created new table; copied and pasted fields from old table; used append query to append the old data to the new table).

Any thoughts about what might be causing this, or what steps I can be taking to try and resolve this? Oh, and also, the back end is compacted nightly. So it's not a question of the back end needing to be compacted.

Thanks!
This post has been edited by ngins: Mar 20 2017, 04:13 AM

--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 07:15 AM
Post#2


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


>> Also, this is a small company, and there are only about 3-4 people at most who would be using the database at any given time <<

Would these users be using the same frontend, or do they each have individual copies of the frontend?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 07:17 AM
Post#3


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


>> Each of the users "own" their own Owner records, so another user wouldn't be editing an Owner record that someone else was working with, since they each manage their own Owner records. <<

How is this implemented?

Do they just have a field in the Owners table identifting each user's records, or do they each have a(n identical) table with their own records in?


--------------------


Regards,

David Marten
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 08:45 AM
Post#4


UA Admin
Posts: 28,846
Joined: 20-June 02
From: Newcastle, WA


This is often a symptom of corruption in a table. It happens when Compact and Repair is run on the accdb. Access will remove primary key restraints in order to preserve as much data as possible during the C&R operation when it encounters corruption in tables.

Please address the questions Dave raised about how the accdb is used. Sharing a single accdb is potentially a big part of the problem.


--------------------
Go to the top of the page
 
ngins
post Mar 20 2017, 10:15 AM
Post#5



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


Dave & George:

QUOTE
Would these users be using the same frontend, or do they each have individual copies of the frontend?


Each user has their own copy of the front end on their C drive, with a shared back end. The front end is an A10 ACCDE file. The users are using the A10 runtime.

I had the client check his runtime version, and it turns out that he was using the original A10 runtime, not the SP1 version that came out a couple of years later. So I sent him a link to the most recent A10 runtime, and said he was going to apply it to the machines. (It happened again after that, though I didn't confirm that he applied the new runtime version.)

QUOTE
Do they just have a field in the Owners table identifting each user's records, or do they each have a(n identical) table with their own records in?


No, it's all in one table. The Owners table is a child table of Municipalities, and each user is assigned a set of Municipalities, and they're the only ones who edit those records. So their name is listed in the municipality record. They go to one of their municipality records, click View Owners, and work with the owners for that muni, in the manner described in my previous post.

So it's POSSIBLE that someone else was editing an owner record at the same time as them, but highly unlikely, especially since in both cases this happened while they were entering new owner records, which other users wouldn't even have unless they requeried their form.

QUOTE
This is often a symptom of corruption in a table. It happens when Compact and Repair is run on the accdb. Access will remove primary key restraints in order to preserve as much data as possible during the C&R operation when it encounters corruption in tables.


We have had a problem with corruption in a different (but similarly-structured) database. In this other database, also with the Owners table, there would from time to time be a corruption where we'd get the "Search key was not found in any record error." Running a Compact and Repair on the back end resolved this. So I set up the databases to run a C&R automatically each night, to resolve any corruption issues that might have come into the database during the day.

I wonder, though, if doing a C&R nightly is too much. Doesn't seem that it should be. But I'd appreciate feedback on that, if it's hurting more than helping to do a C&R on the back end each night.

You might be wondering why there are multiple databases with the same structure (there are three, actually). It's because I didn't set these up, and the person who set them up created a separate database for each province, rather than just adding province as a datapoint in the tables. So we have three similarly-structured databases that I'm in the process of combining into a single database.

In the meantime, the "S" database, which had the periodic problems with "Search key not found," doesn't have that issue anymore, since I started the nightly C&Rs. But the "M" database, which previously didn't have any problems, is losing its PK in the Owners table, and the relationship between Owners and Properties, periodically. Has happened 2 or 3 times now in the past month or two.

Thanks for your feedback.




--------------------
Neil
Accessing since '96
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 10:32 AM
Post#6


UtterAccess VIP
Posts: 8,775
Joined: 6-December 03
From: Telegraph Hill


Are all the users connected over a wired network, or do any ever use wireless?

--------------------


Regards,

David Marten
Go to the top of the page
 
ngins
post Mar 20 2017, 10:52 AM
Post#7



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


No, they're all connected via a wired LAN.

The owner of the company sometimes works from home. But I'm pretty sure he just copies the back ends to his home computer and run reports. I don't believe he does any long-distance editing through the Internet, though I could ask him about that. Pretty sure he doesn't, though.

And I'll also double-check on the LAN, that none are wireless connections.

Did you have any thoughts about the compact and repair frequency question, and whether compacting nightly could harm the database?

Thanks.

--------------------
Neil
Accessing since '96
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 10:56 AM
Post#8


UA Admin
Posts: 28,846
Joined: 20-June 02
From: Newcastle, WA


I don't think there is any problem with C&R regularly and frequently.

The problem is that, if there is corruption already present in a table, that is when a C&R causes the table to lose its PK. It's a symptom that corruption has already occurred, not a cause of it. And the loss of the PK is the smoking gun, so to speak.
This post has been edited by GroverParkGeorge: Mar 20 2017, 11:04 AM

--------------------
Go to the top of the page
 
BuzyG
post Mar 20 2017, 11:01 AM
Post#9



Posts: 260
Joined: 20-September 12
From: Cornwall UK


I once created a database that periodicaly pulled together millions of lines of data from other data sources, mainy Excel sheets that couldn't handle the number of lines. Each time it ran it bloated significantly. I therefore used to run compact and repair every time it ran. To my knowledge it has never corrupted. That said I have had instances were, what appeared to be and uncurrupted database(but probably wasn't) has failed to work after a compact and repair. As a result I would always backup the database, before runing compact & repair.

--------------------
Live to Surf
Go to the top of the page
 
ngins
post Mar 20 2017, 11:17 AM
Post#10



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


QUOTE
The problem is that, if there is corruption already present in a table, that is when a C&R causes the table to lose its PK. It's a symptom that corruption has already occurred, not a cause of it. And the loss of the PK is the smoking gun, so to speak.


That is interesting, because with the "S" database, the C&R cleared up the corruption that it encountered. But with the "M" database, it causes it to lose its PK. I guess different types of corruption.

Also, keep in mind that losing the PK is only one problem. The main problem, I think, is that they end up with mysteriously deleted Owner records from time to time. (As noted, they close the Edit Owner form and find that the Owner record they had been in shows "#Deleted" in all the fields.) That's very bizarre, and I've never seen anything like that, though I've seen many corrupted databases. Never seen records automatically deleted, though!

And I wonder if using the original runtime version, instead of the SP1 version, was a factor?

Thanks!

--------------------
Neil
Accessing since '96
Go to the top of the page
 
ngins
post Mar 20 2017, 11:18 AM
Post#11



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


Buzy:

Good to know. Thanks for the input!

--------------------
Neil
Accessing since '96
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 11:45 AM
Post#12


UA Admin
Posts: 28,846
Joined: 20-June 02
From: Newcastle, WA


Not so bizarre. THAT IS CORRUPTION in the table causing that and it needs to be addressed.

I would create a whole new accdb. I would import all of the tables that appear to be stable and complete. Then I would append records from this corrupted table into a new one in the new accdb. Watch what happens when it tries to append some records. It will probably fail to do so for some records, and those are the corrupted records. You may have to delete and recreate them from scratch.


--------------------
Go to the top of the page
 
ngins
post Mar 20 2017, 11:57 AM
Post#13



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


QUOTE
I would create a whole new accdb.


Just did that yesterday.

QUOTE
I would import all of the tables that appear to be stable and complete.


Same.

QUOTE
Then I would append records from this corrupted table into a new one in the new accdb.


Did that too yesterday -- both Owners and Properties (even though Properties didn't seem to be corrupted). Created new table; copied all the fields from the old Owners table while in table design view; then pasted those fields into the new table in design view; then used an append query to append records from the old table to the new table; then deleted the old table.

QUOTE
Watch what happens when it tries to append some records. It will probably fail to do so for some records, and those are the corrupted records.


Nope. All records appended without any problem, except for two which were missing data in a required field. These were very old records from before that field was set to required. I put some data in that required field, and then those two appended fine as well. No problems whatsoever in appending the records with either table.
This post has been edited by ngins: Mar 20 2017, 11:58 AM

--------------------
Neil
Accessing since '96
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 12:24 PM
Post#14


UA Admin
Posts: 28,846
Joined: 20-June 02
From: Newcastle, WA


Okay, so going forward.

Make sure you always have a good, recent backup. Heck, more than one backup, so if something happens you don't lose everything.

Keep a close eye on this table. It should not continue to lose PKs and lose data.

--------------------
Go to the top of the page
 
ngins
post Mar 20 2017, 12:33 PM
Post#15



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


OK, will let you know how it goes. Thanks for your assistance!

--------------------
Neil
Accessing since '96
Go to the top of the page
 
ngins
post Mar 20 2017, 04:18 PM
Post#16



Posts: 293
Joined: 18-August 05
From: DFW, TX, USA


Just got a new bit of information. Just spoke to the user to whom it happened most recently, and she said she went into the Owners form, navigated to a particular Owners record, and as soon as she clicked in one of the fields, she got a message that popped up that said something like "Record deleted." When she closed the dialog box, the record had "#Deleted" in the fields.

So this is strange -- a self-combusting database record!

There is a Delete Owner button on the form. But it gives a confirmation message first, and then deletes all the properties and then the owner record. In this case, the owner record was deleted, but the properties remained, so I doubt very much that she accidentally clicked on that button -- especially since she would've gotten a confirmation message first.

So this is strange. Does this sound like potential behavior from a corrupted back end -- records spontaneously being deleted -- or more likely the front end causing it? I did do a decompile on the code, but, apart from that, can't figure out what it might be.

Thanks!


--------------------
Neil
Accessing since '96
Go to the top of the page
 
GroverParkGeorge
post Mar 20 2017, 07:32 PM
Post#17


UA Admin
Posts: 28,846
Joined: 20-June 02
From: Newcastle, WA


" Does this sound like potential behavior from a corrupted back end ..."

In my opinion, that is a classic indication of corruption. There may be another cause, but I'll bet money on corruption in that table.

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd March 2017 - 03:08 AM