Problem with table relationships when importing from SQL Server 2005

[bifteki via AccessMonster.com]

I want to import a DB from SQL Server 2005 to Acces 2007.
When I do this, all tables and views are imported properly but there are no
relationships between the tables.
I do this through the External Data menu -->Import -->ODBC Database.
Am I doing something wrong?
Shouldn't the relationships be imported along with the tables?

 

[Chris O'C via AccessMonster.com]

You can only import relationships along with the tables when you import Jet
tables or ACE tables, not odbc tables. In your db manager app, export the
original foreign key constraints as scripts, copy each constraint as the sql
in a Jet query (only 1 sql statement per query), modify it if you have new
names, and run each one in Access.

Chris
Microsoft MVP

 

[bifteki via AccessMonster.com]

Thank you for your reply.

I scripted each FK in SQL Server and then I imported the tables in Access
again.
I tried to make a query with the script but I don't know what type of query
it should be.
I copied it in the SQL View and tried to run it but I get a
"Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT' or
'UPDATE' "
message.
What query type should I use?

PS: I also have another question: Do I need all these 'sys_' and
'INFORMATION_SCHEMA' tables when importing to Access?

 

[Chris O'C via AccessMonster.com]

When you have the query in sql view or design view, use the menu, Query > SQL
Specific > Data Definition. Paste your script to create a foreign key on a
single table. Example:

ALTER TABLE tblChild
ADD CONSTRAINT tblChild_tblParent_FK
FOREIGN KEY (Parent_primarykey)
REFERENCES tblParent (Parent_primarykey);

Only one sql statement per query. Jet can't process multiple sql statements
in the same query.

You don't need the sys_ or INFORMATION_SCHEMA tables in Access. Access has
its own system tables.

Chris
Microsoft MVP