Access 2003 Possible Corruption?

[Jana]

Howdy! I have an Access 2003 SP1 where data tables reside on a server
& each workstation runs the front end locally. All 5 users are on the
same version of Access. We've been having problems on our main data
entry form where information input for one record will suddenly attach
itself to some other record. Then, once it starts doing this, it seems
all users have the same problem. After an exhaustive search of the
main table, I found a record with chinese characters in it. We were
able to determine the proper information, delete the chinese record and
re-enter the information in a new record. After correcting this, we
seemed to be OK, but are now starting to see the problem again. This
time I see no chinese records and nothing seems out of the ordinary.
We copied & pasted the records to a new table (so as to keep the
primary keys), but it still is periodically attaching information to
the wrong records. We've even re-created the form from scratch, to no
avail. I have scoured our code & made sure to close all database,
query def and recordset objects that are referenced in code, but it
doesn't seem to have made a difference. I am STRONGLY considering
uninstalling 2003 & going back to using 97, but this seems
drastic...anyone have any thoughts?

Any help would be appreciated.

Jana

[Darryl Kerkeslager]

I've seen something similar when primary keys, indexes, and relationships
were mangled.

Are all primary keys unique? Is there ever a situation where someone might
somehow change a primary key value? Are they all autonumber? Are all your
keys indexed? Do you enforce referential integrity?

If all this is okay, try importing all objects into a brand new mdb file.
--
Darryl Kerkeslager

[Sherwood Wang]

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Howdy! I have an Access 2003 SP1 where data tables reside on a server
& each workstation runs the front end locally. All 5 users are on the
same version of Access. We've been having problems on our main data
entry form where information input for one record will suddenly attach
itself to some other record. Then, once it starts doing this, it seems
all users have the same problem. After an exhaustive search of the
main table, I found a record with chinese characters in it. We were
able to determine the proper information, delete the chinese record and
re-enter the information in a new record. After correcting this, we
seemed to be OK, but are now starting to see the problem again. This
time I see no chinese records and nothing seems out of the ordinary.
We copied & pasted the records to a new table (so as to keep the
primary keys), but it still is periodically attaching information to
the wrong records. We've even re-created the form from scratch, to no
avail. I have scoured our code & made sure to close all database,
query def and recordset objects that are referenced in code, but it
doesn't seem to have made a difference. I am STRONGLY considering
uninstalling 2003 & going back to using 97, but this seems
drastic...anyone have any thoughts?

Any help would be appreciated.

Jana

post chinese characters please. is maybe secret message or fortune ok?

Sherwood Wang
***MVP***

[Jana]

Darryl:

Am checking these items now, I know that the primary key is definitely
indexed and is an autonumber, no way for user to type their own key in,
I don't have referencial integrity turned on, so that could be causing
some problems. Will look to see if I have any duplicate primary keys,
too. Thanks for the quick reply!!

Jana

[Jana]

OK, this might be a dumb question, but should my relationships show up
in the data mdb or in the front end mdb? I noticed that a lot of the
relationships that should have been defined in the data database
weren't there...I have recreated them, but am unsure if I got them all.
Does this matter?

[Jana]

LOL...sorry, already deleted the chinese record...

[Bruce Rusk]

The relationships display in the frontend should reflect whatever
relationships exist in the backend, but they should be defined from within
the backend.

I don't know what effect it would have on corruption already in the db, but
enforcing referential integrity should help reduce the number of problematic
records in the future ... and also reveal any possible design flaws in
forms, queries, and code ... you should probably ask your users to let you
know about any new error messages, since its possible that enforcing
referential integrity will suddenly cause some previously-frequent (but
incorrect) behaviour to stop.

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

OK, this might be a dumb question, but should my relationships show up
in the data mdb or in the front end mdb? I noticed that a lot of the
relationships that should have been defined in the data database
weren't there...I have recreated them, but am unsure if I got them all.
Does this matter?

[Jana]

Bruce:

Thanks, I have recreated the relationships (some of them were still
defined in the backend to our old corrupt table instead of the new &
hopefully clean table) and then created a new blank front end, relinked
to the back end and imported all my objects from the front end. We'll
give that a shot & see how things work out. I will also look at
enforcing referential integrity, but that's going to be a grueling
process...

Thanks for the help, I'll post when I know more.

Jana

[Allen Browne]

Jana, you've had some good answers, but this seems like an important enough
question that you might like some suggestions on how to prevent this
recurring.

To ensure the BACK END is right:
1. Open it an uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Ensure you use Referential Integrity everywhere you possibly can. (Use
cascading updates only where the PK is editable. Use cascading deletes with
caution.)

4. Consider marking your foreign key fields as Required (to prevent orphan
records), and remove the Default Value (i.e. the zero Access puts in numeric
fields.)

