I have come across the most bizarre thing I've ever seen in decades of programming in Access and I'm totally stumped!
I am using Access 2013 as a FE to a SQL Server 2012 BE. I have numerous linked tables in the FE to views on the BE and have created Access queries on those linked tables. The link to the NonRevenue view is returning #deleted in all fields instead of the actual values. The SQL view returns ~16,000 records without parameters, so I know the data set is not huge. The Revenue view is nearly identical and returns ~800,000 records without parameters. I can open the linked table on this one and the proper data values are shown. Querying against both views on the server produces the correct results.
I have tried the following to no avail:
- refreshing the links via Linked Table Manager
- deleting the linked table & re-creating it
- deleting the view on the server & re-creating it, then re-creating the link
- changing the view on the server to be identical to the one that works
- deleting the linked table, decompiling the Access file, re-compiling and re-creating the link
- creating a blank database & creating links to several of the views - this one produced a slight difference. Of the 4 links, 2 were to very small resultsets, 1 was to the NonRevenue view and one was to the Revenue view. The 2 smaller data sets open fine, but both the Revenue & NonRevenue links show #Deleted in all fields.
The only thing I'm aware of that has changed (wrt data connections) between the time the Revenue link was created and when the NonRevenue link was created is that the connection string I use when running SQL stored procedures has changed from using an ODBC string to using SQLOLEDB & Integrated Security. However, I would not expect that to cause issues with any linked tables, since it's only used in the VBA code.
I have run out of things to try and am at my wits end with this! A search has not produced any similar situattions.
Any help would be most gratefully received!
Cheers, May
I am using Access 2013 as a FE to a SQL Server 2012 BE. I have numerous linked tables in the FE to views on the BE and have created Access queries on those linked tables. The link to the NonRevenue view is returning #deleted in all fields instead of the actual values. The SQL view returns ~16,000 records without parameters, so I know the data set is not huge. The Revenue view is nearly identical and returns ~800,000 records without parameters. I can open the linked table on this one and the proper data values are shown. Querying against both views on the server produces the correct results.
I have tried the following to no avail:
- refreshing the links via Linked Table Manager
- deleting the linked table & re-creating it
- deleting the view on the server & re-creating it, then re-creating the link
- changing the view on the server to be identical to the one that works
- deleting the linked table, decompiling the Access file, re-compiling and re-creating the link
- creating a blank database & creating links to several of the views - this one produced a slight difference. Of the 4 links, 2 were to very small resultsets, 1 was to the NonRevenue view and one was to the Revenue view. The 2 smaller data sets open fine, but both the Revenue & NonRevenue links show #Deleted in all fields.
The only thing I'm aware of that has changed (wrt data connections) between the time the Revenue link was created and when the NonRevenue link was created is that the connection string I use when running SQL stored procedures has changed from using an ODBC string to using SQLOLEDB & Integrated Security. However, I would not expect that to cause issues with any linked tables, since it's only used in the VBA code.
I have run out of things to try and am at my wits end with this! A search has not produced any similar situattions.
Any help would be most gratefully received!
Cheers, May
