Access 2000: Is this corruption or not?

Hi All -

I have a client for whom I developed an Access 2000 database. The
database is split into 2 files - front-end (forms, queries, reports),
and back-end (tables). An .mde file has been made for the front end
file.

Recently, they have been noticing some "problems" with the system.
Basically this system creates invoices for their customers. They have
been noticing that some of the invoices are now "wrong", specifically
that different (and incorrect) items were showing up on invoices, but
the totals are always correct.

This led me think that the Product Master records were being changed
(even though they assured me that products would _never_ change).
Here is the basic table structure:

PRODUCTS:
product_id - autonumber
category_id - FK to Categories table
product_code - 4 digit number that means something to them
product_desc - description of the product
....
other insignificant fields

To research the problem, I created a new database, and imported (into
2 separate tables, of course) the Products table from both a backup
copy of the database and the current database. I ran a query that
joined the 2 tables on product_id and looked for product_desc not
being equal. I found 10 records.

It looks to me that someone changed the descriptions on the records.
Oddly enough, the "new" descriptions are exactly those of some other
existing products. Of course, the client swears that there is no way
anyone edited any of the Product Master records. Only the
product_code and product_desc have changed on these 10 records.

I scanned all VBA code in the front-end for SQL Update statements.
There are no Update statements that update the Products table.

I've been on here researching corruption, and people talk about all
the errors they get, and how the database won't Compact/Repair. I am
not having any of those issues at all. I regularly Compact/Repair, as
well as back up the databases.

One interesting note: There is only one .mde, and it is located on
the server. They have people in the office accessing it from desktop
shortcuts using mapped network drives. The best part is, they have 4
to 6 people at a time "out in the field" accessing the .mde on the
server via Terminal Server. None of this was my recommendation.

This is only my 2nd or 3rd post, so I hope I have provided enough
information to help you all: My question is this - Is there any way
this could database corruption? Even the slightest possiblility?
Honestly, to me it looks like someone edited records. It is possible
to do that through one of the forms.

I don't want to get in the habit of manually editing production data.
Its annoying. I can't think of any Access malfunction that would edit
existing records yet not cause obvious symptoms of corruption, but I
guess that is why I am posting this.

Thank you in advance for any help -

Doug

re: Access 2000: Is this corruption or not?

Hi Doug.

Does not sound like a corruption.
Here is a possible scenario though.

Open Northwind. The Order Details subform is based on a query named Order
Details Extended. The query is based on both 2 tables: Order Details and
Products, and therefore displays the Product Name. Open the query, and type
into the ProductName column. The change gets *written* to the Products
table!

Do you have this kind of multi-table query as the source for a form/subform?
Any chance your code is making an assignment to product_code or
product_desc?
Any chance the user is able to update these text boxes?

Also, would it make sense to put a Unique index on Product_code?
Whatever the cause, that should notify you/them of the occasion when the
reassignment is being attempted.

(The bit I did not follow was why the database is split, if there is only a
single MDE front end file used by everyone. Probably not relevant to this
problem though.)

--
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.

"Doug Vogel" <chezrolez@yahoo.com> wrote in message
news:b15413d.0406031946.13363c9a@posting.google.co m...[color=blue]
>
> I have a client for whom I developed an Access 2000 database. The
> database is split into 2 files - front-end (forms, queries, reports),
> and back-end (tables). An .mde file has been made for the front end
> file.
>
> Recently, they have been noticing some "problems" with the system.
> Basically this system creates invoices for their customers. They have
> been noticing that some of the invoices are now "wrong", specifically
> that different (and incorrect) items were showing up on invoices, but
> the totals are always correct.
>
> This led me think that the Product Master records were being changed
> (even though they assured me that products would _never_ change).
> Here is the basic table structure:
>
> PRODUCTS:
> product_id - autonumber
> category_id - FK to Categories table
> product_code - 4 digit number that means something to them
> product_desc - description of the product
> ...
> other insignificant fields
>
> To research the problem, I created a new database, and imported (into
> 2 separate tables, of course) the Products table from both a backup
> copy of the database and the current database. I ran a query that
> joined the 2 tables on product_id and looked for product_desc not
> being equal. I found 10 records.
>
> It looks to me that someone changed the descriptions on the records.
> Oddly enough, the "new" descriptions are exactly those of some other
> existing products. Of course, the client swears that there is no way
> anyone edited any of the Product Master records. Only the
> product_code and product_desc have changed on these 10 records.
>
> I scanned all VBA code in the front-end for SQL Update statements.
> There are no Update statements that update the Products table.
>
> I've been on here researching corruption, and people talk about all
> the errors they get, and how the database won't Compact/Repair. I am
> not having any of those issues at all. I regularly Compact/Repair, as
> well as back up the databases.
>
> One interesting note: There is only one .mde, and it is located on
> the server. They have people in the office accessing it from desktop
> shortcuts using mapped network drives. The best part is, they have 4
> to 6 people at a time "out in the field" accessing the .mde on the
> server via Terminal Server. None of this was my recommendation.
>
> This is only my 2nd or 3rd post, so I hope I have provided enough
> information to help you all: My question is this - Is there any way
> this could database corruption? Even the slightest possiblility?
> Honestly, to me it looks like someone edited records. It is possible
> to do that through one of the forms.
>
> I don't want to get in the habit of manually editing production data.
> Its annoying. I can't think of any Access malfunction that would edit
> existing records yet not cause obvious symptoms of corruption, but I
> guess that is why I am posting this.
>
> Thank you in advance for any help -
>
> Doug[/color]

