【Access】.accdbのデータを更新クエリーで修正するAccessマクロの作り方

2022 8/09

2021年6月7日 2022年8月9日

Accessの更新クエリーを使ってデータベースのデータを修正する

元データExcelの原表がミスっていて誤データが既にテーブルに存在する

まずは、原表のExcelVBAを修正し、次からのデータを正常にしておく！

競馬データの騎手名欄に減量騎手の場合、名前の前に減量記号が付くことがあるのだがマークが追加変更されたタイミングでVBAの修正をしていない場合、そのまま騎手名としてデータ登録されてしまうので、これを修正する一連の作業をココに記録しておく。

馬やレースに付く記号

ExcelVBAの修正前サンプル

'''減量12
                    wk_nm1 = Sheets(sh_nm).Cells(dtLC, 4)
                    If wk_nm1 = "勝負服の画像" Then
                        wk_nm1 = Sheets(sh_nm).Cells(dtLC + 3, 4)
                    Else
                        wk_nm1 = Sheets(sh_nm).Cells(dtLC + 2, 4)
                    End If

                    If Left(wk_nm1, 1) = "▲" Or Left(wk_nm1, 1) = "△" Or Left(wk_nm1, 1) = "☆" Then
                        Sheets("出走D").Cells(LC + 1, 12) = Left(wk_nm1, 1)
                        Sheets("出走D").Cells(LC + 1, 13) = Right(wk_nm1, Len(wk_nm1) - 1)
                    Else
                        Sheets("出走D").Cells(LC + 1, 12) = ""
'''騎手13
                        Sheets("出走D").Cells(LC + 1, 13) = wk_nm1
                    End If

If Left(wk_nm1, 1) = “▲” Or Left(wk_nm1, 1) = “△” Or Left(wk_nm1, 1) = “☆” Then

これに「★」と「◇」を追加すれば良い！

ExcelVBAの修正後サンプル

                    If Left(wk_nm1, 1) = "▲" Or _
                       Left(wk_nm1, 1) = "△" Or _
                       Left(wk_nm1, 1) = "☆" Or _
                       Left(wk_nm1, 1) = "★" Or _
                       Left(wk_nm1, 1) = "◇" Then
                        Sheets("出走D").Cells(LC + 1, 12) = Left(wk_nm1, 1)
                        Sheets("出走D").Cells(LC + 1, 13) = Right(wk_nm1, Len(wk_nm1) - 1)
                    Else
                        Sheets("出走D").Cells(LC + 1, 12) = ""
