Excel

【ExcelVBA】Accessと連携してデータ取得や更新などの操作を行う方法

更新日：2023/12/15

ExcelからVBAでAccessのデータを操作する方法を、とりあえずこれだけ知っておけば基本的な操作ができる、というレベルまで紹介します。

 

ADOの参照設定

ExcelはAccessを操作する機能を持っていません。
そこで、ExcelとAccess間でデータのやり取りをおこなってくれる外部プログラム（ADO)を、Excelから操作する設定（参照設定）を行います。

ADOは『Microsoft Active Data Object』の頭文字です。

次の手順で参照設定をおこないます。

  1. VBエディターの メニュー：ツール⇒参照設定 を選択

    メニュー：ツール⇒参照設定

  2. 『Microsoft Active Data Object 6.1 Library』にチェックを入れます。

    『Microsoft Active Data Object 6.1 Library』にチェック

    Mキーを押すと頭文字がMのライブラリに移動するので、そこから下方向にスクロールすると素早く探すことができます。

  3. 『OK』を押して確定します。

参照設定はプロジェクト内に保存されるので、次回以降は必要ありません。

 

プロバイダ名の確認

プロバイダ名とは、ADOを呼び出すときに必要な名前です。
ほとんどの環境では、プロバイダ名は次のようになります。

Microsoft.ACE.OLEDB.16.0

しかし一部の環境では、エラーになる可能性があります。
エラーが表示されたら、次の手順でプロバイダ名を確認してください。

  1. Excelのビット数確認

    Excelシート上で、メニュー：ファイル⇒アカウント⇒Excelのバージョン情報 を選択します。
    表示された画面で、ビット数を確認します。

    Excelのバージョン情報

    Windwsが64ビットでも、Excelのビット数が異なることがあるので確認が必要です。

  2. ODBCデータソースアドミニストレーターを起動する

    コマンドプロンプトまたはファイル名から実行から、次のパスをコピペしてExcelのビット数と同じODBCデータソースアドミニストレーターを起動します。

    %systemdrive%\Windows\System32\odbcad32.exe
    %systemdrive%\Windows\SysWoW64\odbcad32.exe

    環境によって、32ビット版と64ビット版が入れ替わることがあるので両方確認してください。

    起動すると、タイトルにビット数が表示されます。

    ODBCデータソースアドミニストレーター(32ビット)

  3. バージョンとファイルを確認する

    ドライバータブをクリックして、『Microsoft Access Driver(*.mdb,*.accdb) 』を探します。

    バージョンの上から二つ、上図なら "16.00" が確認するバージョンです。
    ファイルは、ACEODBC.DLLです。

  4. プロバイダ名の決定

    次の①と②を、確認したバージョンとファイル名で置き換えます。
    Microsoft.①.OLEDB.②

    ①: ファイルがACEODBC.DLLなら"ACE"、ODBCJT32.DLLなら"JET"
    ②: バージョン。ただし二けた目は0のみ

    バージョンが"16.00"、ファイルがACEODBC.DLLなら、次のようになります。
    Microsoft.ACE.OLEDB.16.0

    補足：プロバイダ名の OLEDB(OLE DB) は、アプリケーション間におけるデータの取得や更新方法の規格、の中の一つです。
    ADOは、この規格で読み書きします。

 

開始と終了

Accessのデータを操作するときは、まずはAccessに接続する必要があります。
操作後は、接続を切断します。

Accessへの接続

Accessへの接続は、ADODB.connectionオブジェクトにプロバイダ名等をセットしてOpenメソッドを実行します。


Dim connection As New ADODB.connection
With connection
    .provider = プロバイダ名
    .Properties("Data Source").Value = Accessファイルのパス
    .Properties("Jet OLEDB:Database Password").Value = パスワード
    .Open
End With

または接続情報を連結した文字列を引数として、Openメソッドを実行します。


Dim connection As New ADODB.connection
connection.Open "Provider=" & プロバイダ名 _
               & ";Data Source=" & Accessファイルのパス _
               & ";Jet OLEDB:Database Password=" & パスワード _
               & ";"

