Maintain Autonumber when importing data

[Guest]

I have an application which we are making general database schema changes to.

This will require data to be imported from older versions of the DB, with
some of the fields being autonumber. How do I insert data from another
database, maintaining the autonumber fields, and at the same time, maintain
the ability to have that DB field continue autonumbering.

Access complains both about inserting pre-existing data into an autonumber,
or converting an integer field to an autonumber.

Essentially, is there a workaround, hack, fix?

-David

 

[Guest]

Hi, David.

One may append AutoNumbered records into a table of the same structure, but
the AutoNumbers being imported cannot duplicate the numbers already in the
table. Remember that an AutoNumber is a Long data type, not an Integer data
type, so the Integer field needs to be converted to the Long data type before
the append.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[peregenem]

Remember that an AutoNumber is a Long data type, not an Integer data
type, so the Integer field needs to be converted to the Long data type before
the append.

Wouldn't all values be implicitly cast? For example (to save confusion,
I'll use Jet 4.0 synonyms):

CREATE TABLE Test1 (
key_col INTEGER2 NOT NULL,
data_col NVARCHAR(50) NOT NULL
)
;
INSERT INTO Test1 (key_col, data_col) VALUES (1, 'One')
;
INSERT INTO Test1 (key_col, data_col) VALUES (2, 'Two')
;
INSERT INTO Test1 (key_col, data_col) VALUES (123456, 'Illegal')
;

That last line fails with an overflow, of course.

CREATE TABLE Test2 (
key_col INTEGER4 IDENTITY(1,1) NOT NULL,
data_col NVARCHAR(50) NOT NULL
)
;
INSERT INTO Test2 (key_col, data_col) VALUES (123456, 'Legal')
;
INSERT INTO Test2 (key_col, data_col)
SELECT key_col, data_col FROM Test1
;

All rows are successfully inserted, therefore I assume there is no
problem with implicit casting from INTEGER2 to INTEGER4. Or have I
missed the point?

Also, remember that an AutoNumber can be a GUID a.k.a. replication ID
;-)

 

[Guest]

I guess I should specify what I'm doing:

Database Old has 4 records autonumbered, 1,2,3,5
Database New is Clean

Desired outcome, import old records into new DB, maintaining their autonumbers

Scenario 1, I import the data, Access autonumbers them 1,2,3,4 on import
(fail)

Scenario 2, Set number column to Long Integer (had been doing this I
improperly said integer), import the data, records are numbered 1,2,3,5
When I try to change the type of the column to autonumber, Access complains
that
"Once you enter data in a table, you can't change the data type of any field
to AutoNumber, even if you haven't yet added data to that field. Add a new
field to the table, and define its data type as AutoNumber, MSO Access then
enteres data in the AutoNumber field automatically, numbering the records
consecutively starting with 1. [OK]"
(fail)

So, how to import the data, such that when I create my next record its 6.
Or for that matter so it autonumbers.

Thanks,

-David

 

['69 Camaro]

Remember that an AutoNumber is a Long data type, not an Integer data

Wouldn't all values be implicitly cast?

I was addressing David's complaint:

"Access complains . . . about . . . converting an integer field to an
autonumber."

I have no idea what method David is using to convert the Integers into the
AutoNumbers to bring about this problem, but if they are Longs, there
shouldn't be a problem.

Also, remember that an AutoNumber can be a GUID a.k.a. replication ID

Run from anyone selling this snake oil. ;-)

Please see MichKa's (former MVP) article, "Replication and GUIDs, the Good,
the Bad, and the Ugly," on the following Web page:

http://www.trigeminal.com/usenet/usenet011.asp?1033

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[Guest]

I have been perhaps (lazily, and sheepishly admit) using copy from one table
and paste into another table. I'll give it a test with SQL and see if this
solves my problem. I guess I mistakenly assumed that Access would handle
this. I'll report on my findings.

Also,
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb

Seems to propose an agreeable solution if SQL itself doesn't work.

-David