Remove and add ID key index from Access table in VBA

Question

I have an Access DB to automatically unify several CSV's in one table hourly (Aux_Table1) via macro (converted to VBA). Before unification, I run a query to clean Aux_Table1 table, but Access is coded to NOT reuse KEY IDs, so each time I clear and unify CSV's, keys IDs keep counting.

The only method I know to "reset" ID KEY numeration is to delete an create manually this primary key in table's design mode, but I want to run it all automatically (it's part of a huge batch process).

How can I make this process via VBA to INCLUDE in my (VBA) function? (see this 4Mb GIF demo).

Example of my function:

Function MacroUnify()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "00_Borrar_Aux_Tabla1", acViewNormal, acEdit
    >>> Here I need to _REMOVE_ PrimaryKey from Aux_Tabla1 <<<
    >>> Here I need to _CREATE_ PrimaryKey from Aux_Tabla1 <<<
    DoCmd.OpenQuery "10_Crea_Tabla definitva", acViewNormal, acEdit
    DoCmd.OutputTo acOutputTable, "zzz_resultado_Combinado", "Excel97-Excel2003Workbook(*.xls)", "D:\access\fullCatalog.xls", False, "", , acExportQualityPrint
End Function

I've tryed with:

 CurrentDb.Execute "ALTER TABLE Aux_Tabla1 DROP CONSTRAINT PrimaryIndex"
 DoCmd.RunSQL "CREATE INDEX PrimaryIndex ON Aux_Tabla1(ID) WITH PRIMARY"

The function runs without problem, but the key index is still counting without reset.

PS: The index name is PrimaryIndex and the field name is ID

Remove and add ID key index from Access table in VBA

2 Answers