We're using Access as a 'temporary' gateway to a fairly complex SQL Server backend. Today I stumbled into an issue between DB design and this interface -- I don't even know if this is a bug, really.

When I link to a DB table with NO primary key, Access prompts for "unique record identifier." The table in question has two foreign keys (but is designed for nulls), so I selected those -- and I get the wrong results. Access reports records in something of an unfortunate Cartesian query...

Specifics. The table ObjectGroup contains:
GroupID (fk, int, not null)
ObjectID (fk, int, not null)
SampleID (int, null)
LocationID (int, null)
SortOrder (smallint, not null)

A couple of the SampleID fields were associated with the wrong fk fields, which was just a lucky catch. (No, I don't have any influence over design at this point. Happy cog.)

Is there an appropriate solution to match this type of table design in Access? What's the allegory for Access's unique identifier? This applied PKs across the fields, which isn't appropriate for creating a "mirrored" data tool.

Thanks for your insights,
Jeff

Jeff Norville wrote:
Don't select any fields. Just cancel out of that dialog. This makes
the link read-only which is exactly what a table with no PK should be.

There can be serious ramifications to selecting fields in that dialog
that do NOT constitute actual uniques values in the table.