1.概要

Excelでデータベース(ここではRDB:リレーショナルデータベース)を使用する方法について説明します。 ここではデータベースが無い環境でも試せるように、主にExcelのワークシート上のデータをデータベースとして使用して、ADO(Microsoft Active Data Object)でデータベース接続して操作したサンプルを紹介しています。Excelをデータベース(RDB)として使用することを推薦している訳ではありません。Excel固有の部分もありますが、Excelから他のデータベースを扱う時の参考になると思います。

2.参照設定

ADOを使用するには参照設定で述べたようにMicrosoft ActiveX Data Objects 2.0 Library等の参照の設定が必要です。

3.データベース接続

次のプロシージャは自分自身のBookをデータベースとしてデータベース接続したサンプルです。

Public AdoCon As ADODB.Connection   '←接続の変数は多くのプロシージャから参照
                                    'されるためPublic変数として宣言します。

Sub DB_Connect()
    On Error GoTo Err_Handler
    
    Set AdoCon = New ADODB.Connection  '←接続のObjectを作成しSetステートメント
                                       'で接続変数に参照を与えます。
    AdoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ThisWorkbook.FullName & ";" & _
                "Extended Properties=""Excel 8.0;HDR=Yes"";"
    
    Exit Sub
    
Err_Handler:
    MsgBox "DB Connect Error: " & Err.Number & " : " & Err.Description, _
         vbCritical
    ErrMessage_Set "DB Connect Error: " & Err.Number & " : " & Err.Description
               'ErrMessage_Setは後述

    DB_DisConnect            '←後述
End Sub

データベース接続ステートメントは、以下のように複数行で記述することもできます。

    AdoCon.Provider = "Microsoft.Jet.OLEDB.4.0"
    AdoCon.Properties("Extended Properties") = "Excel 8.0;HDR=Yes"
    AdoCon.Open ThisWorkbook.FullName    '←最後にData Sourceを指定して接続します。

Data Source:ExcelのBookにデータベース接続する場合は、そのファイルをフルパスで指定します。他のBookを指定する場合は 「C:\temp\AddressList.xls」 のようにします。
Extended Properties:ソース データベースの種類(ISAMのバージョン)でExcelのバージョンにより以下のようになります。

ExcelバージョンISAMのバージョン
Excel 95Excel 5.0
Excel 97/2000/2002/2003/2007/2010Excel 8.0

HDR: Header Row(一番上の行)がField名の場合指定します。YesがDefaultで、上記の場合省略できます。

 

代表的なデータベース/接続方法とOLE DB プロバイダ

データベース/接続方法OLE DBプロバイダー
Jet4.0 (Access2000以降)Microsoft.Jet.OLEDB.4.0
SQL ServerSQLOLEDB
OracleMSDAORA
ODBC 経由MSDASQL
DB2IBMDADB2
  • 以下は各データベースへの接続ステートメントの例です。赤字は実際の値になります。
  1. ) Accessの場合
        AdoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=C:\temp\AddressList.mdb;" 
    
  2. ) SQL Serverの場合
        AdoCon.Open "Provider=SQLOLEDB;Password=password;User ID=user_id;" & _
             "Data Source=data_source;DATABASE=myDatabase;"
    
  3. ) Oracleの場合
        AdoCon.Open "Provider=MSDAORA.1;Password=password;User ID=user_id;" & _
             "Data Source=data_source;Persist Security Info=True"
    
  4. ) DB2の場合 DB2の場合ODBCの設定が必要です。ODBCで設定したデータベース別名を使用します。 データベース接続は以下のようにWithステートメントで記述することも出来ます。また、ユーザーID、パスワードをPropertiesで設定することも出来ます。
         With AdoCon
            .ConnectionString = "Provider=IBMDADB2; DSN=DatabaseAlias"
            .Properties.Item("Password") = Cryptogragh(password, False)
            .Properties.Item("User ID") = user_id
            .CursorLocation = adUseClient
            .Open "DatabaseAlias"
        End With
    
    }}

4.終了処理

