Solved

ACCESS SQL Query to Create Relationship

Posted on 2004-08-23
7
391 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

831 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