I would like to have a routine that would check the keys of all the tables in a database and re-establish those that are missing.
It seemed possible to have a table which stores information about the other tables in a database and draw from it to examine and re-establish the keys.

Has anyone done this? Seems like a very useful feature to have; particularly useful after running a Compact/Repair to verify and re-establish keys.
Of course the next step would be to automate the re-establishment of relationships, but that can be for another question.

I have seen a couple of programs that do this, but would rather just have it built into a backup/compact app I have created.

Thanks for any input or help!

I'm trying to picture how this would work in practice.

Let's say you have a table with 100 records in it. So your "inventory" table would have 100 records duplicating the records from that table along with a field for the name of the source table in it?
So you do a C & R and for some reason, 1 of the records in the original table goes missing. You look in your inventory using a Frustrated outer join and find that record 22 is the missing one. So you can do an append query to restore it.

Is that what you have in mind, more or less?

Basically, a plan like this could work, but it will double the size of the database because every record in every table will have to be stored in your inventory table so it can be retrieved, if needed. Let's say that the table has 100,000 records. Doubling the size of the database to handle that inventory table would be now be a different kind of problem.

Again, feasible but a lot of storage space and work to make it happen.

Why not just make a regular back up of the database just prior to doing the C&R? That way, you don't have to manage duplication code to keep the "inventory" table up to date, plus if something goes wrong, you restore the backup and be done with it.

I can see doing some sort of integrity check after a C&R, using the backup copy for comparison, if you feel the need.

But, you say you've seen other programs that do this. How do they handle it? Are they really creating internal tables duplicating all of the records in all of the tables? I know SQL Server mantains transaction logs that can be used to restore an mdf, if the need arises, but that does seem like a different kind of thing from what you're describing.

Thanks for your response

The keys I am speaking of is not the actual data, but which field is set as the Primary Key for each table in the database.

I envisioned a table that would have a record for each table in the database. In the record would be information regarding which field is the primary key and possibly other indexes.
The routine would scan each table in the database and would check to see if the Primary Key is still in place and if not, re-establish the appropriate field as the primary key.
Does that make sense?

Go to Allen Browne's website and download his DAO code library for primary/foreign keys. Create a table to store something like:
ParentTable, ChildTable, ParentPK(s), ChildFKs.

then you can walk the indexes collection of each table and write the data to your table before Compact & Repair, then compare them after. If they don't match, take some kind of action.

Hmm, I've never had a Primary key lost after a C&R. Theoretically, this should be doable. Creating your table of keys is nothing. Assigning a field as a PK is also easy. Detecting whether a PK is missing is a different issue.

"The keys I am speaking of is not the actual data, but which field is set as the Primary Key for each table in the database."

That's a rather different kind of concern. So you are worried that C&R might "disable" or "remove" the Primary Key designation for a field in a table? I suppose that could happen, but like Scott I have never seen or heard of it happening. Has this happened to your databases?

Anyway, as MadPiet and Scott say, creating the inventory table of tables and key fields is relatively simple. Even comparing a pre-Compact and Repair table of keys to a post-Compact and Repair table of keys might not be all that difficult. In the end, though, it just seems like a solution in search of a problem.

I generally would agree, but I have found recently a database we use that had a missing primary key in a few tables that had gone under the radar for some time.
While the database has seldom come up corrupt ( I mean like maybe once or twice in 5 years), I assume that the key and relationships could be lost during a compact/repair.

This routine check would just provide an immediate heads up should it ever happen, that a primary key designation has been lost.

Thanks for all the input!

When you said that detecting a key is missing is a different issue..
Is there VB that can look at a table and see if there is any Primary Key and which field it is?

" a database we use that had a missing primary key in a few tables that had gone under the radar for some time."

Do you SPECIFICALLY know that these missing primary keys were the direct result of a C&R? That's the point. If the PKs were, at one time defined, but then, DIRECTLY following a C&R and BEFORE doing anything else, they were no longer defined, then you are looking at a substantiating case. However, "under the radar for some time" suggests to me that any number of other things could have happened, including human intervention--unintended or intended.

I am not going to flat out assert that it could not happen. After all, strange things do occur.

Nonetheless, this is just not a problem that I have personally seen or heard of. That said, what I will do now is post the question to some MVP forums where we can hope to get more information.

Follow MadPiet's advice and look at Allan Bfrowne's site for examples

That's the problem. I've never encountered any VBA that would do that. Doesn't mean it doesn't exist, but I've never heard about it being done.

