ado

vba を使用した excel と accessの接続は、ADOで簡単・高速、軽快に動作します!


vba excel access 簡単です! Homeへ
ado

vbaで簡単に使えるADO(ActiveX Data Objects)は、高速・小容量メモリのため軽快に動作します。


ADOでレコードの追加・更新・削除・検索が簡単にできます。

1. まず始めに、ADOで接続するために Dim文でProgIDが「ADODB.Connection」であるオブジェクト変数を宣言します。
''VBEの「ツール」ー「参照設定」で Microsoft ActiveX Data Objects X.XX Library にチェックを入れます。
※ 詳細は、「接続」記事を参照願います。

2. 次に、レコードセットに対してADOでは、 Dim文で ADODB.Recordset オブジェクト変数を宣言します。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。

3. ADOの接続は、Set文でオブジェクトの参照を代入した変数に対して行われます。
※ 詳細は、「エクスポート」、「連携」記事を参照願います。

4. カーソル(Cursor)は、一連のデータに順にアクセスする際の検索条件および「現在位置」を保持するデータベースの要素です。
※ カーソル(データベース) Wikipediaより引用
 次の図の黄色の部分は、一連のデータの3番目を「現在位置」としたカレントレコードです。
図の右辺には、カーソルタイプに従い、カレントレコードを移動させるメソッドを5種類示しました。
レコードの位置は、Move n によりカレントレコードから「移動するレコード数」 n 分移動できます。
カーソルを開いたとき、レコードの位置は、先頭レコードですので、 recordset.Move 2 により 3番目のレコードに移動します。
RecordSetオブジェクトの Move メソッドを使用してカレントレコードの位置を移動できます。
MoveNextにより次のレコードに移動でき、MovePreviousにより前のレコードに移動できることを示します。
また、MoveFirstにより先頭レコードに移動でき、MoveLastにより最終レコードに移動できることを示しています。
ただし、レコード位置を1つの方向・順方向にのみ移動制限するタイプがあります。 
※ 詳細は、後半の (3)カーソルタイプの説明を参照願います。

カーソル

上記の一連のデータは、レコードセットの「カーソル」を開いた結果として得ることができます。
次のステートメントは、レコードセット・オブジェクトのメソッド Open の引数(省略可能)を示しています。

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

(1) 最初の引数 Sourceは、SQL文、テーブル名、クエリ名、有効な Command オブジェクトなどを指定します。
(2) ActiveConnectionは、オブジェクトの参照を代入した変数または接続情報文字列を指定します。
(3) CursorTypeは、次の3つのカーソルの機能を指定する定数です。
    【a】 カレントレコード位置の移動性
    【b】 データの更新可能性
    【c】 他のユーザーが行ったデータベースの変更結果の可視性

これにより一連のデータにアクセスする方向や追加・編集・削除・検索したレコードが参照可能か否かを明確にできます。
このカーソルタイプには、次の種類があります。
   1) adOpenForwardOnly : 既定値 順方向のみ
      レコード位置が順方向にのみ移動でき、個々のレコードを検索できず、レコード数を返しません。
      参照のみ可能であり、高速に動作できます。 他のユーザーによる更新レコードを表示できません。
   2) adOpenStatic : 静的
      レコード位置が順・逆の2方向に移動でき、Findを使用でき、レコード数を返すことができます。
      他のユーザーによる更新レコードを表示できません。
   3) adOpenDynamic : 動的
      レコード位置を全ての方向に移動でき、Findを使用でき、レコード数を返すことができます。
      レコードの全ての更新を表示できます。
   4) adOpenKeyset : キーセット
      レコード位置を全ての方向として順・逆方向に移動でき、Findを使用でき、レコード数を返します。
      既に記録されていたレコードの更新を表示できます。
      削除されたレコード、新しく記録されたレコードの更新を表示出来ません。
   5) adOpenUnspecified : 指定せず
      カーソルの種類を指定しません。

(4) LockTypeは、編集時 recordset を開くときに使用するロック(同時作用)の種類を指定する定数です。
このロックタイプには、次の種類があります。
   1) adLockReadOnly : 既定値 読み取り専用、データを変更できません。
   2) adLockPessimistic : レコード単位の排他的ロック、編集直後にデータソースでレコードをロックします。
   3) adLockOptimistic : レコード単位の共有的ロック、Update メソッドを呼び出した場合のみロックします。
   4) adLockBatchOptimistic : 共有的バッチ更新を示しています。 バッチ更新モードの場合に設定します。
   5) adLockUnspecified : 指定せず。 ロックの種類を指定しません。

(5) Optionsは、省略可能です。 
   ※ 詳細は、Microsoft Access の開発者リファレンスを参照願います。

5. ADOによるレコードの追加・編集・削除・検索・抽出
先ず最初にVBEの「ツール」−「参照設定」で Microsoft ActiveX Data Objects X.XX Libraryにチェックを入れます。
ADOのレコードに対する機能は、主にRecordSetオブジェクトのメソッドまたはプロパティにより実現できます。
(1)追加  AddNewUpdateメッソドを使用します。
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

上記のマクロは、アクティブシートの1行目をレコードに追加しています。
最初のフィールドは、主キーとして使用しているので、2番目のフィールドから入力し、最終レコードの次に追加します。
ADOのCloseメソッドに対する注意があります。
このメソッドを使用してRecordSetを閉じる前にConnection オブジェクトを閉じる場合があります。
その場合、その接続に関連するアクティブな Recordset オブジェクトもすべて閉じてしまいます。
この結果、保留中の更新または編集がキャンセルされますので、注意が必要です。
最初に RecordSet オブジェクトを Close し、保留中の更新または編集を完了しなければなりません。

(2)編集  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
  '*************

このマクロは、1番目のレコードから最終のレコードまでデータの末尾に z を追加します。

(3)削除  ADOのDeleteメソッドを使用します。
上記のマクロの一部を「削除」用に修正しました。
  '*************
  Do
    oRs.Delete
    oRs.MoveNext
  Loop Until oRs.EOF
  '*************

このマクロは、開いたレコードセットのすべてのレコードを削除します。

(4)検索  ADOのFindメソッドを「ただ1つのフィールド名」に対して使用します。
書式は、Recordset.Find 検索条件, SkipRows, SearchDirection, Start です。
1)検索条件は、列の名称1つだけ指定できます。
2)SkipRowsは、読み飛ばすレコード数を指定します。省略時、カレントレコードから検索します。
3)SearchDirectionには、adSearchBackward, adSearchForward の2つがあります。
省略可能です。検索が成功しなかった場合、それぞれBOF、EOFに移動して止まります。
4)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
  '*************

このマクロは、2番目のフィールドデータに文字列 333 を含む場合、イミディエイトウィンドウに出力します。

(5)抽出  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 = "" '空白を代入し、フィルタを解除します。
  '*************

このマクロは、2、3番目のフィールドデータに文字列 333 を含む場合、イミディエイトウィンドウに出力します。

5. 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の開発者用リファレンス「15章: ADOXに関する基本事項」を参照願います。
  なお、接続は、"Provider='Microsoft.ACE.OLEDB.12.0';"であることが必要です。



ADOとオートメーションのvba を使用した excel と access の接続を活用しましょう!


vba excel access 簡単です! Homeへ
posted by Yy at 05:33 | Comment(0) | ado | このブログの読者になる | 更新情報をチェックする
×

この広告は1年以上新しい記事の投稿がないブログに表示されております。