Solved

ACCESS SQL Query to Create Relationship

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

914 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

22 Experts available now in Live!

Get 1:1 Help Now