EXCEL VBAコミュのEXCELとACCESSの連携 その3（最終）

みなさん、こんにちは。

EXCELとACCESSの連携の解説についての続きとなります。
今回でこの連載は終了します。

今回はコードの説明と、データベースへの書き込みについても解説します。
少し長くなりますが、お付き合いくださいね。


では、前回紹介したコードです。

----------------------------------コードここから------------------------------
01. Public Const DB As String = "（データベースファイル名をフルパスで入力）"
02.
03. Sub 出力テスト()
04.
05. Dim objCnn As New ADODB.Connection
06. Dim objCmd As New ADODB.Command
07. Dim objRec As New ADODB.Recordset
08. Dim SQL As String
09.
10. 'データの取得の準備
11. objCnn.Open ConnectionString:="Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DB & ";"
12. Set objCmd.ActiveConnection = objCnn
13.
14. 'SQLをデータベースに送り実行
15. SQL = "SELECT * FROM DB例"
16. objCmd.CommandText = SQL
17. objCmd.CommandType = adCmdText
18. Set objRec = objCmd.Execute
19.
20. 'A1セルを起点にして全データを吐き出す
21. Worksheets(1).Range("a1").CopyFromRecordset objRec
22.
23. 'ADODBオブジェクトをクリア
24. If objRec.State <> ADODB.adStateClosed Then
25. objRec.Close
26. Set objRec = Nothing
27. End If
28.
29. If objCnn.State <> ADODB.adStateClosed Then
30. objCnn.Close
31. Set objCnn = Nothing
32. Set objCmd = Nothing
33. End If
34.
35. End Sub
----------------------------------コードここまで------------------------------

少し見づらくなってしまいましたが、説明するために各行に行番号を振りました。
以降、行番号で説明します。


【01】
データベースファイルのファイルのフルパスを定数化しています。


【05〜07】
各種ADODBオブジェクトを定義しています。
参照設定でMicrosoft ActiveX Data Objects Libraryを参照しているため、このような定義が可能となります。
ちなみに、ライブラリを使わずオブジェクト定義する場合は、下記のようになります。
コネクションオブジェクトを例にします。

（ライブラリ使用）
Dim objCnn As New ADODB.Connection

（ライブラリ不使用）
Dim objCnn As Object
Set objCnn = CreateObject("ADODB.Connection")

おそらくライブラリを使用しない方がメジャーかもしれません。
ただ、この方法では、06行のコマンドオブジェクトは使用できません。


【11〜12】
データベースにアクセスするためのプロバイダを指定し、コネクションオブジェクトを開きます。


【15】
SQL文をSQL変数に格納しています。
このSQ文をデータベースで実行します。


【16〜18】
コマンドオブジェクトを利用して、レコードセットオブジェクトにSQLで実行した結果を格納します。


【21】
SQL文で全データを引っ張り出すよう指定しているので、そのデータを加工なしでA1を起点にして吐き出します。
ちなみに「CopyFromRecordset」を使用しないで全データを吐き出そうとすると下記のようなコードになります。

----------------------------------コードここから------------------------------
i = 1
Do Until objRec.EOF
Range("a" & i) = objRec("製品名").Value
Range("b" & i) = objRec("品番").Value
Range("c" & i) = objRec("色番").Value
　　　・
　　　・
　　　・
　　　・
i = i + 1
objRec.MoveNext
Loop
----------------------------------コードここまで------------------------------

このコードのほうが一般的かもしれません。


【24〜33】
各オブジェクトを閉じて開放しています。
「adStateClosed」はオブジェクトが閉じているか判別できるメソッドです。
閉じているオブジェクトを閉じようとするとエラーになるので、このように状態を判別しています。
ただ、今回は普通にオブジェクトを開いているので、状態判別をしなくても問題ありませんが、もっと複雑なコードになるとオブジェクトが開かないまま終了してしまうような状況が発生することがあるので、私は念のためこのようなエラー回避をしています。


また、今回は単純に全データを吐き出していますが、特定のフィールドで昇順にしたり、条件に合うものだけ抽出とかいうこともできます。
そういう場合は、【15】のSQL文をいじるだけです。

例1：品番を昇順にしたい
SQL = "SELECT * FROM DB例 ORDER BY 品番"

例2：担当部署を「調達02」だけに絞り込みたい
SQL = "SELECT * FROM DB例 WHERE 担当部署='調達02'"

VBAのコードをほとんどいじらず、SQL文を書き換えるだけで、様々なことができます。



次に、追加と更新です。
EXCELは入出力とするので、出力だけでなく入力も必要です。

入力も基本はSQL文をいじるだけです。

追加のSQL文は「INSERT INTO 〜 VALUES 〜」、更新のSQL文は「UPDATE 〜 SET 〜 WHERE 〜」を使用します。


まずは更新です。
上のコード内の【15】のSQL文を下記のように変更してください。

SQL = "UPDATE DB例 SET 仕入先住所='東京都' WHERE 仕入先='C社'"


また、【21】のA1を起点に吐き出すコードを消すか、コメントアウトして実行しないようにしてください。


そして、コードを実行すると、C社の住所が「群馬県」から「東京都」に変更されます。

更新というのは、今あるデータを別のデータで上書きするというイメージです。


次に追加です。
更新同様【15】を下記のSQL文に書き換えてください。

SQL = "INSERT INTO DB例(製品名,品番,仕入先,担当部署) VALUES('K2','775K2-10010','D社','調達01')"


今回の場合も【21】のコードは実行できないようにしておいてくださいね。


実行すると、製品名と品番、仕入先、担当部署にそれぞれデータが入ったレコードが1行追加されているはずです。



更新と追加の基本形は以上です。
後はSQL文内の、特定の文字等を変数化すれば、応用が利くと思います。


最後に、読み出しと更新と追加の3つをまとめて実行するコードが下記になります。
最初とどこが変わっているのか、みなさんで確認してみてください。

----------------------------------コードここから------------------------------
Public Const DB As String = "（データベースファイル名をフルパスで入力）"

Sub 出力テスト()

Dim objCnn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRec As New ADODB.Recordset
Dim SQL As String

'データの取得の準備
objCnn.Open ConnectionString:="Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & DB & ";"
Set objCmd.ActiveConnection = objCnn

'読み出し
SQL = "SELECT * FROM DB例"
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
Set objRec = objCmd.Execute

Worksheets(1).Range("a1").CopyFromRecordset objRec

'更新
SQL = "UPDATE DB例 SET 仕入先住所='東京都' WHERE 仕入先='C社'"
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
Set objRec = objCmd.Execute

'追加
SQL = "INSERT INTO DB例(製品名,品番,仕入先,担当部署) VALUES('K2','775K2-10010','D社','調達01')"
objCmd.CommandText = SQL
objCmd.CommandType = adCmdText
Set objRec = objCmd.Execute

'ADODBオブジェクトをクリア
If objRec.State <> ADODB.adStateClosed Then
objRec.Close
Set objRec = Nothing
End If

If objCnn.State <> ADODB.adStateClosed Then
objCnn.Close
Set objCnn = Nothing
Set objCmd = Nothing
End If

End Sub
----------------------------------コードここまで------------------------------


以上です。
コメントもお待ちしています。