Primary Key No Longer Primary Key!

Sep 15, 2006

I have created a very simple Access database with a CompaniesTbl, ContactsTbl and CallsTbl. The database is used to record telesales contact with customers. The tables are set up like this:

The CompaniesTbl has an AutoNumber Primary Key field called CompanyID.

The ContactsTbl has an AutoNumber Primary Key field called ContactID and a foreign key called CompanyID.

The CallsTbl has an AutoNumber Primary Key field called CallID and foreign key called ContactID.

The relationships are set up in the relationships window and referential integrity imposed.

Twice now, while editing a contact record, an error has occured. Task Manager has had to be used to get out of it. The error is either a 'run-time error with a message that does not mean anything' or it says 'unrecognized database format' (!?).

If you open the database again the ContactID field in the Contacts Table is no longer a Primary Key field. If you look in the relationships window - the relationship between the Companies and Contacts tables no longer exists.

Any ideas much appreciated.

View Replies


ADVERTISEMENT

Get Last Primary Key Value

Nov 12, 2007

Hi,
May I know how do I go about getting the last value of the primary key that I get inserted? I need this PK to link the parent and child tables together.
Thanks for any valuable help!

View 5 Replies View Related

Primary Key

Feb 27, 2005

Hello

I have a problem with a primary key. A table called relaties has a column relatienumber with date type: number, which is also the primary key. When a button is pressed, a function searches info from the table relaties.
The problem is the following: I figured out that when i remove the primary key constraint from the column relatienumber and save the table. the function doesn't work anymore, also when i remake the primary key for this column. How is this possible, because i changed nothing futher.

This is important because i want to change to access db to mssql db and in mssql the function also doesn't work.

It has something to do with this primary key constraint, i think :)

Who has the info???

View 3 Replies View Related

Primary Key

Nov 4, 2005

Is it important that primary key would be a number for performance purposes?
How do I set 2 different fields as one primary key?
How do I set 2 different fields as two separate primary keys?

View 2 Replies View Related

Primary Key

May 30, 2006

when defining tables, i normally set an autonumber as a primary key. However, i am working on a particular table, and i would like that no record will have identical values, in three particular fields



aaa bbb 123
aaa ccc 234
ddd fff 555
aaa bbb 123 <- i want to prevent this since a record with those three values already exists

Shall i set these three fields together as a primary key, or can i leave the autonumber as primary key and prevent such duplication in any manner ?

Thank You

View 6 Replies View Related

Primary Key

Oct 15, 2007

Can a PK be a calculated field?

I want to be able create a PK that consists of three strings being added together. The first two would be collected from cascading combo boxes. The third field is a simple text field.

I would then run DLookup to make sure the final string is unique.

I have reasons for not using an autonumber field (there are compatibility issues with existing tables from a much older dbase database).

View 11 Replies View Related

Primary Key - PLEASE HELP

Sep 10, 2006

I have a database that I got up and running but one problem. I have as the primary key a social security number, but I can't seem to be able to add additional information to the same person. All I can seem to do is just replace or edit the one record. Is there a way that I can have more then one order for an individual instead of taking the primary key off of social security numbers?

Do I need a seperate form to enter information because I am currently workin with one form where I can enter and edit the information but no duplicates so lets say if my social was 099-999-9999 I can't add an additional order it seem unl.ess I make a whole new record and make a dummy number...

View 1 Replies View Related

Configuring Primary Key

Nov 21, 2005

I have a tblState, every state can have multiple Counties, and every County can have multiple Municipalities
This should be a One to Many to Many, right????

However, several states might have the same County name

In order to resolve this, should I add 2 primary Keys in the tblCounty????

If so, I already tried and couldn’t configure it.

I have attached a sample DB
You can open the relationship view for a better explanation

I appreciate any kind of support

Thank you
Joe

View 13 Replies View Related

Primary Keys

Nov 28, 2005

I was wondering if anyone would be able to help me? I have two tables which can be linked together as they both contain a field called Company Name. However, in one of the tables I want Company Name to become my primary key but this field contains duplicates. How can Isolve this so that I can still link my two tables together?

View 2 Replies View Related

Reset Primary Key

Jan 21, 2006

Hi all,

Ive written a program in .net and am currently using an access db as the backend.
In the db I had 200 test records which i deleted to get the db ready for deployment. When i add a new record now the primary key on my "booking" table carries on from the old test number so 201.

Can I resest this so it starts back at #1 as once the db gets updated, the program i wrote then takes the data and updates the accounting system and uses the primary key as the invoice number and so I would like to start at 1.

Thanks in advance

Scott

View 5 Replies View Related

How Do You Change Primary Key?

May 31, 2006

How would I go about changing the primary key to a different field in a table? :o Thanks for any help!

View 2 Replies View Related

Primary Key Cannot Take NuLL Value

Jul 26, 2006

hey guys. i have a field by the name of ISSUE. i am using this afterupdate event procedure
which is as follow:

Private Sub ISSUE_AfterUpdate()
If Not IsNull(ISSUE) Then
ISSUE = Format(ISSUE, "000000")
End If
End Sub

if a user enter like (23) in my ISSUE field my procedure will run and it will replace
the number by (000023) to make it a six digit. i wanted to create a primary key so then a user cannot enter the same number again
or cannot duplicate a record but i cannot because primary key cannot take a null value. Can you all tell me anything to add in my procedure so that once the number is entered like (23)
another user cannot enter (23) again in the record.
Thanks