どちらの構文でも、Access側でパスワードロックを行っていない時は、パスワードの行を削除してください。

次のコードは、Accessへの接続を関数化したものです。

' Accessに接続
'  accessFilePath ... Accessファイルのフルパス
'  password(オプション) ... Accessファイルのパスワード
'  return ... 接続情報
'
Function openAccess(accessFilePath As String, Optional password As String = "") As ADODB.connection
    Const provider As String = "Microsoft.ACE.OLEDB.16.0;"
    
    Dim connection As New ADODB.connection
    With connection
        ' プロバイダ名のセット
        .provider = provider
        ' データソース（ファイル名）のセット
        .Properties("Data Source").Value = accessFilePath
        ' パスワードのセット
        If password <> "" Then
            .Properties("Jet OLEDB:Database Password").Value = password
        End If
        .Open
    End With
    Set openAccess = connection
End Function

以降のコードは、この関数を使用します。

Accessからの切断

Accessの操作が終わったら、できるだけ早く接続を切ってリソースを解放します。

切断は接続時に使用したADODB.connectionオブジェクトのCloseメソッドを実行します。

ADODB.connection.Close

切断後は念のため変数にNothingをセットして、ADODB.connectionオブジェクトへの参照をクリアしておきます。

'
' Accessへの接続を切断
'  connectio ... 接続情報
'
Function closeAccess(connection As ADODB.connection)
    connection.Close
    Set connection = Nothing
End Function

以降のコードは、この関数を使用します。

 

データ取得

Accessからデータを取得してセルに表示する方法を3つ紹介します。

データ取得①:個別セット

Accessからデータを取得してフィールド値を個別にセルにセットするときは、ADODB.RecordsetオブジェクトのOpenメソッドでデータを取得します。
次に、Fieldsプロパティで各フィールドから値を取得して、セルにセットします。

コードは、次のようなイメージになります。


ADODB.Recordsetオブジェクト.Open ソース, ADODB.connectionオブジェクト
Do Until ADODB.Recordsetオブジェクト.EOF
        Cells(行, 列) = ADODB.Recordsetオブジェクト.Fields(フィールド名).Value
        ・・・
        ADODB.Recordsetオブジェクト.MoveNext
Loop

ソースは、Access上のテーブル名やクエリ名およびSQL文を指定できます。

コード例です。

Sub getData1()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    rs.Open "テーブル1", con
    
    Dim i As Long
    i = 1
    
    Do Until rs.EOF
        Cells(i, 1) = rs.Fields("ID").Value
        Cells(i, 2) = rs.Fields("フィールド1").Value
        Cells(i, 3) = rs.Fields("フィールド2").Value
        i = i + 1
        rs.MoveNext
    Loop
    rs.close
    Set rs = Nothing
    closeAccess con
    
End Sub

Openメソッドで取得したデータは、最初の行にカーソル（カレントレコード）がセットされます。
Fieldsプロパティを使用すると、カレントレコードから値を取得できます。

カレントレコードを次の行に変更するときは、MoveNextメソッドを使用します。
最終行よりも後ろにカレントレコードが移動するとEOFプロパティがTrueになります。
※取得したデータが空のときも、EOFプロパティはTrueです。

そのため全てのレコードを取得するときは、EOFがTrueになるまでループします。

数値文字列をセルに代入すると、数値になります。
文字列として代入するときは、セルの書式を文字列にするか、先頭に "'"(アポストロフィ)をセットします。
例：

Cells(i, 3) = "'" + rs.Fields("フィールド2").Value

データ取得②:配列にセット

Accessから取得した全データを配列に格納するときは、RecordsetオブジェクトのGetRowsメソッドを使用します。


配列 = ADODB.Recordsetオブジェクト.GetRows
   または
配列 = Application.WorksheetFunction.Transpose(ADODB.Recordsetオブジェクト.GetRows)

GetRowsメソッドは、一次元がフィールドで、二次元に行です。
Application.WorksheetFunction.Transposeメソッドを使用すると、次元を入れ替えることができます。

