Solved

ACCESS SQL Query to Create Relationship

Posted on 2004-08-23
7
389 Views
Last Modified: 2013-12-25
I had two tables Customer & Source which are linked by SourceID. The Join was an OuterJoin. There was no referential integrity between them.

In the Source Table, SourceID datatype was Number. I would like it change to AutoNumber Without any problem to existing data.

I am using ADO to connect to MDB file.

I did this one using Query...

I removed the relationship between the Customer and Source.
Created a TempTable with Source Table Structure with AutoNumber.
Deleted the Source Table
Renamed the temp table to Source

Till this it is working fine. But when I try to bring back the relationship,  it has a referntial integirty problem with Customer table and query fails. When relationship is added exixting data are checked and the query fails.

How can u create the relationship between tables with NO REFERENTIAL INTEGRITY and with OUTER JOIN as the default join type. How do i create a query for this .

 I need all this one in a query, since this will be executed together with my aplication and all the client database will update their database.
0
Comment
Question by:nishikanth
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 11871519
Lets see your query.

Leon
0
 
LVL 8

Author Comment

by:nishikanth
ID: 11877160
Convert Number to Autonumber

'First Drop Relationship between Customer and Source
           
           strSQL = "Alter Table Customer Drop Constraint SourceCustomer"

'Create the New Table   - Temporary Table ---
           
            strSQL = "Create TABLE Source1 (SourceName Text(50) Not Null)"

'Transfer Contents from OLD Table to TEMP Table
           
            strSQL = "Insert Into Source1(SourceName) Select SourceName from Source"
           
'Remove the Old Table
           
            strSQL = "Drop TABLE Source"
           
'Create the Table   - With Original Name + AutoNumber ---

            strSQL = "Create TABLE Source (SourceID COUNTER CONSTRAINT PrimaryKey PRIMARY KEY , SourceName Text(50) Not Null)"

'Transfer Contents from Temp Table to New Table
           
            strSQL = "Insert Into Source(SourceName) Select SourceName from Source1"
            g_clsAdo.ExecuteSQL (strSQL)

Bring Back the RelationShip            
            strSQL = "ALTER TABLE Customer ADD CONSTRAINT  SourceCustomer FOREIGN KEY (SourceID) REFERENCES Source(SourceID)"

'''' CANNOT CRREATE REALTIONSHIPO OVER HERE
Cannot create relationships to enforce referential integrity.  Existing data in table 'Customer' violates referential integrity rules in table 'Source'.            

I need to bring back the rlnship with JoinType OUTER JOIN.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 63 total points
ID: 11880930
0
 
LVL 8

Author Comment

by:nishikanth
ID: 11888592
How can u create the relationship between tables with NO REFERENTIAL INTEGRITY and with OUTER JOIN as the default join type. How do i create a query for this.


0
 
LVL 1

Assisted Solution

by:ppswet
ppswet earned 62 total points
ID: 11924197
Try disabling the foreign key constraint to create a non-referential relationship.

ALTER TABLE Customer DISABLE CONSTRAINT SourceCustomer FOREIGN KEY (SourceID) REFERENCES Source(SourceID)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now