Alter Table Auto Increment starting number criteria from another query or field

I have a database that stores all our distribution partner sales. Every week I get updated sales that I load into what I call tblImport. I then have some queries I run that parse out new sales lines to tblUpdate and tblNewAccounts. What I am struggling with is automating what I call the Bill To ID(BT_ID) field in the NewAccounts table. I know how to find the highest BT_ID from the master customer table. What I cannot figure out is how to get the tblNewAccounts to start auto numbering the BT_ID field with the max BT_ID +1 from the master customer table.

I figured out Alter Table and here is the code I used in an SQL Query that works but I need the value 128071 to actually be 1 plus the MAX BT_ID from the Master Customer Table.

ALTER TABLE [tblUpdate2_NewAcct]
ALTER COLUMN [BT_ID] AUTOINCREMENT(128071,1)

Here is something else I tried but I keep getting errors.

SELECT Max(tblALX_BillTo.BT_ID) AS MaxOfBT_ID
FROM tblALX_BillTo;
ALTER TABLE [tblUpdate2_NewAcct] ALTER COLUMN [BT_ID] AUTOINCREMENT([MaxOfBT_ID],1);

I have tried doing this in VBA as well and it hasn’t worked either. I am assuming it all has to do with that criteria field for the start of AutoIncrement.

Answer

With fairly easy VBA code, you can use DMax to grab the maximum current BT_ID value plus 1. Then write that value into your ALTER TABLE statement, and execute it.

Dim strDDL As String
Dim lngNewSeed as Long
lngNewSeed = DMax("BT_ID", "tblALX_BillTo") + 1
strDDL = "ALTER TABLE tblUpdate2_NewAcct" & vbCrLF & _
    "ALTER COLUMN BT_ID AUTOINCREMENT(" & lngNewSeed & ", 1)"
CurrentProject.Connection.Execute strDDL