Refreshing a linked table (housed on MS SQL Server) now breaks

[03Explorer]

We had a former developer who built most of the forms in our MS Access database. When we first built the database in Access office 365 we did refreshing of tables from our dev version before publishing to production. All was great back then, before the revamping of the managing of linked tables. Since the rework on linked table manager we can no longer just refresh a table. It now asks what we want to use for Unique Record Identifiers. I do not know what the former developer used for those, but from my research I've been doing fine with the cancel at that screen. We manage the record identifiers at the SQL server side. WELLLLLLLL... now I have ONE table that works great from an archive version, but for only one table I do a refresh & the cancel at Unique question give me an error within a form. The error is either something got corrupted or there was a Unique mapping. How can I find out what the Unique identifier options were selected (if they were used) or is it a corrupt (something)??

The error I now get is Run-time error '2105': You can't go to the specified record.

I am at a loss how do deal with this.

Rob

 

[03Explorer]

*thinking* wonder why this thread is not getting any attention.

 

[lameid]

What access does behind the scenes is create an index representing the unique fields when you do that.
I would look into inspecting the index on the linked table. I've not had to do this myself.
I think many of us don't visit these threads often now... I have my own problem in other topics about corruption and hoping someone else has hit the pain...

 

[dhookom]

When you link to the SQL Server tables I assume you are being asked to identify the unique columns/fields.
Are there primary keys or unique indexes on the SQL Server tables?
Every table should have a primary key. If not, I would add one on the SQL server table.

You can also use a DDL query on the Access side to create a unique index. Of course the chosen field(s) must be unique.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016