vba を使用した excel と accessの接続は、ADOで簡単・高速、軽快に動作します！
vba excel access 簡単です！ Homeへ
ado
vbaで簡単に使えるADO(ActiveX Data Objects)は、高速・小容量メモリのため軽快に動作します。
ADOでレコードの追加・更新・削除・検索が簡単にできます。
１． まず始めに、ADOで接続するために Dim文でProgIDが「ADODB.Connection」であるオブジェクト変数を宣言します。
''VBEの「ツール」ー「参照設定」で Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
※ 詳細は、「接続」記事を参照願います。
２． 次に、レコードセットに対してADOでは、 Dim文で ADODB.Recordset オブジェクト変数を宣言します。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。
３． ADOの接続は、Set文でオブジェクトの参照を代入した変数に対して行われます。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。
４． カーソル（Cursor）は、一連のデータに順にアクセスする際の検索条件および「現在位置」を保持するデータベースの要素です。
※ カーソル（データベース） Wikipediaより引用
次の図の黄色の部分は、一連のデータの３番目を「現在位置」としたカレントレコードです。
図の右辺には、カーソルタイプに従い、カレントレコードを移動させるメソッドを５種類示しました。
レコードの位置は、Move n によりカレントレコードから「移動するレコード数」 ｎ 分移動できます。
カーソルを開いたとき、レコードの位置は、先頭レコードですので、 ｒecordｓet.Move ２ により ３番目のレコードに移動します。
RecordSetオブジェクトの Move メソッドを使用してカレントレコードの位置を移動できます。
MoveNextにより次のレコードに移動でき、MovePreviousにより前のレコードに移動できることを示します。
また、MoveFirstにより先頭レコードに移動でき、MoveLastにより最終レコードに移動できることを示しています。
ただし、レコード位置を１つの方向・順方向にのみ移動制限するタイプがあります。
※ 詳細は、後半の （３）カーソルタイプの説明を参照願います。
上記の一連のデータは、レコードセットの「カーソル」を開いた結果として得ることができます。
次のステートメントは、レコードセット・オブジェクトのメソッド Ｏｐｅｎ の引数(省略可能）を示しています。
recordset.Open Source, ActiveConnection, CursorType, LockType, Options
（１） 最初の引数 Sourceは、ＳＱＬ文、テーブル名、クエリ名、有効な Command オブジェクトなどを指定します。
（２） ActiveConnectionは、オブジェクトの参照を代入した変数または接続情報文字列を指定します。
（３） CursorTypeは、次の３つのカーソルの機能を指定する定数です。
【a】 カレントレコード位置の移動性
【b】 データの更新可能性
【c】 他のユーザーが行ったデータベースの変更結果の可視性
これにより一連のデータにアクセスする方向や追加・編集・削除・検索したレコードが参照可能か否かを明確にできます。
このカーソルタイプには、次の種類があります。
１） adOpenForwardOnly ： 既定値 順方向のみ
レコード位置が順方向にのみ移動でき、個々のレコードを検索できず、レコード数を返しません。
参照のみ可能であり、高速に動作できます。 他のユーザーによる更新レコードを表示できません。
２） adOpenStatic ： 静的
レコード位置が順・逆の２方向に移動でき、Findを使用でき、レコード数を返すことができます。
他のユーザーによる更新レコードを表示できません。
３） adOpenDynamic ： 動的
レコード位置を全ての方向に移動でき、Findを使用でき、レコード数を返すことができます。
レコードの全ての更新を表示できます。
４） adOpenKeyset ： キーセット
レコード位置を全ての方向として順・逆方向に移動でき、Findを使用でき、レコード数を返します。
既に記録されていたレコードの更新を表示できます。
削除されたレコード、新しく記録されたレコードの更新を表示出来ません。
５） adOpenUnspecified ： 指定せず
カーソルの種類を指定しません。
（４） LockTypeは、編集時 recordset を開くときに使用するロック（同時作用）の種類を指定する定数です。
このロックタイプには、次の種類があります。
１） adLockReadOnly ： 既定値 読み取り専用、データを変更できません。
２） adLockPessimistic ： レコード単位の排他的ロック、編集直後にデータソースでレコードをロックします。
３） adLockOptimistic ： レコード単位の共有的ロック、Update メソッドを呼び出した場合のみロックします。
４） adLockBatchOptimistic ： 共有的バッチ更新を示しています。 バッチ更新モードの場合に設定します。
５） adLockUnspecified ： 指定せず。 ロックの種類を指定しません。
（５） Optionsは、省略可能です。
※ 詳細は、Ｍｉｃｒｏｓｏｆｔ Ａｃｃｅｓｓ の開発者リファレンスを参照願います。
５． ADOによるレコードの追加・編集・削除・検索・抽出
先ず最初にVBEの「ツール」−「参照設定」で Microsoft ActiveX Data Objects X.XX Libraryにチェックを入れます。
ADOのレコードに対する機能は、主にRecordSetオブジェクトのメソッドまたはプロパティにより実現できます。
（１）追加 AddNew、Updateメッソドを使用します。
Sub ado_add()
Dim oCn As ADODB.Connection
Dim oRs As ADODB.Recordset
Set oCn = New ADODB.Connection
Set oRs = New ADODB.Recordset
oCn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office14\ACCWIZ\Imp_Accdb.accdb;"
oRs.Open "SELECT * FROM Sheet1;", oCn, adOpenDynamic, adLockOptimistic
'*************
oRs.AddNew
For j = 1 To (oRs.Fields.Count - 1)
oRs.Fields(j).Value = Cells(1, j).Value
Next j
oRs.Update
'*************
oRs.Close
oCn.Close
Set oRs = Nothing
Set oCn = Nothing
End Sub
上記のマクロは、アクティブシートの１行目をレコードに追加しています。
最初のフィールドは、主キーとして使用しているので、２番目のフィールドから入力し、最終レコードの次に追加します。
ADOのCloseメソッドに対する注意があります。
このメソッドを使用してRecordSetを閉じる前にConnection オブジェクトを閉じる場合があります。
その場合、その接続に関連するアクティブな Recordset オブジェクトもすべて閉じてしまいます。
この結果、保留中の更新または編集がキャンセルされますので、注意が必要です。
最初に RecordSet オブジェクトを Close し、保留中の更新または編集を完了しなければなりません。
（２）編集 ADOのUpdateメッソドを使用します。
上記のマクロの一部を「編集」用に修正しました。
'*************
Do
For j = 1 To (oRs.Fields.Count - 1)
oRs.Fields(j).Value = oRs.Fields(j).Value & "z"
Next j
oRs.Update
oRs.MoveNext
Loop Until oRs.EOF
'*************
このマクロは、１番目のレコードから最終のレコードまでデータの末尾に z を追加します。
（３）削除 ADOのDeleteメソッドを使用します。
上記のマクロの一部を「削除」用に修正しました。
'*************
Do
oRs.Delete
oRs.MoveNext
Loop Until oRs.EOF
'*************
このマクロは、開いたレコードセットのすべてのレコードを削除します。
（４）検索 ADOのFindメソッドを「ただ１つのフィールド名」に対して使用します。
書式は、Recordset.Find 検索条件, SkipRows, SearchDirection, Start です。
１）検索条件は、列の名称１つだけ指定できます。
２）SkipRowsは、読み飛ばすレコード数を指定します。省略時、カレントレコードから検索します。
３）SearchDirectionには、adSearchBackward, adSearchForward の２つがあります。
省略可能です。検索が成功しなかった場合、それぞれBOF、EOFに移動して止まります。
４）Stratは、検索開始位置を指定できます。省略すると、カレントレコードから検索を開始します。
上記のマクロの一部を「検索」用に修正しました。
'*************
oRs.MoveFirst '検索は、開始するカレントレコードを明確に設定する必要があります。
Do
oRs.Find oRs.Fields(1).Name & " Like '%333%' "
If Not (oRs.EOF) Then
Debug.Print oRs.Fields(1).Value
Else
Exit Do
End If
oRs.MoveNext
Loop Until oRs.EOF
'*************
このマクロは、２番目のフィールドデータに文字列 333 を含む場合、イミディエイトウィンドウに出力します。
（５）抽出 ADOのFilterプロパティを使用します。複数フィールドでも使用できます。
上記のマクロの一部を「抽出」用に修正しました。
'*************
oRs.Filter = "Data1 Like '%333%' OR Data2 Like '%333%' "
If oRs.RecordCount = 0 Then
MsgBox "抽出できるレコードがありません"
Else
Do
Debug.Print oRs.Fields(1).Value
oRs.MoveNext
Loop Until oRs.EOF
End If
oRs.Filter = "" '空白を代入し、フィルタを解除します。
'*************
このマクロは、２、３番目のフィールドデータに文字列 333 を含む場合、イミディエイトウィンドウに出力します。
５． ADO X によりテーブルやフィールドを新しく作成する方法
ADOには、ADOX(ADO Extensions for DDL and Security)と呼ばれるADOの拡張コンポーネントがあります。
このADOXには、データベースの構造を保守するための各種プロパティとメソッドがサポートされています。
ADOXにより、Table や Field を新規に作成することができます。
Office 2010 をインストールしている環境で、実際にテーブルとフィールドを作成し、削除するサンプルを示しました。
先ず最初にVBEの「ツール」−「参照設定」で Microsoft ADO Ext. X.X for DDL and Security にチェックを入れます。
Sub Main_table_add_delete()
On Error GoTo CreateTableError
Dim tbl As New Table
Dim cat As New ADOX.Catalog
'カタログを開きます。
cat.ActiveConnection = "Provider='Microsoft.ACE.OLEDB.12.0';" & _
"Data Source='C:\Documents and Settings\Administrator\My Documents\ノースウィンド.accdb';"
tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl
Debug.Print "テーブル 'MyTable' を追加しました。."
'実際に作成したテーブルを削除します。
cat.Tables.Delete tbl.Name
Debug.Print "テーブル 'MyTable' を削除しました。"
'接続を解除し、リソースメモリを解放します。
Set cat.ActiveConnection = Nothing
Set cat = Nothing
Set tbl = Nothing
Exit Sub
CreateTableError:
Set cat = Nothing
Set tbl = Nothing
If Err <> 0 Then
MsgBox Err.Source & "エラー： " & Err.Description, , "Error"
End If
End Sub
※ 詳細は、Access 2010の開発者用リファレンス「１５章： ADOXに関する基本事項」を参照願います。
なお、接続は、"Provider='Microsoft.ACE.OLEDB.12.0';"であることが必要です。
ADOとオートメーションのvba を使用した excel と access の接続を活用しましょう！
vba excel access 簡単です！ Homeへ
