Excel VBA 第10回 ADOでSQLを試してみる

はじめに

今回はExcelでADO (ActiveX Data Objects)でSQLを使用します。
これまで使用したことがなく、今回初めて使用しますので、よくわからないことが多いのですが、まずは試してみたことを備忘として載せようと思います。
実際のところもう少し使ってみないとよりよい使い方はわからないなあという感触ですが・・・w

そもそもADOを使用しようと思ったきっかけが、これまでExcel表の中からデータを取り出すのに、For分で回してみたり、AutoFilterをかけたりして、必要なデータを取り出しており、これでも実現はできていたのですが、取り出したいデータの構造によって、For分の数やAutoFilterの数が違っていて、そのたびに似たようなソースを書いており、ソースが見づらくメンテナンス性にかけていました。
これをもっとシンプルにしてメンテナンス性をあげ、もっと高速に動作させることができないのかなと思いました。
For文やAutoFilterでもソースをしっかり書けばできそうな気もしたのですが、インターネットで調べているとADOを使った例がいくつかみつかり、これのほうがシンプルで楽そうだなということで試してみることにしました。
試してみたところ、こちらのほうがシンプルで分かりやすそうだなというのが今の感触です。
ですが、SQLを使用するところはちょっと覚えるのがつらいかもとか、ADOの癖みたいのはあり、For文やAutoFilterでもよさそうなシーンはありそうだなとは思いました。SQLは慣れれば使いこなせそうなんだけれど、今まで使用していなかったので手間取ってますw。
処理速度は検証していないので、どっちがいいかわからないです（For文やAutoFilterの整理したソースを書くのが面倒なので）。

今回実施する内容

ADOを試してみるということで、

• ADODBのクラスを作る
• ADODBを使用してExcelファイルのデータをSQL Selectする
• ADODBを使用してExcelファイルのデータをSQL Updateする
• ADODBを使用してExcelファイルのデータにSQL Insertする
• ADODBを使用してExcelファイルのデータをSQL Deleteする(ExcelファイルではDeleteは不可)

ソースコード(Git Hub)

VBA_10_ADODB_SQL

環境

OS:Windows 10 JP
Excel: Excel 2021 (64bit)

参考

Microsoft.ACE.OLEDBについてまとめてみた
Microsoft.ACE.OLEDBのバージョン確認方法など記載があります。

Open メソッド (ADO Recordset)
RecordsetのOpenメソッドの説明の記載があります。

CursorType プロパティ (ADO)
CUrsorTypeの説明の記載があります。

RecordCount プロパティ (ADO)
RecordCountの説明の記載があります。

Execute メソッド (ADO Command)
Command.Executeの説明の記載があります。

用語

ADO

ActiveX Data Object。

ADODB

ADOのDB。

ADODBの使い方のポイント

本記事を振り返ってADODBを使おうと思ったところ、ポイントというか、記事をしっかり読まないとわからないことが多かったので、気になったポイントをまとめます。

テーブルの設定方法

記載方法	例
直接記載	[DB$A2:E12]
名前の定義	[TableA]

通常のシートのセル参照では、[DB!$A2:$E12]のようになるが、大きく違う点です。
また、名前の定義で定義済みであれば、[TableA]のように参照可能。

読み込み可能な行は、65535行目まで

テーブルの範囲を65535行を超える設定を行うと以下のようなエラーがでます。
色々調べたのですが、対処がなく、範囲の行数ではなく、65536行目までしか読み込めないようです。
[DB$A2:E]のように行数を記載しないとうまくいくという情報があったので試してみたのですが、エラーは出ないものの、65537行目以降はSQL文が処理されておらず、例えばUpdateしてもUpdateされないという状況でした。
これはかなり残念で、使おうと思ったデータが軽く行数を超えていたので、使えないなと思いました。

SelectはGetRecordSet、Update、InsertはCommand.ExecuteかConnection.Execute

SQL	例
Select文	GetRecordSet
Update、Insert文	Command.ExecuteかConnection.Execute
Delete	Excelでは使用不可

