TOP > カテゴリ > Excel >

データベース操作のまとめ(接続/挿入/更新/削除/トランザクション/ODBC) [ExcelのVBA]

事前準備

データベースの操作をする為にはデータベースが必要となります。この「データベース操作のまとめ」の前半はAccessのデータベースファイルを使用します。そのAccessのデータベースのテーブルは次のようにします。

テーブル名は「営業部」、列(カラム)には「社員番号、氏名、ひらがな」を設定して、社員番号をプライマリーキー(主キー)にします。

Accessでテーブルを作るのが大変な方は、ダウンロードできます。
excel_vba_61.accdb 424 KB (434,176 バイト)

記事の後半ではODBCドライバを使用して「MySQL」「PostgreSQL」「SQL Server」「Oracle」「Firebird」のデータベースにアクセスする方法を解説します。

データベース

ADO(ActiveX Data Objects)によるデータベースの接続(パスワード付き含む)、挿入、更新、削除、トランザクションの操作解説となります。また、基本的な操作はODBC接続でも同様な操作が可能です。

Wikipediaで補足:ADO トランザクション

接続

データベースに接続後、テーブル「営業部」から全データを取得します。

Private Sub CommandButton1_Click()

  Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim i As Long
 
  ' シート全体をクリアする
  Worksheets("Sheet1").Cells.Clear
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName
  
  ' SQL文(INNER JOINやLEFT JOINも可能)
  SQL = "SELECT 社員番号,氏名,ひらがな FROM 営業部"
 
  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' レコードセット内の全ての行の読込が
  ' 終了するまで処理を繰り返す
  i = 1
  Do Until adoRs.EOF
    Cells(i, 1) = adoRs!社員番号
    Cells(i, 2) = adoRs!氏名
    Cells(i, 3) = adoRs!ひらがな
   
    i = i + 1
    ' 次の行に移動する
    adoRs.MoveNext
  Loop
 
  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果]

ADODB.Connection.OpenのDB接続文字の一覧

バージョン接続文字
Office2007以降Microsoft.ACE.OLEDB.12.0
Office2000-2003Microsoft.Jet.OLEDB.4.0
Office97Microsoft.Jet.OLEDB.3.5.1

※DB(データベース)にアクセスできない場合は接続文字を変更して下さい。

接続(パスワード付き)

パスワード付きのデータベースに接続してテーブル「営業部」から全データを取得します。パスワードは「123」としています。

Private Sub CommandButton1_Click()
  Dim adoCon As Object ' ADOコネクション
  Dim adoRs As Object  ' ADOレコードセット
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim i As Long
 
  ' シート全体をクリアする
  Worksheets("Sheet1").Cells.Clear
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName & ";" & _
              "Jet OLEDB:Database Password=123;"
  
  ' SQL文
  SQL = "SELECT 社員番号,氏名,ひらがな FROM 営業部"
 
  ' SQLの実行
  Set adoRs = adoCon.Execute(SQL)
  
  ' レコードセット内の全ての行の読込が
  ' 終了するまで処理を繰り返す
  i = 1
  Do Until adoRs.EOF
    Cells(i, 1) = adoRs!社員番号
    Cells(i, 2) = adoRs!氏名
    Cells(i, 3) = adoRs!ひらがな
    
    i = i + 1
    ' 次の行に移動する
    adoRs.MoveNext
  Loop
 
  ' 解放処理
  adoRs.Close
  adoCon.Close
  Set adoRs = Nothing
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoRs = Nothing
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果]

※DBにアクセスできない場合はDB接続文字を変更して下さい。

アクセスのデータベースにパスワードを付加するにはファイルを「排他モード」で開きます。
※対象のファイルが既に開いている場合は「閉じて」から開きます。

次にファイルメニューの情報の「パスワードを使用して暗号化」をクリックして設定します。

※パスワードを解除するにはファイルメニューの情報の「データベースの解読」で設定します。

Office2013の場合はパスワードを付加する前にファイルメニューの「オプション」「クライアント設定」の暗号化方法を「以前の暗号化方法を使用する」をオンにする必要があります。※Office2016以降も必要かも知れません。

これは、VBA側が最新の暗号化に対応していない為です。「既定の暗号化方法を使用する」で暗号化したものをVBAからアクセスすると「パスワードが正しくありません。」というエラーが表示されてデータベースにアクセスできません。

挿入

