And you have to live with the identity PK in access? You cannot switch to a GUID PK or add a GUID as an alternate KEY, so that you can produce a guid upfront?
In an accounting system that I have we maintain the next batch and transaction numbers for a fiscal year in a control table and get the next ID using this logic in a SPROC. I just checked on of my client sites and one of these tables has 374,000 rows and never got a problem using logic like the below.
Nice thing about this logic is no transaction/lock is required - it's multi-user SQL safe from the get go.
Code:
RedoB:
Set @DataValue=(select DataValue from Control_T where FiscalYr=@FiscalYr and Item='LAST BATCH')
Set @NewDataValue=Right('000000'+Cast(Cast(@DataValue as int)+1 as VarChar(6)),6)
Update Control_T Set DataValue=@NewDataValue,Tdate=@Tdate
Where FiscalYr=@FiscalYr and Item='LAST BATCH' and DataValue=@DataValue
If @@RowCount<>1 Goto RedoB