RE: Importing and Autonumber Problem

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You want to see a mess! you should see the pig I have to try to keep upright
and moving. Who ever put this thing together thinks relationships are what
happens after working hours.

Anyway, don't obsess about the Autonumbers. They are (or should be) only
for establishing relationships. Whether there are gaps makes not difference.
If a number is missing and gets used by a new record, so what? The only
problem you could have is if some oprhans are lingering in child tables.
Hopefully, referential integrety is set up and and that won't be an issue.

It is never a good idea to expect Autonumbers to be sequential.

"Klatuu" wrote:

When you say "paste", I crawl under my desk and start crying. I hope you
don't really mean that.

I think what will help is to create an append query that will append the
records from your linked table to your Client table, but do not include the
Autonumber field in the query. It will take care of itself.

As to going from 97 to 2003, that is not recommended. I would support
getting there, but it is recommended you go from 97 to 2000 then to 2003.

"Michael" wrote:

I have just inherited a Access97 database with lots and lots of tables and
relationships and forms and queries and well you get the idea. They want to
step away from the 97 and goto to 2000 (for the life of me don't ask why, I
have been asking the same thing, why not go to 2003 since you are doing this
but heck I am not in charge). Anyway, before I got here, they contracted an
outside company to convert the database structure for them to 2000. They did
that but they either missed this little ditty or franckly didn't care.

One of the tables has a field called ClientID which is generated using
Autonumber and is a primary key. That table has a one to many relationship
with two other tables using the ClientID where its a Long Integer number in
the other two and a many to many relationship with more than a dozen tables
with other fields being used to link them. Integrity is enforced and here is
where the problem lies.

When you "paste" the data from the linked or copied tables (whichever you do
it has the same problem) it starts the autonumber and sequentially and
perfectly generates the new numbers. here is the problem though, the original
sequence is not a perfect continuous generation. 1,2,6,9,25,46,47,48 you get
the idea and now it becomes 1,2,3,4,5,6,7,8. As you can see the problem, this
causes the integrity to fail in other tables that rely on this table and
therefore are "missing" their link to a data that is necessary or previously
recorded.

Here is my question now that you have the basic history behind the problem,
how in the world can I fix this problem? PLEASE! I mean I have tried
converting the autonumber field to integer which we all know can't happen but
was worth a shot. Breaking the relationships and manually fixing this is out
of the question because the whole database was "ingeniously" designed
(sarcastic) to fail if you break even one relationship to this table. So I am
up S* creek without a paddle here and running circles expected to fix this
mess and out of options. I am asking anyone who can take the time and knows
really well how to do this to help me please.

I have been using Access for YEARS and I would be considered somewhat of an
expert and I am stuck! I am hoping someone out there has a trick or more
experience than me to help me out. I am on a deadline too which sucks, so
anything soon would be greatly appreciated and will help your good karma for
years to come.
.

Relevant Pages

  • Re: do all primary keys use autonumber
    ... that you won't have any duplicate values, and an Autonumber field occupies only about 4 bytes per record, much shorter than many other fields you might choose to use. ... choice of first resort, because it satisfies the requirements for a primary key, is small, and is not bound to anything else in the model, making it practically immune to any need for revision). ... Say, a table with just two columns, a random integer column named ID ...
    (microsoft.public.access.tablesdbdesign)
  • Re: "Indexed or Primary Key cannot contain a Null value" / Autonum
    ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ... >> c) Is there any Default Value in the text box for the autonumber field>> on ...
    (microsoft.public.access.modulesdaovba)
  • Re: "Indexed or Primary Key cannot contain a Null value" / Autonum
    ... CaseAutonumber ... Allen Browne - Microsoft MVP. ... post the exact error message you are receiving. ... Is there any Default Value in the text box for the autonumber field ...
    (microsoft.public.access.modulesdaovba)
  • Re: DB Design and Relationship Questions
    ... created a 1:n from ClientID in tblClients to ClientID in tblClientPhones? ... > table serves as a foreign key in the child table, BUT the primary key of the ... > ClientID (primary key, Autonumber) ... > phone number for the lender - not the phone number of a specific person who ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Creating an Orders Form with multiple order types
    ... autonumber function, but I need sequential number for each type of order. ... the DMAX function. ... you can simply use an autonumber field and make the order number unique ...
    (microsoft.public.access.forms)