【ExcelVBA】ADOX の操作（mdb作成～レコードをインデックスで検索迄）

2019.07.28ADOXの操作,ExcelVBA

1. ADOX 関連ライブラリーの有効化
2. MDBファイルの作成
3. テーブルの作成・削除・表示
4. インデックスの作成・削除・表示
5. レコードの追加・削除・変更・検索(SQL)
6. レコードをインデックスで検索
- 6.1. Seekメソッド使用

ADOX 関連ライブラリーの有効化

下記のライブラリーがADOX関連のライブラリーになります。

Microsoft ActiveX Data Objects x.x Library
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

※インデックスで検索した結果を表示させたい場合は、「６．レコードをインデックスで検索」を参照のこと。

レコードの件数

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. レコードの追加

ディスカッション

コメント一覧

まだ、コメントがありません