コード例です。
全ての行を配列に取り込んだ後、セルにセット（貼り付け）をしています。

Sub getData2()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    rs.Open "テーブル1", con
    
    If Not rs.EOF Then
        Dim tableData() As Variant
        
        tableData = Application.WorksheetFunction.Transpose(rs.GetRows)
        
        Range(Cells(1, 1) _
            , Cells(1 + UBound(tableData, 1) - 1, 1 + UBound(tableData, 2) - 1)) _
                = tableData
    End If
    rs.close
    Set rs = Nothing
    closeAccess con
End Sub
データ取得①:個別セットと同じように、数値文字列は数値としてセルに入力されます。

データ取得③:CopyFromRecordsetを使用

RangeオブジェクトのCopyFromRecordsetメソッドを使用すると、Recordsetオブジェクトをセルに貼り付けることができます。


Rangeオブジェクト.CopyFromRecordset data:=Recordsetオブジェクト

行方向にレコード、列方向にフィールドを出力します。
※データ取得②:配列にセットで、Application.WorksheetFunction.Transposeメソッドを使用したパターンと同じです。

構文では省略していますが、MaxRowsでレコード（行）、MaxColumnsで列（フィールド）の最大表示数を指定できます。

コード例です。

Sub getData3()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    rs.Open "テーブル1", con
    
    Range("A1").CopyFromRecordset data:=rs, MaxRows:=10
    
    rs.close
    Set rs = Nothing
    closeAccess con
End Sub

開始セルのみを指定すればいいので、お手軽ですね。

なお、CopyFromRecordsetメソッドは数値文字列を文字列としてセルに入力します。

 

SQL文の実行

SELECTでレコードを取得するSQL文を実行するときは、ADODB.RecordsetオブジェクトのOpenメソッドを使用します。

レコードを更新したり追加や削除をおこなうなど行を返さないときは、ADODB.CommandオブジェクトのExecuteメソッドを使用します。
Executeメソッドを使用することで、影響を受けた件数を取得できます。


With ADODB.Command
    .ActiveConnection = ADODB.connection
    .CommandText = SQL文
    .CommandType = adCmdText
    .Execute 影響を受けた件数
End With

CommandTypeは省略できますが、CommandTextの内容（テーブルか？SQL文か等）チェックをパスできるので、指定する方が処理が速くなります。
影響を受けた件数は省略可能です。

コード例です。

Sub executeSql()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim result As Long   
    Dim cmd As New ADODB.Command
    
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = _
            "INSERT INTO テーブル1(フィールド1,フィールド2) VALUES('こんにちは',10000);"
        
        .Execute result
        Debug.Print result & "件追加しました"
        
        .CommandText = _
            "UPDATE テーブル1 set フィールド1 = 'こんばんは' where ID = 1;"
        
        .Execute result
        Debug.Print result & "件更新しました"
        
        .CommandText = _
            "DELETE FROM テーブル1 where ID = 2;"
        .Execute result
        Debug.Print result & "件削除しました"
        
    End With
    
    Set cmd = Nothing
    closeAccess con
End Sub

 

レコードの追加

テーブルにレコードを追加するときは、Recordsetオブジェクトでテーブルを開いて、AddNewメソッドを実行します。
次にフィールド値をセットして、最後にUpdateメソッドを実行します。


With ADODB.Recordsetオブジェクト
    .Open テーブル名 , ADODB.connectionオブジェクト, adOpenForwardOnly, adLockOptimistic
    .AddNew
    .Fields(フィールド名).Value = 値
    .Update
End With

Openメソッドは、規定値で読み込み専用で開きます。
変更可能にするには、4つめの引数Locktypeを指定します。（Locktypeの値)
3つ目の引数は、CursorTypeです。（CursorTypeの値)

コード例です。

