UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Replacing A Table And Recreating All The Relationships?, Access 2013    
 
   
EdNerd
post Mar 13 2019, 04:06 PM
Post#1



Posts: 816
Joined: 23-May 11



A few years ago I created an employee database. (Could not have done it without so much help from UA!! notworthy.gif uarulez2.gif ) I am no longer the "admin" for it - that's been shuffled over to someone else who is good at Excel and took an Access familiarity class. And she was tasked with revamping the database.

Long story short, I'm doing the remodel work (funny how that happens, yah??). They want the main table "adjusted" - it's got data fields they no longer want to track, and needs some different fields that aren't in the original. (Really, I can do what I want - they are familiar with Excel, do not understand the inner workings of Access, and really only know what they want to see and do in the forms.)

I created the new table as they desired, making sure the existing fields had the same name - especially the PK. I renamed the original table as "tblEmplData_Old", and named the new table as the original - "tblEmplData". But as I open the relationships window, all of my relationships to the main table PK are to "_Old"???

Is there an easy way to slide out the old table and slide the new one into it's place, keeping all the previous relationships? Or would it be better just to adjust the fields in the original ta ble and not mess with trying to replace it? Or go ahead and replace it, but I'm going to have to recreate all the relationships (about a dozen other tables)?

Ed
Go to the top of the page
 
theDBguy
post Mar 13 2019, 07:01 PM
Post#2


Access Wiki and Forums Moderator
Posts: 74,683
Joined: 19-June 07
From: SunnySandyEggo


Hi. Not knowing how much work you really have to do, I would have probably copied the original table and name it with _bak, then, just adjust the original table to the new design.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Mar 14 2019, 04:25 PM
Post#3



Posts: 816
Joined: 23-May 11



I'll give that a shot.
Thank you!!
Go to the top of the page
 
theDBguy
post Mar 14 2019, 04:37 PM
Post#4


Access Wiki and Forums Moderator
Posts: 74,683
Joined: 19-June 07
From: SunnySandyEggo


Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Mar 14 2019, 11:19 PM
Post#5



Posts: 816
Joined: 23-May 11



Ok -- big wrinkle!! Part of the update is to delete all the existing data and replace it with new data. Same fields, but deleting people who aren't here now and adding in many new ones. It looks easiest just to delete all records and paste in all new. Remaining employees are contained in both data sets.

But I get an error in deleting - other tables have related records. Of course they do -- this is a relational db. I can't just paste in the new record set -- I'd duplicate employee numbers, which is the PK. I could sort out the new people not in the existing table and paste them in with no conflicts. And the remaining employees are not an issue.

But the old ones that need deleting?? How do I get by the conflict error??

Ed
Go to the top of the page
 
PaulBrand
post Mar 15 2019, 03:44 AM
Post#6



Posts: 1,687
Joined: 4-September 02
From: Oxford UK


You can bypass referential integrity

--------------------
Paul
Go to the top of the page
 
EdNerd
post Mar 15 2019, 06:06 AM
Post#7



Posts: 816
Joined: 23-May 11



>>> You can bypass referential integrity

So -- in the Relationships window? Adjust any connection to not enforce referential integrity?
Or is there an easier way??

Ed
Go to the top of the page
 
theDBguy
post Mar 15 2019, 08:14 AM
Post#8


Access Wiki and Forums Moderator
Posts: 74,683
Joined: 19-June 07
From: SunnySandyEggo


Hi Ed. Rather than bypass, maybe you could take advantage of Cascade Delete.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
EdNerd
post Mar 19 2019, 10:54 PM
Post#9



Posts: 816
Joined: 23-May 11



As usual, Sir, that was the answer!!
Thank you!!!
Go to the top of the page
 
theDBguy
post Mar 20 2019, 12:30 AM
Post#10


Access Wiki and Forums Moderator
Posts: 74,683
Joined: 19-June 07
From: SunnySandyEggo


Hi. You’re welcome. Glad you got it sorted out.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th March 2019 - 04:10 AM