小さい会社の1人SEの開発記というか奮戦記

久しぶりに Access – Excel 連携の Update文ではまったので、備忘録として残しておこうと思います。結論から言えばだだの覚え違いなんですが、今回は「ADODB.Recordset と ADODB.Command の使い分け」の話をまとめます。

ADOを使ったデータ更新方法

大きく分けて２通りの方法があります。

1. レコードセットを作成して、列に値を代入し、Update する。
2. SQL文をCommandTextに代入し、Execute する。

1の方法は1行単位に処理するので対象が複数行ある場合はEOFまでMoveNextする必要があります。

2の方法は一括で更新が終わるのでSQL文を書くことが苦にならない人はこちらを好む傾向もあります。わたしもこちら派だったりします・・・。

更新例

アクセスファイル：d:\test\test.accdb

テーブル：T_TEST

データ内容

ID=5 の AA列を”CC0005″, BB列を”CCCCCC”に更新します。

ADODB.Recordset を使った例

    '   接続文
    TXT_CN = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="
    TXT_CN = TXT_CN & "D:\test\test.accdb"

    '   ADOコネクションオブジェクトを作成
    Set ADO_CN = CreateObject("ADODB.Connection")

    '   データベース接続
    ADO_CN.Open TXT_CN
        TXT_SQ = "SELECT * FROM T_TEST WHERE ID = 5;"
        'ADOレコードセットオブジェクトを作成
        Set ADO_RS = CreateObject("ADODB.Recordset")    
        With ADO_RS
            .Open TXT_SQ, ADO_CN, adOpenDynamic, adLockOptimistic
                !AA = "CC0005"
                !BB = "CCCCCC"
            .Update
            .Close
        End With

    '   接続解除
    ADO_CN.Close
    Set ADO_CN = Nothing

SQL文が苦手でもこれなら何とかなりますよね。

ADODB.Command を使った例

    '   接続文
    TXT_CN = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source="
    TXT_CN = TXT_CN & "D:\test\test.accdb"

    '   ADOコネクションオブジェクトを作成
    Set ADO_CN = CreateObject("ADODB.Connection")

    '   データベース接続
    ADO_CN.Open TXT_CN
        TXT_SQ = "UPDATE T_TEST SET AA='CC0005',BB='CCCCCC' WHERE ID = 5;"
        'ADOコマンドを作成
        Set ADO_RS = CreateObject("ADODB.Command")
        With ADO_RS
            .ActiveConnection = ADO_CN
            .CommandText = TXT_SQ
            .Execute
        End With

    '   接続解除
    ADO_CN.Close
    Set ADO_CN = Nothing

SQL文さえ分かればこちらはかなり簡単です。Insert も Delete も自由自在です。

※宣言は先頭にまとめて書いているので未記述です。申し訳無い。

まとめ

経験的に言うと新規行の登録や、大量の列を更新する場合は ADODB.Recordset を使った方が楽です。SQL文も 「SELECT * FROM テーブル名 WHERE 検索条件」で簡単に済みます。

ADODB.Command は更新したい対象をIF～ELESEIF～END IFで切り替えて使いたい時や大量の行を更新する場合におすすめです。

上記の例だと
TXT_SQ = “UPDATE T_TEST SET ”
TXT_SQ = TXT_SQ & “AA=’CC0005′, ”
TXT_SQ = TXT_SQ & “BB=’CCCCCC’ ”
TXT_SQ = TXT_SQ & “WHERE ID = 5;”
で、AAやBBに値を設定するところを行単位にIF文と組み合わせて使います。