gcgcit
asked on
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.
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.
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?
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?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's set up as follows:
tblIncidents(intIncidentID -primary key)
tblPeople(intPeopleID-prim ary key)
tblIncidentPeople (intIncidentID, intPeopleID - togeter make primary key)
tblIncidents(intIncidentID
tblPeople(intPeopleID-prim
tblIncidentPeople (intIncidentID, intPeopleID - togeter make primary key)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
If I turn off the primary keys on my junction table... (tblIncidentPeople) will that cause issues with the system?
try to make one primary key, then make it from SQL table
http://www.sql-server-helper.com/error-messages/msg-1776.aspx