データの参照、書き込みの説明の前に終了処理について説明します。

  1. ) データベース切断
    切断はCloseメソッドだけの簡単な処理ですが、ちゃんと接続された状態で使用しないとランタイム・エラーを表示してしまいます。また、将来複数のデータベースと接続する場合も考慮して切断のプロシージャを2段階に分けました。
    Sub DB_DisConnect()
    
        Sub_DB_DisConnect AdoCon
        
    End Sub
    
    接続に失敗したときにOn Errorステートメントだけではランタイム・エラーが表示されてしまうので、接続変数に参照が与えられているか、Stateが接続された状態かチェックして処理しています。
    Sub Sub_DB_DisConnect(conn As ADODB.Connection)
        On Error Resume Next
        If Not (conn Is Nothing) Then          '←Set、Newステートメントでオブジェクトの
                                               '参照が与えられているかチェックします。
            If conn.State = adStateOpen Then   '←接続されているかチェックします。
                conn.Close
            End If
        End If
        Set conn = Nothing
    End Sub
    
  2. ) レコードセットのクローズ
    参照の場合はレコードセットのクローズが必要です。レコードセットも接続と同様に、変数に参照が与えられているか、Stateがオープンの状態かチェックして処理します。
    Sub DB_RS_Close(rs As ADODB.Recordset)
        On Error Resume Next
        If Not (rs Is Nothing) Then
            If rs.State = adStateOpen Then
                rs.Close
            End If
            Set rs = Nothing
        End If
    End Sub
    
  3. ) 一括終了処理
    上記のプロシージャを呼びだしエラー処理等で、レコードセットのクローズ、DBの切断とステータスバーのリセットを一括で処理するプロシージャです。
    Sub DB_End(rs As ADODB.Recordset)
    
        DB_RS_Close rs
        DB_DisConnect
        Application.StatusBar = False
        
    End Sub
    
  4. ) エラーメッセー
    エラーメッセージ処理は入力規則にあるサンプルのプロシージャMessage_Setを呼び出しています。
    Sub ErrMessage_Set(msg As String)
        Dim sht As Worksheet
        
        Set sht = ThisWorkbook.Sheets("ErrorMsg")
        Message_Set sht, msg
    
    End Sub
    

5.データ参照

データを参照するためには接続オブジェクトを使用して、Recordsetオブジェクトを操作します。

  1. ) 接続オブジェクトのExecuteメソッドを使用する場合
    Sub example1601(strSQL As String)
        Dim adoRS       As ADODB.Recordset
    
        Set adoRS = AdoCon.Execute(strSQL, adOpenKeyset, adLockReadOnly)
    'または、後ろのパラメータを省略して
        Set adoRS = AdoCon.Execute(strSQL)
        
    End Sub
    
  2. ) RecordsetオブジェクトのOpenメソッドを使用する場合
    先ずRecordsetオブジェクトを作成する必要があります。
    Sub example1602(strSQL As String)
        Dim adoRS       As New ADODB.Recordset
    
        adoRS.Open strSQL, AdoCon, adOpenKeyset, adLockReadOnly
        'example1601と同様に、後ろのパラメータは省略可能です。    
    End Sub
    
    または、以下のように後でSetステートメントでNewしたオブジェクトの参照を与えます。Withステートメントを使用した例です。
    Sub example1603(strSQL As String)
        Dim adoRS       As ADODB.Recordset
    
        Set adoRS = New ADODB.Recordset
    
        With adoRS
            .ActiveConnection = AdoCon   '←接続オブジェクトをセットします。
            .CursorType = adOpenKeyset
            .LockType = adLockReadOnly
            .Open strSQL               '←最後にSQL文の文字列を指定してOpenします。
        End With
        
    End Sub
    
  3. ) SELECT文作成とデータ参照プロシージャExec_Select2ListSubKeyを使用した例
    Sub example1604(sht As Worksheet, address As SortedList)
        Dim strItems    As String
        Dim strTables   As String
        Dim strConds    As String
        Dim strGroup    As String
        Dim strOrderBy  As String
        Dim strSQL      As String    
        
        strItems = "*"
        strTables = "[" & sht.name & "$]"
        strConds = ""
        strGroup = ""
        strOrderBy = ""
    
        strSQL = Select_Sql(strItems, strTables, strConds, strGroup, strOrderBy)
     'または
        strSQL = Select_Sql(strItems, strTables, "", "", "")
     'とも記述できますが、はじめのようにしておくと変更や他のプロシージャ作成の時に楽です。
        '上記はシート名がAddressとすると
        'strSQL = "SELECT * FROM [Address$]"と同じです。
        Exec_Select2ListSubKey AdoCon, strSQL, address, _
                               sht.name & "でエラーが発生しました。"
        
    End Sub
    
    strTablesのところはSheet名、Rangeアドレス、Name Rangeを指定します。Sheet名の場合は上記のようにシート名の後に$を付加します。
    Rangeアドレスの場合
    strTables = "[A1:F15]"
    
    シート名を指定したRangeアドレスの場合(対象のシートが1番目に無い場合)
    strTables = "[Address$A1:F15]"
    
    Name Rangeの場合
    strTables = "[住所]"
    
    また、example1604の例のように条件が無くシート全体のデータを参照する場合は、 SELECT文ではなく次のように単にシートを指定することもできます。
    strSQL = "[Address$]"
    
    でもこれでは、SQLを使用するという目的から外れてしまいます。

