Great thanks.

I agree with Carl as it seems there would be identical rows in the resultset which doesn't make any sense to me.

No sequence table is used. 

Will performing the select inside the transaction prevent the id from increasing while the batch writes occur? Will it lock and block itself?

Begin Transaction

 SELECT @MaxShipToCode = MAX(SomeID)

 FROM MyTable

Insert Into MyTable

Insert Into...

THanks

I forgot to say that the access code is being converted to T-SQL  so that it can run as a SQL job during off hours.

Currently there is user created code in an access db that gets the

Max(someid) and in VBA Code loops thru and adds one to the corresponding column in Access table,...

Remi yes I know everything you said is true. As I said this database had poor design by the vendor that created it, I can't change it. This is not for...