Corrupted DB, now problems entering data

[TOP]

Total DB noob. I sent my DB off to a data recovery company after it
apparently became corrupted. They sent back the repaired DB, but users have
been having problems with it since then:

New entries in a Results form. The first field in the form is "client". If
users type anything in that field, they get the wrong ResultID (which is an
AutoNumber). The Results table's last Result ID is 21403, but if anything is
typed (don't have to hit Enter, just type a letter in the field) the
ResultID is showing 20897. It started incrementing at 20882 and is up to
20897, but it should have started at 21404.

The Results form has a "Filter" button. If users click this button and enter
an ID (e.g. 21403) they get an error "Filter error, may be out of memory".
This usually happens after trying a few other things. Closing and reopening
Access will usually allow the filter to work.


There is also a Release form. If users choose a project title from a
dropdown menu (this is the first field) and click a print button at the
bottom of the form, the wrong project (wrong record) prints.

On that same form there is a button to View/Edit Products. Clicking that
button opens a new window, which I'm pretty sure links to another table of
products that are 'released' for film projects. That new window shows
products currently released to that project, and has a button to add
products. Clicking that brings up another window of products, with "+"
buttons to add them to the project. Clicking the + doesn't do anything
anymore. Going back to the "new" window doesn't show any products as being
added.


One more. There is a Projects form. One of the fields is Production Company.
Entering new data brings up a window saying something like 'this does not
exist, do you want to add it?' Clicking Yes usually works, but now it
returns a Run-time error '3022': The changes you requested to the table wree
not succesful because they would create dupliate values in the index,
primary key, or relationship. Change the data in the field or fields that
contain duplicate data, remove the index, or redefine the index to permit
duplicate entries and try again.


I'm guessing that this is either something really simple that I need to do,
which will fix all the problems, or it's so complicated that I'm going to
need to hire a DBA to come in and fix it.

Any clues?

TIA

 

[Guest]

Seems like there were missing records in your ResultID and the data recoevery
folks just renumbered everything sequential.

Do you have any older backup copies that could help in sorting it out?

 

[TOP]

The most recent DB backup is from six months ago (told you I was a DB noob).
I'll try opening both tables side by side and see if there are any glaring
inconsistencies.

Thanks

 

[TOP]

Well I found a backup that was only(!) three months old. Its records go up
to 20603. I scrolled both tables down to that number and compared a page of
data and there were no differences. I guess that's not conclusive, since it
would make sense that any corrupted data might be near the end.

I'm half tempted to keep opening and closing the DB 500 times until the
autonumber catches up to 21403 and see what happens, but that seems like a
collosal waste of time.

 

[John Vinson]

I'm half tempted to keep opening and closing the DB 500 times until the
autonumber catches up to 21403 and see what happens, but that seems like a
collosal waste of time.

Yes, it is.

An autonumber has one purpose, and one purpose ONLY: to provide a
guaranteed (almost, there have been bugs) unique key.

Autonumbers will ALWAYS have gaps. If gaps aren't acceptable - don't
use autonumbers; instead, use a Long Integer field and maintain it
manually or in VBA code.

John W. Vinson[MVP]

 

[TOP]

Thanks for the response. AFAIK, gaps are fine. Any idea what could be
causing the rest of the behavior?

 

[Albert D.Kallal]

New entries in a Results form. The first field in the form is "client". If
users type anything in that field, they get the wrong ResultID (which is
an AutoNumber). The Results table's last Result ID is 21403, but if
anything is typed (don't have to hit Enter, just type a letter in the
field) the ResultID is showing 20897. It started incrementing at 20882 and
is up to 20897, but it should have started at 21404.

As mentioned else where, autonumbers are just internal numbers that can be
used for referencing a record. However, these numbers are NOT to be given
any meaning to your end users, and are not sequential in nature. Doing a
compact and repair usually re-sets the number to the next highest availing.
Anyway, regardless, they are not to be given any meaning to your users. I
certainly hope that this mistake was not the reason you had to get your file
fixed, as this is normal behavior.

It is always sad to see time and resources wasted like this. You might as
well just though out some food you just cooked (you could use that food to
feed the poor, or just throw it in the garbage) By making these mistakes,
you are wasting precious resources that could help other people, or help
yourself more. And, if this is on company time, then you are wasting their
resources, and making them less productive.

The Results form has a "Filter" button. If users click this button and
enter an ID (e.g. 21403) they get an error "Filter error, may be out of
memory". This usually happens after trying a few other things. Closing and
reopening Access will usually allow the filter to work.

Are you taking about the built in filter button, or one with custom code.
Again, you should for questions of this type start a new post. A few things
to check:

Does all your code compile in this application?

Have you installed the bug fixes and patches from Microsoft? It does not
make ANY sense to run an application that has had MANY MANY bug fixes
issued, and then you fail to install these fixes. You then have problems
that may have been fixed long ago. So, it makes ZERO sense to run your
application without all of the MANY bug fixes installed. You don't mention
what version of ms-access, but I certainly would install the patches and
fixes and updates to BOTH office, and JET.

There is also a Release form. If users choose a project title from a
dropdown menu (this is the first field) and click a print button at the
bottom of the form, the wrong project (wrong record) prints.

Well, forms are for entering of data, and reports are for printing. Just
like the autonumber fiasco, you are using something for the wrong use. Build
a nice report that has all the fields and data on it. Then, the code behind
a button to print the one record would then be:

me.Refresh
docmd.OpenReprot "yourreprot",acViewPreview,,"id = " & me!id

So, once again:
forms - they are for data entry
reports - they are for printing things

On that same form there is a button to View/Edit Products. Clicking that
button opens a new window, which I'm pretty sure links to another table of
products that are 'released' for film projects. That new window shows
products currently released to that project, and has a button to add
products. Clicking that brings up another window of products, with "+"
buttons to add them to the project. Clicking the + doesn't do anything
anymore. Going back to the "new" window doesn't show any products as being
added.

You should make a separate question here. It is not one bit clear as to what
code runs when you click on the + button, or how this works. Did this code
ever work?

One more. There is a Projects form. One of the fields is Production
Company. Entering new data brings up a window saying something like 'this
does not exist, do you want to add it?' Clicking Yes usually works, but
now it returns a Run-time error '3022': The changes you requested to the
table wree not succesful because they would create dupliate values in the
index, primary key, or relationship. Change the data in the field or
fields that contain duplicate data, remove the index, or redefine the
index to permit duplicate entries and try again.

The above sounds like your relationships are incorrect. It is also possible
that the code you have to make the update is wrong, or trying to write the
wrong key value (actually foreign key value) to the table. Check your
relationships here, as these values used to link a 'child table' for
relationships are NOT autonumbers, but in fact regular long integer fields.
And, since 'many' records can be related to the master, then obviously the
index must allow duplicates for that field. So, check the index on the
field, and make sure it is set to allow duplicates.

I'm guessing that this is either something really simple that I need to
do, which will fix all the problems, or it's so complicated that I'm going
to need to hire a DBA to come in and fix it.

If the above instructions and suggestions don't make sense, then perhaps you
should bring in a professional to clean up the application and getting it to
work for you. Not knowing your experaince level here makes this question
hard to answer.

I don't want you to waste your food, or for you to waste you time and
resources
that can be better spent doing something else.

Only YOU can decide what is better use of your time here. The problems you
mention don't seem too difficult, but it is not clear if these problems all
of a sudden appeared, or were just many things that never worked very well
in the first place due to poor design choices.

It should be noted that well built ms-access application as a general rule
is
MORE reliable then running word, or even outlook on your pc.