Importing and Autonumber Problem

[Michael]

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.

 

[Klatuu]

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]

Klatuu, I feel the same way about "paste" too and I am glad you caught that,
no that was not my idea but the genius who recieved the "converted" copy and
decided to just copy and paste them is the one who has pretty much rolled the
ball and now I am trying to clean it up.

As far as the conversion, I agree, I didn't mean straight to 2003 although I
have seen it done with very little complications but these guys want to STICK
with 2000 like for good. Anyway, their system, their dime but when I am stuck
trying to clean up a mess, you can imagine why it would make a man sore.

Yeah, I tried the append query but the problem with that was the neat
sequencing in the new table not accounting for the gaps in the autonumber
sequence from the original table and that's what I was actually trying to
figure out. If you have a way to setup an append query that WON'T take an
unclean sequence and make it a clean sequence, then by all mean please help
out with details, you will be on my prayer list.

Otherwise, I am thinking of trying to band-aid the problem by converting the
autonumber field in the "new" table to long integer regular number and then
using the import function to maintain the number sequence already done by the
original and then using some VBA to autoincrement in the future so that the
lazy users don't have to know and think all is well in the world and only
tech side knows we are going roundabout to get it done. any thougths and
inputs will be greatly appreciated.

thanks.

 

[Klatuu]

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.

 

[Michael]

yeah tell me about it. well I don't expect autonumbers to be sequential and I
try not to use them when they play such a critical role that can fall apart
with a gap. However, that's the way these people have set it up and
unfortunately the whole thing revolves around the freakin ClientID which is
autonumbered and every single other reference and tracking of the customer is
based on this number. so any data that is already recorded in other tables
using whatever the number was, will become orphaned and obsolete and
inaccessable or error loaded while the numbers don't match in the primary
table. the integrity was supposed to be enforced, however, for some reason it
is not doing it and as I said, they made the problem worse by the initial
attempt at "paste".

I am still trying to figure something out, if you come up with something
else, let me know please. thanks.