Primary Key Problem

[Shailja]

Hi,

My back end is access. I have 2 tables in it.
1) State Master
Fields: StateID Number (Primary key)
StateName

2) City Master
Fields: CityID Number (P.K.)
CityName
StateID Number (Foreign key)

I have following data in State Master
1 Gujarat
2 Maharashtra

Following data in City Master
1 Surat 1
2 Mumbai 2

Now i am changing the ID of StateId in State Master table from 2 to 3. So the record in City Master must be 2 Mumbai 3 instead of s Mumbai 2. I am not able to update. how it is done through front-end or back-end? My front is vb and back end is access.

ie.When I change the ID of the state ie StateID , I want to make change in CityMaster also. How to do that? Please tell me if u know.

[ansumansahu]

Hi,

My back end is access. I have 2 tables in it.
1) State Master
Fields: StateID Number (Primary key)
StateName

2) City Master
Fields: CityID Number (P.K.)
CityName
StateID Number (Foreign key)

I have following data in State Master
1 Gujarat
2 Maharashtra

Following data in City Master
1 Surat 1
2 Mumbai 2

Now i am changing the ID of StateId in State Master table from 2 to 3. So the record in City Master must be 2 Mumbai 3 instead of s Mumbai 2. I am not able to update. how it is done through front-end or back-end? My front is vb and back end is access.

ie.When I change the ID of the state ie StateID , I want to make change in CityMaster also. How to do that? Please tell me if u know.

If you want to do it from the backend you will have to first drop the relationship between the master table and the child table. Put the new values in and then re create the relationship between the tables.

thanks
ansuman sahu

[Shailja]

If you want to do it from the backend you will have to first drop the relationship between the master table and the child table. Put the new values in and then re create the relationship between the tables.

thanks
ansuman sahu

How to do that in access? I dont know. Can u tell me?

[vijaydiwakar]

Hi,

My back end is access. I have 2 tables in it.
1) State Master
Fields: StateID Number (Primary key)
StateName

2) City Master
Fields: CityID Number (P.K.)
CityName
StateID Number (Foreign key)

I have following data in State Master
1 Gujarat
2 Maharashtra

Following data in City Master
1 Surat 1
2 Mumbai 2

Now i am changing the ID of StateId in State Master table from 2 to 3. So the record in City Master must be 2 Mumbai 3 instead of s Mumbai 2. I am not able to update. how it is done through front-end or back-end? My front is vb and back end is access.

ie.When I change the ID of the state ie StateID , I want to make change in CityMaster also. How to do that? Please tell me if u know.

See its a bad practise.
y ur changing the id?
So in future be aware of it.
For the time
goto Tools>Relationship
If u've made any relation ship then tbls will sbe shown if not then right click the window and add tables and create/break the relation ship and proceed
Good Luck

[Dököll]

These are great options, vijaydiwakar!

Another way of doing this would be to go right to VB and change data. Please forgive the lack of interest to use access. I work with it on the job, but I find it easier to control data through the front-end of things, than in access; especially that there is a VB Gui in the mix, I say go for it in that fashion. Nonetheless, I am leaning to agree with vijaydiwakar, you can easily update the data needed through access, I do it every six months. It's just not too friendly. If you are you are comfotable with access, perhaps you ought to give it a whirl.

I would also post in the Access Forum just to be covered, if I were you.

Please stay tuned :-)

Dököll