Corrupt Encrypted Back-end. Any suggestions?

Well, after reading and hunting all over the web, including here, I
still haven't been successful in my attempts to resolve my situation.
So, I thought maybe I'd just ask. Here's the situation:

I have an Access 2000 database (~15 users), split into a front- and
back-end. Each user has a local copy of a workgroup file and the data
file is out on a server.

The problem started when the IS department upgraded the server at
night, touching every file in the process. One of my users decided not
to log out at night and left an open connection to the data file. Thus,
I come in the next day to find everyone getting error messages (can't
connect, not a valid mdb, etc.) and those that can get in are
experiencing a weird error: one of the forms is displaying records for
the wrong patients.

The form affected (which probably isn't the only one but it is easily
noticable) pops up listing records linked to the customer. It's a
progress notes form with timestamped entries. Now wrong customers'
notes are showing up, getting criss-crossed.

I booted everyone off, made a copy of the data file to explore, and
found that when I searched for some of the sub-table's records they
were being displayed with one foreign key but when I filtered on it, it
displayed that record plus a bunch of records with a different foreign
key. The database seems to store the correct key with the record but
when you look at it in the table it shows a different key.

So here's what I did. I created a new database from scratch, using my
administrative account in our security file, imported the tables from
the copy of the corrupt data file, re-linked a copy of my front-end to
the new data file and built a new mde. When I went to test it, things
were working again. But I come in the next day and everyone starts
getting the same errors. The one catch is that a single user logged in
to the new data file with the old mde before I got a chance to upgrade
hers. Now, I'll try all of this again to see if that old mde hitting
the data file is what corrupted things all over again but I am begging
for additional suggestions.

Can I create a blank database with the standard workgroup file and
import the tables from the encrypted one? Can I export the encrypted
tables to text files or spreadsheets so I can import them into a
non-encrypted data file and scrap encryption? I plan on trying to
migrate the backend to Oracle soon, using Access only as a front-end
with ADO. But I need to get things limping along until then.

Ideas?

re: Corrupt Encrypted Back-end. Any suggestions?

nepdae@vmmc.org wrote:[color=blue]
> Well, after reading and hunting all over the web, including here, I
> still haven't been successful in my attempts to resolve my situation.
> So, I thought maybe I'd just ask. Here's the situation:
>
> I have an Access 2000 database (~15 users), split into a front- and
> back-end. Each user has a local copy of a workgroup file and the data
> file is out on a server.[/color]

