Autonumbers and large data transfers

  • Thread starter Thread starter ged
  • Start date Start date
G

ged

I have a problem where i have multible tables that are needed to be
moved from one db to another with slightly differant fields. I have
coded it so that the destination can import the tables and then sort
through the imported tables and get the data into the destination
tables. Destination tables do have more fields then the source which
doing this way i could leave default and not worry about.

That was easy even assigning the primary autonumber keys that are used
in the other tables as a link the data is placed in perfectly...

Until in several tables you attempt to create a new record and you
find that the recordset.Update falls over saying that the table is
trying to create a entry with lue that exists in a unique field. The
friggin auto number being assigned is actually no where near the end
of the required level.

The strange thing is it isnt in every table just some.

My question is: "Is there any way to set what the next autonumber
value will be so that after my import i can just scan for the biggest
and set it to biggest plus one??????"

OR

Am I an Idiot and could i move the data in an easier fashion. (Reason
I have done it like this is because of the complexity of the
relationships and the very rare occasional need to do this)
 
"Is there any way to set what the next autonumber
value will be so that after my import i can just scan for the biggest
and set it to biggest plus one??????"
Click to expand...

No, not using an Access Autonumber value. An Autonumber has one and only one
function: to create a meaningless unique value. It's not guaranteed to be
sequential, and it's certainly not controllable!

You'll need to use a Long Integer field and increment it yourself, using VBA
code or a query.
 
From your description of the problem, I would guess that you're using
Access 2002 or before? If so, then resetting the Autonumber should be just
a matter of compacting the database.

For 2003 and above, the procedure is significantly more difficult. Try
googling "reset access autonumber" and you'll come up with lots of hits.


Rob
 
No, not using an Access Autonumber value. An Autonumber has one
and only one function: to create a meaningless unique value. It's
not guaranteed to be sequential, and it's certainly not
controllable!
Click to expand...

Er, what?

An Autonumber is a special default value for a Long Integer field,
nothing more, nothing less. You can assign a value to it in an
APPEND operation in SQL (as long as it doesn't conflict with the
index).

You can also manipulate the next value by setting the Autonumber
Seed value (this was added in Jet 4.0, and caused no end of problems
until it was finally fixed in Jet 4.0 Service Pack 6). I've never
found a need to manipulate the seed value myself, but the capability
is definitely there.
You'll need to use a Long Integer field and increment it yourself,
using VBA code or a query.
Click to expand...

Yes, this is correct if one wants full control over every value that
is appended. But if you are just wanting to set the Seed value some
of the time, it's not necessarily the best solution.
 
Back
Top