#deleted record problem

[ump38]

No doubt this question has probably been asked elsewhere and if I'm in the wrong forum I apologize in advance.

Using Access '95 on Windows NT platform in a multi-user (12-15 on average) environment. I have one particular table in a back-end database file that has recently become a problem.
The table has 2 fields which are indexed [SSN (123456789) and SSN_Sequence # (123456789-1) which is also the primary key] as individuals may appear multiple times in this table. Periodically (maybe every 3-4 days) it appears that the indices for this table get "corrupted". We repair the database and things are fine again for 3-4 more days.

The front-ends for these users have no delete functionality, only "add" and "edit". What I've noticed on the database before the last repair action was done was a record with all fields showing in the table as "#deleted". As there is no delete options available, I'm trying to determine what that's really trying to tell me.

I'm trying to determine if there's something I'm mishandling in the multi-user environment or exactly what maybe to look for that might be causing an index to get build improperly. There are 40,000+ records in this table and when things get "squirrelly", thus far the repair option has managed to clean things up.

Any wild ideas or thoughts on things I should be considering here? Thanks!!!!

 

[Darrylles]

Hi Ump,

I understand it as this:

1 table
2 fields in table
fieldnames?
pk field?
Problem: 'Indices "appear corrupted"' - How do you get to
this conclusion - error messages?

Can you elaborate and clarify?

Regards,

Darrylle "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[ump38]

Hey Darrylle,

To clarify a bit: this one particular table called 'CapturedFingerprintResults' contains 40,000+ records and has many (30+) fields... however only 2 are indexed. The PK field called "SSN_Sequence" which contains unique values for every occurrence of a particular SSN which can appear multiple times (EX: 123456789-1, 123456789-2, 223344556-1, etc.) There is one other indexed field called "SSN" which contains only the 9-digit SSN. EVERY record has a value in at least these 2 fields. One thing I failed to mention earlier is that the entry/update form contains only unbound controls that get saved via recordset updates when the user clicks "save". The save process does have logic to handle simultaneous updates although that really doesn't happen simply by the nature of who's handling each particular record.

As far as my indication of the problem: worst-case scenario is that the database won't open and it throws up the message "Access can't open this file. Database may need repaired"... and repair corrects this.

More typical scenario is that update queries get to a particular record and a Dr. Watson (GPF) error occurs. Debugging allows me to isolate the record in question and that's where I see the actual record fields all showing as "#deleted". Another typcial scenario is user reports trying to view and/or update a particular record and the form never opens. That's how I was actually again able to see the "#deleted" indication in the table.

This might not truly be an "index" problem but maybe just the way I perceive what the "repair" feature does to make things work for 3-4 days. This might not explain my scenario any better than before.

 

[Darrylles]

Hiya,

I'm in the UK - 3am here and I've gotta go to bed.
Hopefully someone will respond after this (before I get to it), but, just to make clear to you:

PK is important: primary key - unique value for record.

Indexes irrelevant (in this problem) - just specified
field(s) that make searches quicker. Are you confusing index with primary key?

This is intriguing (even at 3am!).

Quick thought - you have unbound fields that use code to 'bind' unbound fields logically - is ANY of this code recently changed?
If so, have the problems recently started?

I've got this on email notification and am looking forward to a quick solution from the Tek-Tips experts!

Kind regards,

Darrylle







"Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[Steve101]

ump,

I can relate to that problem; had something similar a few years ago, and it was an absolute nightmare. Just a couple of general observations, which will probably not be any use at all, but anyway, here goes:

(a) Access 95 is an absolute nightmare of bugs; get rid of it as quickly as you can, at least moving to Access97, preferably 2000. I dont remember the effort involved to upgrade your applications.

(b) Has anything changed in your operating environment that could have triggered these problems; hardware upgrades; software upgrades; Operating System upgrades. Check carefully.

(c) Do you have a reliable network. What can happen with Access is that a corrupt data packet can find its way into an Access database, causing one or more Access objects to be corrupted; All it takes is one 'rogue' workstation with a faulty network card. I'm no expert on communications/networking, but check with your network administrators that appropriate TCP/IP error correction protocols are installed on all computers.

(d) Finally, there's quite a lot of stuff on the net relating to Access database corruptions. Surf around a bit, using a good search engine. You should find some things that you find useful.

Goopd luck, Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au