UpdateやInsertをRecordSetで実施するとそのままRecordSetが閉じられてしまうためです。

ADODBのクラスを作る

ADOを使用するうえでは、いくつかお決まりの記載があります。
CreateObjectでActiveXの参照を作ったり、読み込むファイルの種類など設定したりと、そういったものをクラスにしてまとめ、容易にADODBへアクセスできるようにします。
モジュールに記載しておいてもよいのですが、個人的にはクラスにしてインスタンスを呼び出すほうが好みなのでクラスを作ります。

ソース

Cls_ADODB.cls

Option Explicit

'-----Constant-----
Private Const adStateClosed As Long = 0
Private Const adOpenDynamic As Long = 2
Private Const adOpenStatic As Long = 3
Private Const adLockPessimistic As Long = 2

'-----Global variable-----
Private G_cn As Object
Private G_rs As Object
Private G_cmd As Object
Private G_recordCount As Long

'クラスの初期化。
'G_cnとG_rsを設定。
'Providerを設定。
'Excelファイルであることを設定。
Private Sub Class_Initialize()
    Set G_cn = CreateObject("ADODB.Connection")
    Set G_rs = CreateObject("ADODB.RecordSet")
    Set G_cmd = CreateObject("ADODB.Command")
    
    G_cn.Provider = "Microsoft.ACE.OLEDB.12.0"
    G_cn.Properties("Extended Properties") = "Excel 12.0;HDR=YES"
End Sub

'SQLを実行時のレコード数を取得する。
'SQLを実行していない場合0となる。
'
'@return SQLの結果のレコード数。
Public Property Get RecordCount()
    RecordCount = G_recordCount
End Property

'ConnectionのExtended Propertiesの値を設定する。
'
'@strValue 設定する値。
Public Property Let ExtendedProperties(strValue As String)
    G_cn.Properties("Extended Properties") = strValue
End Property

'ADODB接続を実施する。
'
'@strFilePath 読み込むExcelのファイルフルパス。
'@return 設定したADODB接続。
Public Function GetADODBConnection(strFilePath As String) As Object
    G_cn.Open (strFilePath)
    Set GetADODBConnection = G_cn
End Function

'RecordSetを取得する。
'
'@strSource 設定するソース。
'@return 設定したRecordSet。
Public Function GetRecordSet(strSource As String) As Object
    On Error GoTo AnyErr
    
    Call G_rs.Open(strSource, G_cn, adOpenStatic, adLockPessimistic)
    If G_rs.State <> adStateClosed Then
        G_recordCount = G_rs.RecordCount
    End If
    Set GetRecordSet = G_rs
    Exit Function
AnyErr:
    MsgBox (Err.Description)
    End
End Function

'ADODB接続をクローズする。
Public Function CloseADODBConnection()
    If G_cn.State <> adStateClosed Then
        G_cn.Close
    End If
End Function

'RecordSetをクローズする。
Public Function CloseRecordSet()
    If G_rs.State <> adStateClosed Then
        G_rs.Close
    End If
End Function

'Commandを実行する。主にUpdate、Insert SQL用とする。
'Commandで実行する場合、Executeのiの値をRecordCountとして設定する。
'RecordCountは、UpdateやInsertのSQL文では該当したレコード数となる。
'RecordCountは、SelectのSQL文では0となる。
'
'@strSource 設定するソース。
'@return 設定したRecordSet。
Public Function CommandExecute(strSource As String) As Object
    Dim lngRecordAffected As Long
    
    On Error GoTo AnyErr
    
    G_cmd.ActiveConnection = G_cn
    G_cmd.CommandText = strSource
    Set CommandExecute = G_cmd.Execute(lngRecordAffected)
    G_recordCount = lngRecordAffected
    Exit Function
AnyErr:
    MsgBox (Err.Description)
    End
End Function

