Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Linked table showing #Deleted

Linked table showing #Deleted

Linked table showing #Deleted

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

RE: Linked table showing #Deleted

Is Access able to identify the column or columns that make up a primary/unique key?

Hook'D on Access
MS Access MVP

RE: Linked table showing #Deleted

Yes. The primary key in the underlying table in the view is designated as the key in the Access linked table.

Cheers, May

RE: Linked table showing #Deleted

Hmm. Possibly. I will double check both that & the primary key when I'm in the office on Wednesday. Thanks for your help!

Cheers, May

RE: Linked table showing #Deleted

Greetings! I finally found the answer to this problem. The primary key on the SQL table is a bigint and apparently Access 2013 doesn't recognize bigints as numbers, so it's creating the index in the linked table as text. Check out this link for more info:

Thanks for your help!

Cheers, May

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close