Access linked table to SQL Server shows data as #Deleted

Question

Hi,

I have an application with an Access front end that has linked tables to a MS SQL Server back end. This application has been been developed over the course of many years. My company runs the front end application on multiple PC's. We have been in the process of upgrading some of these PC's from Access 2013 to 2016. and some to Microsoft 365 32-bit. I found that the change from 2013 to 2016 forced me to change a few library references, but it is now working fine on Access 2016. With these changes, the same front end application that works on Access 2016 seems to work fine on *most* of our PC's that are running Microsoft 365 32-bit. I do have one Microsoft 365 PC, however, on which it is not working properly. The issue is that some, but not all, of the linked tables are not retrieving data properly. For every linked table in this application, Access shows the connection string as follows:

ODBC;Description=YIC NJ Data Source;DRIVER=SQL Server;SERVER=LUMINERSQL;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=YICSQLNJ;TABLE=dbo.SomeTableName

On the problem PC, I can open the linked table and view all of the records with no problem in some, but not all, of the linked tables. On other linked tables, I am able to open them (by double-clicking the table name), but it shows every field in every record as "#Deleted". I know there is data in these tables because I can open the same application (with the same connection strings to the back-end) and see all the data just fine.

I am just wondering if anyone here has an idea why a connection string like I've shown above would work on some PC's running Microsoft 365, but not on one particular PC.

Any suggestions will be greatly appreciated.

Thanks and best regards,
Paul

Answer 1

You first need to download and install one of the two working drivers. ODBC 17 or 18. Since 18 is the newest, that's the one I'd use.

You have to install it on ALL users' computers before you can use it, unfortunately. That's one of the reasons many folks don't bother to replace the legacy SQL Server driver that's installed by default with Windows. However, this is not the only reason we recommend you do use the newer driver.

Next, use Doug's DSN less code to relink using this new driver.

Now you can distribute the updated Access FE to your users. Make sure they can relink with the newer ODBC driver after you've installed it for them.

By the way, make sure you get the same bitness (32 or 64) as your Access version. Don't worry about the bitness of the server, only the Access application matters.

Answer 2

This sounds like a recent bug that Microsoft released this week. You are using the legacy SQL Server driver. The problem occurs with PK fields based on NVarChar fields. If that is your case, it's highly likely this is the reason.

You can get more details and a suggested way to handle it here. If this is not the problem in your situation, let us know and we'll dig further with you.

Answer 3

Hi George,

Thank you for your reply. The linked tables that are showing #Deleted for me do in fact have an NVarChar field as the Primary Key. According to the article you referred me to, it seems like there is a good chance I can solve this problem by relinking my tables tables using the “ODBC Driver 17 for SQL Server”. I'd like to do this, but I am not sure of the best way to go about it. As I mentioned, the connection strings for my linked tables currently look like this....

ODBC;Description=YIC NJ Data Source;DRIVER=SQL Server;SERVER=LUMINERSQL;Trusted_Connection=Yes;APP=Microsoft Office 2013;DATABASE=YICSQLNJ;TABLE=dbo.SomeTableName

... I notice that this string is stored in the "Description" property when I look at the Property Sheet for the linked table. I imagine changing the driver is not as simple as editing the string here. (Please correct me if I am wrong). Thinking that this was not my solution, I went into the Linked Table Manager, but it looked to me that this only allows me to choose a DSN for my connection, whereas I am currently using DSN-less connections. (Again, please correct me if I am wrong).

Several months ago when I had a different question, you referred me to the following article...

http://www.accessmvp.com/DJSteele/DSNLessLinks.html

... this article explains how to programmatically setup DSN-less connections. Would you recommend that I take this approach? Or is there an easier way that I can relink my tables with the new driver using the Access user interface without having to write code?

I really appreciate your help.

Thanks again and best regards,
Paul

Answer 4

Hi kraemerpw,

Please check the following article which describes that A Microsoft Access-linked table that contains one or more datetime or datetime2 columns and that’s connected to a Microsoft SQL Server database and has a compatibility level of 130 or larger (the compatibility level for SQL Server 2016) returns #Deleted in the results.

Access linked table to SQL Server database returns Deleted - Office | Microsoft Docs

The issue occurs because the datetime or datetime2 columns contain fractional seconds values. How fractional seconds are handled for datetime2 types was changed starting in Microsoft SQL Server 2016. 

Kind regards,

Neha

Answer 5

Dear kraemerpw

Thanks for posting in Microsoft Answers Community.

We would love to help you with your query about database, however, our team focuses on general query, for example, installation and activation issue of Office 365 products. The situation you mentioned is related to VBA code/database, you can to refer to this article: Office VBA support and feedback | Microsoft Docs to go to Stack Overflow by using the VBA tag, along with any other relevant tags as there are also many experienced engineers and experts in the forums there.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites, or any support related to technology.

At the same time, we will keep this thread open, so other Community members and Experts in this forum can share their suggestions and inputs.

Thank you for your cooperation and understanding!

Best Regards,

Neha Singh | Microsoft Community Moderator