'Connection.Executeを実行する。主にUpdate、Insert SQL用とする。
'Connection.Executeで実行する場合、Executeのiの値をRecordCountとして設定する。
'RecordCountは、UpdateやInsertのSQL文では該当したレコード数となる。
'RecordCountは、SelectのSQL文では0となる。
'
'@strSource 設定するソース。
'@return 設定したRecordSet。
Public Function ConnectionExecute(strSource As String) As Object
    Dim i As Long
    
    On Error GoTo AnyErr
    
    Set G_rs = G_cn.Execute(strSource, lngRecordAffected)
    G_recordCount = lngRecordAffected
    Set ConnectionExecute = G_rs
    Exit Function
AnyErr:
    MsgBox (Err.Description)
    End
End Function

ConstantとGlobal variable

Constantについては、ADODBで使用される定数を一部定義しただけです。
Global variableは、ADODB.Connection, ADODB.RecordSet, ADODB.Command, ADODB.Connection.RecordCountをグローバル化したもので、この後説明します。

Class_InitializeとExtendedProperties

クラスの初期化時に、ADODB.Connection、ADODB.RecordSet、および、ADODB.CommandのActiveXオブジェクトへの参照を作成しつつ、それぞれをGlobal variableである、G_cn、および、G_cmdに設定します。
また、ADODB.Connectionのプロパティとして、ProviderとPropertiesとして設定します。

Providerは、デフォルトで設定されているものもあるようですが、Microsoft Accessのデータベースエンジンの12.0を使用するようにします。これ以外に16.0もあるようですが、うまく動作しなかったため、12.0にしました。12.0も64bit版対応なのでこれでいいかなと思いました。
Microsoft.ACE.OLEDBについてまとめてみたでインストールされているバージョンは確認できることを知りました。

PropertiesのExtended Propertiesは読み込みするファイルの形式を示すようで、CSVとかExcelとかどういったものかをあらかじめ、Propertiesに設定するもののようです。
今回は、Excelファイルの情報をSQLで取得したいため、Excel 12.0;HDR=YESとしました。16.0もありますが、まあProviderと同じバージョンがよかろうと思いました。
後ろのHDR=YESはヘッダーの有無を示します。
読み込むファイルのテーブルの一行目にヘッダーを含む場合はYES、含まない場合はNOを設定します。
今回はYESにしましたが、設定変更できるようプロパティを作ったほうがよさそうだなと思いました。

RecordCount

SQL実行時のレコード数を示す。
Select文実行時は、RecordSet.RecordCountから値を取得できるのですが、Update文やInsert文実行時は、すぐにRecordSetが閉じられてしまうため、RecordSet.RecordCountを取得できません。
そのため、Connection.ExecuteやCommand.Executeの引数から、RecordCount相当の値を取得できるため、それをG_RecordCountとして保存し、本プロパティで取得できるようにしました。

GetADODBConnection

ADODB.Connectionを確立し、そのConnectionオブジェクトを取得します。
といいつつ、実際には取得したConnectionを使用しませんでした。
クラスからRecordSetなど実行できるため不要でした。
Connectionの確立は必要なので、まあよいでしょう。

GetRecordSet

ADODB.RecordSetをOpenし、そのRecordSetを取得します。
G_rs.Openで、引数で設定したSQL文を実行し、その結果のRecordSetを本関数の戻り値として取得します。
主にSelect文で使用する想定であり、結果がこのRecordSetに含まれるため戻り値としました。
G_rs.Openの引数CursorTypeの値はadOpenStaticとしました。
RecordCount プロパティ (ADO)によると、以下の記載があります。

レコードセットオブジェクト内のレコードの数を調べるには、 RecordCountプロパティを使用します。 ADO がレコードの数を特定できない場合、またはプロバイダーまたはカーソルの種類が RecordCountをサポートしていない場合、プロパティは-1 を返します。 閉じたレコードセットのRecordCountプロパティを読み取ると、エラーが発生します。
：
レコード セット オブジェクトのカーソルの種類は、レコード数を決定できるかどうかに影響します。 RecordCountプロパティは、順方向専用カーソルの場合は-1 を返します。静的カーソルまたはキーセットカーソルの実際の数。データソースに応じて、-1 または動的カーソルの実際の数のいずれかです。

