Relationships with SQL Server BE

[jean marhucolle]

Today we have a multi-user replicated Access 2003 FE/BE application, the main
BE on the companies’ corporate LAN network. The idea is to chance the
replicated BE for a simple SQL BE. After our corporate CIO people moved the
dbm tables to the SQL server on the LAN, I set up ODBC links in the Access FE
to the SQL tables.
The FE does access the SQL tables just as it did the Access tables before,
but, although I got the confirmation that all tables, indexes and
relationships etc were migrated, the FE curiously does not see the table
relationships, and consequently, queries with more than 1 table just hang.
When I reestablish the links within the queries, they work fine.
Going through questions in discussion groups, I found that Nils Pettersson
11/16/2007 had a similar experience (Subject: Referential Integrity
Unavailable) - apparently Nils copied all relationships on the FE site, but
such duplication doesn’t look good to me.
Anybody can give me a clue why this happens, and what the solution is for
this? -
Thanks for any help.

 

[Valentín Playá]

Jean,

the behavior may be due to the option of Autocorrection of names. I am
not sure about the exact english name of the options because I use the
Spanish version but I hope you may find the right options.

I'd start with a complete FE mdb with everything correct, turn off all
the Autocorrection options in Tools/Options/General, delete the linked
tables and relink them to the SQL Server tables. I believe all your
queries will remain as they were.

Regards,

Valentín Playá
Sonotronic S.A.
Madrid, Spain
*********************************************************************************

 

[aaron_kempf]

you cannot enforce referential integrity if you use JET.
Move everything to SQL Server-- little tables-- big tables-- and
enforce RI using 'database diagrams' on the server side.

Access JET is a waste of time.
Upsize to Access Data Projects.

-Aaron