Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 307
  • Last Modified:

Error on Database Split

I'm using 2010 and get the following error when I'm trying to split the database to a Access FE and SQL BE.   How can I fix this in Access so then it will split it without errors?

SQL that Caused Errors:

ALTER TABLE tblIncidentPeople
WITH NOCHECK ADD
        CONSTRAINT "tblIncidentPeople_FK00"
        FOREIGN KEY (intIncidentID) REFERENCES tblIncidents(intIncidentID),

        CONSTRAINT "tblIncidentPeople_FK01"
        FOREIGN KEY (intPeopleID) REFERENCES tblPeople(intPeopleID)
ALTER TABLE tblIncidentPeople
NOCHECK CONSTRAINT "tblIncidentPeople_FK00"
ALTER TABLE tblIncidentPeople
NOCHECK CONSTRAINT "tblIncidentPeople_FK01"

Error(s):
Server Error 1776: There are no primary or candidate keys in the referenced table 'tblPeople' that match the referencing column list in the foreign key 'tblIncidentPeople_FK01'.
Server Error 1750: Could not create constraint. See previous errors.
0
gcgcit
Asked:
gcgcit
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
nsonbatyCommented:
0
 
peter57rCommented:
Don't understand.

If you are using Access with SQL server then there has never been a point when the application was NOT split, so whatever action you are trying it is not 'split'.

I assume you are running the sql as a passthrough query or via an ado connection.
The error message is quite explicit - are you saying it is wrong?
0
 
gcgcitAuthor Commented:
I had an Access 2010 database... I want to split it to have a SQL back-end.  I'm using the splitter tool in Access... and when I run it, I get the above error.
0
Independent Software Vendors: 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!

 
Richard DanekeAdjunct FacultyCommented:
The referenced article suggests a problem with SQL and foreign keys.  It suggests that Foreign Keys in SQL must be Primary Keys in the original table.  
In short, are:
tblIncidents(intIncidentID) and tblPeople(intPeopleID) the Primary Keys in those tables?
0
 
gcgcitAuthor Commented:
It's set up as follows:

tblIncidents(intIncidentID-primary key)

tblPeople(intPeopleID-primary key)

tblIncidentPeople (intIncidentID, intPeopleID - togeter make primary key)
0
 
nsonbatyCommented:
I think, you can not make a primaRY key of table as a primary key in other table, you make it Foreign
0
 
peter57rCommented:
If the error is occurring in the Upsizing Wizard then there is not much you can do about it at the time.

Does the wizard complete and do all your tables get created in SQL Server?
If so then I think all you can do is fix the problems in the sql server database afterwards, using sql server management studio , or running your own code to fix them.



0
 
gcgcitAuthor Commented:
If I turn off the primary keys on my junction table... (tblIncidentPeople) will that cause issues with the system?
0
 
nsonbatyCommented:
try to make one primary key, then make it from SQL table
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now