これらのキーワードがハイライトされています：ISAM

１．概要 †

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

↑

２．参照設定 †

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

↑

３．データベース接続 †

次のプロシージャは自分自身の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
    }}
↑

４．終了処理 †

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

  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
↑

５．データ参照 †

データを参照するためには接続オブジェクトを使用して、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を使用するという目的から外れてしまいます。
↑

６．データ書込み †

データの書込みは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
↑

７．データ更新 †

データの更新は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
↑

８．データ削除 †

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
↑

９．ユーティリティー †

  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
↑

１０．使用例 †

  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. ) 結果（その１）
    実行結果はイミディエイトウィンドウに以下のように表示されます。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. ) （その２）
    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 (3697d)