working w/ Autonumber in a temp table 2

[simon551]

Hi,
What I have noticed about Access tables is that when you delete a record the autonumber increment seems to live on. If you delete records 1, 2, and 3, and then add a new record, the new record ID is 4. Is that always the case?

I am using a temp table for appending from a client database to a server database. I use an autonumber field in the temp table to reference a main entry/detail entry relationship in the server database. Therefore: I append the primary key from the temp table_main to the server and then append the temp detail table to the table_details on the server referencing the temp table's primary key, and the server table_main primary key newly created.

Does that make sense?
My worry is that if the client table is messed up there will be errors in appending to older records.
Should I be concerned about this?

 

[traingamer]

Anytime you 'compact and repair' the 'next' autonumber will be reset to the lowest number available. Thus your method could potentially have substantial problems.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[simon551]

Thank you, sir. Back to the drawing board I guess.

 

[simon551]

how would you handle this? I'm trying to append to a main table and then to a detail table.

 

[MichaelRed]

w/o some additional details, it is difficult to understand completly ... if the SQLServer tables include the @@Identity (SQL Servers' version of AutoNUm?) then appending the data from your temp table will automatically create the identity (Key). Apprnd the first/parent table first, getting the identity of each record and append that value ti the associated records in the child recordset for all associated records. in SQL Server, simply identify the (primary recoredset's Identity field as the Foregin key of the table.


?????????????????????



MichaelRed

 

[simon551]

Sorry. I'm using an access db as a a back-end server.

 

[simon551]

MichaelRed,
The back-end server main_table does have an autonumber pk, however I don't know how to reference it when I go to append the details. This is why I created a reference in the back-end table to store the pk of the temp_main table.

thanks for your help,
-s

 

[SBerthold]

>If you delete records 1, 2, and 3, and then add a new record, the new record ID is 4.
>Is that always the case?

Actually, it should be. An Autonumber should be forever unique with-in the same table.
Then, the only way to re-number should be by dropping the Autonumber field and then re-creating it.

>Anytime you 'compact and repair' the 'next' autonumber will be reset to the lowest number available.

If you have the latest Jet 4 service pack, that shouldn't be the case, at least that is how I understood it to be.

 

[traingamer]

I stand corrected. On testing, the autonumber is indeed retained after a 'compact and repair'.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[simon551]

led me to an interesting exercise, anyway. I decided to create a unique identifier comprised of userid&Now(year,mo,day,minute,second)

Thanks for the insights!