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

目次

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

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

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

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

馬やレースに付く記号

見習騎手の減量記号
区分 勝利度数 減ずる重量
男性騎手 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

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

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

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

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

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

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

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

➡田:Accessデータ

➡🌍:SQL-Serverデータ

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

③MS_RD:出馬表データ  (①dbo_MS_RD:出馬表データの退避用SQL-Server)

④MS_DT:結果データ   (②dbo_MS_DT:結果データの退避用SQL-Server)

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

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

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

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

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

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

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

→「更新」を選択

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

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

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

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

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

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))="△"));

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

少し脱線してしまった。。。同じ要領で③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をコピーして4つ作るのだが、この場合SQL文を直接触った方が修正しやすい!!この辺はたくさん作っていけば理解できるようになる。

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

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

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

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

  • 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))="△"));

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

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を見た時、この更新マクロ群は何をやっているのかが分かりやすくなる。
履歴として保存管理するのは次に同じようなデータ更新をする場合に流用できて便利だし
開発時間も大幅に短縮されることがある。

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

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

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

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

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

編集後記

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

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

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

この4つになるが、上手く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を利用するといった使い方をするのが、一番コスパに優れている。

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

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

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

それでは、またね~😎

この記事を書いた人

マクロで半自動化!GO!GO!!PROGRAMMING
管理人:あどのまつり
馬齢:牡50代(後)✖1

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

目次