【Access】accdbとSQL-ServerのデータをAccessの更新クエリーで修正する

Access
スポンサーリンク

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

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

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

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

馬やレースに付く記号

馬やレースに付く記号:競馬のルール JRA
見習騎手の減量記号
区分勝利度数減ずる重量
男性騎手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と棲み分けが出来て面白さは格段に上がる。と私は思っている。

それでは、またね~😎

タイトルとURLをコピーしました