設定しない場合のデフォルトでは、adOpenForwardOnlyで、上記の順方向専用カーソルにあたり、RecordSet.RecordCountの値は-1が応答されます。
該当する件数を取得するためには、adOpenStaticである静的カーソルが必要でした。

G_rs.Openの引数LockTypeはadLockPessimisticにしました。
デフォルトはadLockReadOnlyのようですが、UpdateやInsert文を想定し、更新できるように上記にしました。

CloseADODBConnection、CloseRecordSet

この二つは、ADODB使用終了時に設定します。Class_Terminate時に実行してもよかったのかもしれません。

CommandExecute、ConnectionExecute

主にUpdate、およびInsert文を使用するときは、GetRecordSetではなく、こちらを使うのがよさそうです。
というのも、Open メソッド (ADO Recordset)に以下のような記載があります。

Open メソッドの Source 引数を使用して、レコードを返さないアクション クエリを実行するのは、お勧めできません。これは、呼び出しが成功したかどうかを容易に判断できないためです。このようなクエリによって返された Recordset は閉じられます。SQL INSERT ステートメントのように、レコードを返さないクエリを実行する場合は、代わりに Command オブジェクトの Execute メソッドまたは Connection オブジェクトの Execute メソッドを呼び出してください。

Select文をRecordSet.Openで呼び出す場合は、RecordSetが戻されSelectされた結果を取得できます。
しかし、UpdateやInsert文をRecordSet.Openで呼び出す場合、実行はされますが、RecordSetは閉じられて、結果を参照できません。
かわりに、Execute メソッド (ADO Command)に以下の記載があります。

• RecordsAffected
  省略可能です。 この操作の影響を受けたレコード数をプロバイダーが返す長整数型 ( Long ) の値です。 RecordsAffected パラメーター は、アクション クエリまたはストアド プロシージャにのみ適用されます。 RecordsAffected は、結果を返すクエリまたはストアド プロシージャによって返されるレコードの数を返しません。 この情報を取得するには、 RecordCount プロパティを使用 します。 Execute メソッドは、コマンドが非同期的に実行されると、メソッドが返された時点で影響を受けるレコードの数がまだ分からないので、adAsyncExecute と一緒に使用した場合、正しい情報を返す必要はありません。

ということで、Command.Exexute、もしくは、Connection.Executeを実行して、引数RecordsAffectedから影響を受けたレコード数を取得できるため、これでUpdateやInsert文を実行できたのか、もしくは失敗したのか判断できるように思います。
そこで、RecordAffectedをlngRecordAffectedに設定し、さらにG_recordCountに値を代入することで、レコード数を取得できるようにしました。
一方、SQL文に何かエラーがあったらエラーを表示するようにOn Error GoTo AnyErrでエラー処理をいれて、MsgBoxで画面表示するようにしました。
Command.Execute、もしくは、Connection.Executeのいずれかであればよいのですが、両方で動作を見ようと思ったため、二つ関数を作りました。

ところで、本クラスを使用して何かプログラムを作るとしたら、SQL文がSelect、Insert、Update文などで違う関数を呼び出さないといけないというのは面倒なので、SQL文をチェックしてRecordSetを使うのか、Connection.Execute、もしくは、Command.Executeを使うのかを振り分ける関数を作ってもよいのかなあとは思いました。

ADODBを使用してExcelファイルのデータをSQL Selectする

作成したADODBクラスを使用して、Select文を実行して結果を取得します。
結果は今回はDebug.Printに表示するシンプルなものです。

まず元となるテーブルは以下のようなものにします。

ソース

Module1_Select.bas

Option Explicit

'TableAからタイプがBであるレコードを取得し、その値をDebug.Printに表示する。
Public Sub SelectTableAWhereTypeB()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "SELECT"
    strSQL = strSQL & " *"
    strSQL = strSQL & " FROM [DB$A2:E12]"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [タイプ] = 'B'"
    
    Set objRS = ClsADODB.GetRecordSet(strSQL)
    
    MsgBox objRS.RecordCount
    
    Do Until objRS.EOF
        Debug.Print objRS!No & ", " & objRS!製品名 & ", " & objRS!タイプ & ", " & objRS!ベンダ名 & ", " & objRS!価格
        objRS.MoveNext
    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection
