Records are being lost from Access 2002-03 opened in Access 2007

[Joseph J]

We have been working on Access 2002-03 placed on a server and opening it on
the terminals without any problems. In the last week we have upgraded to
Access 2007 but we are still using the old access format on 2007 format
software. In the last 4 days, we have discoved that the records we add are
being lost, and the access database when it is opened it seems to go back to
an earlier version! Any idea what is causing this and how one can rectify it?
This does not happen all the times!

The records we add do not indicate that there has been any records deleted,
as the autonumber remains sequential!

Thanks for the help in anticipation for a quick reply

 

[Jeff Boyce]

Joseph

See comments in-line below...

We have been working on Access 2002-03 placed on a server and opening it
on
the terminals without any problems. In the last week we have upgraded to
Access 2007 but we are still using the old access format on 2007 format
software.

It isn't clear whether Access 2007 is installed on the server or on
individual PCs.

In the last 4 days, we have discoved that the records we add are
being lost, and the access database when it is opened it seems to go back
to
an earlier version!

If you only installed Access 2007, then used it to open an Access 2002-03
..mdb file, Access is unlike to have converted the .mdb to the new .accdb
(Access 2007) format. In fact, you HAVE to use Save As and the new A07 to
convert it.

How are you determining that the records are being lost?

Any idea what is causing this and how one can rectify it?
This does not happen all the times!

The records we add do not indicate that there has been any records
deleted,
as the autonumber remains sequential!

Thanks for the help in anticipation for a quick reply

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joseph J]

Thanks for the reply.

Access 2007 is installed on the local PC machine but the actual database
file is stored on the server as a shared file.

We are dtermining that these recotrds are lost or have disappeared as for
example the number of records we have is 8340 at time of opening and 8350 at
time of close. We find that we open the file again it went back to 8340!

The Access 2007 did not convert the file to 2007 format; it is remaining in
2002-03 format throughout the process. I have not saved it as 2007 format not
as yet anyway!

I hope this helps and many thanks for yourn help.

 

[aaron.kempf]

SQL Server doesn't 'randomly lose data' and it has plenty of auditing
tools to see who did what when

With Access-- you can code your own solution; but it won't work when
someone disables macros.

With SQL Server- you can run profiler to see 'who is doiugn what and
when'.
More importantly-- SQL Server is secure, so you can prevent people
from deleting records.

Is that _EVEN_ possible in MDB ? lol

-Aaron

 

[Jerry Whittle]

More importantly-- SQL Server is secure, so you can prevent people
from deleting records.

Why yes it is with a little skill and knowledge. I will grant you that it
isn't really possible with an ACCDB though.

 

[Jeff Boyce]

Joseph

I believe you mentioned the Autonumber ... for the record, the Autonumber is
NOT guaranteed to be sequential.

Are you determining the count of records by using a query, or by looking at
the value of the Autonumber?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joseph J]

Hello Jeff,

That is true in relation to the autonumber. I am determining the count of
records by countuing the number of records at the bottom of the form where we
enter the records and also by checking that there no duplicate recioords for
those records where we had to enter them twice becuase they "disappeared"
after the first time. These records also appear in a report that we produce
on a daily basis, so if there were duplicates of them they would appear twice
on the report and yet that does not happen. All that goes to confirm that
these records have disappeared. The reason we know these recorsd have been
created is that beacuse we merge the data we enter with word documents which
we save. We are able to retrieve the owrd documents for all these "lost
records" which contain the demographics of patients!

Sorry about the long reply. I hope this helps.

 

[Jeff Boyce]

Joseph

Let's see if I can paraphrase...

You (your users) open a form. The navigation info at the bottom tells you
"n of mmm" so you know how many records you have.

You enter new information on the form and ?save? it. The navigation info at
the bottom of the form now says "n of mmm+1".

You close the database and reopen it ... and your form is back to "n of
mmm".

The next place I'd look would be to add a new record via the form, then
confirm via the Tables window (and by checking directly in the table) that
the record has been added.

If that record disappears from the underlying table (not from the form) when
you shut down and restart, then it certainly sounds, like others responding
have suggested, that there may be some kind of "trigger" that is dumping the
new records.

The other approach I'd check is to ensure that the back-end data source that
the form is using is the correct source. Given the copy/move, there's an
(outside) chance that the tables/links have ended up pointed at the wrong
source.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Joseph J]

Jeff,

Your paraphrasing is there 99%, very good for someone who has not had a look
at the database. However, we have never had to save the records entered in
the "Form". However, I have noticed now 3 times and that is after we
discovered the problem, that everytime I save it the records are still there
after you close down the database and reopen it! Now I do know if this is the
answer! Does one really need to save the data in the form? I am not aware
that this is a requirement, however one can do that if that is required to
resolve it!

Any idea what that "triger" might be so wecan avoid it?

The other approach I'd check is to ensure that the back-end data source that
the form is using is the correct source. Given the copy/move, there's an
(outside) chance that the tables/links have ended up pointed at the wrong
source.

I am not so technical to check for this! Are you able to help if I send you
a dummy copy or perhaps you can takle me through what I need to do and how to
do it on the phone? I will be happy to call you. I am still in the office (in
London).

Kindest regards

 

[Jeff Boyce]

Joseph

Since I have no idea how your form is "wired", I could only guess about the
"saving".

One way to wire up a form that WOULD require saving every record would be if
the form were not bound to an underlying dataset (or table). If the form is
unbound, YOU have to do all the work to get the data saved (and loaded into
the form initially, for that matter).

If you are working solely with MS Access files, I'm not aware of any
"trigger" capabilities (this becomes an option when you migrate your
back-end/data to something like SQL-Server).

It sounds like, for now, if you can force the users to save before closing,
the data gets saved.


Regards

Jeff Boyce
Microsoft Office/Access MVP