【ExcelVBA】ADOX の操作(mdb作成~レコードをインデックスで検索 迄)
目次
ADOX 関連ライブラリーの有効化
下記のライブラリーがADOX関連のライブラリーになります。
Microsoft ActiveX Data Objects x.x Library
Microsoft ADO Ext. x.x for DDL and Security
Microsoft ADO Ext. x.x for DDL and Security
- マクロの編集画面(Microsft Visual Basic for Applications)を開く。
- 画面上部の「ツール」→「参照設定」
- 「Microsoft ActiveX Data Objects x.x Library」有効化する。
- 「Microsoft ADO Ext. x.x for DDL and Security」有効化する。
MDBファイルの作成
テーブル作成の前に、mdbファイルを作成する。Sub mdbファイルの作成() Dim myCat As ADOX.Catalog, conStr As String, DBName As String Set myCat = New ADOX.Catalog DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myCat.Create conStr Set myCat = Nothing End Sub
テーブルの作成・削除・表示
テーブルの作成
mdbファイルを作成したら、支払実績テーブルを新規作成する。Sub テーブル作成() Dim myCon As New ADODB.Connection Dim conStr As String, DBName As String, mySQL As String '接続準備 DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName 'テーブル作成SQL mySQL = "CREATE TABLE 支払実績 (" & _ "個人ID TINYINT NOT NULL PRIMARY KEY," & _ "氏名 CHAR(40)," & _ "支払 MONEY" & _ ");" 'データベース接続とSQL実行 With myCon .Open conStr .Execute mySQL .Close End With Set myCon = Nothing End Sub
テーブルの削除
支払実績テーブルを削除する。Sub テーブル削除() Dim myCon As New ADODB.Connection Dim conStr As String, DBName As String DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName With myCon .Open conStr .Execute "DROP TABLE 支払実績;" .Close End With Set myCon = Nothing End Sub
テーブルの表示
MyDB.mdbに存在するテーブルをイミディエイト画面に表示する。Sub テーブル一覧() Dim myCat As ADOX.Catalog, conStr As String, DBName As String Dim myTbl As ADOX.Table Set myCat = New ADOX.Catalog DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myCat.ActiveConnection = conStr For Each myTbl In myCat.Tables If myTbl.Type = "TABLE" Then Debug.Print myTbl.Name End If Next myTbl Set myCat = Nothing End Sub
インデックスの作成・削除・表示
インデックスの作成
支払実績テーブルの個人IDフィールに個人IDXインデックスを作成する。(個人IDフィールドはプライマリーキーで作成されているものとする。)
Sub インデックス作成() Dim myCon As New ADODB.Connection Dim idx As New ADOX.Index Dim conStr As String, DBName As String, mySQL As String '接続準備 DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName 'インデックス作成SQL mySQL = "CREATE INDEX 個人IDX ON 支払実績 (個人ID);" 'データベース接続とSQL実行 With myCon .Open conStr .Execute mySQL .Close End With Set myCon = Nothing End Sub
インデックスの削除
支払実績テーブル内にあるインデックスを全部削除する。Sub インデックスの削除() Dim myCat As ADOX.Catalog, conStr As String, DBName As String Dim myTbl As ADOX.Table Dim myIdx As ADOX.Index DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName Set myCat = New ADOX.Catalog myCat.ActiveConnection = conStr Set myTbl = myCat.Tables!支払実績 continue: For Each myIdx In myTbl.Indexes myTbl.Indexes.Delete myIdx.Name GoTo continue Next Set myTbl = Nothing Set myCat = Nothing End Sub
インデックスの表示
支払実績テーブル内にあるインデックス名をイミディエイト画面に表示する。Sub インデックス表示() Dim myCat As ADOX.Catalog, conStr As String, DBName As String Dim myTbl As ADOX.Table Dim myIdx As ADOX.Index DBName = ThisWorkbook.Path & "\MyDB.mdb" conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName Set myCat = New ADOX.Catalog myCat.ActiveConnection = conStr Set myTbl = myCat.Tables!支払実績 For Each myIdx In myTbl.Indexes Debug.Print myIdx.Name Next myIdx Set myTbl = Nothing Set myCat = Nothing End Sub
レコードの追加・削除・変更・検索(SQL)
レコードの追加
支払実績テーブルにレコードをSQLで追加する。Sub レコード追加() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer DBName = ThisWorkbook.Path & "\MyDB.mdb" myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open "支払実績", myCon, adOpenDynamic, adLockPessimistic '新規レコード追加 With myRS .AddNew !個人ID = 17 !氏名 = "福沢 諭吉" !支払 = 10000 .Update End With myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub
レコードの削除
個人IDが17のレコードをSQLで検索して、削除する。Sub レコード削除() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer Dim SQL As String DBName = ThisWorkbook.Path & "\MyDB.mdb" SQL = "SELECT 個人ID,氏名,支払 FROM 支払実績 where 個人ID = 17" '個人IDが17のレコード検索 myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open SQL, myCon, adOpenDynamic, adLockPessimistic Do Until myRS.EOF '削除対象レコードをイミディエイト画面に表示 Debug.Print myRS!個人ID, myRS!氏名, myRS!支払 'レコード削除 myRS.Delete '次のレコードへ myRS.MoveNext Loop myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub
レコードの変更
氏名が"福沢 諭吉"のレコードをSQLで検索して、"渋沢 栄一"に変更する。Sub レコード変更() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer Dim SQL As String DBName = ThisWorkbook.Path & "\MyDB.mdb" SQL = "SELECT 氏名 FROM 支払実績 where 氏名 = ""福沢 諭吉""" '氏名が"福沢諭吉"のレコードを検索 myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open SQL, myCon, adOpenDynamic, adLockPessimistic Do Until myRS.EOF With myRS 'レコード変更(氏名) !氏名 = "渋沢 栄一" '次のレコードへ .MoveNext End With Loop myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub
レコードの検索(SQL)
個人IDが17のレコードをSQLで検索して、イミディエイト画面に表示Sub レコード検索() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer Dim SQL As String DBName = ThisWorkbook.Path & "\MyDB.mdb" SQL = "SELECT 個人ID,氏名,支払 FROM 支払実績 where 個人ID = 17" '個人IDが17のレコード検索 myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open SQL, myCon, adOpenDynamic, adLockPessimistic Do Until myRS.EOF 'レコードをイミディエイト画面に表示 Debug.Print myRS!個人ID, myRS!氏名, myRS!支払 'レコードをシートに書き出す(書き出しはシートの2行目以降です。使用する場合はコメント解除してください) 'With ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) ' Cells(.Row + 1, 1).Value = myRS!個人ID ' Cells(.Row + 1, 2).Value = myRS!氏名 ' Cells(.Row + 1, 3).Value = myRS!支払 'End With '次のレコードへ myRS.MoveNext Loop myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub※インデックスで検索した結果を表示させたい場合は、「6.レコードをインデックスで検索」を参照のこと。
レコードの件数
Sub レコード件数() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer Dim SQL As String DBName = ThisWorkbook.Path & "\MyDB.mdb" SQL = "SELECT COUNT(*) AS cnt FROM 支払実績" myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open SQL, myCon, adOpenDynamic, adLockPessimistic 'レコード件数をイミディエイト画面に表示 Debug.Print myRS!cnt myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub
レコードをインデックスで検索
Seekメソッド使用
Sub インデックス検索() Dim myCon As New ADODB.Connection, myRS As New ADODB.Recordset Dim DBName As String, i As Integer Dim SQL As String DBName = ThisWorkbook.Path & "\MyDB.mdb" myCon.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBName myRS.Open "支払実績", myCon, adOpenStatic, adLockReadOnly, adCmdTableDirect 'Indexプロパティにインデックス名「個人IDX」を設定 myRS.Index = "個人IDX" 'インデックス検索実行 myRS.Seek 17 '個人IDXが17のレコードを検索する If myRS.EOF Then MsgBox "該当レコードなし" Else MsgBox myRS!個人ID & vbCr & myRS!氏名 & vbCr & myRS!支払 End If myRS.Close myCon.Close Set myRS = Nothing Set myCon = Nothing End Sub
レコードをインデックスで検索 するには、以下の項目を完了しておく必要があります。
1. ADOX 関連ライブラリーの有効化
2. MDBファイルの作成
3.1. テーブルの作成
4.1. インデックスの作成
5.1. レコードの追加
1. ADOX 関連ライブラリーの有効化
2. MDBファイルの作成
3.1. テーブルの作成
4.1. インデックスの作成
5.1. レコードの追加
ディスカッション
コメント一覧
まだ、コメントがありません