SQL Server Relationships displayed in Access

Question

In Access, you can setup a one-to-many relationships between tables.

There is then a (+) sign in one table which links to many rows in the second table.

When you click the (+) sign it shows all the rows in Table 2 that are related to Table 1.

Is there anyway to do something similar with SQL Server as the data store and Access Linked Tables?

I have my data setup in SQL Server tables and wanted to use Access 2010 as the front-end.

Answer 1

I don't believe you can make Access automatically enable the subdatasheet (+) feature for ODBC linked tables.

The way it works with native Access tables is that the SubdatasheetName property defaults to [Auto]. And with [Auto], Access will look at the defined relationships and select a related table as the subdatasheet.

With SQL Server linked tables, Access doesn't examine the relationships defined in SQL Server and pick a table to use as the subdatasheet. You could define the relationships between SQL Server linked tables again in Access (though it doesn't offer to enforce referential integrity), but it still won't apply a subdatasheet. And you could manually set the SubdatasheetName, LinkMasterFields, and LinkChildFields properties from Design View of your linked table, but that lasts only until you close the table.

The only way I know to get anything close to what you want is by using VBA to alter the TableDef. In this example, both Products and Categories are ODBC links to SQL Server tables.

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim prp As DAO.Property

Set db = CurrentDb
Set tdf = db.TableDefs("Products")

Set prp = tdf.CreateProperty("SubdatasheetName", dbText, "Categories")
tdf.Properties.Append prp

Set prp = tdf.CreateProperty("LinkMasterFields", dbText, "RefID")
tdf.Properties.Append prp

Set prp = tdf.CreateProperty("LinkChildFields", dbText, "CategoryID")
tdf.Properties.Append prp

Set prp = Nothing
Set tdf = Nothing
Set db = Nothing

Finally, subdatasheets can be useful, but they bring other challenges. See the SubdatasheetName section from Problem properties.

Answer 2

There is also a way how to do this in UI:

• Open the table definition (Design view) for the master table (table Products in VBA example). Ignore the warning.
• Open the properties window in the design view of the table
• Enter in property Sub Datasheet Name a text Table.Categories (a subdatasheet table)
• The linking fields (LinkMasterField and LinkChildFields) should get filled automatically using existing relation. If they do not, fill the column names for relationship, i.e. CategoryID (primary + foreign key)
• Close the table and save the design changes

That is it.