データベースのテーブル「営業部」に「社員番号:4、氏名:山縣有朋、ひらがな:やまがたありとも」を挿入します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object ' ADOコネクション
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim RecordsAffected As Long  ' 変更された行数
  Dim i As Long
  
  ' 定数
  Const adExecuteNoRecords = &H80
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName
  
  ' SQL文
  SQL = "INSERT INTO 営業部 (社員番号,氏名,ひらがな) " & _
        "  VALUES(4,'山縣有朋','やまがたありとも');"
        
  ' SQLの実行
  ' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
  adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
  
  ' RecordsAffectedには変更された行数が返される
  Debug.Print "変更された行数:" & CStr(RecordsAffected) & "行"
 
  ' 解放処理
  adoCon.Close
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果]

テーブル「営業部」の社員番号はプライマリキー(主キー)にしていますので何度もこのコードを実行すると重複エラーが発生します。

※コードを実行する前に社員番号4の行を削除すればエラーは出ません。

更新

データベースのテーブル「営業部」の社員番号4の氏名を「東條英機」、ひらがなを「とうじょうひでき」に変更します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object ' ADOコネクション
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim RecordsAffected As Long  ' 変更された行数
  Dim i As Long
  
  ' 定数
  Const adExecuteNoRecords = &H80
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName
  
  ' SQL文
  SQL = "UPDATE 営業部 SET 氏名='東條英機'," & _
        "                  ひらがな='とうじょうひでき' " & _
        " WHERE 社員番号= 4 "
        
  ' SQLの実行
  ' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
  adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
  
  ' RecordsAffectedには変更された行数が返される
  Debug.Print "変更された行数:" & CStr(RecordsAffected) & "行"
 
  ' 解放処理
  adoCon.Close
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果]

削除

データベースのテーブル「営業部」の社員番号4の行を削除します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object ' ADOコネクション
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim RecordsAffected As Long  ' 変更された行数
  Dim i As Long
  
  ' 定数
  Const adExecuteNoRecords = &H80
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName
  
  ' SQL文
  SQL = "DELETE FROM 営業部 WHERE 社員番号=4 "
        
  ' SQLの実行
  ' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
  adoCon.Execute SQL, RecordsAffected, adExecuteNoRecords
  
  ' RecordsAffectedには変更された行数が返される
  Debug.Print "変更された行数:" & CStr(RecordsAffected) & "行"
 
  ' 解放処理
  adoCon.Close
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果]

トランザクション

トランザクションを使用して社員番号5の犬養毅を挿入します。そしてメッセージボックスで「はい」を押すコミットします。「いいえ」を押すとロールバックします。

コミットはSQL操作の確定でロールバックはSQL操作を元に戻します。

※本来のロールバックはエラーが発生した場合などに使用します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object ' ADOコネクション
  Dim SQL As String    ' SQL
  Dim AccessFileName As String ' アクセスファイル名
  Dim i As Long
  
  ' 定数
  Const adExecuteNoRecords = &H80
 
  ' 接続するアクセスファイルのフルパス
  AccessFileName = "C:\excel_vba_61.accdb"
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

On Error GoTo ErrorTrap

  ' Accessファイル(*.accdb)を開く
  adoCon.Open "Provider=Microsoft.Ace.OLEDB.12.0;" & _
              "Data Source=" & AccessFileName
  
  ' トランザクションの開始
  adoCon.BeginTrans
  
  ' SQL文
  SQL = "INSERT INTO 営業部 (社員番号,氏名,ひらがな) " & _
        "  VALUES(5,'犬養毅','いぬかいつよし');"
        
  ' SQLの実行
  ' adExecuteNoRecordsは行を返さないのでパフォーマンスが向上
  adoCon.Execute CommandText:=SQL, Options:=adExecuteNoRecords
     
  If MsgBox("コミットするなら「はい」、" & _
             "ロールバックするには「いいえ」を押します。", _
             vbYesNo) = vbYes Then
    ' コミット
    adoCon.CommitTrans
  Else
    ' ロールバック
    adoCon.RollbackTrans
  End If
 
  ' 解放処理
  adoCon.Close
  Set adoCon = Nothing
Exit Sub

ErrorTrap:
    Set adoCon = Nothing
    MsgBox (Err.Description)
End Sub

[結果 - 「はい」を押した場合]

[結果 - 「いいえ」を押した場合]

