Relationships in password protected split database (Access 2010)

Question

I am working on a split database in Access 2010 where both the front and back end are password protected. I have noticed that the relationships between the tables in the back end database carry through to the front end when the back end database is not password protected, but when it is password protected the process of re-linking the tables to the front end removes the relationships. This means for example that if I want to create a form with fields from two related tables, the front end doesn't see that these tables are related (so a 'define relationship' pop up appears and I have to manually create the relationship. When I have tried to manually re-create all the relationships in the front end relationship view and then create a form using fields from two related tables, access crashes. Does anybody know of a way to fix this? I am aware I can create the forms, queries etc in the back end and then copy them across, but I will be working on the database a lot and I would prefer not to add this step to the process every time I want to do something involving related tables.

Answer 1

You appear to be using the form wizard to create the forms.  When you select columns (fields) from tables in a one-to-many relationship type in the wizard it creates a parent form in single form view, and a subform in datasheet view.  You can achieve the same by first creating the parent form in single form view, and then create the subform separately, making sure that the latter is in datasheet view, or for a better standard of presentation, in continuous forms view.  With the parent form in design view you can then drag and drop the subform from the navigation pane onto the parent form, where you can reposition and resize it as desired.  The LinkMasterFields and LinkChildFields properties of the subform control can be added manually in the subform control's properties sheet to create the link.

An alternative approach would be to create the parent form in single form view, and in design view add a subform control from the toolbox, using the control wizard, this will then prompt you for the table and columns to be added to the subform.  Again the LinkMasterFields and LinkChildFields properties of the subform control can be added manually.

The same approaches can be followed when creating a report and subreport.

Creating queries in design view in the front end is merely a case a adding the tables and dragging from the primary key of one to the relevant foreign key in the other to create the join, which by default is an INNER JOIN, but can be changed to a LEFT or RIGHT OUTER JOIN in the join type dialogue where appropriate.

Note that the relationships in the relationships window in a front end have no bearing whatsoever on the integrity of the data.  That is achieved by the enforced relationships in the back end.