To ensure the FRONT END is right:
1. Uncheck the Name AutoCorrect boxes.

2. Compact.

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, and reference ambiguities are
resolved.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Bruce:

Thanks, I have recreated the relationships (some of them were still
defined in the backend to our old corrupt table instead of the new &
hopefully clean table) and then created a new blank front end, relinked
to the back end and imported all my objects from the front end. We'll
give that a shot & see how things work out. I will also look at
enforcing referential integrity, but that's going to be a grueling
process...

Thanks for the help, I'll post when I know more.

Jana

[Jana]

Allen:

Wow! Thanks for all the input. With respect to the Name Autocorrect
option, that was turned off a long time ago as I had heard rumblings
that it could cause corruption. I will double check the references,
but I am pretty sure we only have what we need and I always compile
whenever I make changes to the code. I have the db set up to compact
and repair on close, so we're good on that front. I will definitely
have to go in and enforce referential integrity, but that is going to
take some time as I know some records got deleted that have now created
orphans in related tables, so I have my work cut out for me. I have
never used that command line option for compiling, so will also do
that. Rest assured, I will double check all my assumptions as well.

Thanks again for such a comprehensive reply, I'll let you all know if
this seems to have fixed the issue, but since it only happens
sporadically, it may be a bit before I know if the changes were
successful in permanently correcting the situation.

Jana

[David W. Fenton]

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in
news:43***********************@per-qv1-newsreader-01.iinet.net.au:

At this point, you should have a database where the
name-autocorrect errors are gone, the indexes are repaired,
inconsistencies between the text- and compiled-versions of the
code are fixed, and reference ambiguities are resolved.

If it is still a problem, the next step would be to get Access to
rebuild the database for you. Follow the steps for the first
symptom in this article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

What a superb post, Allen.

I would only suggest one thing, and that's to test what version of
Jet the machines have. It looks like memo field corruption to me,
and one way to see if it's a Jet 4 problem is to convert the back
end to Access 97 (if there are no properties in the back end that
won't convert backward).

Another thing I'd seriously considered is making all memo field
editing unbound.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Wow! Thanks for all the input. With respect to the Name
Autocorrect option, that was turned off a long time ago as I had
heard rumblings that it could cause corruption. I will double
check the references, but I am pretty sure we only have what we
need and I always compile whenever I make changes to the code. . .
.
I don't believe code corruption has anything to do with your
problem, but I would suggest that on your development machine you go
to options in the VBE and turn off COMPILE ON DEMAND. This can
greatly increase code stability because it means that your code is
going to be compiled less often, which means there are fewer chances
for code marked compiled to become invalid without being recompiled.
See:

http://www.trigeminal.com/usenet/usenet004.asp

especially, the part about the 11 states of compilation in VBA.
. . . I
have the db set up to compact and repair on close, so we're good
on that front. . . .
I don't think this is a useful thing to do. Only the back end should
need to be compacted on a regular basis, and users are not opening
the back end. Even if they were, they wouldn't be able to compact it
unless they were the only ones in it.

The front end should not need to be compacted often at all, and
anyway, front ends are disposable.

In any event, I don't think it's likely that the problem is caused
by your front end. It sounds entirely like memo pointer corruption,
which is generally the result of dropped connections or bad versions
of Jet 4.
. . . I will definitely have to go in and enforce
referential integrity, but that is going to take some time as I
know some records got deleted that have now created orphans in
related tables, so I have my work cut out for me. . . .

Hmm. I don't know how you ever created a db without RI, as then it's
just a collection of tables, rather than a relational database.
That's something you should do at the beginning of every project, as
it insures that invalid data cannot get inserted into your database.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Darryl Kerkeslager]

"David W. Fenton" <dX********@bway.net.invalid> wrote

Another thing I'd seriously considered is making all memo field
editing unbound.

I've for the most part avoided memo fields because of size issues, but I do
have one that I considered unavoidable. But what is this issue you are
talking about? I'm not familiar with it.

--
Darryl Kerkeslager

[Bruce Rusk]

Remember ... the "find unmatched" query wizard is your friend.

"Jana" <Ba********@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

Bruce:

Thanks, I have recreated the relationships (some of them were still
defined in the backend to our old corrupt table instead of the new &
hopefully clean table) and then created a new blank front end, relinked
to the back end and imported all my objects from the front end. We'll
give that a shot & see how things work out. I will also look at
enforcing referential integrity, but that's going to be a grueling
process...

Thanks for the help, I'll post when I know more.

Jana

[Jana]

(blush) about not setting up RI at the outset, I must admit I was a
total newb when first creating the application, but have been
progressing by leaps and bounds since! Thank goodness for Google
Groups :) How do I 'test what version of Jet the machines have'?

