Can I force a autocount field in a copied database to be in sequen

[Tina Garcia]

I had to copy a mail log database. The users who have XP can not acces it
any longer (created in Win 98). They can access the new "copied" database
but they want the auto count field (which is also the primary key) to start
up where it left off. It won't do that. It goes from 1097 (the last record
in the original database) to 3296 (first new record in the "copied"
database). Is there any way to force the database to start counting the new
records where it left off? The original database was created with some kind
of lock out and the new employees (with XP) can't access it, so copying the
data into a new database was the only way to get rid of the locks and have
everyone able to access the mail log.

 

[Vincent Johns]

I was thinking of suggesting that you replace the Autonumber field with
one that you compute, but it would no longer be an Autonumber type.

Usually it's a good idea to keep Autonumber fields hidden and use them
only for linking, but in your case you can probably set up a new Table
by copying only the structure (no data) from the [mail log] Table.
Append 1097 blank records to it, then use an Update Query to copy fields
from the old [mail log] Table to the records in the new Table that have
matching values in the primary key field. You might have to disable
some "no duplicates" indices to allow you to do this, then turn them
back on when you've finished.

You should then have a Table with the kind of Autonumber field your
users want. But watch out -- if any records be (or have been) deleted,
you will not be able to re-use their Autonumber values. It may be that
not all of the 1097 numbers in the new Table have corresponding records
in the old Table, and you'll have to delete the ones that don't.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[peregenem]

You can specify the seed (starting) value for an autonumber (a.k.a.
IDENTITY) column:

http://support.microsoft.com/default.aspx?scid=kb;en-us;202117
http://support.microsoft.com/default.aspx?scid=kb;en-us;202121

You can also explicitly INSERT values into an autonumber column, so
this should take care of values already assigned.

 

[Tina Garcia]

Thanks, I can read this at lunch time. Some of what was done with this
database is easy to duplicate, but some of it I'm still learning the how and
the why it was done that way. The links you posted, does that tell me how to
INSERT values into a autonumber column? A lot of this is new territory for
me, I've been to training but it didn't even touch this stuff. Most of the
more advanced things I've learned have been by trial and error. Thank you
for the links, I've been blessed with creating new databases and maintaining
our current databases and I can use all the help I can get.
Thanks again,
Tina

 

[Tina Garcia]

Thank you very much, I wouldn't have thought of doing that. I'm keeping the
original database separate, that way in case I do "mess" up at least I have
the original to continue manipulating. Trust me, none of the new databases I
create will be set up the way this one was - He had a good idea but I guess
he didn't count on new people and new updated machines!
Again, Thank you
Tina

I was thinking of suggesting that you replace the Autonumber field with
one that you compute, but it would no longer be an Autonumber type.

Usually it's a good idea to keep Autonumber fields hidden and use them
only for linking, but in your case you can probably set up a new Table
by copying only the structure (no data) from the [mail log] Table.
Append 1097 blank records to it, then use an Update Query to copy fields
from the old [mail log] Table to the records in the new Table that have
matching values in the primary key field. You might have to disable
some "no duplicates" indices to allow you to do this, then turn them
back on when you've finished.

You should then have a Table with the kind of Autonumber field your
users want. But watch out -- if any records be (or have been) deleted,
you will not be able to re-use their Autonumber values. It may be that
not all of the 1097 numbers in the new Table have corresponding records
in the old Table, and you'll have to delete the ones that don't.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I had to copy a mail log database. The users who have XP can not acces it
any longer (created in Win 98). They can access the new "copied" database
but they want the auto count field (which is also the primary key) to start
up where it left off. It won't do that. It goes from 1097 (the last record
in the original database) to 3296 (first new record in the "copied"
database). Is there any way to force the database to start counting the new
records where it left off? The original database was created with some kind
of lock out and the new employees (with XP) can't access it, so copying the
data into a new database was the only way to get rid of the locks and have
everyone able to access the mail log.

 

[peregenem]

The links you posted, does that tell me how to
INSERT values into a autonumber column?

Well ... (thinks) ... you just INSERT the value!

CREATE TABLE Test (
key_col INTEGER IDENTITY(1,1) NOT NULL,
data_col INTEGER NOT NULL)
;
INSERT INTO Test (data_col)
VALUES (1)
;
INSERT INTO Test (key_col, data_col)
VALUES (10, 2)
;
INSERT INTO Test (data_col)
VALUES (3)
;
INSERT INTO Test (key_col, data_col)
VALUES (2147483647, 4)
;
INSERT INTO Test (data_col) VALUES (5)
;
SELECT key_col, data_col FROM Test
;

Notice how INSERTing key_col = 10 seeds the autonumber to 11 but
INSERTing key_col = 2147483647 (the maximum value for INTEGER) doesn't
affect the seed.

 

[Vincent Johns]

Thank you very much, I wouldn't have thought of doing that. I'm keeping the
original database separate, that way in case I do "mess" up at least I have
the original to continue manipulating.

I HOPE that not only did you copy the database file to a safe place
before you began fiddling with it, but also that you are making another
backup copy every day or so, so you won't have to re-create too much
stuff in case the version you're working on falls apart somehow. Best
is to put a copy onto a separate computer, just in case the hard disk on
your computer goes into early retirement!

Trust me, none of the new databases I
create will be set up the way this one was - He had a good idea but I guess
he didn't count on new people and new updated machines!
Again, Thank you
Tina

One happy feature of relational database systems like Access is their
flexibility -- rarely will you have to start over if you're using an
Access database. (I can think of examples, but they're kind of
pathological.) If you keep good backups, you can make progress by
copying data from old Tables to new ones, then deleting the old ones
when you're sure you have everything you need.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Tina Garcia]

Since I am a tad paranoid, I have a copy that I'm playing with, the original
database is based on the server and I have a copy of that on my hard drive
(I've tried to back it up about every 2-3 days, if there has been a lot of
mail then more often). I've learned the hard way about drives retiring way
before their time!
Tina

 

[Tina Garcia]

Thanks! I know that was a dumb question, I'm still learning and boy am I
learning way more than they taught in Access Class!
Tina

 

[Vincent Johns]

Since I am a tad paranoid, I have a copy that I'm playing with, the original
database is based on the server and I have a copy of that on my hard drive
(I've tried to back it up about every 2-3 days, if there has been a lot of
mail then more often). I've learned the hard way about drives retiring way
before their time!
Tina

I try to set the time between backups according to how long I think it
will take to reconstruct the changes, if I lose the file.

And I have another suggestion: In addition to using Tools --> Database
Utilities --> Compact and Repair Database to save space, you might also
compress the database files that you save as backups. Even a
"Compact"ed database file uses more space than it really needs.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.