Sub addNewRecord()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    
    With rs
        .Open "テーブル1", con, adOpenForwardOnly, adLockOptimistic
        .AddNew
        .Fields("フィールド1").Value = "こんにちは"
        .Fields("フィールド2").Value = 11111
        .Update
        .Close
    End With
    Set rs = Nothing
    closeAccess con
        
End Sub

 

レコードの更新（上書き）

レコードを更新するときは、Recordsetオブジェクトの変更したい行をカレントレコードにします。
次に変更後の値をフィールドにセットします。
最後に、Updateメソッドを実行します。


With ADODB.Recordsetオブジェクト
    変更したい行をカレントレコードにセット
    .Fields(フィールド名).Value = 値
    .Update
End With

コード例です。

Sub updateRecord()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    
    With rs
        .Open "SELECT * FROM テーブル1 WHERE ID=1", con, adOpenForwardOnly, adLockOptimistic
        .Fields("フィールド1").Value = "こんにちは"
        .Fields("フィールド2").Value = 11111
        .Update
        .Close
    End With
    Set rs = Nothing
    closeAccess con
        
End Sub

 

レコードの削除

レコードを削除するときは、Recordsetオブジェクトのカレントレコードを変更したい行に移動させて、Deleteメソッドを実行します。


With ADODB.Recordsetオブジェクト
    変更したい行をカレント行にセット
    .Delete
End With

コード例です。

Sub deleteRecord()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim rs As New ADODB.Recordset
    
    With rs
        .Open "SELECT * FROM テーブル1 WHERE ID=1", con, adOpenForwardOnly, adLockOptimistic
        .Delete
        .Close
    End With
    Set rs = Nothing
    closeAccess con
        
End Sub

 

トランザクション

一連のデータベース変更処理中にエラーが発生して続行できなくなったとき、整合性を保つために変更した内容をキャンセルして、処理前の状態に戻す必要があります。
トランザクションを使用すると、戻す処理を簡単に実装できます。


On Error GoTo ErrHandler エラーハンドラー
ADODB.connection.BeginTrans ' トランザクション開始

・・・変更処理
・・・変更処理

ADODB.connection.CommitTrans' トランザクション終了。変更を保存して確定
On Error GoTo 0
exit sub または function

エラーハンドラー:
ADODB.connection.RollbackTrans' トランザクション終了。変更を破棄

connectionオブジェクトのBeginTransメソッドでトランザクションを開始します。
connectionオブジェクトのCommitTransメソッドでデータべースの変更を保存して、トランザクションを終了します。

connectionオブジェクトのRollbackTransメソッドを呼び出すと、データべースの変更をキャンセルしてトランザクションを終了します。
これにより、処理開始前の状態にデータベースを戻すことができます。

Function dataChange() As Boolean
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    On Error GoTo ErrHandler
    con.BeginTrans
    
    Dim rs As New ADODB.Recordset
    
    With rs
        .Open "テーブル1", con, adOpenForwardOnly, adLockOptimistic

        .AddNew
        .Fields("フィールド1").Value = "あいうえお"
        .Fields("フィールド2").Value = 11111
        .Update
        
        .AddNew
        .Fields("フィールド1").Value = "あかさたな"
        .Fields("フィールド2").Value = 22222
        .Update

        .Close
    End With
    Set rs = Nothing

    con.CommitTrans
    On Error GoTo 0
    closeAccess con
    
    dataChange = True ' 正常終了 Trueを返す
    Exit Function

ErrHandler:
    con.RollbackTrans
    ' Recordsetがopenされているかどうかを確認してcloseする
    If rs.State <> adStateClosed Then
        rs.Close
    End If
    Set rs = Nothing
    closeAccess con
    
    dataChange = False ' 異常終了 Falseを返す
    MsgBox Err.Description
End Function

 

SQLインジェクション対策

SQLインジェクションは、想定外な値を入力することでSQLに意図と異なる意味を持たせ、情報を抜き取ったりデータベースを破壊するなどの行為を指します。
文字列を & で連結してSQL文を作成するコードは狙われやすいので注意が必要です。

対策として文字列連結をやめ、値指定をパラメーター化します。

