"spontaneous" record deletion? (Access '03)

[jan001]

We have a particular database I built and we've been using virtually problem-free since 2008. In the past couple of weeks, another staff member has taken it upon herself to create forms, etc., apparently with management's blessing. In that time, two things have happened:

1. One of the forms she created appears to be essentially a filter allowing each staff member to view only his/her projects (I had already built queries for each one to do this, and they've been working fine, but apparently she/they want it at forms view). I'm not sure whether it's a filter or something else because I have intentionally not touched them and do not intend to. I do know, however, that when one staff member used theirs last week, the notes in a Memo field displayed with #Deleted in place of what she had typed. In the actual record, the notes were there (thank goodness). When another staff member ran one of my original queries to list the projects, everything showed up just fine.

2. This morning, a project manager reported that the entire record of one of her projects had vanished and indeed it had. It's been recreated, so that much is fixed. Meanwhile the staff member who's been newly involved with the database has sent out an email saying "we'll get to the bottom of it". I replied to all giving the date and time I last knew that record to be in the database and completely intact and ended with, "Hope this helps."

My feeling at the moment is that I'm not inclined to take responsibility for fixing something that is apparently no longer my sole purview, so I'm taking a hands-off approach and letting this play out. Here's my question:

Do any of you know of circumstances not involving human action that would cause Access to do either of those things? I'm waiting for someone to make this my problem and am trying to be ready.

On the other hand, if either one could be caused by the software itself, any help on how to troubleshoot would be appreciated. As always, thanks for any input.

 

[revans]

when one staff member used theirs last week, the notes in a Memo field displayed with #Deleted in place of what she had typed. In the actual record, the notes were there (thank goodness).

I would look at the forms this other person created and what code is in there. This (above) tells me the form is doing something it shouldn't. Without specifics it's hard to say what that is.

That's where I'd start.

hth,

Rich

 

[xenou]

Sounds like record-locking behavior here too ... again, hard to say. How many concurrent users are there? Is someone leaving their database open all the time? Do users update the same records at various times?

 

[jackd]

Is the database split?
Is there an operational backup process allowing db to be restored to a known point?

 

[jan001]

GREAT questions!

revans - My curiosity is killing me to open one up one of the forms created by the other person, just to see what's there, but for now I'm maintaining hands-off, so I can't be held accountable for their failure to function as expected. (voice of experience)

xenou - It's entirely possible people are leaving the db open all the time, despite having been asked not to going back to 2008. There are maybe a dozen people or so who would have reason to be in there for various reasons. It's possible but extremely unlikely that two people would try to edit the same record at the same time; each record is for a specific project that's managed by a specific individual.

jackd - Is the database split? I'll answer that this way: Not that I have done or that I know of. I have no idea what this other person has done in there and all I've done since this started is run a couple of routine weekly reports for the brass using queries I created ages ago, which went fine. No, there shouldn't be any backup processes happening during work time (happens overnight), and ITS has blocked everyone's ability to set/use restore points so that's not it.

I was told yesterday of another weirdness in the other person's form -- one staff member for whom she created a dedicate form opened his up for the first time and all he got was a blank record instead of the first of all his project records as expected.

Any other ideas, questions, suggestions, postulates, etc., are welcome. Love this board!

 

[revans]

I'd be curious to see if the form was bound or unbound. Also if that text box is filled via some function or other. A simple bound form without any funky loading of data or Before/After Update event code usually won't see a #Deleted except perhaps for some concurrency thing as Xenou pointed out.

I understand there might be political fallout for what you do/don't do. You might think about making a copy of the db and rooting through that just to see. Of course, if you figure it out there might be issues if you figure it out "too soon".

hth,

Rich

 

[jan001]

I understand there might be political fallout for what you do/don't do. You might think about making a copy of the db and rooting through that just to see. Of course, if you figure it out there might be issues if you figure it out "too soon".

Have flirted with that -- maybe take a copy home and put on my home computer and poke around in it. Then my "hell no" flares up. The end result is that I haven't done it. Yet. As for the last sentence of your post... you nailed it.

 

[jackd]

Can you tell us a little more about the database - at least as it was when "you" built it?

If it is multiuser, then splitting it into a backend on server and a front end per user would seem to be a sound design.
If it isn't split, then multi user updates could be the source of conflicting/record locking issues.

How do updates occur?
How is database design and testing done? Do you have a production and test environment?
Is there any sort of testing/QA before changes get put into production? Is there any documentation for the system/database?

How is data base (management) organized in your company/department?

Solving the current problem may be an opportunity to put a little more discipline into the development/testing/change management processes.

If you solve the issue at home -- what is the probable outcome? So you'll know what's wrong. But will management be aware of what (processes/procedures) allowed the problem to occur. You really don't want this to be a "pissing contest" and have management believe there really wasn't a problem/issue and it's just a case of a few people not "getting along or not communicating". I think you're pointing to a problem with procedures, and that problem can affect "production data" and possibly management decisions.
Better to identify and solve the real issue, not a symptom.

Just a few thoughts that may help you with a strategy for resolving the current issue.

 

[jackd]

Google search shows the possible cause for the #DELETED Memo field is

SYMPTOMS

After you edit a Memo field in a Microsoft Access database, "#Deleted" appears in the field, and the original information is lost.

If you try to use the "Compact and Repair Database" utility on the database, the field may have several number signs in it, as the follows:
################

CAUSE

Multiple instances of Microsoft Access are editing the Memo field at the same time. This behavior commonly occurs in an environment where multiple users are using the same shared database on a network.

full detail here http://support.microsoft.com/kb/237333


Other responses here

http://bytes.com/topic/access/answers/193451-deleted-appears-memo-field

http://www.buzzle.com/articles/conc...rruption-and-produces-the-deleted-symbol.html

 

[jan001]

From the "Better Late than Never" office: It does sound like an eminently plausible explanation. I'm willing to bet that the person who was monkeying around with things didn't consider whether there was anyone else currently in the database at the time and what the ripple effect would be from the changes under that circumstance.

I'm keeping mum about all this for the time being. Things are back to normal now; I'm saving these explanations in case I need it in the future if that person gets itchy fingers again. Just wish that weren't necessary.

Thanks.