Primary Key is removed from table, but can't understand why!!

[jimtmelb1]

Hi,

I have an access 2000 database. Every now and then a user will get an error message "This recordset is not updateable". When i have a look at their database i see the primary key is missing from one of the main tables.
When i change back to primary key the error goes away.

Sometimes when i go to reinstate the primary key the ID field has duplicate records. So then i have to look for duplicate records, remove them and then reinstate the primary key.

Can someone please tell me why the primary key would be removed from the table in the first place. Is there something i need to look for.

If anyone can give me any information to help this sort of issue i would be very grateful.

Regards,
Jim

 

[willir]

Well Jim

I guess the first thing I would think about is security. The fact you have duplicate records suggests one of two things -- records are being added after the primary key is lost, or perhaps some one is having a problem and just maybe took to resolving the problem.

Can you estimate when the duplicate records occurred? This should give you a time frame.

Next, are you using autonumber, or your own mechanism for defining the primary key? If it is user driven, then this would further suggest user intervention.

Another stream of thought -- was the database imported / manipulated with code / queries in the past? These import codes and queries can be a killer if a user plays with them.

I can not see how a priamy field can be dropped by Access. But it is always a good idea to run the compact and repair.

Richard

 

[jimtmelb1]

Hi,

The user has no way of manipulating the tables directly.

The tables use autonumber field for primary key. My main concern is how the primary key can be removed when the user has no way of accessing the table direclty or using design view.

Any other suggestions
Jim

 

[ad2]

The following is copied from a thread here at tek-tips, sorry I don't know which thread, but maybe it holds a clue for you:


Why should an AutoNumber field NOT be used as a primary key


There is a good reason why autokeys shouldn't be used as primary keys in access but what this guy said doesn't make any sense. Access will always create a new (unique)key every time a record is inserted. Access also does not allow autokeys to be manually created by users or code. The problem is when many users are inserting into the same table at once. There is the unlikely, but possible, chance that two users will try to insert a new record at the same time and because Jet SQL creates autokeys on the front end first, a duplicate key could get inserted into the table. Large scale applications use a form of record locking to prevent this but for small applications you don't really need to worry about this happening. Hope this clears things up. You can further prevent duplicates by using more than one primary key, usually called combined keys.


I usually don't use autonumbers as primary keys because, as the "aside" statement indicates, there usually is nothing to prevent a duplicate record from being entered in an autonumber as primary key situation. Since autonumber generates a new number every time a new record is entered, the primary key has nothing to check against previous records that were entered to see if there is duplicate information.
On the other hand, for example, if you use something like a social security number as a primary key, you would not be generating a new number at the time the record is entered, as autonumber does. If you are trying to enter a SS# that has already been entered for an employee, the PK should catch it and return an error message, and you will not end up with a duplicate record for that employee. And, of course, you should never have more than one SS# for any one employee. That's what makes it a good primary key candidate.
There are instances where I use autonumber, for example when I have a table of transactions, (say, financial transactions, for example, which can often contain duplicate information) and I simply need a field to act as a control number when I am using code to track user input.
Hope this helped somewhat.


Consider the following table and data

Table
Field Type
ID Autonumber Primary Key
ProductID Text (10)
Product Text (50)
Price Currency

Data
ID ProductID Product Price
1 W001 Widgets $0.05
2 D002 DooHickeys $0.20
3 G001 Gadgets $1.00
4 W001 Widgets $0.05

The data is complying with the table settings, but you can still duplicate data. If however, the Primary Key is set to the ProductID field, the duplicate data will result in an error message advising of duplicate primary keys.

Of course, there are times when using the Autonumber field as the Primary key doesn't matter. These tables are quite often tables on the MANY side of a One-to-Many relationship, where the PK is only there to speed up sorting and querying and the main identifier is a Foreign Key..

 

[willir]

ad2

I believe the snipit from Tek-Tips was written by MichaelRed -- it is an FAQ in the Access tables and relationships forum.

We use 100's of Access database, just about all with autonumber, and I can not recall a single instance where autonumber caused a problem.

On larger systems, with many users, the chance of experiencing a problem with autonumber increases, and I concede to Nichael's advice.

For smaller systems, even with 10,000x's of records, but just a few users, I feel the chance of having a problem with autonumber is very small. The autonumber is just so much easier to use when one has less programming experience.

Now, as a side bit, I disagree with the way Microsoft generates the autonumber -- it is down before the insert event -- you start entering a number and the ystem grabs the next ID number. It should be done before the update event; the "before update" event occurs after the "before insert" event. I believe this is how the bigger systems, Oracle and Informix, generate their sequential number.

Back to jimtmelb1's issue...
He feels he can rule out security as a problem, which suggests that he is having problems with the database instead. Your idea to use a manually managed field for the primary key makes sense, but it is a fair amount of work -- changing primary keys -- have to change design, forms, reports and data.

However, I feel quite strongly that using a social security number is not a good idea since publicies confidential information to anyone using the database.

There are various ways of generating a unique key; regardless of the methodology, I feel it should be done in a centralized module.

Some ideas...

- Recently, one gentleman used a "select max(id)" to find the last number used and added one -- okay for small systems but there is the potential on larger systems of more than one person running the "select max(id)" during the same interval when a record is to be inserted.

- I have seen the date+time used. Same issues.
- I have seen a central control table used with record locking.

My preference would be to use a two prong approach. Generate the number (select max, or time, etc) plus use a component unique to the user. For example, user's (unique) initials + time. It is unlikely that the same person would update the same table at the same time.

 

[Sorrells]

jimtmelb1,

I find your question interesting. Did you solve it? I just recently discovered that in backing up data tables to a 'backup dB' that the primary key was removed. Most likely this is due to the fact that the backup takes place in the Front End and from it's perspective all tables are linked. Therefore, queries had to be run to replicate the tables in the backup dB.

You mentioned that 'you had a dB' but when looking at a 'user's dB' you found the PK missing. Is there any relationship to how you distribute the dB among your users? Is this happening to the same users repeatedly? How much control do the users have on the dB?

I'd be curious as to how this turned out!


Regards, Sorrells