【Access】特定フィールドを並び替えて、オートナンバーを採番し直す方法

欠番したオートナンバーを振り直す

マイクロソフトアクセス（以下Accessに略）ですが、オートナンバーというキーになるフィールドが存在するが、レコードを削除したりすると、その番号が欠番してしまい気分的に良くありません。

そこで、オートナンバーの振り直しという事になりますが、これが簡単には行きません。

VBAを使えば複雑な事も一瞬で終わりますので、ネットからVBAを拾ってきて実行してみました。

Sub AutoNumberReset()
　Const TargetTable = "Table1" 'リセットしたいテーブル
　Const TargetAutoID = "ID" 'テーブルのオートナンバーのフィールド名
　Const TargetFields = "Data1, Data2" 'オートナンバー以外のフィールド名
　
　Const TempTable1 = "temp1" '作業用テーブル1
　Const TempTable2 = "temp2" '作業用テーブル2
　　
　'確認メッセージなし
　DoCmd.SetWarnings False
　
　'ターゲットを作業用テーブルにコピーし削除
　DoCmd.CopyObject , TempTable1, acTable, TargetTable
　DoCmd.CopyObject , TempTable2, acTable, TargetTable
　DoCmd.DeleteObject acTable, TargetTable
　
　'作業用テーブル2のレコード削除する
　DoCmd.RunSQL "delete * from " & TempTable2
　
　'作業用テーブル2をターゲットにコピー（この時点でリセットされる）
　DoCmd.CopyObject , TargetTable, acTable, TempTable2
　
　'作業用テーブル1からレコードの内容をターゲットに戻す（オートナンバー以外のデータ）
　DoCmd.RunSQL "insert into " & TargetTable & " select " & TargetFields & " from " & TempTable1 & " order by " & TargetAutoID
　
　'作業用テーブルの削除
　DoCmd.DeleteObject acTable, TempTable1
　DoCmd.DeleteObject acTable, TempTable2
　
　'確認メッセージあり
　DoCmd.SetWarnings True
　
End Sub

このVBAを実行するには、オートナンバーをリセットしたいテーブルが開いてない状態で実行しないとエラーとなります。

自分は、別フォームにボタンを設置して実行しました。

これで、オートナンバーの欠番がなくなり、振り直しができました。

別フィールドの並び替えでオートナンバー振り直す

欠番したオートナンバーの振り直しができましたが、できれば、

という事もある。

'作業用テーブル1からレコードの内容をターゲットに戻す（オートナンバー以外のデータ）
DoCmd.RunSQL "insert into " & TargetTable & " (" & TargetFields & ") select " & TargetFields & " from " & TempTable1 & " order by user_id ASC"

この部分を修正するだけで、user_idの並び替えを行い、更にオートナンバーの欠番なく振り直しができるようになりました。