Primary Keys in MS Access 2007

Forums › IT Pro and developers › Primary Keys in MS Access 2007

Sparky787

69 posts

Master Geek
+1 received by user: 2

#116793 10-May-2013 09:35

I hate to admit that I am using MS Access - however it is what is being taught to us right now in class for an introductory business/ICT paper on Databases.

Is it possible to enforce a unique value on the Primary Key

Have imported data from Excel - The PK is P1 through to P40

How can I enforce any new data to automatically know that the PK should be P41 for a new record?

Any suggestions?

Cheers Guys!

Sparky

freitasm

BDFL - Memuneh
80652 posts

Uber Geek
+1 received by user: 41036

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#815029 10-May-2013 09:42

Is it a number or string? Are the values "P1" or "1"?

Referral links: Quic Broadband (free setup code: R587125ERQ6VE) | Samsung | AliExpress | Wise | Sharesies

Support Geekzone by subscribing (browse ads-free), or making a one-off or recurring donation through PressPatron.

sittingduckz

689 posts

Ultimate Geek
+1 received by user: 288

ID Verified

#815031 10-May-2013 09:43

I think I understand correctly, make the PK field AutoNumber with a format \P#.
With each new record it will count up P1 P2 P3...

Or drop the P and just stick with Integers

I'm not a complete idiot, I still have some parts missing.

networkn

Networkn
32862 posts

Uber Geek
+1 received by user: 15453

ID Verified

Trusted

Lifetime subscriber

#815033 10-May-2013 09:50

I thought the best practices way was to use a guid.

sittingduckz

689 posts

Ultimate Geek
+1 received by user: 288

ID Verified

#815036 10-May-2013 09:54

networkn: I thought the best practices way was to use a guid.

But it sounds like he is importing data that already has existing ID values, changing to a guid would mean having to correctly change all the related tables as well?

I'm not a complete idiot, I still have some parts missing.

Sparky787

69 posts

Master Geek
+1 received by user: 2

#815287 10-May-2013 14:58

Hi Guys

I imported the data from excel. The Alpha-numeric was to make it able to identify between the three different tables.

http://www.databasedev.co.uk/reset_autonumber.html

Found the above link, it looks like it might work, but need to test it.

Cheers Guys

Sparky

sagasu

1 post

Wannabe Geek

#815532 10-May-2013 23:10

If you cannot make the primary key field unique it means that you must have imported a duplicate value. A primary key must be unique no matter what database management system you're using.

Mighty Ape

Shop now at Mighty Ape (affiliate link).

ShadyG

26 posts

Geek

#815629 11-May-2013 09:43

As Sagasu says, If you can't make the key field unique, then you've got some duplicate values in there. You need to inspect the table to find what they are.
If you absolutely must use P1... Px for the Pk, then what you have to do is this (C# pseudocode):

1. get the maximum value from the ID column. As all the values in it start with 'P' then it will return the max value of the trailing integer.

2. Split the trailing integer from the leading string, by reading the (length - 1) right hand characters and converting to an integer.

3. Add 1 to the number you get from step 2, convert it to a string, and add 'P' to the front of it.

4. Use the resulting string as your index.

OR...

set a new column in your Access table as ID (or somesuch), and set its Data type to auto number. That will take care of all your ID issues.