ssaimo
asked on
#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.
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.
(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.
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.
ASKER
Thank you both for your replies.
The tables are SQL Server.
I have checked through Enterprise Manager, the records are in fact gone.
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.
ASKER
It was an existing query.
Only queiries that exist are select queries.
Only queiries that exist are select queries.
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
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.
I guess I'll try to stomach chalking it up to a fluke.
Thanks to everyone for your help.
ssaimo,
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.
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.
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.
-Jim