?
Solved

#Deleted - Access DB / Linked Tables

Posted on 2005-05-04
10
Medium Priority
?
805 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:ssaimo
10 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13929771
>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.
-Jim
0
 
LVL 17

Expert Comment

by:Arji
ID: 13930077
(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.
0
 

Author Comment

by:ssaimo
ID: 13931150
Thank you both for your replies.

The tables are SQL Server.

I have checked through Enterprise Manager, the records are in fact gone.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:Arji
ID: 13931754
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.
0
 

Author Comment

by:ssaimo
ID: 13932017
It was an existing query.

Only queiries that exist are select queries.
0
 

Author Comment

by:ssaimo
ID: 13932026
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?
0
 
LVL 17

Accepted Solution

by:
Arji earned 2000 total points
ID: 13932161
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.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 13932642
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
0
 

Author Comment

by:ssaimo
ID: 13960759
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.
0
 
LVL 17

Expert Comment

by:Arji
ID: 13961409
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question