End Sub

'TableBからベンダIDが2であるレコードを取得し、その値をDebug.Printに表示する。
Public Sub SelectTableWhereID2()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "SELECT"
    strSQL = strSQL & " *"
    strSQL = strSQL & " FROM [TableB]"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [ベンダID] = 2"
    
    Set objRS = ClsADODB.GetRecordSet(strSQL)
    
    Do Until objRS.EOF
        Debug.Print objRS!ベンダID & ", " & objRS!ベンダ名
        objRS.MoveNext
    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

'TableBからベンダIDが3であるレコードを取得し、その値をDebug.Printに表示する。
'Command.Executeで実行する。
Public Sub SelectTableCommandExecute()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "SELECT"
    strSQL = strSQL & " *"
    strSQL = strSQL & " FROM [TableB]"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [ベンダID] = 3"
    
    Set objRS = ClsADODB.CommandExecute(strSQL)
    
    MsgBox ClsADODB.RecordCount
    Do Until objRS.EOF
        Debug.Print objRS!ベンダID & ", " & objRS!ベンダ名
        objRS.MoveNext
    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

'TableBからベンダIDが4であるレコードを取得し、その値をDebug.Printに表示する。
'Connection.Executeで実行する。
Public Sub SelectTableConnectionExecute()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "SELECT"
    strSQL = strSQL & " *"
    strSQL = strSQL & " FROM [TableB]"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [ベンダID] = 4"
    
    Set objRS = ClsADODB.ConnectionExecute(strSQL)
    
    MsgBox ClsADODB.RecordCount
    Do Until objRS.EOF
        Debug.Print objRS!ベンダID & ", " & objRS!ベンダ名
        objRS.MoveNext
    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

SelectTableAWhereTypeB

「TableAからタイプがBであるレコードを取得し、その値をDebug.Printに表示する」Sub関数です。

ClsADDODBに先ほど作成したクラスのインスタンスを生成し、GetADDOBConnectionでExcelのワークブックのフルパスを設定します。
続いて、strSQLに実行するSQLのSelect文を記載します。
FROMの後は、[DB$A2:E12]としましたが、これはDBシートのA2:E12の範囲をテーブルとして指定する記載です。
この記載方法をMicrosoftのページで見つけられず、インターネットで調べて見つけたのですが、記載方法は通常のExcelの記載方法とは異なるようで、以下のようなものではNGでした。
[DB!$A2:$E12]
[DB$A$2:$E$12]
のような記載は本関数実行するとエラーが出て失敗しました。
もう一つの記載方法として、「名前の管理」で範囲を「名前の定義」を実施したうえで、その名前をFROMで設定します。
両方ともそうなんですが、Insert文でデータを追加したときテーブルの範囲は変わらないようなので、Insert文を実施して複数データ挿入していくときはテーブル範囲の再設定が必要だなと思います。

Cls.ADDOB.GetRecordSetでSQLのSelect文を実行します。
MsgBoxでは実行結果のレコード数を表示します。
実行結果のobjRSをDo Loopで回してDebug.Printで画面表示します。ループにあたり、objRS.MoveNextを実行することで結果のデータを１件ずつループします。
今回のSelect文はタイプがBであるものを取得するため、2件のデータが得られます。

3, cccc, B, ベンダ２, 10000
4, dddd, B, ベンダ３, 10000

SelectTableWhereID2

「TableBからベンダIDが2であるレコードを取得し、その値をDebug.Printに表示する」Sub関数です。

基本は先ほどと同じですが、違いは、

• FROMを「名前の定義」で定義した名前TableBにしたこと
• WHEREでベンダIDを設定しますが、整数のため、'2'のようにはせず、2と数値を代入していること。

データの型が一致しないと（たとえば、上記で'2'のようにすると、次のようなエラーが出て失敗します。数値と文字列は気を付けないといけないようです。
計算式とか、他セル参照はできるっぽいです。

