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

事前準備

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

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

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

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

Wikipediaで補足:データベース表(テーブル) 列(カラム/column/属性) 行(ロウ/row/組) プライマリーキー(主キー) ODBCドライバ

データベース

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-2003	Microsoft.Jet.OLEDB.4.0
Office97	Microsoft.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の行を削除して下さい。

Wikipediaで補足:トランザクションコミットロールバック

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

Pocket
LINE

前の記事：	その他の操作まとめ [ExcelのVBA]
次の記事：	WindowsAPIの使い方 [ExcelのVBA]

公開日：2015年08月01日最終更新日：2016年02月21日

記事NO：01255

1位	VBAでPDFファイルのページを分割(抽出)する[ExcelのTIPS]
2位	リボンを固定にする[Excelの基本操作(初級)]
3位	TRUE関数の使い方 - 論理値 TRUE(真)を返す[Excelの関数]
4位	クエリをVBAで操作・取得する[Excel2016]
5位	エクセルのフリーズ(固まる)を治す方法
6位	時間を15分単位、30分単位で表示する[ExcelのTIPS]
7位	ファイルの操作(Shift-JIS/UTF8対応)[ExcelのVBA]
8位	What-if分析(シナリオ/ゴールシーク/データテーブル)の使い方[Excelの基本操作(上級)]
9位	マルチページの使い方 [ExcelのVBA]
10位	エクセルのオプションを設定する[Excelの基本操作(中級)]
11位	ファイルをオープンする為のファイル番号を返す - FreeFile関数 [ExcelのVBA]
12位	NOT関数の使い方 - 引数の論理値(TRUEまたはFALSE)を逆にして返す[Excelの関数]
13位	プロジェクトエクスプローラーの操作方法[ExcelのVBA]
14位	署名欄、印鑑署名欄を追加する[Excelの基本操作(中級)]
15位	関数のアドレスを返す - AddressOf関数 [ExcelのVBA]
16位	セルの内容を全てクリアする[Excelの基本操作(初級)]
17位	リボンのアドイン、チームタブを削除または非表示にしたい[アドイン開発][ExcelのTIPS]
18位	ローソク足チャートを作成する[Excelの基本操作(上級)]
19位	フォームコントロールの使い方[Excelのマクロ]
20位	Excelから作成者などの個人情報を削除する[ExcelのTIPS]

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

事前準備

データベース

接続

接続(パスワード付き)

挿入

更新

削除

トランザクション

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

ODBC接続

ODBC接続 - MySQL

ODBC接続 - PostgreSQL

ODBC接続 - SQL Server

ODBC接続 - Oracle

ODBC接続 - Firebird

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

関連記事

ピックアップ

最新の記事

人気の記事 (毎週月曜日～)

作詞・作曲(オリジナル曲)

Webアプリ

3Dゲーム

パズルゲーム

スマートフォン

AI(人工知能)

ロボット

ゲーム記事