Access Table HELP! 1

[kovas]

A few questions here...

I have a customer info table. When I view it in Ascending sort it shows 7824 records, When I view it in Descending it shows 8764. Why is that?

I use CustomerID as an autonumber which should be unique?

But for some reason I have a few customers with a duplicate ID. I tried to delete the duplicates but it wouldnt met saying that "The search key was not found in any record"

I tried changing CustomerID for the duplicates and it wouldnt let me.

After all this I decided to Repair the database. When I go to Compact and Repair the Database in tools, it deletes all the records with an ID higher than 7849. Basicly it only keeps the records that I can see in Ascending Sort. But like I said when I sort Descending there are 1000 extra records that I can see, which get deleted when I use the repair feature.

Any help/suggestions greatly appreciated, thank you

 

[vb6novice]

kovas,

You questions spawn more questions.

What are you looking at that you think tells you how many records are in the table?

What field are you sorting on?

If you remove all Filters & Sorts (right click on the table's title bar and select Remove Filter/Sort) how many records do you think you have?

 

[kaushal78]

Basiclly when u give the Auto Number to any field and that as the primary key u cannot have duplicate value...
U have click the Auto Filter jut remove the auto filter
or
pass a query with the select statement and check how many records are there...
If u find the duplicate records just delete the records and with the other coulumn applying where condition
--i hope it would solve ur doubt
Bye
kaushal

 

[kovas]

vbnovice i removed all the sort and filters.

It shows 7849 records

but there are about 1000 more than that.

If I sort descending with no filters there are 8765 records.

The database somehow got corrupted since i have 14 duplicate autonumbers. If i try to repair it, it deletes all the records with an autonumber higher than 7849.

I tried to get rid of autonumber and change it to numeric and make my own ID number but this gets rid of all Autonumbers higher than 7849 as well. Making them blank.

Deleting the duplicate autonumbers gives an error "The search key was not found in any record"

Very frustrating cause I cant afford to lose these records.

Anyone with any ideas?

thanks

 

[Nyg]

"The search key was not found in any record"


I have had this problem, the problem was created when I made a mistake and changed an autoincr field manually then when I tried to exit the record I got a message box saying the record was not in exclusive mode and someone else may be editing the record even though it was on a stand alone pc and then asked me whether I would like to "Save to Clipboard", "Cancel" and one other can't remember the other option.

I saved to clipboard but from then on I could not go back in to the record to correct the error and change the autoincr number back. The files somehow get corrupted and then corrupted all related records, I don't know how to fix the problem but this reason may help someone else who knows the answer.

I am sorry but after all of that I had to use a previous backup to reinstall and the re-enter all info from then.

 

[ssecca]

What you might try is to set up a make table query that that copies all the data except the autonumber field to a new table. This will protect the data you currently have. Then modify your make table query so that it includes the autonumber but include it like this

OldAutoNum:cstr([oldAutoname])

I've never done this but what I believe will happen is you will create a new table that contains the old autonumber field as a text field for the sake of reference you can then modify the incorrect data and go from there.

The biggest problem will be getting it back to what you need with out losing the links to other tables. To do that once the data is corrected you need to create an append query that will append the data into an exact copy of the original table (different name) remembering to convert the now text autonumber field back to a long integer. If you take time and care you will probably be able to fix this problem.

One last point I cannot stress enough to people that using the repair routine is really a bed idea. Only use it when the application suggest to use it and then with great trepidations it can cause a lot more problems than it resolves if used at a wrong time or for the wrong reasons.