2, ベンダ２

SelectTableCommandExecute、SelectTableConnectionExecute

「TableBからベンダIDが3であるレコードを取得し、その値をDebug.Printに表示する。Command.Executeで実行する」Sub関数です。
GetRecordSetではなく、Command.Executeでの実施例です。

実行するとMsgBoxで0となります。

CommandExecute、ConnectionExecuteで記載した通り、Command.Executeの引数RecordAffectedで影響のあったデータ数を取得できるのですが、Select文の場合は取得できません。UpdateやInsert文では取得ｄけいるといったところで、一律Command.Executeを使えるわけではないようです。
SelectTableConnectionExecuteもSelectTableCommandExecuteと同様です。

ADODBを使用してExcelファイルのデータをSQL Updateする

作成したADODBクラスを使用して、Update文を実行してテーブルを更新します。テーブルはSelect文のときのものを使用します。
結果はMsgBoxで影響のあったデータ数を表示するシンプルなものです。

ソース

Module2_Update.bas

Option Explicit

'TableAからタイプがBであるレコードを取得し、価格を9000に設定する。
'RecordSet.Openで実行する。
Public Sub UpdateTableATypeBTo9000()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "UPDATE [TableA]"
    strSQL = strSQL & " SET"
    strSQL = strSQL & " [価格] = 9000"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [タイプ] = 'B'"
    
    Set objRS = ClsADODB.GetRecordSet(strSQL)
    
    MsgBox ClsADODB.RecordCount
    
'    Do Until objRS.EOF
'        Debug.Print objRS!No & ", " & objRS!製品名 & ", " & objRS!タイプ & ", " & objRS!ベンダ名 & ", " & objRS!価格
'        objRS.MoveNext
'    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

'TableAからタイプがBであるレコードを取得し、価格を9000に設定する。
'Command.Executeで実行する。
Public Sub UpdateTableACommandExecute()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "UPDATE [TableA]"
    strSQL = strSQL & " SET"
    strSQL = strSQL & " [価格] = 9000"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [タイプ] = 'B'"
    
    Set objRS = ClsADODB.CommandExecute(strSQL)
    
    MsgBox ClsADODB.RecordCount
    
'    Do Until objRS.EOF
'        Debug.Print objRS!No & ", " & objRS!製品名 & ", " & objRS!タイプ & ", " & objRS!ベンダ名 & ", " & objRS!価格
'        objRS.MoveNext
'    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

'TableAからタイプがBであるレコードを取得し、価格を9000に設定する。
'Connection.Executeで実行する。
Public Sub UpdateTableAConnectionExecute()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "UPDATE [TableA]"
    strSQL = strSQL & " SET"
    strSQL = strSQL & " [価格] = 9000"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [タイプ] = 'B'"
    
    Set objRS = ClsADODB.CommandExecute(strSQL)
    
    MsgBox ClsADODB.RecordCount
    
'    Do Until objRS.EOF
'        Debug.Print objRS!No & ", " & objRS!製品名 & ", " & objRS!タイプ & ", " & objRS!ベンダ名 & ", " & objRS!価格
'        objRS.MoveNext
'    Loop
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection

End Sub

UpdateTableATypeBTo9000

「TableAからタイプがBであるレコードを取得し、価格を9000に設定する。RecordSet.Openで実行する」Sub関数です。
内容は、ほぼSelect文と一緒ですが、strSQLのSQL文はUpdate文にしています。
MsgBoxの値は0となります。

UpdateやInsert文の場合、Record.Open後すぐにRecordSetがクローズされるため、Cls_ADODB.GetRecordSetのG_rs.StateがadStateClosedとなり、値が設定されません。
したがって、RecordCountの値は初期値の0になります。
また、そのあとにojbRSのDo Until文をコメントアウトしていますが、これはエラーになるため、コメントアウトしました。

最後のClsADODB.CloseRecordSetはUpdate文では上記のとおりRecordSetはクローズされるためなくてもよいのですが、プログラムを作るときはおそらくUpdateだからとか気にせずにお作法として記載することになるだろうと思い、記載しています。