何度もコードを実行する場合は社員番号5の行を削除して下さい。

ODBCドライバのインストール

Oracle、SQL Server、MySQL、PostgreSQLなどのデータベースにアクセスする為にはODBCドライバをインストールする必要があります。ここではMySQLのODBCドライバのインストール方法を解説しますが、その他のデータベースも同様です。

MySQL コネクタにアクセスして「ODBC Driver for MySQL (Connector/ODBC)」をダウンロードします。

ダウンロードの注意点はWindowsが64bitの場合でもOfficeは32bitですので32bit版をダウンロードします。

また、ダウンロードページで会員登録したくない場合はページ下部の「No thanks, just start my download.」をクリックすれば未登録でダウンロード可能です。

ダウンロードしたファイルをクリックしてインストールします。

ODBCドライバの確認は[コントロール パネル][管理ツール][データ ソース (ODBC)]の「ドライバー」タブで確認できます。

Windowsが64bitの場合は
「C:¥Windows¥SysWOW64¥odbcad32.exe」で確認します。

ODBC接続

ODBCを使用して各データベースにアクセスする方法はADODB.Connection.OpenでDB接続文字列を設定するだけです。ADOですので接続、挿入、更新、削除、トランザクションも前述した方法で実行可能です。

次はDB接続文字列の一覧となります。

接続文字意味
DRIVER{ドライバー名}
SERVERデータベースが実行しているサーバー名
※IPアドレス、サーバー名(localhostなど)
DATABASEアクセスするデータベース名
UIDユーザーID
PWDパスワード

接続文字列のドライバー名の部分は「ODBCデータソースアドミニストレーター」の「ドライバー」タブに表示されているドライバーの名前を指定します。

ODBC接続 - MySQL

ODBCを使用してMySQLに接続します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

  ' ODBC接続
  adoCon.Open _
      "DRIVER={MySQL ODBC 5.3 Unicode Driver};" & _
      " SERVER=サーバー名;" & _
      " DATABASE=データベース名;" & _
      " UID=ユーザーID;" & _
      " PWD=パスワード;"

  adoCon.Close
  Set adoCon = Nothing
End Sub

ODBC接続 - PostgreSQL

ODBCを使用してPostgreSQLに接続します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

  ' ODBC接続
  adoCon.Open _
      "DRIVER={PostgreSQL Unicode};" & _
      " SERVER=サーバー名;" & _
      " DATABASE=データベース名;" & _
      " UID=ユーザーID;" & _
      " PWD=パスワード;"

  adoCon.Close
  Set adoCon = Nothing
End Sub

ODBC接続 - SQL Server

ODBCを使用してSQL Serverに接続します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

  ' ODBC接続
  adoCon.Open _
      "DRIVER={SQL Server};" & _
      " SERVER=サーバー名;" & _
      " DATABASE=データベース名;" & _
      " UID=ユーザーID;" & _
      " PWD=パスワード;"

  adoCon.Close
  Set adoCon = Nothing
End Sub

ODBC接続 - Oracle

ODBCを使用してOracleに接続します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

  ' ODBC接続
  adoCon.Open _
      "DRIVER={Microsoft ODBC for Oracle};" & _
      " SERVER=サーバー名;" & _
      " DATABASE=データベース名;" & _
      " UID=ユーザーID;" & _
      " PWD=パスワード;"

  adoCon.Close
  Set adoCon = Nothing
End Sub

ODBC接続 - Firebird

ODBCを使用してFirebirdに接続します。

Private Sub CommandButton1_Click()
  Dim adoCon As Object
  
  ' ADOコネクションを作成
  Set adoCon = CreateObject("ADODB.Connection")

  ' ODBC接続
  adoCon.Open _
      "DRIVER={Firebird/InterBase(r) driver};" & _
      " SERVER=サーバー名;" & _
      " DATABASE=データベース名;" & _
      " UID=ユーザーID;" & _
      " PWD=パスワード;"

  adoCon.Close
  Set adoCon = Nothing
End Sub

接続文字列のドライバー名の部分はバージョンによって異なる場合がありますので「ODBCデータソースアドミニストレーター」の「ドライバー」タブで確認してください。

エクセル講座のクイックリンク

ホーム 新機能 基本(初級) 基本(中級) 基本(上級) 関数 マクロ VBA TIPS





関連記事



公開日:2015年08月01日 最終更新日:2016年02月21日
記事NO:01255