6.データ書込み

データの書込みはSQLのINSERTステートメントを実行します。Excelはデータがあった行を覚えていて、シート内のデータをクリアーしてもデータのあった次の行からデータが追加されます。初めからデータを追加する場合は、データのあった行を削除する必要があります。

Sub example1605()
    Dim strSQL      As String
    strSQL = "INSERT INTO [Address$] (番号,郵便番号,苗字,名前,県,市) " & _
             "VALUES (15,'320-8540','山田','伍郎','栃木','宇都宮'"
    AdoCon.Execute strSQL
End Sub

以下は、エラー処理を含めてSQLステートメント作成と実行を分けてプロシージャ化した例です。

Sub example1606()
    Dim strTable    As String
    Dim strItems    As String
    Dim strVALUEs   As String

    strTable = "[Address$]"
    strItems = "番号,郵便番号,苗字,名前,県,市"
    strVALUEs = "15,'320-8540','山田','伍郎','栃木','宇都宮'"

    Insert_Data strTable, strItems, strVALUEs 

End Sub
Sub Insert_Data(strTable As String, strItems As String, strVALUEs As String)
    Dim strSQL As String

    strSQL = "INSERT INTO " & strTable & " (" & strItems & ") " & _
                "VALUES (" & strVALUEs & ")"
    DB_Execute strSQL
                
End Sub
Sub DB_Execute(strSQL As String)
    
    On Error GoTo Err_Handler
    
    AdoCon.Execute strSQL
                
    Exit Sub
           
Err_Handler:
    MsgBox "DB_Execute Error: " & Err.Number & " : " & Err.Description, _
         vbCritical
    
    DB_DisConnect

End Sub

7.データ更新

データの更新はSQLのUPDATEステートメントを実行します。

Sub example1607()
    Dim strSQL      As String
    strSQL = "UPDATE [Address$] " & _
             "SET 郵便番号 = '320-852',県 = '栃木',市 = '宇都宮' " & _
             "WHERE 番号 = '10'"
    AdoCon.Execute strSQL
End Sub

以下は、エラー処理を含めてSQLステートメント作成と実行を分けてプロシージャ化した例です。

Sub example1608()
    Dim strTable    As String
    Dim strVALUEs   As String
    Dim strCONDs    As String
 
    strTable  = "[Address$]"
    strVALUEs = "郵便番号 = '320-852',県 = '栃木',市 = '宇都宮'"
    strCONDs = "番号 = '10'"

    Update_Data strTable, strVALUEs, strCONDs

End Sub
Sub Update_Data(strTable As String, strVALUEs As String, strCONDs As String)
    Dim strSQL As String

    strSQL = "UPDATE " & strTable & " SET " & strVALUEs & " WHERE " & strCONDs
    
    DB_Execute strSQL
                
End Sub

8.データ削除

Jet OLE DB プロバイダを使用すると(つまりExcelでは)、Excel ブックのレコードの挿入および更新はできますが、DELETE の操作を行うことはできません。これはExcel固有の制限です。 DELETE 操作を実行すると、次のエラー メッセージが表示されます。

 この ISAM では、リンク テーブル内のデータを削除することはできません。 

