Access linked table to SQL Server database returns Deleted

Question

I have a rash of users that use MS Access 2016 64Bit, and Office 365 64Bit, that seem to have an issue with the 2205 Build. We are are using SQL Server 2012. Other posts on this subject seem to point to a change to MS Access TimeStamp, DateTime, and DateTime2 data types. These posts point to a checkbox in Current Database/Data Type Support Options/ Support DateTime Extended Data Type for Linked/Imported Tables. The note that I saw suggest that this works for SQL server compatibility level of 130 or larger. We are at 110. And the changes to MS Access do not seem to help. Our plan is to upgrade the SQL server but not simply because an update to the desktop broke our app. https://learn.microsoft.com/en-us/office/troubleshoot/access/access-linked-table-deleted

Any help would be much appreciated.

Answer 1

Hi, happened to us today following upgrading to latest build though latest 365 office version, managed to stop auto updates on most of our machines but two was affected.

We spoke to Microsoft. They didn't solve the issue, but gave us an easy way to rollback to previous access build.

For us, it was; C:\Program Files\Common Files\microsoft shared\ClickToRun\officec2rclient.exe" /update user updatetoversion=16.0.15128.20248

I guess you will need to amend to your version. You can also use the Office Deployment Tool to roll back here;
https://support.microsoft.com/en-us/topic/how-to-revert-to-an-earlier-version-of-office-2bd5c457-a917-d57e-35a1-f709e3dda841

We used both methods on two machines.

Answer 2

In SMS generated the create script changed the name of the table and all constraint names, ran it linked to it in access and the link showed an empty table. In SMS, inserted one row from the original source, the insert ran fine but then the link could not read the data. I then realized that there are no DateTime, DateTime2, and Timestamp datatypes, there are however several fields of smalldatetime type.

Answer 3

In the table create script I can run, insert rows, and the build the link and read the data if I comment out the following in the table create script:

--WITH (PAD_INDEX = OFF,
-- STATISTICS_NORECOMPUTE = OFF,
-- IGNORE_DUP_KEY = OFF,
-- ALLOW_ROW_LOCKS = ON,
-- ALLOW_PAGE_LOCKS = ON,
-- FILLFACTOR = 80) ON [PRIMARY]

other tables seem to have these same properties but can be read from the link, so the above is likely just changing the symptoms of the root problem, and not a fix.

Answer 4

TomPhillips-1744, AlisterCruickshanks-7615,

The real question is why is it broke? It takes two to tango and if MS Access can read data from sql linked table one day and then the next day after an update it can't. Is it because of the MS Access patch? Or was it because the MS Access Patch required something else on the PC to continue to use the tables as designed? Yes I could have redesigned 30 table so that the primary key field was not of an NVarchar type and the new version of MS Access would have worked. But that would have taken days of work and countless bugs maybe a month in total... Or I could install SQL server driver 17 on the PC and open the MS Access mdb file and refreshed my link tables and views and based on that driver and with little to no risk of any structural changes / bugs. Ya give me the ladder... We installed the sql server driver 17 (64 bit for my 64 bit os) on all pc's and rebuilt all links based on that driver, problem fixed. Thanks for responding to this question, and for your suggestion, did post in ms access as well.

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=msaccess-bug-v2205-odbc-deleted-nvarchar-primary-key

Answer 5

Hi,

Microsoft fixed the bug yesterday. They used a Change/FeatureGate for this. Therefore there is no update and no new version. Quit Access and open it again is enough for the problem to be gone.

Servus
Karl

---

Access News
Access DevCon