#Deleted - Access DB / Linked Tables

I had an issue today which is making me question the reliability of an Access database (using SQL Server linked tables) that we currently employ.

One of my users reported that while viewing data through a query, the values in a single field all changed to "#Deleted"

When she closed the query and re-opened, all the records had been deleted. She was the only person accessing the data at that time.

I am able to recover from a backup, but this was rather disturbing. Any ideas what could cause such a thing?

Has a "fishy" feeling to it, but now I'm second guessing everything.
Who is Participating?
ArjiConnect With a Mentor Commented:
Yes, I agree....from your description it sounded like just a single record.  This is a very strange situation.  Does anyone else have access to Enterprise Manager?  I don't generally blame anyone for those types of issues, but when when you look at how it could have possibly happened, that could be one way.  In general I list all things that could have caused a problem and then go down the list to eliminate each item.  It is very unlikely SQL would have done something like that spontaneously.....Microsoft would go out of business. :-)  You might want to make sure all your OS and SQL service packs are installed to possibly prevent any future occurances.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>the values in a single field all changed to "#Deleted"
This usually means that another user has deleted those records while the first user had them open.
Another possibility is a corruption issue, which means the best thing to do is create a new .mdb and import all tables/queries/forms/etc. into it, then compact-repair.

Hope this helps.
(using SQL Server linked tables)

Are your tables in SQL server or Access?  You probably won't see this problem with SQL tables.  The reliability of an Access DB is related to the table storage side.  A client-side app should be as reliable as the programmer's techniques.  Client-side corruption ordinarily won't cause SQL table corruption unless it's a 'display' problem as opposed to actual missing records.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

ssaimoAuthor Commented:
Thank you both for your replies.

The tables are SQL Server.

I have checked through Enterprise Manager, the records are in fact gone.
Did your user create the query herself?  Is it possible she accidentally created a "Delete" query?  I have never heard of SQL server spontaneously deleting records......and I hope I never hear of it.
ssaimoAuthor Commented:
It was an existing query.

Only queiries that exist are select queries.
ssaimoAuthor Commented:
Other odd thing, in case it was overlooked before is generally, when records are deleted while being viewed, all fields will go to "#Deleted", not just a single field, correct?
there is an explanation for #deleted that doesn't relate to records being deleted

from www.sidfishes.net
10/13/2004 The Cure for #Deleted
If you are using an Access front end connected to an SQLServer/MySQL/Oracle backend and are seeing #Deleted in tables and forms, the cure is most often to add a Timestamp field to your table. Microsoft Jet uses imprecise timings. It is actually possible to have jet report write conflicts when you are the only user in the DB. The timestamp field can eliminate this problem. Note: If you are using queries, you must include the timestamp in each for this fix to work.

but if the records are verified to be -gone- then it's likely a coding or corruption problem
ssaimoAuthor Commented:
I'm the only one with access to enterprise manager, and all SP's are up to date.

I guess I'll try to stomach chalking it up to a fluke.

Thanks to everyone for your help.

Wish we could have you out on this one.  I know it's a worrysome problem.  Hopefully, you won't see it again.  :-)

Good luck!

Good for you on doing your backups.  You might want to tell your backup software or schedule SQL server to do backups on an hourly basis.  Just a thought.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.