Why has each user has a local copy of the workgroup file? Makes it harder
to administer users. Any reason for not also sharing it on the server?
[color=blue]
> The problem started when the IS department upgraded the server at
> night, touching every file in the process. One of my users decided not
> to log out at night and left an open connection to the data file.
> Thus, I come in the next day to find everyone getting error messages
> (can't connect, not a valid mdb, etc.) and those that can get in are
> experiencing a weird error: one of the forms is displaying records for
> the wrong patients.
>
> The form affected (which probably isn't the only one but it is easily
> noticable) pops up listing records linked to the customer. It's a
> progress notes form with timestamped entries. Now wrong customers'
> notes are showing up, getting criss-crossed.
>
> I booted everyone off, made a copy of the data file to explore, and
> found that when I searched for some of the sub-table's records they
> were being displayed with one foreign key but when I filtered on it,
> it displayed that record plus a bunch of records with a different
> foreign key. The database seems to store the correct key with the
> record but when you look at it in the table it shows a different key.
>
> So here's what I did. I created a new database from scratch, using my
> administrative account in our security file, imported the tables from
> the copy of the corrupt data file, re-linked a copy of my front-end to
> the new data file and built a new mde.[/color]

Did you try a repair/compact first?
[color=blue]
> When I went to test it, things
> were working again. But I come in the next day and everyone starts
> getting the same errors. The one catch is that a single user logged in
> to the new data file with the old mde before I got a chance to upgrade
> hers. Now, I'll try all of this again to see if that old mde hitting
> the data file is what corrupted things all over again but I am begging
> for additional suggestions.[/color]

Maybe force users to shut down the database? (eg. if no activity for 10min
close all forms bound to data, or if time between 6pm and 6am close?).
[color=blue]
> Can I create a blank database with the standard workgroup file and
> import the tables from the encrypted one?[/color]

When did encryption come into this?

You can import as long as you have permissions.
[color=blue]
> Can I export the encrypted
> tables to text files or spreadsheets so I can import them into a
> non-encrypted data file and scrap encryption?[/color]

What do you mean "scrap encryption"? Can't you just turn encyption off?
[color=blue]
> I plan on trying to
> migrate the backend to Oracle soon, using Access only as a front-end
> with ADO. But I need to get things limping along until then.
>
> Ideas?[/color]
--
regards,

Br@dley

re: Corrupt Encrypted Back-end. Any suggestions?

>Did you try a repair/compact first?

Yeah, the problem is that everything seemingly works (manually
verifying newly added data integrity) until the next day when users
start logging back in. Then things fall apart again. No out of the
ordinary user actions are taking place. They do exactly what I do when
I test things.

[color=blue]
>Maybe force users to shut down the database? (eg. if no activity for 10min
>close all forms bound to data, or if time between 6pm and 6am close?).[/color]

We have established new protocols for database use (too little, too
late) but it's not that they are continuously re-creating the original
scenario. That server upgrade only tainted the data file once. All
users are logged off at the end of the day and the data file isn't
being touched by outside processes.
[color=blue][color=green]
>> Can I create a blank database with the standard workgroup file and
>> import the tables from the encrypted one?[/color][/color]
[color=blue]
>When did encryption come into this?[/color]

The data file was encrypted from day one. During my research, I came
across posts indicating that encrypted data files are more prone to
corruption. I'm just grasping at straws right now. If that is the case,
I would happily trade that for stability until we migrate to Oracle
whereby security and data integrity will be much more stringently
enforced.
[color=blue]
>You can import as long as you have permissions.[/color]

Well, the thing is that I have imported the whole data file tableset
into a clean mdb while using the secured mdw and logged in as my
adminstrative account. But that imported copy still exhibits the same
problems after a day. I guess I'm just trying to figure out what action
being taken is the cause for re-corruption. Like I said, there was that
one user who tried to access a new, imported and supposedly stable data
file with an mde still linked to the corrupted data file. The new data
file was identically named and put in place of the old, corrupt one but
I thought maybe not using an mde specifically linked to the clean copy
and re-compiled might have tripped things up. Does that sound
plausible? If not, I don't know what else to look at.

- Dae

re: Corrupt Encrypted Back-end. Any suggestions?

nepdae@vmmc.org wrote in
news:1141870145.502042.244760@p10g2000cwp.googlegr oups.com:
[color=blue]
> Well, the thing is that I have imported the whole data file
> tableset into a clean mdb while using the secured mdw and logged
> in as my adminstrative account. But that imported copy still
> exhibits the same problems after a day. I guess I'm just trying to
> figure out what action being taken is the cause for re-corruption.
> Like I said, there was that one user who tried to access a new,
> imported and supposedly stable data file with an mde still linked
> to the corrupted data file. The new data file was identically
> named and put in place of the old, corrupt one but I thought maybe
> not using an mde specifically linked to the clean copy and
> re-compiled might have tripped things up. Does that sound
> plausible? If not, I don't know what else to look at.[/color]

The errors you're encountering sound like a corrupted index. I would
suggest importing the empty table structures, rather than structure
plus data, and then appending the records to it. This will then mean
that any corruption you have will be in the records and not in the
table structures themselves.

As far as I know, there is no evidence that encrypted databases
(which any MDB secured by Jet user-level security should be, or it's
not really secure, since anyone could browse the data by loading it
into any editor directly) are more prone to corruption.

I don't know what the issue of using an MDE has to do with it. Only
the front end should be an MDE, as there is no effect on a data-only
MDB when converted to an MDE (since there are no
forms/reports/modules in it).

You should also check that everyone has patched versions of Access
and Jet. I've seen machines reverted to older unpatched versions
that caused all sorts of havoc.

If you find that everything is OK in that regard, and you're still
experiencing the corruption, I'd suggest that you send the file to
Peter Miller of PKSolutions (PKSolutions.com). He'll not charge you
for analyzing the file, only if he's able to recover it, and he's
very cheap (in addition to being very reliable).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Corrupt Encrypted Back-end. Any suggestions?

I think you might have confused Encryption with WorkGroup
Security.

Encryption you can turn on or off by opening the database
and going to Tools, Security, Encrypt/Decrypt.

This is independent of and not related to WorkGroup
Security.

After compacting and repairing a database, you should
relink and compact each of the databases that connect
to it. Database design changes are not integrated
into the database design until you compact the database.
When you compact a database and integrate any recent
database design changes, you invalidate linked tables
which have stored the old database schema. This can
certainly cause errors and confusion, and might reasonably
be supposed to cause corruption. Repairing a damaged
database might reasonably be supposed to cause design
changes: Repair will normally drop damaged indexes.

After repairing a corrupt database, it is good practice
to import all of the objects into a new database, and to
check that none of the indexes have been dropped. It is
better practice to import all of the structures, and then
append all of the data. It is even better practice to
create all of the structures from scratch, then append
the data. In 2003, XML may be one way to do this. The
last step is pretty extreme, and rarely justified.

Since the IS department has 'upgraded' the server, you
should immediately suspect network failures. Access is
a canary in a coal mine to network failures, and you
should start complaining now. Make sure they have applied
all service packs: ask them to monitor bad packets,
and suggest turning off opplocks while you get this
problem sorted.

(david)


<nepdae@vmmc.org> wrote in message
news:1141863988.705588.295530@v46g2000cwv.googlegr oups.com...[color=blue]
> Well, after reading and hunting all over the web, including here, I
> still haven't been successful in my attempts to resolve my situation.
> So, I thought maybe I'd just ask. Here's the situation:
>
> I have an Access 2000 database (~15 users), split into a front- and
> back-end. Each user has a local copy of a workgroup file and the data
> file is out on a server.
>
> The problem started when the IS department upgraded the server at
> night, touching every file in the process. One of my users decided not
> to log out at night and left an open connection to the data file. Thus,
> I come in the next day to find everyone getting error messages (can't
> connect, not a valid mdb, etc.) and those that can get in are
> experiencing a weird error: one of the forms is displaying records for
> the wrong patients.
>
> The form affected (which probably isn't the only one but it is easily
> noticable) pops up listing records linked to the customer. It's a
> progress notes form with timestamped entries. Now wrong customers'
> notes are showing up, getting criss-crossed.
>
> I booted everyone off, made a copy of the data file to explore, and
> found that when I searched for some of the sub-table's records they
> were being displayed with one foreign key but when I filtered on it, it
> displayed that record plus a bunch of records with a different foreign
> key. The database seems to store the correct key with the record but
> when you look at it in the table it shows a different key.
>
> So here's what I did. I created a new database from scratch, using my
> administrative account in our security file, imported the tables from
> the copy of the corrupt data file, re-linked a copy of my front-end to
> the new data file and built a new mde. When I went to test it, things
> were working again. But I come in the next day and everyone starts
> getting the same errors. The one catch is that a single user logged in
> to the new data file with the old mde before I got a chance to upgrade
> hers. Now, I'll try all of this again to see if that old mde hitting
> the data file is what corrupted things all over again but I am begging
> for additional suggestions.
>
> Can I create a blank database with the standard workgroup file and
> import the tables from the encrypted one? Can I export the encrypted
> tables to text files or spreadsheets so I can import them into a
> non-encrypted data file and scrap encryption? I plan on trying to
> migrate the backend to Oracle soon, using Access only as a front-end
> with ADO. But I need to get things limping along until then.
>
> Ideas?
>[/color]