Linked tables do not show SQL Server Primary key

  • Thread starter Thread starter bbitzer
  • Start date Start date
B

bbitzer

I am attempting to link two tables from SQL server to an
access application. Both contain the same column names and
the same fields as concatenated primary key but the tables
are named differently, have different values and used
differently. When these tables are linked in access, one
of the tables changes to a single primary key. When this
table is opened, you see many duplicates (of what was the
concatenated primary key) but the count of records is
still equal to SQL Server. When I query in Access for
duplicates, no results are returned. I drop the table and
re-link it, but it still shows a single key. How can I get
Access to accept the true table schema?
 
1. Re-link and choose more than one column if prompted for a Unique Record
Idenitfier.

2. If you are not prompted then do it "after the fact" like this:

You can create a "Fake" index in Access that tells Access which columns make
a Unique record in the linked table. The index is only used by Access is
completely unknown to the Server.

The following example creates an index on an ODBC linked table. The table's
remote database is unaware of and unaffected by the new index. The following
example won't work if you simply paste it into Microsoft Access; you first
need to create an ODBC linked table called OrderDetailsODBC.

(CREATE INDEX Statement in Help has some examples )

CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID);


double check the syntax for multiple oclumns but I guess it is probably:
CREATE UNIQUE INDEX OrderID ON OrderDetailsODBC (OrderID, Field2, Field3);
 
Back
Top