Sorry about all the reading, but it really explains my question.

MS Access Table relationships

Question 1: When I make a database and tables are NOT ODBC linked to SQL tables, it's no problem to create table relationships, like main table = 1 to secondary table = Many... NOW, when I create a database with linked tables to the SQL tables, I cannot create the 1-many relationship in the database. I understand that the SQL tables are secondary tables which makes it so, I cannot create this 1-many in the MS Access db.

Question 2: how do I create this 1-to-many relationship using linked tables. The goal is to have a form with a list box, user highlights item in list box, click a hyperlink to open another form which has a source of a query! This query is created from the secondary table (the many) and will present all the many records, related to that one the user highlighted from the list....

  • 2
    Sounds like someone had too much coffee! I read this 3 times and I am still confused what is needed, can you just describe either what you need the end result to be or just how to do a single step in Access? – jimjim Jul 11 '11 at 13:38

You will have to create those relationships between the SQL tables and the Access tables within the query in Access.

Note that SQL tables aren't really secondary tables. They are just tables elsewhere.

  • That confirms what I was thinking I needed to do. – LtDan Jul 12 '11 at 11:40

you're looking for a master detail setup.

You could set the value of the form's datasource based on a query that filters the foreign key based on the selected value (primary key)

so upon opening the form, you grab a argument passed in that filters the list.

that's the answer when you can't edit the sql server source (which happens in the real world)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.