re: Access 2000: Is this corruption or not?

Doug Vogel wrote:
[color=blue]
> I scanned all VBA code in the front-end for SQL Update statements.
> There are no Update statements that update the Products table.[/color]

Have you checked for recordsets? Do you update info in a recordset?
It's possible that you can open up a recordset and update the info
without going to the proper record.

re: Access 2000: Is this corruption or not?

Doug Vogel wrote:[color=blue]
> This led me think that the Product Master records were being changed
> (even though they assured me that products would _never_ change).[/color]

As others are already answering your main question, as a quick aside I
just want to mention this: NEVER trust your users when they say
something like this. Smile, nod, and design the system to be able to
handle it if the things they say would never change end up changing.

re: Access 2000: Is this corruption or not?

chezrolez@yahoo.com (Doug Vogel) wrote in
news:b15413d.0406031946.13363c9a@posting.google.co m:
[color=blue]
> Is there any way
> this could database corruption? Even the slightest possiblility?[/color]

Peter Miller could likely answer that definitively, but I'd say
there's zero chance that such an error could happen due to
corruption.

It would require too perfect a swapping of two pieces of equivalent
data.

It has to have happened from human error or from something in the UI
allowing it to happen accidentally.

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

re: Access 2000: Is this corruption or not?

John Baker <baker-j@ix.netcom.com> wrote:
[color=blue][color=green]
>> This led me think that the Product Master records were being changed
>> (even though they assured me that products would _never_ change).[/color]
>
>As others are already answering your main question, as a quick aside I
>just want to mention this: NEVER trust your users when they say
>something like this. Smile, nod, and design the system to be able to
>handle it if the things they say would never change end up changing.[/color]

<chuckle> Agreed.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

re: Access 2000: Is this corruption or not?

John Baker <baker-j@ix.netcom.com> wrote in message news:<gz%vc.24754$DG4.19448@fe2.columbus.rr.com>.. .[color=blue]
> Doug Vogel wrote:[color=green]
> > This led me think that the Product Master records were being changed
> > (even though they assured me that products would _never_ change).[/color]
>
> As others are already answering your main question, as a quick aside I
> just want to mention this: NEVER trust your users when they say
> something like this. Smile, nod, and design the system to be able to
> handle it if the things they say would never change end up changing.[/color]


Thanks to all the have replied.

The reason for splitting the database was to be able to make front end
changes without affecting the back end. Often times, I make minor
tweaks to forms or reports. This allows me to only have to change the
front end database, and not worry about downtime for the client, or
data concurrency issues.

Interesting point about the Northwind example. My system has plenty
of subforms that show only datasheet views of records from the
affected tables. However, by my design, all fields on subforms are
set to Enabled=No and Locked=Yes. The exact opposite is Access's
default, so I manually have to change them all. Also, by my design,
to edit a record showing in a subform, the user has to highlight it,
and click a button, which takes them to a separate form. So the
inadvertant editing in the subform window is out.

In addition, since my last post, I found something else similar in
another table. The table def is:
ROOMS:
room_id - autonumber
room_name - text

10 of the Rooms records have been edited somehow, again to room names
from other existing records. For example, I now have 4 instances of
"Bedroom 2". I have triple-checked - the user interface front end
doesn't even have a way to see or edit the existing rooms. They can
add a new one, but the backside VBA only lets you add a room that
doesn't already exist. Any dropdowns that have the rooms list don't
let you type anything into them that isn't in the dropdown to begin
with. The ironic part is that it is 10 records in each table that
have been edited, but the Rooms table has no interface through the
..mde.

We restored the database from a recent backup. Since they have
hardcopies of all invoices (thank heavens), they are going to manually
re-enter all invoices from the date of the backup.

I suppose someone could have accessed the back-end database through
the file system and made these changes...does anyone know a way to
password protect the back-end database for opening directly, which
would not affect using the database through the linked .mde file??

Thanks again for all the help -
Doug

re: Access 2000: Is this corruption or not?

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message news:<Xns94FE77659CDDDdfentonbwaynetinvali@24.168. 128.78>...[color=blue]
> chezrolez@yahoo.com (Doug Vogel) wrote in
> news:b15413d.0406031946.13363c9a@posting.google.co m:
>[color=green]
> > Is there any way
> > this could database corruption? Even the slightest possiblility?[/color]
>
> Peter Miller could likely answer that definitively, but I'd say
> there's zero chance that such an error could happen due to
> corruption.
>
> It would require too perfect a swapping of two pieces of equivalent
> data.
>
> It has to have happened from human error or from something in the UI
> allowing it to happen accidentally.[/color]

David -

I totally agree. I have been looking over the UI again and again.
There's not even any VBA code that updates the tables in question.
There is a form where it is possible to edit PRODUCTS records, but
there is no UI to the existing ROOMS records.

A new development in the situation is this: One of the regular users
is out in the field with a laptop that is using a dial-up internet
connection to access the application via Terminal Server. Recently he
has noticed that his computer has been incredibly slow (much more than
normal). I also found out that he has no virus protection software on
the laptop. Again, like you said, it would require a perfect swapping
of the data, but now I am beginning to wonder.

In addition to the no virus protection issue, I guess it is possible
for someone to just open the back-end database and change things.