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 95 | Excel 5.0 |
Excel 97/2000/2002/2003/2007/2010 | Excel 8.0 |
HDR: Header Row(一番上の行)がField名の場合指定します。YesがDefaultで、上記の場合省略できます。
代表的なデータベース/接続方法とOLE DB プロバイダ
データベース/接続方法 | OLE DBプロバイダー |
Jet4.0 (Access2000以降) | Microsoft.Jet.OLEDB.4.0 |
SQL Server | SQLOLEDB |
Oracle | MSDAORA |
ODBC 経由 | MSDASQL |
DB2 | IBMDADB2 |
- 以下は各データベースへの接続ステートメントの例です。赤字は実際の値になります。
- ) Accessの場合
AdoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\temp\AddressList.mdb;"
- ) SQL Serverの場合
AdoCon.Open "Provider=SQLOLEDB;Password=password;User ID=user_id;" & _
"Data Source=data_source;DATABASE=myDatabase;"
- ) Oracleの場合
AdoCon.Open "Provider=MSDAORA.1;Password=password;User ID=user_id;" & _
"Data Source=data_source;Persist Security Info=True"
- ) 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.終了処理 †
データの参照、書き込みの説明の前に終了処理について説明します。
- ) データベース切断
切断は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
- ) レコードセットのクローズ
参照の場合はレコードセットのクローズが必要です。レコードセットも接続と同様に、変数に参照が与えられているか、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
- ) 一括終了処理
上記のプロシージャを呼びだしエラー処理等で、レコードセットのクローズ、DBの切断とステータスバーのリセットを一括で処理するプロシージャです。
Sub DB_End(rs As ADODB.Recordset)
DB_RS_Close rs
DB_DisConnect
Application.StatusBar = False
End Sub
- ) エラーメッセー
エラーメッセージ処理は入力規則にあるサンプルのプロシージャ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オブジェクトを操作します。
- ) 接続オブジェクトの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
- ) 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
- ) 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.ユーティリティー †
- ) 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でも同様の関数があれば利用できると思います。
- ) 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
- ) 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
- ) 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.使用例 †
- ) 今までに紹介したサンプルを組み合わせて実行してみましょう。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
- ) 結果(その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][鈴木][九郎][福島][福島]
- ) (その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][山田][八郎][山形][山形]