Linked Tables Losing Data

[Andy]

Hi there, I have a number of databases that have linked tables back to
Database A so as not to duplicate tables and fill up servers. In these
databases, I have run a number of queries some of which are based on the
table from Database A.

On returning to the table in Database A, I am frequently finding that this
core table has lost records, dropping it to a total of 10,000 or 20,000 rows
instead of circa 60,000.

None of the queries/work I am doing should affect this core table, so why is
it losing records?!?! If I haven't realised that records have gone missing,
obviously all of my other work is completely futile!!!

Access problem?

 

[David Cox]

How do you know how many records you have?
Are you perhaps just opening the table with a filter set?
(I seem to remember being bitten by this in a previous version. In Access
2007 the table opens in view all mode, and you have a toggle filter button
to apply the saved filter.)

 

[Larry Daugherty]

There's no way that anyone here could have a clue as to what has
happened but it's not an Access problem.

If a kid uses a hammer to break a window is the broken window a hammer
problem?

HTH

 

[Tim Ferguson]

If a kid uses a hammer to break a window is the broken window a hammer
problem?

To a man with a box of nails, every problem looks like a hammer problem...


Tim F

 

[Jerry Whittle]

Databases , not even Access, just don't lose records. Some possibilities:

1. You are linking to the wrong BE MDB file. I've done this.

2. Other users are deleteing records. Seen that.

3. The network drive crashed or something happened to cause a restore from a
backup. They just forgot to tell you. Been there too.

4. Access is running into a severe, persistant corruption problem and is
dropping records when repaired. If so somebody should see this happening. Not
likely.

5. As David Cox wisely mentioned, you are mistaken about the number of
records or querying the records incorrectly. The guy who looks at me in the
mirror while I'm shaving has done this.

 

[Gertrude]

Access databases DO lose records. It happens to my databases sometimes
(especially where the network is unstable or where users use Ctrl Break to
stop a query). I always make sure that I can restore records from backups,
but I would like to know why Access does this.

 

[Jerry Whittle]

I submit that Access is not losing records. If you network is unstable, the
network is losing records. Consider this: I you are working on a Word
document out on the network and the network crashes, did Word lose your
changes?

If your users are doing a Ctrl + Break to stop a query, they are messing up
Access and could leave some tables in an unstable, maybe even corrupted,
state. Again I submit that Access is not the cause of the problem.

You could possibly avoid some of these problems by going to a more
industrial strength database like SQL Server or Oracle. Access is not a
client/server database in that the computing is done on your PC whereas the
other two mentioned above do their work on the server. A network problem
would not necessarily stop the database from completing its task. Also with
Oracle you need to commit changes. Normally if someone stops a update,
delete, or append query while it is running, the changes are not commited to
the database.

In all cases, even with SQL Server and Oracle, nothing beats a recent,
complete backup!

 

[lewie]

Gary Robinson has a good book on security. It has limited to full blown
..
It might be worth investing in to tighten things up a bit. It has
sample code, a logging form that keeps track of who was in what table
at what time. You might be sure you use forms in front of all your
queries and lock the back up with the startup form and proceed from
there.
Just my 2 cents worth.
Lewie

 

[Armen Stein]

Access databases DO lose records. It happens to my databases sometimes
(especially where the network is unstable or where users use Ctrl Break to
stop a query). I always make sure that I can restore records from backups,
but I would like to know why Access does this.

I'll add another way that Access can "seem" to lose records.

- You add records to a table.
- You look at the data using a query, or a form or report based on a
query.
- The query joins to another table using an inner join (I.e. no
arrowheads on the lines).
- For your new records, there is no matching record in the other table,
so Access omits those records.
- You wonder where your records went.

 

[Gertrude]

I submit that Access is not losing records. If you network is unstable, the
network is losing records. Consider this: I you are working on a Word
document out on the network and the network crashes, did Word lose your
changes?

I can imagine that the last changes are lost if they have not been saved to
the BE database due to network problems. But this does not explain why
hundreds of records that have been created a few months ago (and saved into
the BE, and printed on reports!) are suddenly not there anymore.
The only thing I can think of that because of a query being ended by a user
(CTRL+Break), or Access being shutdown by the task manager, the records are
corrupted (but not the whole table) and with the next Compact+repair, they
are removed from the table. Does this sound familiar to anyone?
I have never been there when it actually happens, but afterwards I have to
append the records again from a backup database. (If the records were hidden
due to a filter or a join, the indexes that I have set on the table wouldn't
let me append the backup records, so please stop assuming that the records
only "seem to disappear", they are really LOST.)
Could it be a service pack issue or is this really due to bad network
connections that corrupt part of the tables sometimes?