0

Using a MS Access 2007 database which has data and relationships between the tables. I uploaded all the tables to SQL db. I then created the ODBC connection to update the db. But I cannot restore the "Enforce Referential Integrity".. Do I need to recreate all the tables without data, create the relationships (with ref integ) then add all the data back in. I sure hope there is another way to creat the "Enforce Referential Integrity"..

Pls, any suggestions??

2 Answers 2

1

Realize that both tables must be present in the same Microsoft Access database. If they are linked, they must both be in Access format. You must open the database that contains these tables before you can set referential integrity.

You do not meet this requirement if the tables are in an SQL db. From here

8
  • Yes I am opening the database that contains both tables (although the tables are linked to SQL db). I don't see how this is different then you suggest. Does Access not allow creating Referential Integrity to linked tables, within the same database???
    – LtDan
    May 31, 2011 at 19:50
  • Apparently not unless that database is MS access. It mentions that it must be present in the same MS Access db, not general db that you are using Access to connect to.
    – soandos
    May 31, 2011 at 19:52
  • LtDan, generally it is considered polite to mark something as the answer if it answers the question.
    – soandos
    May 31, 2011 at 20:25
  • Ok, I clicked the check mark now, because the above comment is an answer to the question. I hope that is all I am suppose to do is click the check mark. Although I am pretty sure you can still create a referential integrity relationship in an Access database when those tables are linked to an SQL back end.
    – LtDan
    May 31, 2011 at 20:58
  • Does not seem so, and the check mark is not clicked (refresh page to check for yourself).
    – soandos
    May 31, 2011 at 22:11
0

You should not be trying do relationships in access if your data is in a SQL server. You should be setting up your constraints on the SQL server. Exactly how you do that depends on the server, and in the case of some servers, what type of table you are using.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.