1)値を ? に置き換え、CommandオブジェクトのCommandTextにセットする
2)Parametersパラメータに値配列をセットして、CommandオブジェクトのExecuteを実行する。

コード例です。

Sub executeSql2()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim result As Long
  
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = _
            "INSERT INTO テーブル1(フィールド1,フィールド2) VALUES(?,?);"
        .Execute RecordsAffected:=result _
                , Parameters:=Array("こんにちは222", 100400)
        
        Debug.Print result & "件追加しました"
    End With
    Set cmd = Nothing
    closeAccess con
End Sub

文字列パラメータに文字列以外の値のセットを制限したいときは、次のようにParameterオブジェクトを使用します。

1)値を任意の名前に置き換え、CommandオブジェクトのCommandTextにセットする
2)CommandオブジェクトのCreateParameterメソッドで、Parameter オブジェクトを作成する
3)CommandオブジェクトのParameters.Appendメソッドで、作成したParameterオブジェクトを追加する
4)値をセットする
5)CommandオブジェクトのExecuteを実行する。

コード例です。

Sub executeSql3()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim result As Long
    Dim cmd As New ADODB.Command
 
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdText
        .CommandText = _
            "INSERT INTO テーブル1(フィールド1,フィールド2) VALUES(field1,field2);"
      
        .Parameters.Append .CreateParameter(Name:="field1", Type:=adBSTR)
        .Parameters("field1").Value = "こんにちは"
        
        .Parameters.Append .CreateParameter(Name:="field2", Type:=adInteger)
        .Parameters("field2").Value = 2222
        
        .Execute RecordsAffected:=result
        
        Debug.Print result & "件追加しました"
    End With
    Set cmd = Nothing
    closeAccess con
End Sub

CreateParameterメソッドのTypeは、値の型（DataTypeEnum)です。
この値については、DataTypeEnumを確認してください。

 

パラメータクエリの実行

Accessのパラメータクエリを実行するときは、パラメータクエリで定義されているパラメータ名でParameterオブジェクトを作成します。
次にCommandオブジェクトのParametersにセットします。

また、CommandTypeにはadCmdStoredProcを指定します。

Sub paramQuery1()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim result As Long
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandText = "クエリ1"
        .CommandType = adCmdStoredProc
        
        .Parameters.Append .CreateParameter(Name:="field1", Type:=adBSTR)
        .Parameters("field1").Value = "こんにちは"
        
        .Parameters.Append .CreateParameter(Name:="field2", Type:=adInteger)
        .Parameters("field2").Value = 2222
        
        .Execute RecordsAffected:=result
        Debug.Print result & "件追加しました"
        
    End With
    Set cmd = Nothing
    closeAccess con
End Sub

またはExecute実行時に、Parametersを指定します。

Sub paramQuery2()
    Dim con As ADODB.connection
    Set con = openAccess("Database1.accdb")
    
    Dim result As Long
  
    Dim cmd As New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandText = "クエリ1"
        .CommandType = adCmdStoredProc
        
        .Execute RecordsAffected:=result _
                , Parameters:=Array("こんにちは", 1010400)
        
        Debug.Print result & "件追加しました"
    End With
    Set cmd = Nothing
    closeAccess con
End Sub

更新日：2023/12/15

書いた人（管理人）：けーちゃん

関連記事

スポンサーリンク

記事の内容について

null

こんにちはけーちゃんです。
説明するのって難しいですね。

「なんか言ってることおかしくない？」
たぶん、こんなご意見あると思います。

裏付けを取りながら記事を作成していますが、僕の勘違いだったり、そもそも情報源の内容が間違えていたりで、正確でないことが多いと思います。
そんなときは、ご意見もらえたら嬉しいです。

掲載コードについては事前に動作確認をしていますが、貼り付け後に体裁を整えるなどをした結果動作しないものになっていることがあります。
生暖かい視線でスルーするか、ご指摘ください。

ご意見、ご指摘はこちら。
https://note.affi-sapo-sv.com/info.php

 

このサイトは、リンクフリーです。大歓迎です。