ここでは一般論(他のデータベースを扱う場合)として、データの削除について説明します。削除は次のようなSQLのDELETEステートメントを実行します。

Sub example1609()
    Dim strSQL      As String
    strSQL = "DELETE FROM スキーマ.テーブル名 WHERE 番号 = '11'"
    AdoCon.Execute strSQL
End Sub

以下は、エラー処理を含めてSQLステートメント作成と実行を分けてプロシージャ化した例です。

Sub example1610()
    Dim strTable    As String
    Dim strCONDs    As String
 
    strTable = "スキーマ.テーブル名"
    strCONDs = "番号 = '11'"

    Delete_Data strTable, strCONDs

End Sub
Sub Delete_Data(strTable As String, strCONDs As String)
    Dim strSQL As String

    strSQL = "DELETE FROM " & strTable & " WHERE " & strCONDs
    
    DB_Execute strSQL
                
End Sub

9.ユーティリティー

  1. ) SELECTステートメント作成:Select_Sql
    Function Select_Sql(Items As String, Tables As String, Conditions As String, _
                            Group As String, OrderBy As String) As String
    
        Select_Sql = "SELECT " & Items & " FROM " & Tables
        If Conditions <> "" Then
            Select_Sql = Select_Sql & " WHERE " & Conditions
        End If
        If Group <> "" Then
            Select_Sql = Select_Sql & " GROUP BY " & Group
        End If
        If OrderBy <> "" Then
            Select_Sql = Select_Sql & " ORDER BY " & OrderBy
        End If
    
    End Function
    
    以下のユーティリティーはOracleを扱った時に作成したものです。他のDBでも同様の関数があれば利用できると思います。
  2. ) Tiemstamp変換(Oracle):TO_TS
    OracleでTO_DATE関数を使用して文字列をTimestamp書式に変換する場合。
    Function TO_TS(timestamp As String) As String
    
        TO_TS = "TO_DATE('" & timestamp & "','YYYY/MM/DD HH24:MI:SS')"
    
    End Function
    
  3. ) Date変換(Oracle):TO_DT
    OracleでTO_DATE関数を使用して文字列を日付の書式に変換する場合。
    Function TO_DT(timestamp As String) As String
    
        TO_DT = "TO_DATE('" & timestamp & "','YYYY/MM/DD')"
    
    End Function
    
  4. ) Timestamp2Char書式(Oracle):TO_TS2CHR
    OracleでTO_CHAR関数を使用してTimestampの結果を所定の文字列書式に変換する場合。 変換しない場合、例えば 2012/1/1 0:0:0となるのを2012/01/01 00:00:00に変換します。結果をExcelで文字列として大小を比較することが出来ます。
    Function TO_TS2CHR(item As String) As String
    
        TO_TS2CHR = "TO_CHAR(" & item & ",'YYYY/MM/DD HH24:MI:SS')"
    
    End Function
    

