4

A new issue has cropped up this morning. I have databases that reside on SQL Server and I use Access for the front end. One of the databases which has been in use for at least 10 years now suddenly stopped working today, and I have found that the issue is one that is affecting 2 (possible more, I've not checked them all) tables.

When I open the table in access all I get is #DELETED in all the rows and colums. I have seen this behaviour before and it is usually something to do with the data type but this doesn't seem to be the case in this instance.

To troubleshoot the problem I have created a view that retrieves all the columns from the table and when this view is linked and opened in Access I have the same issue. I have found that if I link to the view without selecting a unique record identifier I can see the data without any problem. I could use this as a work-around, but clearly it is not ideal.

The SQL server version is 14.0.2037.2 and I am accessing it using SQL Server Native Client 11.0.

davebrads
  • 109
  • 4
  • 1
    Have you tried SSMS instead of Access? – SezMe May 30 '22 at 10:28
  • Odd. If the table structure (data types) has not changed, then it must be something in the data. Check (in SSMS) the newest records for peculiarities. Especially the PK - what data type is it? -- For debugging purposes, try creating a view that selects only a few old records and link this with the PK. – Andre May 30 '22 at 10:42
  • maybe the size of the resultset got to large for access to handle ? Why do you use access anyway ? – GuidoG May 30 '22 at 11:13
  • 1
    I found the cause and solution, see below. I use Access because I came into it via coding for Excel and it was a natural progression and an easy way into database design. I am totally self taught and although I have progressed to storing the data on SQL server and am aware of the limitations of Access I have found the learning curve to VB to be very steep and I don't have the time or inclination to spend that much time learning new skills. – davebrads May 31 '22 at 13:14

2 Answers2

6

I have found the cause and solution. The affected tables had nvarchar fields as the primary keys. SQL Server Native Client has been deprecated for some time now and is replaced by MS OLE driver which is our mistake. The reason this problem has only reared its head now is due to an update to MS Access 365. I found this which has more details:

#DELETED when linked with ODBC

davebrads
  • 109
  • 4
2

I had the same issue. This situation emerged this past weekend (5/29/2022).

This is a bug created by a 365 Office update. My update occurred 5/29/2022. The best remedy is to roll back your most-recent 365 update.

In my application, the #Deleted value appeared in all cells linked to any SQL Server table having a nvarchar field included in any unique index - it didn’t have to be a primary key. In my case, eliminating the unique attribute in any index including a nvarchar caused the problem to go away.

Bill Tidwell
  • 31
  • 2