【続】Access VBAにて、INSERT文を使ってAテーブルのデータを、Bテーブルに書き込みたい
今、2つのテーブルがあります。
Aテーブル「worksheet_売上一覧」には、以下のような情報が入っております。
https://f.hatena.ne.jp/moon-fondu/20201030164656
業種ID、業種名、支店ID、支店名、社員ID、社員名・・・等の項目です。
そしてデータは全て0か、何も入っていない状態です。
Bテーブル「T_売上管理」には、似たような項目があります。
https://f.hatena.ne.jp/moon-fondu/20201030165452
業種コード、業種名称、支店コード、支店名称、社員コード、年度・・・等の項目です。
そしてこちらは、データも入っております。
この状況におきまして。
「F_注文履歴」フォームに、（特にデータ的な紐づけは無いのですが）「売上実績」というボタンを設けるとします。
そしてこのボタンを押した際、Bテーブル「T_売上管理」データのうちの上記6項目の列、全ての情報を。
Aテーブル「worksheet_売上一覧」の上記6項目に、書き込みたいのです。（Aテーブルの列名は据え置きのままです）
前回、類似の質問させていただきました。https://q.hatena.ne.jp/1604053612
何点かアドバイスをいただき、再考してみました。
ボタンをクリックしてもKeyDownイベントは起きない、というコメントをいただきました。
T'sWare Access Tips #633 ～テキストボックスの指定キー入力でアクションを起こすには？～
https://tsware.jp/tips/tips_633.htm
を読むと、どうやらKeydownイベントの引数にIf文で条件を入れているみたいで。
自分には入っていなかったので、「If keycode = vbKeyReturn then」と、入れてみました。
また、変数にSQLを入れたからって勝手にSQLが実行されるわけではない、ということでしたので。
executeメソッドの1行に「mtable.Execute tsql」を加えました。
mtableはRecordset型のローカル変数で宣言されている、けど宣言した後Recordsetを開いているわけではないので中身はNothig、何も処理できない問題について。
データベースへの参照を代入するため、オブジェクト変数dbを宣言し、「Set mtable = db.openrecordset(tsql,dbopendynaset)」と、してみました。
OpenRecordsetメソッドを利用して、クエリtsqlを実行したかったからです。
定義済みのクエリを実行する
https://www.moug.net/tech/acvba/0040023.html
も、参考にしました。
Currentdb.Execute("insert into テーブルＡ(f1,f2,f3) select f1,f2,f3 from テーブルＢ")…という書き方は熟練の方の書き方のようだったので、取り入れなかったです。
しかし、「売上実績」ボタンをクリックしても。
「worksheet_売上一覧」テーブルに、データを書き込むことができなかったです(泣)
前回から修正したのが下記のコードです。
今の最新ファイルはこちらです。
Private Sub 売上実績_KeyDown(KeyCode As Integer, Shift As Integer)
Dim tsql As String
Dim db As dao.Database
Dim mtable As dao.Recordset
If KeyCode = vbKeyReturn Then
Set mtable = db.OpenRecordset(tsql, dbOpenDynaset)
tsql = tsql & "insert into worksheet_売上一覧("
tsql = tsql & "業種ID,"
tsql = tsql & "業種名,"
tsql = tsql & "支店ID,"
tsql = tsql & "支店名,"
tsql = tsql & "社員ID,"
tsql = tsql & "社員名"
tsql = tsql & ")"
tsql = tsql & "values("
tsql = tsql & mtable.Fields("業種コード").Value
tsql = tsql & ",'" & mtable.Fields("業種名称").Value & "'"
tsql = tsql & "," & mtable.Fields("支店コード").Value & "'"
tsql = tsql & "," & mtable.Fields("支店名称").Value & "'"
tsql = tsql & "," & mtable.Fields("社員コード").Value & "'"
tsql = tsql & "," & mtable.Fields("社員名").Value & "'"
tsql = tsql & ")"
mtable.Execute tsql
End Sub
一番左端のID列のせいでしょうか…それとも書き込む側の「T_売上管理」テーブルの列の指定方法の誤りとかが原因なのでしょうか…。
書き込みが行われるためにはどのように直せばよいのか、再度、お教えいただけないでしょうか。
よろしくお願い致します。
No.1
317
115
500pt
_KeyDownのイベントで書かれていますが、
素直に_Clickでテストしました。
https://youtu.be/KOLjRBJ1hnk?t=41
↑にデバッグ作業の内容を動画で載せとくので、↓のコードと合わせてチェックしてみてください。
236
235
Private Sub 売上実績_Click()
Dim tsql As String
Dim db As dao.Database
Dim mtable As dao.Recordset
Set db = CurrentDb 'データベースの指定、ここはカレントにする
'T_売上管理分、データを追加するテスト
'Set rsTable = dbs.OpenRecordset("Table1", dbOpenTable)
Set mtable = db.OpenRecordset("SELECT * FROM T_売上管理", dbOpenDynaset)
Do While Not mtable.EOF '↑上で作ったレコードセットが終わるまで
tsql = "" '初期化する
tsql = tsql & "insert into worksheet_売上一覧("
tsql = tsql & "業種ID,"
tsql = tsql & "業種名,"
tsql = tsql & "支店ID,"
tsql = tsql & "支店名,"
tsql = tsql & "社員ID,"
tsql = tsql & "社員名"
tsql = tsql & ")"
tsql = tsql & "values("
tsql = tsql & mtable!業種コード
tsql = tsql & ",'" & mtable!業種名称 & "'"
tsql = tsql & "," & mtable!支店コード
tsql = tsql & ",'" & mtable!支店名称 & "'"
tsql = tsql & "," & mtable!社員コード
tsql = tsql & ",'社員名はどこから？'"
tsql = tsql & ")"
db.Execute tsql 'SQLでインサート、追加
mtable.MoveNext '次のデータへ
Loop
'Cleanup
mtable.Close
MsgBox "終了"
End Sub
↑やりたいことが？うまくつかめなかったので、
売上実績ボタンが押されたら
T_売上管理
から(全てのレコードをレコードが無くなるまで一件一件ループさせ)
worksheet_売上一覧 テーブルへ
insert into文のSQL文字列を作成して、
db.Execute tsql 'SQLでインサート、追加
そんな感じの処理サンプルを作成してみました。
※デバッグの流れで、そんな感じに勝手に作ってみました。
アレンジして、使えればいいのですが・・・
問題解決の参考となれば幸いです。
https://youtu.be/KOLjRBJ1hnk?t=41
↑にデバッグ作業の内容
![[f:id:ken3memo:20201211052456j:image]](http://f.hatena.ne.jp/images/fotolife/k/ken3memo/20201211/20201211052456.jpg "[f:id:ken3memo:20201211052456j:image]")
No.2
52
500pt
質問の課題はすでに解決済だと思うので余計なお世話感があるんだけど、偉そうな指摘のみで実際のコード例を示さないのはどうかと思ったので、ちょっとコードを書いてみました。
休日で時間があったのと、偉そうに言ったことの呵責も少々ありましてｗ
これです→ https://xfs.jp/BVtqrY
■ 画面の解説を少し。
・上の段のテーブルのレコードを下の段のテーブルにコピーしようとしています。
・コピー方法は「レコードセットとINSERT VALUES」、「INSERT SELECT FROM」、「レコードセットどうしでAddnew」の3パターン。
「generate Copy Source」は、1行目のレコードと同じものを「Copy Count」数分コピー元テーブルに追加するだけです。
1行目にデータを入力して1000と入れてボタンを押すと同じものが1000レコード足されるわけです。
コピー元データをたくさん作りたいのでやっているだけで、本来の目的ではないです。
まん中の三つ並んでいヤツは、それぞれの方法でのコピー処理とその処理時間の表示。
下の段がコピー先テーブルの表示部分。
■ テーブルの説明を少し。
コピー先テーブルはテキスト型2列、数値型2列、日付型1列の5列です。
コピー元テーブルはすべてテキスト型の5列です。
■ コードの説明を少し。
自分がよくやるやつを引用して書いたものなので、余計な処理が結構あります。
（もっとも自分はもう何年もまともにプログラムを書いていなくて、最近は自分の仕事の効率化のためにJavascriptやpythonを時々書く程度なので、なにか不備があっても生暖かく許してください。）
説明したほうが良さそうなのは・・・
1. トランザクションの処理が入っています。
トランザクションはデータの一貫性のために入れるもので、例えば口座Aから口座Bにお金を移すような場合に途中で何かエラーがあったらまとめて戻したいから入れるものです。
自分の仕事上のクセで入れないと気持ち悪いので入れているだけで、特に必要がなければ気にしなくていいでしょう。
2.SQLStrとかSQLNumとかSQLDateとか。
ＳＱＬを文字列として組み立てるときに、テキストなら””で囲う、日付なら##で囲う、数値なら文字に変換などの処理が必要なのでそれをしている関数。
また、たいていのＤＢでは「長さゼロの文字列や数値の0」と「値が入っていない状態のNull」は別のものなので、その処理もしている。
ちょっと詳しく説明すると、テーブルの列やフォームのTextboxなどに値が入っていないとそれはNullの状態。
SQLを組み立てるとき、例えばテキスト型の時に、「tsql = tsql & ",'" & xxxx & "'" 」などとやって、xxxがNullだと「,''」が作られる。
''（長さ0文字列）を許可しないDBは多いしAccessも以前のバージョンでは明示的に指定しなければ「空文字列は許可しない」だった。
また、数値型の列に「tsql = tsql & "," & xxxx 」などとやって、xxxがNullだと「,」だけが付け加えられる。
例えば2列目が数値の場合、「insert into tblA (f1, f2 f3) values('a',,'b')」のようなSQLが作られるわけだけど、このSQLはエラーになる。
「insert into tblA (f1, f2 f3) values('a',Null,'b'」としなければならないんで、その処理をしているってことです。
DBにおいて、すべての列に必ずデータが入っているとは限らないからね。
ちなみにこれはSQLを文字列として組み立ててDBエンジンに渡す場合の話で、「INSERT SELECT FROM」や「Recordset.AddNew」の場合は、DBエンジン自身がそのあたりの処理を自分でやってくれるので必要ないです。
まあ、その分コーディングが簡素だし処理も早い。DBエンジンは当然ネイティブコードでAccessVBAはインタープリタだからね。
SQLを文字列で渡すってのはコストのかかる処理なんですよ。
そんな感じでサンプルを作ってみました、自己満足的に作っただけなので参考にしてもしなくてもいいけど、せっかく作ったのでアップしました。
ありがとうございます、また拝見させていただきます！！
ごろえもさんHatenaExample1、ありがとうございます！
さっそく、「Generate Copy Source」で行をたくさん作成しまして。
「Recordset→InsertSQL」「InsertSQL＋select」「Recordset→Recordset.Addnew」の3つのボタンを押して、処理を確かめようと思ったのですが。
私のPCがwindows10ですが64bit版なのか、「Generate Copy Source」押下時に、エラーが出てきました。
https://f.hatena.ne.jp/moon-fondu/20201225155633
（イベントプロパティに指定した式 クリック時 でエラーが発生しました:このプロジェクトのコードは、64bitのシステムで使用するために更新する必要があります。Declareステートメントの確認および更新を行い、次にDeclareステートメントにPtrSafe属性を設定してください。）
ptrsafe属性を追加して、使えるようにしてみます。
＞「insert into tblA (f1, f2 f3) values('a',Null,'b'」としなければならないんで、その処理をしているってことです。
の話は、全然知らなかったです。
属性が数値型でもNull入れられるんですね。Nullなら、エラーにならないんですね。
「SQLを文字列として組み立ててDBエンジンに渡すのはコストのかかる処理」「1レコードごとに個別にSQLを組立てて処理する」というのは、「Recordset→InsertSQL」ボタンを押した場合の処理ですね。
3つとも押して動きを見てみます！
ごろえもん
2020/12/05 19:58:09
1
4
2
ごろえもんさん、コメントありがとうございます！
すみません、年末のバタバタでせっかくご回答いただいたken3memoさんとごろえもんさんのコメントをチェックできていない状況です。
またじっくり確認させていただきます！！
＞ken3memoさん
SQLステートメントを指定できます、と書いてますね！
だから「SELECT * FROM T_売上管理」を入れることもできるのですね。
確かに自分は、
---------------
strSQL = "SELECT * FROM T_売上管理"
Set mtable = db.OpenRecordset(strSQL, dbOpenDynaset)
--------------
のような記述は見かけたことがあるので、特に違和感を覚えなかったので馴染みがありますが。
レコードセットを作るOpenRecordsetメソッドで直接、SQL文を入れられるというのは勉強になりました。
手元の本にも、テーブルの内容をそのまま参照する「テーブルタイプ」というレコードセット、クエリ実行時に取得した結果を参照する「スナップショット」、レコードセットに変更を加えた場合でもデータベースに反映して更新にも対応できる「ダイナセット」の3種類が大きく分けてあると記載がありましたので。
「SELECT * FROM T_売上管理」はテーブルタイプですね。
ありがとうございます。
＞ごろえもんさん
そうですね、「なぜSQLの文法やDAOのメソッドやプロパティの仕様を公式ドキュメントにあたって調べないのか。」というのは、公式ドキュメントが、難しいからです。
公式ドキュメントは、正しいことが書かれているかもしれませんが、初心者にとっては解説が不親切な部分がある印象です。
学校の勉強とかでも、教科書は最低限のことしか書いてないので、参考書買ったりするとの似たような感じですね。
前回の質問でのごろえもんさんのアドバイスの4つ、
1.ボタンなのにばなんでKeydownイベントで処理しているのか、ボタンクリックでKeydownは起きない
2.変数にSQLを入れたからって勝手にSQLが実行されるわけない
3.変数は宣言したがけじゃ空っぽの器で、それではなにもできない
4.レコードの挿入だけなら、recordsetを開くよりただのinsert select from のほうが適している
は、1個1個なら調べて理解していたつもりなのですが。
それを組み合わせるのが、難しいのです。
せっかくアドバイスいただいたのに、いちゃもんとか言ってしまって、すみませんでした。