96本目 Accessデータを取得（マスタ結合&抽出）

VBA100本ノック

VBA100本ノック 96本目を動画で解説しています。Excelの神髄さんの模範解答はコチラ

【VBA】Accessからデータを取得(SQLの基礎)～VBA100本ノック_96～
#VBA100本ノック 96本目DB1.accdbから、以下の出力項目と抽出条件でデータを取得し、シートに出力する。■出力項目取引先CD,取引先名,商品CD,商品名,単価,数量,金額金額は単価*数量■抽出条件2021年以降(2021/01/01～)金額が100万以上※テーブルは画像とサンプルにて※シートは任意Op...
www.youtube.com

問題

#VBA100本ノック 96本目
DB1.accdbから以下の出力項目と抽出条件でデータを取得しシートに出力する。
■出力項目
取引先CD,取引先名,商品CD,商品名,単価,数量,金額
金額は単価*数量
■抽出条件
2021年以降(2021/01/01～)
金額が100万以上
※テーブルは画像とサンプルにて
※シートは任意

解答

Sub ノック96本目()
    
    Dim adoCN As New ADODB.Connection '接続本体
    Dim strDb As String
    strDb = ThisWorkbook.Path & "\DB1.accdb" '絶対パスで指定
    With adoCN
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDb 'Access
        .Open
    End With
    
    Dim ws As Worksheet: Set ws = Sheets("出力"): ws.Cells.Clear
    Dim adoRS As ADODB.Recordset
    Dim strSQL As String
    strSQL = _
    "SELECT T売上.取引先CD,M取引先.取引先名,T売上.商品CD,M商品.商品名,T売上.日付,T売上.単価,T売上.数量,T売上.数量*T売上.単価" & _
    " FROM ((T売上 LEFT JOIN M取引先 ON T売上.取引先CD=M取引先.取引先CD)" & _
                             " LEFT JOIN M商品 ON T売上.商品CD=M商品.商品CD)" & _
    " WHERE T売上.日付>=#2021/01/01# AND T売上.数量*T売上.単価>=100000"
    
    Set adoRS = adoCN.Execute(strSQL)
    Dim i As Long
    For i = 0 To adoRS.Fields.Count - 1
        Cells(1, i + 1) = adoRS.Fields(i).Name
    Next i
    ws.Range("A2").CopyFromRecordset adoRS
    
End Sub

■考え方・流れ
0:00 冒頭・問題確認
2:58 ADO(ActiveX Data Objects)を参照設定
4:42 接続先をDB1.accdbに設定
5:52 Recordsetを設定する
7:42 Recordsetの中身をシートへ出力する
9:22 SQLの基本① SelectとFrom
12:08 SQLの基本➁ Where
14:08 SQLの基本③ Left Join
16:32 フィールド名を出力する

SQLについて解説入れてるので超長いです…
頑張って見てください…w

新人君
新人君

SQLは慣れるまでは

難しいんですよね！

ブチョ
ブチョ

俺は生まれた時から
SQLだったぞ！

新人君
新人君

SQLだった…？

ADO(ActiveX Data Objects)を参照設定

まずは参照設定からだ！！
Accessってのは別アプリだからな！
Microsoft ActiveX Data Objects ○○ Libraryにチェックだ！

そしてAccessも！！！
パワポやワードの時と同様！
アプリ本体の変数と、レコードの変数の、2つを準備しよう！！

ちなみに…ADODB.Connectionってなに？？
Accessのこと…？って声が聞こえるが…！
実はAccess以外もつなげられるんだぞ！！

接続先をDB1.accdbに設定

続いては、接続先を特定のアクセスに設定するぞ！！！
これは神髄さんのサイトに使い方が詳しく載ってるぞ！

そのまんまコピったら、！adoCNのところが神髄さん用になってるから、
ここだけ変更してくれ！

あ、あと、接続先によってはPrioviderを変えないといけないみたいだね！！

Recordsetを設定する

お次は！Recordsetの設定だ！！！
要するに、「Accessから、どんなデータ(レコード)を取り出したいんですか？」書けばよいってこと！