View 5 Replies View Related

How To Use A Compund Primary Key?

Nov 2, 2006

Hi
I have a little problem. I have a database with two tables: Nachweis and Entschuldigungen. In the first one u can add the records of what u have done for every day, the second one is for the days when u were absent.
I need to let the user to add in the second table only records which are NOT included in the first one. So if i have records for 10.10.2006 in the first table, i can't add records for 10.10.2006 in the second one. I wanna do it with help of the Compound Primary Key. But i don't even know where i should start.
Any help will be appreciated
Thanks in advance

View 5 Replies View Related

Primary Key And Indexing

Apr 4, 2007

Could anyone tell me about the pros and cons of Primary Key and Indexing?

View 3 Replies View Related

Foreign Key And Primary Key

Apr 12, 2008

it wuld be helpful if any of u can tell me how to link tables to query using primary key and foreign key or web page where i can solve my problem.

View 1 Replies View Related

Primary Key Problem

Apr 13, 2005

:eek: I have an order database via Access 2000, the backend became corrupted. I ran a a software program to recover the data. It recovered the data, but the autonumber was changed to just number and it removed primary key setting to the same field. I created a duplicate table, then ran an append query from the old table to the new one. The autonumber works, but I get the following error when I define the Primary Key and try to save...
"The changes you requested to the table were not successful, because they would create duplicate values in the index, primary key or relationship. " I cannot find any duplicates, and there is no relationships. Any suggestions? THANKS in advance for your help. Cathi

View 1 Replies View Related

Selection Of Primary Key

Apr 26, 2005

Hi, I have current yards table with Trailer number as the primary key and which cannot have duplicates. When this trailer leaves the yard we send this record to History table. Now if a trailer comes again and leaves History table is not accepting as it is duplicating. So can anyone help in designing table so that it accepts duplicate trailer numbers only in the history table.


Thank You,
Naveen

View 2 Replies View Related

Re-set Primary Key Autonumber

Aug 17, 2005

My shiny new database is ready to go - now I have deleted all my trial data and want to re-set the autonumber primary key of my main table to start at "1" (I thought I could delete it and re-insert it but it won't let me). Suggestions?

View 1 Replies View Related

Primary Key And Table Name

Jul 4, 2006

I am accessing a SQL database using Access but its not letting me view the table in design so I can set a primary or letting me change the table names.

I am able to do this from my home PC but not at work. Is there something in options or ODBC settings on the work the PC that I need to change.

Any Help will be greatly apprectiated.

JC

View 3 Replies View Related

Primary Key Limitation

Aug 1, 2006

I wonder if anyone can help me. I have a table in my database which currently uses 9 fields to make up the primary key. I wanted to add a further 2 fields to this to make 11, however Access will only accept 10. Does anyone know any way around this please?

View 7 Replies View Related

Primary Key Question

Sep 19, 2006

Hi, I want to create a customer no which will be the primary key. The problem is I want it to be of the order of: A0001234. The next entry will have the key: A0001235 and so on.
Is there a way to do this in Access alone or will I have to use a different front end and program this in Java.

Thanks to anyone who responds to this.

Don

View 1 Replies View Related

Table Primary Key

Sep 22, 2006

I have searched Key in the forrum and did not find quite my situation:

I would like to have a table that has a Primary Key that is a combo of 2 fields,

Example: Don't Allow a record to be created for the Same [Name] & [Date]

In the above the primary key would be the [Name] + [Date] but primary key only allows one field for key.

View 1 Replies View Related

Problems With Primary Key

Dec 20, 2006

:D Hi! I have a table that the primary key is autonumeric. I trying to do a form to add data to tnis table... but now i erase som rows of the table, and now the primary key jump to values. what can i do to restart the primary key to have the correct order without jumpping number? please :confused:

View 2 Replies View Related

Generating Primary Key

Jan 17, 2007

I have three fields, One called Key, one called IS and one called BC. I want the table to automatically copy the number from the IS field to the Key field to be used as the primary key. However, this field does not always have a value, when this occurs I want it to take the value from the BC field. Is this possible?

View 3 Replies View Related

Relationships/primary Key

Jan 22, 2007

I hope you guys can help me out, as 'Step by step Access' and the internet didnt help me out...

I'm about to create a database for monitoring assessment. Several patients will be assessed many a time. The main table will be one with a patientnumer and patient characteristics. For each period in time (T) I'd like to create tables which contain the different tests and dates on which they are administered and processed.

The problem I'm having though, is that the tables T don't have unique values and they need to be related to the patientnumbers.
Should I use autonumbering as primary keys in the tables T to relate to the patientnumber? I'd rather have the patientnumbers automatically appear in the tables T, or at least in the forms to be used by the final users of this database.

I hope this description makes some sense (English is not my primary language)

Can anyone help me out?

View 9 Replies View Related

Should A Table Must Have A Primary Key ?

Feb 19, 2007

I'm still new in ADP development and need some advices and helps from you who has already expert on it. Fyi, I used Ms. Access 2007 and SQL Express 2005. I found a problem when working with my project. Here is the problems :

I'm using form wizard to create master/detail form but why I can't insert record in the detail section (the navigation button also became disable) ? The detail table doesn't have primary key only have foreign key that related to the master table. Should a table must have a primary key if we use ADP ? As attached is my relationship diagram.

Many thanks in advance

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved