Solved

ACCESS SQL Query to Create Relationship

Posted on 2004-08-23
7
394 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

696 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