ど、どんなデータ？どんなデータって言われても……
そんな時は！SQLって言う、データ抽出に特化したプログラミング言語的な言葉でで命令を出してあげればいいんですわ！！

ほんで、Recordsetって変数の中に…
connectionのExecuteメソッドの返り値を入れてあげて…

え？皆さんSQLをご存じでない？？？？
。。。
ま、安心して下され！！
この私が超々超ざっくりと教えてあげよう！

あ、ちなみに次の章からね！
一旦はテキトーな文字列だけ入れてお試し！

ちなみに、Recordsetからどうやってデータを取り出すねん！って話につきましては、
Range.CopyFromRecordsetってメソッドがあるのでそれでいけちゃうww
なんて簡単なのww

SQLの基本① SelectとFrom

さあさあ！ここからがSQL講座の始まりじゃいい！！！
とは言え…
なんていうかなぁ…
SQLって…
「とりあえずこれだけ覚えとけ！」ってのが何個かあるのよ…。
だからそんなに難しくはないというか…
ま、とにかく！！ここから(Select From)すべてが始まる！！！

あ、ちなみに…
SQLって、細かいんすよ。おもてる以上に。
スペースが半角じゃないと怒られたり、スペース入れてないと機能しなかったり…
色々あるんスわ…

例えばこんな風に書いた場合↓
「T売上ってテーブルから、全ての列、全てのデータを抽出しますよー」
って言ってるのと同じ！！

Selrctの後ろは、「どの列を抽出しますか？」ってこと！

実行結果↓

Select From!!Select From!!Select From!!
大事なことだから3回言ったぞ！

SQLの基本➁ Where

よし！続いては！！
Where！！！
え？どこ？？惜しい！
絞りたい条件が何か？ってことを指定してあげる句だ！！

例えば、”商品CD”の列が、”S00002″だけで絞りたかったらこんな感じだ！↓

実行結果↓
ちゃんと絞れておる…たったこれだけで…

＝だけじゃなく、<>など、記号も使えるぞ！

ただし！SQLは条件が複雑になればなるほど…
めちゃくちゃコードが長くなっちゃう！！！
VBEで書くときは、”_”を使って改行するか、
セルに直接入力するほうが良いかもしれないな！

SQLの基本③ Left Join

続いて三つ目！
Left Joinって何かというと…

VLOOKUPみたいな感じだ！！！！www
別々のテーブルから、キーとなる項目を紐づけて、データを抽出することもできちゃうのサ！！

使い方はこんな感じだ！！
テーブル名 LEFT JOIN テーブル名 ON 条件式

とまあ、これがSQLの基礎の基礎の基礎の基礎だ！！！！
動画1階ではさすがに伝えきれない…すまん…。
最終的に書き終わったコードがこれだから、是非見て勉強してみてくれ！↓

フィールド名を出力する

最後！列名だけ取得したら終わりだ！！

ちなみにこれも…さっきのサイトに載ってるから安心してくれ！！

※ちなみにさっきのSQLでデータをコピーする先は、A2セルに変更したぞ！！
1行目を空けるためにね！

あとは神髄さんのサイトのコードをほぼコピペして…終了！！

まさか…ここまでとはな…！

ブチョ
ブチョ

お疲れさマグロ！

新人君
新人君

お疲れさまでした！

あとがたり

おはこんばんちは。uぷ主です。
96本目！！Accessからデータを取得する問題でした！

え？Accessからデータ？？
ここにきて？？？w
うそやろー？

SQLなんて…どんだけ頑張って覚えたと思てんの…w

ま、まあええやろ…ｗｗ最後やしやっとこ…
97本目もAccessの問題だったし…やるしかないし…

と思ったけど…、さすがに全部が全部は説明しきれませんでしたね…

それでも、ある程度は説明頑張ったんじゃないかな？(自己満)
まあまあ！新年だし！！
2023年は自己肯定感高くいきたいな～と思てるので、これでいいでしょう。

ま！！と、いうわけで！！
みなさん明けましておめでとうございます！！！
今年もよろしくお願いします！！

良い一年を！！

コメント

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