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

2021年6月7日2024年1月24日

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

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

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

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

減量騎手に付く記号

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

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

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

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

Excel VBA 逆引き大全 600の極意 Microsoft 365/Office 2021/2019/2016/2013対応

Amazon

楽　天

Excel VBA 逆引き大全 600の極意 Microsoft 365

※私が持っているのが2016年版（以下は2016）

ExcelVBAの基本構文
セルの操作
関数
ワークシート、ウィンドウの操作
ブック操作
データ操作
テーブル・ピボットテーブル
図形
グラフ
ユーザーフォーム
印刷
ファイルとフォルダ
データ連携
イベント
バージョン・トラブルシューティング・エラー処理
高度なテクニック
索引

目次が巻頭で検索が巻末。昔のタイプの本の構成だが、索引がページでなくTipsという見出しNo.になっていて慣れるまでは検索しづらい。
Amazonレビューは★3.9

私のKindle-Unlimited評価は★4

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 マクロ入門 ~仕事の現場で即使える

Amazon

楽　天

Access マクロ入門 ~仕事の現場で即使える

マクロの基本と本書のサンプルについて
マクロツールを使ってみよう
フォームを作成しよう
高機能なフォームからクエリやレポートを操作
アプリケーションを仕上げて使いやすくしよう
Excelとデータをやりとりしよう
マクロをもっと使いこなそう
アクションカタログリファレンス
索引

AccessVBAではなく「Accessマクロの専門書」としてはコレしか見たことがない！
個人でデータ加工するなら、Excelの弱点である大量データの更新や一括更新は、Accessで操作した方が正確で素早く加工できるが、皆様あまり使わない。GUIでSQL発行をするので、Accessクエリを勉強するとSQL文も同時に習得できるといった特典付きでも誰もやらないのは、私からすると未だに謎だよｗ
Amazonレビューは★4.2

私のKindle-Unlimited評価は★4

編集後記

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

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

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

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

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

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

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

Accessの機能を使うので、単体モジュールテストが簡単
Accessマクロにない条件分岐処理だけAccessVBAで部品化（関数）
ジョブストリームライブラリ（JCL）として利用すると汎用的に使うことが出来る
本でAccessマクロ特化本はほとんどないので、AccessVBAの解説本を購入するハメになるが、順番が違うように私には思ってしまう。
個人使用のWindowsのデータベースはAccessで管理するのが簡単
.accdbでパンクしそうになったら、SQL-Server(Express)を利用
- SQL-Serverを使うツールとしてAccessでアタッチすればそのまま利用可能！！

ただ2022.5.17時点では、Accessを個人利用している人はあまり多くはないようで、ブログやYoutubeから利用方法を探すのは難しい。書籍もない！ブログもない！！となると、自分で使ってみるしか方法はない！

Accessマクロのデメリット

解説本がほとんどなく自主学習が必要。この一点！

例文がたくさん載っているAccessVBA本を使い回すうちに、Accessマクロの一つ一つの命令は理解できるようになるが、Accessをデータベースとして使い回せるようになるころには、Accessマクロを使うよりもAccessVBAの個人資産が既に出来上がっているといったことが起きるのが現実だろう。

ただ私のようなおじさん？だと、先にデータベースやJCLといったことを知っているので、同じような年代の人は
わざわざAccessVBAから始める必要はなくAccessマクロから入った方が良いと思われる。

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

SQL文をGUIで簡単にデータベースが使えるので、データベース入門としてはかなり優秀だがAccessは今のところ

クラウドでは使えない!

それでは、またね～😎

2024年4月
月	火	水	木	金	土	日
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