Multi-table form - relationships in front or back end?

[thebatfink]

Hi,

I am creating a form which will be used to input data into 3 newly created related tables in my backend database.

Table1 is related to Table2 (1 to 1 relationship) via Table1.PK and Table2.FK
Table2 is related to Table3 (1 to many relationship) via Table1.PK and Table3.FK

I have established these relationships in the backend file and set the integrity level and cascading deletes etc.

In the front end client, I am using the form wizard to create my form. I first add all the fields from Table1, then all from Table2 and then all from Table3. I then click Next and see the following error

You have chosen fields from these tables: Table1, Table2, Table3; One or more of the tables isn't related to the others. Click ok to edit system relationships. You'll need to restart the wizard. Click Cancel to return to the wizard and remove some fields.

This then opens up the relationships view in the front end client. There are no relationships present. So I guess when linking tables in the front end to the back end, the relationships are not imported?
It seems strange that Access would expect me to have to duplicate all the relationships again in the front end? What if you have many and make a mistake? Also all the options for cascading are greyed out - I have read that all of that is enforced in the back end so that makes sense - which makes the whole idea of duplicated the relationships even more senseless to me.

So my question.. do I just recreate the relationships established in the back end, or have I not linked my tables correctly or something?

Any help would be much appreciated! Thanks!

 

[thebatfink]

I think this may have something to do with the backend having password protection. It would seem the relationships aren't normally visable in the front end if there is a password in place.

I really would rather not remove this password as there are many applications connecting and querying the back end that would need all their connections strings rewriting and updating is there not any way to make this work with a password in place?! Seems odd to me? I think once this is sorted its definitely time to start looking at SQL server. The security in Access is so difficult to work with.

 

[thebatfink]

So I decided that I don't need the relationships in the front end because I can just specify the record source myself and add the joins in. But I see behavior I can't figure out. Image my table structure as -

Table1.PartID
Table1.Field2

Table2.PKID
Table2.PartID
Table2.Field2

Table3.PKID
Table3.PartID
Table2.Field2

Relationships as follows -
Table1.PartID - Table2.PartID (1 to 1 relationship)
Table1.PartID - Table3.PartID (1 to Many relationship)

The fields of all three tables are on a single form. The record source is

SELECT Table1.PartID, Table1.Field2, Table2.PKID, Table2.PartID, Table2.Field2, Table3.PKID, Table3.PartID, Table3.Field2
FROM
((
Table1 INNER JOIN Table2 ON Table1.PartID=Table2.PartID
) 
INNER JOIN Table3 ON Table1.PartID=Table3.PartID
);

The strange behavior I can't fix is that when populating the fields on the form, Table1.PartID is auto generating as it is the primary key and an auto number, Table3.PartID is automatically being populated with Table1.PartID value, but Table2.PartID is staying blank.
When I go to save the record obviously it says it cant because there is no matching record in Table2. Why is Table2.PartID not being populated?! I have specified the join in the record source, and the table has this relationship set on these two fields as 1to1?

Thanks