Scott, to me, this seems like any other audit/inventory task. VBA couldn't detect a "missing" PK, but it could do a pre-C&R inventory of all existing PKs and FKs and then a post-C&R inventory. Allen Brown has code to identify relationships and indexes. The specific function is called "DescribeIndexField". It could probably be adapted to this "inventory" requirement.

Then, once you had a pre-C&R table and a post-C&R table you could run Frustrated OUTER JOIN queries on the pre and post tables and see if any PKs or FKs had gone missing.

As I say, though, until we get substantiation that the C&R process itself is capable of removing a PK or FK, I still think it's mostly a theoretical, rather than a practical, issue. And a LOT of work to set up and maintain to boot.

Let's take this to Arvin's MVP forum. Someone may have seen the C&R --> deletes PK/FK problem. I wouldn't flat out say it can't happen even though I've never heard of it before.

Oh, here's another thought that was just passed to me from another MVP.

Changes to databases don't always get saved as we think they do. It is entirely possible, for example, that the "missing" PK designations in your example database were never saved by the person who was supposed to be defining them in the first place. If DoCmd.SetWarnings was set to false (whether on purpose or accidentally left set to false in code), and then changes were made to tables, it could have been the case that the changes weren't saved.

There are other possibilities as well, including importing tables from a different database, one at a time.

Long story short, while the C&R COULD be involved, I think it is much more likely that any "missing" PK designations came from a different source. You'd be better off investing time in tracking that down, rather than trying to solve a problem that isn't very likely to occur, IMO.

Actually, I have seen something similar to this. The issue occurrs when data in the PK field (or FK field) becomes corrupt. When that data becomes corrupt, the db engine can no longer adhere to the constraint of the PK, or possibly the FK can no longer adhere to an RI constraint, so as a reaction to the inability to adhere to a schema constraint, the db engine (Jet/ACE) will drop the PK constraint, and/or drop a Relationship w/RI in order to preserve as much data as possible.

When this has happened to me, I was not warned specifically of this issue, but I did get "wierd" behavior, which prompted my dba reaction to perform a C&R. However, if I recall correctly (and that is a big if), the loss of the PK constraint and the Relationship w/RI occurred prior to the C&R.

That, Brent, makes sense. The reverse explanation would be that the C&R itself introduced the corruption into the table independently of any prior state of the tables. IMO, not likely. It occurs to me to wonder if Michael Kaplan ever talked about this. If anyone knows it would be Michka.

That makes sense. Doing a before an after inventory. I'll have to look at Allen's code one of these days.

I hadn't looked at this particular function until this thread. But he has an example of nearly anything you can think of, so it's a good place to look....

I bingoogled "Missing Primary Key" or something similar and looked for Allan's name in the response list. (IMG:style_emoticons/default/grinhalo.gif)

This post has been edited by GroverParkGeorge: Apr 5 2012, 02:34 PM

Maybe we can thread this needle.

I still don't think C&R by itself drops PK or restraints. No evidence has been brought forward that it does.
However, it does appear to be the case that C&R can result in dropped PKs or restraints in the presence of prior corruption in a table.

Is that a fair statement?

SO, it is apparently agreed upon that a corrupted database could have the PK dropped when going thru the C/R as it ensures that as much data as is possible is retained.

In any case, the purpose of the routine I suggest is to ensure that "everything is in it's place", ie all keys are present.

If a database is corrupt, scanning it prior to a C/R may not be an option if MSAccess decides it is an "Unrecognizable Database".
It seems that a pre-scan would be unnecessary if one already has a list of the keys for each table stored. The post scan would use the "list" to verify that all keys were present.

Thanks for a stimulating discussion and a chance to learn something new.

To be quite honest, I did start out thinking this was a solution looking for a problem.

I can see, though, that for someone managing a lot of different accdbs or mdbs it could be useful. For someone who didn't have a good idea of the architecture in any single one of them, it could be useful to have that reference. Having an "inventory" of key fields might prove to be helpful some day down the road when a database goes corrupt just before a C&R. Pre-scan is a relative term, in this context. As long as you are sure the stored inventory is accurate as of the date of the C&R, it could be many months old and still be usable.

I've always thought that one good way to store a table schema would be a relationship diagram, printed out if it's not too complex, or reverse engineered into Visio. And that's sort of how I look at it for my own purposes. Also, I tend to keep multiple backup generations of the dbs I work on, so I would probably count on yesterday's copy for reference in such cases.

This "inventory" table definitely adds another tool to the toolbox. Thanks again.

Its called a data dictionary and the Access Database Documenter can help create one.