With respect to memo fields, I only have one in the entire app, and
that is in a related table called 'tblChartNotes'. Funnily enough,
I've never had any issues with that table... So when you say to make
all memo field editing unbound, are you saying to have an unbound memo
field and add an event to write the contents to the underlying table
when a change is made? Or am I way off base?

On the 'compact on close', I wasn't clear...I have that on the backend,
and an experienced user goes into the backend once/twice a week at the
end of the day and then closes it so it will compact.

I will also do as you suggest on the VBE Compile On Demand option.

Thanks again for all your fantastic posts,

Jana (not nearly the newb I used to be)

[David W. Fenton]

"Darryl Kerkeslager" <ke*********@comcast.net> wrote in
news:yM********************@comcast.com:

"David W. Fenton" <dX********@bway.net.invalid> wrote

Another thing I'd seriously considered is making all memo field
editing unbound.

I've for the most part avoided memo fields because of size issues,
but I do have one that I considered unavoidable. But what is this
issue you are talking about? I'm not familiar with it.

Memo fields can be easily corrupted, in that the pointer from the
data table with the memo field to the page outside the main data
table where the memo data is actually stored can very easily become
corrupt. There are two scenarios that are particularly susceptible
to memo field pointer corruption:

1. dropped connection during an edit on the memo.

2. in replicated databases, a synchronization that takes place while
a memo field is being edited in a bound control.

The problems with the early versions of Jet 4 (pre-SP6) made it
particularly dangerous to edit memos in bound fields, and most of my
A2K applications no longer edit memos directly (a change I made
during that period when Jet 4 was very dodgy).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David W. Fenton]

"Jana" <Ba********@gmail.com> wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

(blush) about not setting up RI at the outset, I must admit I was
a total newb when first creating the application, but have been
progressing by leaps and bounds since! Thank goodness for Google
Groups :) How do I 'test what version of Jet the machines have'?
Well, manually is easy. Just find msjet40.dll in the system folder
and check the version. The build number starts with the service pack
version, and so the build should be 6000 or higher.

If you want to check the version in code, that's more complex, but
certainly easily doable.
With respect to memo fields, I only have one in the entire app,
and that is in a related table called 'tblChartNotes'. Funnily
enough, I've never had any issues with that table... So when you
say to make all memo field editing unbound, are you saying to have
an unbound memo field and add an event to write the contents to
the underlying table when a change is made? Or am I way off base?
Yes, that's basically it. However, since you have the memo field in
a table by itself, you're probably pretty insulated from these kinds
of things.

It's actually a very wise move to do that, as then if any memo
pointer becomes corrupted, you only lose the record in the memo
table, which basically has nothing but the primary key of the record
it is attached to and the memo itself.

If you put the memo field in the parent table, you could lose all
the data in the main table, not just the memo field data.

As to unbound editing, it's sufficient to do it by just not binding
the textbox you have to the underlying field in your recordsource,
and just using the OnCurrent event to load the data from the
underlying recordsource, and then save any changes in the control's
AfterUpdate event, just writing it to the field in the underlying
recordsource.

In other words, it's not really necessary to do a DAO update, though
with a separate table, there's no reason why that would be a problem
(that *is* a problem if the memo field is stored in the same table
that's being edited with bound controls, because then you've
potentially got an edit from the form colliding with an edit from
DAO).
On the 'compact on close', I wasn't clear...I have that on the
backend, and an experienced user goes into the backend once/twice
a week at the end of the day and then closes it so it will
compact.

OK. That's better, but I still think it's unwise to ever use compact
on close, as there are some circumstances where a compact will cause
you to lose data that would otherwise stay accessible.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Jana]

Hello everyone!

Just wanted to give you a quick update. I have taken all the steps
recommended by Allen in his fabulous post, and all seems to be working
flawlessly thus far...As I previously stated, the issue is sporadic.
At this point, I'm just waiting to see if it happens again. We have
not seen any more Chinese records, so that seems to have been
corrected. The only other issue is the periodic situation in which
data entered on one record updates to another unrelated record in the
same table. As of yet, I haven't seen it happen again, so I'm keeping
my fingers crossed!!

Since the base record information is only rarely changed, I've added
code to confirm that the user is changing data from X to Y whenever X
is not blank. I'm hoping that, if the users see an incorrect X value,
they will say NO and tell me that it was trying to change the wrong
record. For instance, let's say the record has 'Jana' in the field
FirstName, and the user wants to change it to 'Banana'. My code will
say something like 'Are you sure you want to change Jana to Banana?'
If the database asks them instead 'Are you sure you want to change John
to Banana?', then user should say NO and tell me that it was trying to
change another record. I don't know if this will remedy the situation,
but it should dramatically cut down on instances where the user
accidentally typed in a field and didn't mean to change the
information. Hope this makes sense...

Thanks again for everyone's input!

Jana