Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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....

share|improve this question
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? –  Arjang Jul 11 '11 at 13:38
add comment

2 Answers

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.

share|improve this answer
    
That confirms what I was thinking I needed to do. –  LtDan Jul 12 '11 at 11:40
add comment

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)

http://www.datawright.com.au/access_resources/access_docmd.openform_arguments.htm

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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