'''騎手13
                        Sheets("出走D").Cells(LC + 1, 13) = wk_nm1
                    End If

これで次回からの元データはうまくデータベースに乗ってくるだろう～(^^♪

Accessの更新クエリーを使って.accdbとSQL-Serverのデータを修正する

今回のデータ更新用にサブシステムとして新規でAccessの.accdbを１つ作成しAccessとSQLのデータをアタッチしてデータを修正する更新クエリーを作成し、ジョブマクロとして登録し再利用可能とする。

SQL-Server更新用サブシステムを作成する（今回は既に作成してある）

内容は、AccessもSQL-Serverも同じ間違いなので、ここではAccessのDBにアタッチした状態のファイルを簡単に説明した上でデータ修正をする更新クエリーを作成するまでを簡単に記載する。

AccessDBにアタッチした状態の外部データの表示状態

➡付きが、外部ファイルをアタッチしているファイル。

➡田：Accessデータ

➡🌍：SQL-Serverデータ

※今回修正する対象は、①dbo_MS_RD,②dbo_MS_DT,③MS_RD,④MS_DT の４つ。

③MS_RD：出馬表データ　　（①dbo_MS_RD：出馬表データの退避用SQL-Server）

④MS_DT：結果データ　　　（②dbo_MS_DT：結果データの退避用SQL-Server）

※Accessの元データがある.accdbに直接クエリーを作成してしまうと管理が大変になるので、目的ごとにサブシステムとして１つ.accdbを作成するのがAccessDBの管理しやすい使い方になる。

今回の修正サブシステムはSQL-SV-rep用として既に作成してあるDBに更新クエリーとジョブマクロを追加する方法でデータ更新及びサブシステム管理を完成させる。

更新前の誤データを確認する

誤データは1文字目に記号が入っているので、フィルターで抽出してみると・・・

③出馬表で796件あるようだ。①SQL-Serverの方も796件ありデータ件数は一致している。

③MS_RD：減量マークを修正する更新クエリーを作成

Accessメニューバーの「作成」→「クエリーデザイン」→「MS_DT」を選択・・・

→「更新」を選択

選択クエリーで今回更新するデータを確認してみる

「減量」を選択し、ダブルクリック（もしくは下のフィールド欄までドラッグ）
同様に「騎手」を選択し、ダブルクリックする。

※「減量」をクリックしShift＋「騎手」をクリックし、下のフィールド欄までドラッグしても同様の結果を得られる。

テスト表示として、上記メニューバーを「更新」から「選択」に変更し
騎手の抽出条件にマークを指定すると該当するデータが抽出されるので
それが今回更新するデータ群となる。

上記Accessでの表示をＳＱＬにすると下のようになる。
AccessではＳＱＬを知らなくても、ＳＱＬ文を簡単に作成できるのがとても便利なのだ。

SELECT MS_RD.減量, MS_RD.騎手
FROM MS_RD
WHERE (((MS_RD.騎手) Like "★*" Or 
(MS_RD.騎手) Like "◇*" Or 
(MS_RD.騎手) Like "☆*" Or 
(MS_RD.騎手) Like "▲*" Or 
(MS_RD.騎手) Like "△*"));

もしくは、、、

SELECT MS_RD.減量, Left([騎手],1) AS MK
FROM MS_RD
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

クエリー１のタブを右クリックし、ＳＱＬビューを選択すると同じものが表示されるので、一度試してもらいたい。

少し脱線してしまった。。。同じ要領で③MS_RDの減量マーク更新クエリーを作成すると・・・

Rmk01：減量マーク作成更新クエリーの作成完成画像

UPDATE MS_RD SET MS_RD.減量 = Left([騎手],1)
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

Rmk01：③MS_RD.減量マークを更新
Rmk02：④MS_DT.減量
Rmk11：①dbo_MS_RD.減量
Rmk12：②dbo_MS_DT.減量

※Rmk01をコピーして４つ作るのだが、この場合ＳＱＬ文を直接触った方が修正しやすい！！この辺はたくさん作っていけば理解できるようになる。

※更新・追加・削除クエリーを走らす場合、必ず先にデータベースのバックアップを取ってから実行するように心がける。間違ったデータ更新をしてしまうと元に戻せなくなってしまうので注意！！！

これで減量マークは完成～～　次は騎手名　

③MS_RD：騎手名を修正する更新クエリーを作成

同様に今度は騎手名を更新していくクエリーを４つ作成していく。上記クエリーに纏めてしまえば４つの更新クエリーで出来てしまうが、データ修正パッチモジュールは多少時間が掛かっても確実に１つずつ仕留めていく方法をとった方がベターである。※失敗しても復旧しやすい形をとる。

Rmk03：③MS_RD.騎手を更新
Rmk04：④MS_DT.騎手
Rmk13：①dbo_MS_RD.騎手
Rmk14：②dbo_MS_DT.騎手

UPDATE MS_RD SET MS_RD.騎手 = Right([騎手],Len([騎手])-1)
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

更新クエリー名とデータベース名をＳＱＬ文を直接変更するだけで作成できるが、これをAccess更新クエリーから作るとデータベース選択からクリックして作ることになるので、１から作ることになるので面倒。。。

UPDATE MS_DT SET MS_DT.騎手 = Right([騎手],Len([騎手])-1)
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

UPDATE dbo_MS_RD SET dbo_MS_RD.騎手 = Right([騎手],Len([騎手])-1)
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

UPDATE dbo_MS_DT SET dbo_MS_DT.騎手 = Right([騎手],Len([騎手])-1)
WHERE (((Left([騎手],1))="★" Or 
(Left([騎手],1))="◇" Or 
(Left([騎手],1))="☆" Or 
(Left([騎手],1))="▲" Or 
(Left([騎手],1))="△"));

データベースのバックアップを取っているのを確認し
Rmk01から順番に更新クエリーを実行しデータ内容を確認してみる。

今回の出来上がりサンプル表示としてMS_RDを見てみよう～～(^^♪

上記更新クエリー実行後、MS_RD.減量および騎手を表示してみる

MS_RD.騎手

ポップアップメニューから

◇藤田菜七子
★永島まなみ
★古川奈穂

名前が上位に表示されていないので、うまくいっているポイ！

ここまで出来たらこの完成した更新クエリーをジョブマクロとして登録しておけば
次に忘れた頃このAccessDBを見た時、この更新マクロ群は何をやっているのかが分かりやすくなる。
履歴として保存管理するのは次に同じようなデータ更新をする場合に流用できて便利だし
開発時間も大幅に短縮されることがある。

今回は出来上がりのジョブマクロの画像を貼り付けておくので、参考にして頂ければ幸いだ。

ジョブマクロに作成した更新クエリーを登録しておく

実行すると下記のメッセージボックスが表示される

「ＯＫ」と「キャンセルボタン」で処理を分岐。キャンセルだと何も実行せずマクロを終了する。

修正パッチは大概同じ方法で作成可能なので、サンプルを１つ作っておくと便利になる。

編集後記

他人様へのプログラムを納品する場合は、Accessでのフォームやレポートが必要になるが、個人使用となると、データエントリーはExcel！データ出力もExcel！という私同様の方は多いと思う。

そうなると、Accessの立ち位置としてはデータベース一択になるので、利用方法は

テーブル
クエリー
マクロ
VBA

この４つになるが、上手くExcelが機能していると、AccessVBAは個人ではほぼ使うことがない。Excelシートのデータをインポートして、抽出結果をエクスポートする一連の作業は、VBAでなくマクロを使えば簡単に作成できる。

AccessマクロはAccessVBAとは違うモノ

ExcelVBAとExcelマクロは同じモノを指すが、Accessマクロ≠VBAではない！Excelも厳密に言えばExcelマクロは存在するが、今は使用されていない。

Accessマクロを使用する最大のメリットは

Accessの機能を使うので、単体モジュールテストは必要ない
Accessマクロにない条件分岐処理だけVBAで部品化（関数）
ジョブストリームライブラリとして利用すると汎用的に使うことが出来る
本でAccessマクロ特化本はないので、VBA利用となることが多くデータベース利用とは違うプログラミング作成が壁となり、本来の目的と違う「軸がズレた」解説が多い
- この弊害は2000年前後にAcces構築のシステムが安価で外注されたためVBA主流となった
個人利用するならば、WindowsのデータベースはAccessで作成するのが簡単で最適だ！
.accdbでパンクしそうになったら、SQL-Server(Express)を利用すれば良い
- SQL-Serverを使うツールとしてAccessでアタッチすればそのまま利用可能！！

ただ2022.5.17時点では、Accessを個人利用している人はあまり多くはないようで、ブログやYoutubeから利用方法を探すのは、難しい。書籍もない！ブログもない！！となると、自分で使ってみるしか方法はない！のだが、そのガイドとしてAccessVBAを利用するといった使い方をするのが、一番コスパに優れている。

AccessVBA逆引き大全 600の極意

例文がたくさん載っているVBA本を使い回すうちに、Accessマクロの一つ一つの命令が理解できるようになる。わざわざVBAで書く必要性がなく、

データ更新は更新クエリーもしくはSQL文そのもの
更新クエリーを連続して実行するジョブストリームライブラリ(JCL)としての利用

WindowsシステムでGUIで簡単にデータベースが使えるので、入口としてはかなり優秀であり、クラウドではないのでGoogleと棲み分けが出来て面白さは格段に上がる。と私は思っている。

それでは、またね～😎

この記事を書いた人

あどのまつり

マクロで半自動化！GO!GO!!PROGRAMMING
管理人：あどのまつり
馬齢：牡５０代（後）✖１

2023年はコロナ明けでタイ旅行にいきThai!!

見習騎手の減量記号
区分	印	勝利度数	減ずる重量
男性騎手	▲	30回以下	3キログラム
	△	31回以上50回以下	2キログラム
	☆	51回以上100回以下	1キログラム
女性騎手	★	50回以下	4キログラム
	▲	51回以上100回以下	3キログラム

見習騎手以外の騎手の減量記号
区分	印	勝利度数	減ずる重量
男性騎手	なし	なし
女性騎手	◇	2キログラム

月	火	水	木	金	土	日
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30