UpdateTableACommandExecute

「TableAからタイプがBであるレコードを取得し、価格を9000に設定する。Command.Executeで実行する」Sub関数です。
内容は、UpdateTableATypeBTo9000と同じですが、Command.Executeを使用してUpdateをしているところが違います。
こうすると、MsgBoxの値は、2となります。
Command.Executeの引数RecordsAffectedから影響を受けたレコード数を取得したためです。

UpdateTableAConnectionExecute

「TableAからタイプがBであるレコードを取得し、価格を9000に設定する。Connection.Executeで実行する」Sub関数です。
内容は、UpdateTableATypeBTo9000と同じですが、Connection.Executeを使用してUpdateをしているところが違います。
これも、MsgBoxの値は、2となります。
Connection.Executeの引数RecordsAffectedから影響を受けたレコード数を取得したためです。

ADODBを使用してExcelファイルのデータにSQL Insertする

作成したADODBクラスを使用して、Insert文を実行してテーブルにデータを追加します。Excelへのデータ追加が個人的にあまりしないかなと思ったので、1つだけ関数を作りました。

ソース

Module3_Insert.bas

Option Explicit

'TableAにレコードを追加する。
'Connection.Executeで実行する。
Public Sub InsertTableA()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "INSERT INTO [DB$A2:E12]"
    strSQL = strSQL & " ([No], [製品名], [価格])"
    strSQL = strSQL & " Values('11', 'NEW',10000)"
    
    Set objRS = ClsADODB.ConnectionExecute(strSQL)
    MsgBox ClsADODB.RecordCount
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection
End Sub

InsertTableA

「TableAにレコードを追加する。Connection.Executeで実行する」Sub関数です。
実行すると、MsgBoxの値は、1となり、下記のように13行目にデータが追加されます。

Excelで実行するとテーブルの範囲([DB$A2:E12])の最後にデータが追加されます。
もう一度同じSub関数を実行すると、下記のように「指定範囲を広げることはできません。」とエラーが表示されます。

これは、テーブルの範囲([DB$A2:E12])の次の行（13行目)にすでにデータが代入されているためで、13行目を上書きするような処理は実施されず、もしくは、行挿入などしてデータを入れたりすることもなく、エラーになるということです。
したがって、Insertを使うような場合は、テーブル範囲も確認しながら、SQL文を作らないといけないと思いました。

ADODBを使用してExcelファイルのデータをSQL Deleteする(ExcelファイルではDeleteは不可)

ソース

Module4_Delete.bas

Option Explicit

'TableAにレコードを削除する。
'Connection.Executeで実行する。エラーとなる。
Public Sub DeleteTableA()
    Dim ClsADODB As Object
    Dim strSQL As String
    Dim objRS As Object
    
    Set ClsADODB = New Cls_ADODB
    Call ClsADODB.GetADODBConnection(ThisWorkbook.FullName)
    
    strSQL = "DELETE FROM [DB$A2:E12]"
    strSQL = strSQL & " WHERE"
    strSQL = strSQL & " [タイプ] = 'B'"
    
    Set objRS = ClsADODB.ConnectionExecute(strSQL)
    MsgBox ClsADODB.RecordCount
    
    Call ClsADODB.CloseRecordSet
    Call ClsADODB.CloseADODBConnection
End Sub

DeleteTableA

SQL文でDeleteをしてみると、Excelではエラーとなりました。

Deleteしたときにその部分が空白になるわけでも、その行が削除されるわけでもなく、エラーとなります。

おわりに

今回初めてADOのSQLを使用してみました。
それまでAutoFilterやFor文などでフィルタリングして値を取得したり、値を更新する処理をしていましたが、SQL文で書くほうがかなり楽でわかりやすいなと思いました。
SQLについては、今回はシンプルなものしか試していないため、もっと複雑なものも使えるのかわからないですが、私の場合は、上記のようなAutoFilterとかで使う程度なので、このくらいで十分かなと思いました。