10.使用例

  1. ) 今までに紹介したサンプルを組み合わせて実行してみましょう。AddressとWorkという名前のワークシートを事前に作成して下さい。参照設定をします。標準モジュールに必要な変数の宣言、プロシージャ、ファンクション等をコピーして、example1611を実行します。
    Option Explicit
        Public Type Address
            Number      As Integer
            ZipCode     As String
            LastName    As String
            FirstName   As String
            Ken         As String
            Shi         As String
        End Type
    
        Public addBook() As Address
        Const RowMessage As Integer = 1
        Const ClmMessage As Integer = 1
        Public AdoCon As ADODB.Connection
    
    Sub example1611()
        Dim sht As Worksheet
        Dim address As New SortedList
    
        Set sht = ActiveWorkbook.Sheets("Address")
        address.Clear
    
        Address_Set            'テストデータ作成
        'Field行にField名の書込み
        sht.Cells(1, 1) = "番号"
        sht.Cells(1, 2) = "郵便番号"
        sht.Cells(1, 3) = "苗字"
        sht.Cells(1, 4) = "名前"
        sht.Cells(1, 5) = "県"
        sht.Cells(1, 6) = "市"
        
        DB_Connect
        If AdoCon Is Nothing Then  'AdoConの値により接続成功の判断をします。
            Exit Sub
        End If
    
        example1612 sht            'データの書込み。
    
        example1604 sht, address   'データの参照
    
        SortedListSubKey_Debug  address     '結果のイミディエイトウィンドウへの表示
    
        example1608                'データの更新。
        example1606                'データの書込み。
    
        DB_DisConnect
    End Sub
    
    Sub example1612(sht As Worksheet)
        Dim strTable As String
        Dim strCOLUMs As String
        Dim strVALUEs As String
        Dim i As Integer
        
        strTable = "[" & sht.name & "$]"
        strCOLUMs = "番号,郵便番号,苗字,名前,市,県"
        
        For i = 1 To UBound(addBook)
            strVALUEs = addBook(i).Number
            strVALUEs = strVALUEs & ",'" & addBook(i).ZipCode & "'"
            strVALUEs = strVALUEs & ",'" & addBook(i).LastName & "'"
            strVALUEs = strVALUEs & ",'" & addBook(i).FirstName & "'"
            strVALUEs = strVALUEs & ",'" & addBook(i).Ken & "'"
            strVALUEs = strVALUEs & ",'" & addBook(i).Shi & "'"
            Insert_Data strTable, strCOLUMs, strVALUEs
       Next i
    
    End Sub
    
  2. ) 結果(その1)
    実行結果はイミディエイトウィンドウに以下のように表示されます。SortedListのところで述べたようにSortされた1stKeyの順番に注意して下さい。
    1stKey[1] 2ndKey[060-8588][山田][一郎][北海道][札幌]
    1stKey[10] 2ndKey[960-8670][鈴木][十郎][福島][福島]
    1stKey[11] 2ndKey[950-8570][山田][士郎][新潟][新潟]
    1stKey[12] 2ndKey[950-8570][山田][王郎][新潟][新潟]
    1stKey[13] 2ndKey[320-8501][鈴木][圭郎][栃木][宇都宮]
    1stKey[14] 2ndKey[320-8501][山田][田郎][栃木][宇都宮]
    1stKey[2] 2ndKey[030-8570][山田][二郎][青森][青森]
    1stKey[3] 2ndKey[020-8570][鈴木][三郎][岩手][盛岡]
    1stKey[4] 2ndKey[010-8570][山田][四郎][秋田][秋田]
    1stKey[5] 2ndKey[980-8570][鈴木][五郎][宮城][仙台]
    1stKey[6] 2ndKey[990-8570][鈴木][六郎][山形][山形]
    1stKey[7] 2ndKey[990-8570][山田][七郎][山形][山形]
    1stKey[8] 2ndKey[990-8570][山田][八郎][山形][山形]
    1stKey[9] 2ndKey[960-8670][鈴木][九郎][福島][福島]
    
  3. ) (その2)
    example1604
       strItems = "苗字,県,番号,名前"
    とすると結果は以下のようになります。名前と県別にデータが集計されています。
    1stKey[山田] 2ndKey[山形][7][七郎][8][八郎]
    1stKey[山田] 2ndKey[秋田][4][四郎]
    1stKey[山田] 2ndKey[新潟][11][士郎][12][王郎]
    1stKey[山田] 2ndKey[青森][2][二郎]
    1stKey[山田] 2ndKey[栃木][14][田郎]
    1stKey[山田] 2ndKey[北海道][1][一郎]
    1stKey[鈴木] 2ndKey[岩手][3][三郎]
    1stKey[鈴木] 2ndKey[宮城][5][五郎]
    1stKey[鈴木] 2ndKey[山形][6][六郎]
    1stKey[鈴木] 2ndKey[栃木][13][圭郎]
    1stKey[鈴木] 2ndKey[福島][9][九郎][10][十郎]
    
    example1604
       strConds = "郵便番号 ='990-8570'"
    とすると結果は以下のように郵便番号が'990-8570'のデータだけが表示されます。
    1stKey[6] 2ndKey[990-8570][鈴木][六郎][山形][山形]
    1stKey[7] 2ndKey[990-8570][山田][七郎][山形][山形]
    1stKey[8] 2ndKey[990-8570][山田][八郎][山形][山形]
    

最終更新のRSS
Last-modified: 2014-03-11 (火) 01:58:42 (3859d)