Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-23-2010, 07:14 AM   #1
darbid
Newly Registered User
 
Join Date: Jun 2008
Location: Munich
Posts: 1,428
Thanks: 6
Thanked 38 Times in 35 Posts
darbid will become famous soon enough
How to deal with table relationships where table is deleted and Inserted

Access 2003 front end - SQL Server 2000 back end.

I will soon receive 3 tables in Excel that I have to add to SQL Server.

These tables are related and will also have other tables related to them.

I will have to delete all rows from the existing 3 tables and then insert all information from excel.

My problem is I am not sure where to start with relationships. I will surely get an error if I delete all rows if relationships exist.

So what do I do. I am hopeing that from Access I can remove the relationship - delete - insert and add the relationships back.

Is this correct?

edit: This is not a one time event, this is going to happen at least every quarter so I am hoping to automate this for users to do so that I do not have to do it.


Last edited by darbid; 08-23-2010 at 07:21 AM.
darbid is offline   Reply With Quote
Old 08-23-2010, 10:01 AM   #2
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,255
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: How to deal with table relationships where table is deleted and Inserted

I normally only define a relationship in the relationship window if I want to use enforce Referential Integrity (RI).

Since in your case you will not be able to use Referential Integrity (RI), there really is no need to define the relationship in the Relationship Window. The only advantage you would gain by defining the relationship without RI is that Access will automatically create a join in the query designer.
__________________
Boyd Trimmell
Helping your business do more with Microsoft Access.
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Old 08-27-2010, 08:21 AM   #3
SQL_Hell
SQL Server DBA
 
SQL_Hell's Avatar
 
Join Date: Dec 2003
Location: UK
Posts: 1,331
Thanks: 15
Thanked 39 Times in 37 Posts
SQL_Hell will become famous soon enough
Re: How to deal with table relationships where table is deleted and Inserted

You can use drop constraint to delete relationships.

Or SSIS has options to handle this for you

SQL_Hell is offline   Reply With Quote
Old 08-31-2010, 01:39 AM   #4
darbid
Newly Registered User
 
Join Date: Jun 2008
Location: Munich
Posts: 1,428
Thanks: 6
Thanked 38 Times in 35 Posts
darbid will become famous soon enough
Re: How to deal with table relationships where table is deleted and Inserted

Thank you guys for your answers. I must admit I do not understand them, but maybe I do not fully understand this side of things enough yet.

I am tied down doing some other things at the moment, but I will be back as I will have to do this soon.

thanks

David
darbid is offline   Reply With Quote
Old 09-01-2010, 07:52 PM   #5
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,091
Thanks: 6
Thanked 62 Times in 58 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
Re: How to deal with table relationships where table is deleted and Inserted

darbid,

This can get quite complex.

In an ideal world, you can:

1) DISABLE the constraints.
2) Delete/Import the data
3) ENABLE the constraints

However, when you hit step 3, due to RI, you might not be able to ENABLE the constraints.

Then the whole automated process will grind to a halt and some human will have to fix the
data before you can re-enable the constraints.

You probably should consider importing into a seperate set of tables, analyzing the new
data and Inserting/Updating the "real" data. That way any "exceptions" can be easily
flagged for the manual corrections before reprocessing.

Wayne
__________________
Pool Players Know All The Angles
WayneRyan is offline   Reply With Quote
Old 09-01-2010, 10:15 PM   #6
darbid
Newly Registered User
 
Join Date: Jun 2008
Location: Munich
Posts: 1,428
Thanks: 6
Thanked 38 Times in 35 Posts
darbid will become famous soon enough
Re: How to deal with table relationships where table is deleted and Inserted

Thank you Wayne, I understand what you have written.

Basically I am getting the information from another database so I am almost 100% sure that there should not be any problems but you can never be 100% sure. I suppose I could do a join to test that there are no problems with the constraints before adding them again.

In any case I am waiting on "the other database" to give me an example of what they can deliver before I start work on this.

I am sure I will be back.

regards

Davdi

darbid is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Table or no Table - Data Manipulation mattcdse Tables 8 02-16-2009 07:39 AM
Trigger code for Audit Trail ozinm SQL Server 1 01-03-2008 08:30 AM
Access subforms linked to SQL Tables Show Inserted records as deleted Mikee General 0 07-17-2005 10:35 AM




All times are GMT -8. The time now is 01:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World