Relationships links in a split database (Access 2000)

Topic

New Reply

I have a split database and am wondering whether it is “better” to have the relationships defined on the front end or back end. Currently they are defined on both ( I don’ t use the database splitter but in a test of it found that it put them on both too).

I bring this up because when trying to redefine a relationship I got the message “cannot redefine a relationship on a linked table”

I was wondering if it would be easier for development use to put them on the front end only, or do they need to be defined on the back end?

Reply | Quote

Replies

In a split database, relationships should always be place directly with the tables in the backend. For one thing, you won’t be able to enforce referential integrity between tables unless the relationship is with local tables.

Reply | Quote

I agree with Mark ….. the Relational Joins are maintained in the BackEnd.

If you use the Database Splitter built into Access … all of the joins are moved to be BackEnd and there should be “none” left in the FrontEnd. If you used the “Splitter” to split the database, I haven’t a clue to how any Relational Joins where left in FrontEnd …. they should not have been.

HTH
RDH

Reply | Quote

It’s perfectly possible to create joins in both places, but it confuses the query engine and it doesn’t serve any purpose I can think of.except to set up local tables to automatically link to back end tables when dropped on the query grid.

Reply | Quote

I did not say you “could not” create joins in in FrontEnd … only that the Database Splitter will not leave any there after the split process ….. they will all be moved to the BackEnd. Also you will not be able to enforce Referential Integity on the linked tables from the BackEnd in the Relationship window of the FrontEnd. If any additional Joins are needed ….. I usually set them up directly at Query level.

RDH

Reply | Quote

All true. I was merely pointing it out.

Reply | Quote

Thanks. That answers that. I did try using the splitter several times and each time it shows the relationships on both front and back ends. I don’t ususally use the splitter and just do it manually. From now on, however, I’ll be sure to change any relationships in the backend rather than the front end.

Reply | Quote

The frontend will still show the relationships, especially when you tap the “Show All Relationships” button. However, I’d just ignore them. Continue to maintain all relationships in back end.

Reply | Quote