Relationships in Access and SQL Server

[Harry V]

I'm letting users create an mdb of links to an SQL Server database tables for
read-only access to their data. Is there a programatic way to create
relationships in Access, or do I have to create a cookbook for them to do it
manually?

Will any relationships in the SQL Server database carry through in the links?

Thanks

 

[Sylvain Lafontaine]

You should ask these types of question in a newsgroup related about ODBC
linked tables such as m.p.access.odbcclientsvr or m.p.a.externaldata. This
newsgroup is about ADP (Access database projects, a special type of file)
and has nothing to do with MDB database files and ODBC linked tables.

To come back to your post, I can tell you that the relationships defined in
the SQL-Server database are only enforced or used at the SQL-Server level
and are not carried through the links back to the Access database. In the
same way, relationships defined in the frontend are not carried back or
enforced at the backend database; whatever this backend is - SQL-Server or
another MDB database file. So if you want your users to access the
SQL-Server tables through a MDB file, creating relationships in the frontend
is pointless.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Harry V]

Thank you, Sylvain, I will try to post at those sites - but then, these forum
titles aren't really explained by MS, and for the 'old-schooled', ADP has an
entirely different meaning.

 

[Mary Chipman [MSFT]]

It's OK -- you can ask your questions here.

Basically you want to set up all of your data structures and
relationships on the server to maintain data integrity and
consistency. When you link to tables from Access, Access inherits the
restrictions on the data -- the primary and foreign key constraints,
relationships that prevent you from inadvertently deleting data from
the many side of a join, and so on. You don't want your users to be
responsible for this because they could conceivably delete data.

Data definition statements (DDL) are defined in Transact-SQL and can
be executed from Access, but you don't want to go that route. It's
easier to use the GUI tools in SQL Server Management Studio to create
your database objects. HTH,

--Mary