パソコン情報

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

ココに注意

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を実行するには、オートナンバーをリセットしたいテーブルが開いてない状態で実行しないとエラーとなります。

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

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

 

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

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

ココがポイント

user_idを昇順で並び替えた後に、オートナンバーを振り直したい

という事もある。

 

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

 

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