Recording Primary Keys

[loki]

OK here goes.

I have an Access 2000 database that is hit by multiple users. I have many table that depend heavily on their primary keys. There are daily imports that run and if the tables are missing their primary keys they are populated with a lot of duplicates. A compact and repair is run every morning after the import and sometimes due to corrupted records, a MsysCompactError table is created and I lose the primary keys off some of my tables. I want to create some kind of function that will search through some or all of my tables each day and determine if a specific table still has a primary key associated with it. I could store these results in a table and then maybe create a report to get the info. Does anyone have any ideas as to where I might start in creating this function.

Thanks for any help.

 

[llkhoutx]

Another approach is to import your data into a temporary table, verify primary keys and or build new ones. Then query into the table which hold all your data.

 

[The_Doc_Man]

I guess I'm being dense, but ....

If the table in question has that field properly set up as a primary key, how in Bill's name do you ever create a non-unique entry in that field in the first place?

Something doesn't sound right here.

 

[loki]

The problem is that when a compact and repair is run, and there are corrupted records it some how removes the primary keys from the tables. I need a quick and easy way to check all my tables to make sure the primary keys are still there.

 

[loki]

Pat you are a great man. I have not tried the code yet but it looks good.

Thanks a lot

 

[loki]

Hey Pat
I hit this line

Set QD1 = ThisDB.QueryDefs!QdeltblTableIndexes

and it gives me that the item is not found in the collection.

 

[loki]

Hey Pat

I created a query to delete all records from tblTableIndex and called it QdeltblTableIndexes and the code now works great. Thanks again for all your help, It gave me exactly what I was looking for.