VB ADO 操作

【SELECT SQL実行サンプルリスト】

'**************************************************************************
' MS-AccessのDBに接続して、SELECT文を実行後、データを抽出して、DBを切断する
'**************************************************************************
Private Sub getSQL()

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  Dim strSQL
  strSQL = "SELECT * FROM HINMST" 'SQL文

  cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb" 'MS-AccessのDBに接続
  rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText         'SQLを実行

  While Not rs.EOF          'レコード数分ループ
    Debug.Print rs!HINMEI   '「品名」フィールドの内容を抽出
    rs.MoveNext             '次のレコードへ
    DoEvents
  Wend

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing

End Sub

【新規レコード作成サンプルリスト】

'*******************************************************************
' MS-AccessのDBに接続して、新規レコードを作成後、DBを切断する
'*******************************************************************
Private Sub getSQL()

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset

  Dim strTBL
  strTBL = "HINMST" 'テーブル名

  cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb" 'MS-AccessのDBに接続
  rs.Open strTBL, cn, adOpenForwardOnly, adLockOptimistic, adCmdTable   'テーブルを開く

  rs.AddNew                '新規レコードを追加
  rs!HINMEI = "あんぱん"   '「商品名」をセット
  rs!HINCODE = "100"       '「商品コード」をセット
  rs!PRICE = 70            '「価格」をセット
  rs.Update                '更新(保存)

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing

End Sub

●Openメソッド

【機能】データソースへ接続します。

【書式】cn.Open 接続情報

【書式 (MS-Access) 】cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=MDBファイル名"
【書式 (SQL-Server)】cn.Open "Driver={SQL Server};Server=サーバ名;Database=データベース名;Uid=ユーザID;Pwd=パスワード;"
【書式 (EXCEL)     】cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=XLSファイル名;Extended Properties=EXCELバージョン;"

【例】
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb" 'MS-Accessへ接続
   cn.Open "Driver={SQL Server};SERVER=SICILIAN;DATABASE=BITTER;UID=PE;PWD=PU;" 'SQL-Serverへ接続
   cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\WK\TEST.XLS;Extended Properties=Excel 8.0;" 'EXCEL97へ接続

●Closeメソッド

【機能】オブジェクトを閉じます。

【書式】cn.Close

【例】
   Set cn = New ADODB.Connection
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   cn.Close   'コネクションを閉じる
   Set cn = Nothing

※	Connectionオブジェクトを閉じると、接続に関連するアクティブ Recordsetオブジェクトもすべて閉じます。
※	Recordset オブジェクトを開いたまま Connection オブジェクトを閉じると、すべての Recordset オブジェクトの未処理の変更がロールバックされます。
※	オブジェクトを閉じてもメモリからは削除されません。メモリからオブジェクトを完全に削除するには、オブジェクト変数を Nothing に設定します。

●Openメソッド

【機能】カーソルを開きます。

【書式】rs.Open Source, ActiveConnection, CursorType, LockType, Options

Source ：SQLステートメント、テーブル名、ストアドプロシージャ。

ActiveConnection ：Connectionオブジェクトの変数名。

CursorType ：Recordsetを開く際にプロバイダが使うカーソルタイプ。

adOpenForwardOnly	デフォルトの値です。前方スクロールタイプカーソルを開きます。
adOpenKeyset	キーセットカーソルを開きます。
adOpenDynamic	動的カーソルを開きます。
adOpenStatic	静的カーソルを開きます。

Options ：Recordsetを開く際にプロバイダが使うロック(同時実行制御)の種類。

adLockReadOnly	デフォルトの値です。読み取り専用。データの変更はできません。
adLockPessimistic	レコードごとの排他的ロック。通常、編集の際にプロバイダがデータソースでレコードをロックすることにより、確実にレコードを編集できるために必要な最小限のロックを使用します。
adLockOptimistic	レコードごとの共有的ロック。Update メソッドを呼び出した場合のみ、プロバイダが共有的ロックを使用してレコードをロックします。
adLockBatchOptimistic	共有的バッチ更新 ― 即時更新モードに対して、バッチ更新モードの場合に必要です。

LockType ：Source 引数が Command オブジェクト以外の設定値を表す場合、または以前に保存していたファイルから Recordsetを復元する場合に、プロバイダが引数を評価する方法。

adCmdText	プロバイダが Sourceをコマンド文字列による定義として評価することを指定します。
adCmdTable	ADOが Sourceに名前を付けられたテーブルからすべての行を返す SQL クエリを生成することを指定します。
adCmdTableDirect	プロバイダが Sourceに名前を付けられたテーブルからすべての行を返すことを指定します。
adCmdStoredProc	プロバイダが Sourceをストアドプロシージャとして評価することを指定します。
adCmdUnknown	Source 引数に指定されたコマンドタイプが不明であることを指定ます。
adCmdFile	Source 引数に指名されたファイルから永続化(保存)された Recordsetを復元することを指定します。
adAsyncExecute	Source 引数を非同期実行することを指定します。
adAsyncFetch	Initial Fetch Sizeプロパティに指定された初期量の残りの行を非同期でフェッチすることを指定します。フェッチされたことがない行が要求された場合は、要求された行が利用可能になるまでは、メインスレッドはブロックされます。
adAsyncFetchNonBlocking	フェッチ中にブロックされたことのないメインスレッドを指定します。要求された行がフェッチされたことのない場合は、現在の行が自動的にファイルの最後に移動します。

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText 'SQLを発行

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strTBL, cn, adOpenForwardOnly, adLockOptimistic, adCmdTable 'テーブルを開く

●BOF、EOFプロパティ

【機能】カレントレコードの位置が最初のレコードより前にあるかどうか、もしくは、最後のレコードより後にあるかどうかをブール値で返します。

【書式】result = rs.BOF    ※レコードの位置が最初のレコードより前にあるかどうか
【書式】result = rs.EOF    ※レコードの位置が最後のレコードより後にあるかどうか

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
   While Not rs.EOF         'レコード数分ループ
      Debug.Print rs!HINMEI
      rs.MoveNext           '次のレコードへ
      DoEvents
   Wend
   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	Recordsetオブジェクトで最後に残ったレコードを削除しても、カレントレコードの位置を移動するまで BOF および EOFプロパティは Falseのままです。

●RecordCountプロパティ

【機能】レコード数を返します。

【書式】result = rs.RecordCount

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
   result = rs.RecordCount 'レコード数の取得
   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	レコード数がわからない場合、または、プロバイダやカーソルの種類が RecordCountをサポートしていない場合は、プロパティは -1 を返します。
※	RecordCountプロパティは、前方スクロールタイプのカーソルでは -1 を返し、静的カーソルまたはキーセットカーソルでは実際の数を返し、動的カーソルではデータソースによって -1 または実際の数を返します。
※	BOFとEOFプロパティが Trueの場合、RecordCountプロパティ設定値は 0 になります。

●MoveFirst、MoveLast、MoveNext、MovePreviousメソッド

【機能】カレントレコードを最初、最後、次、または前のレコードに移動します。

【書式】rs.MoveFirst       ※カレントレコードを先頭のレコードにします
【書式】rs.MoveLast        ※カレントレコードを最後のレコードにします
【書式】rs.MoveNext        ※カレントレコードを次のレコードにします
【書式】rs.MovePrevious    ※カレントレコードを前のレコードにします

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
   While Not rs.EOF         'レコード数分ループ
      Debug.Print rs!HINMEI
      rs.MoveNext           '次のレコードへ
      DoEvents
   Wend
   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	EOFプロパティが既に Trueの場合、前方へ移動しようとするとエラーが発生します。
※	BOF プロパティが既に True の場合、後方へ移動しようとするとエラーが発生します。
※	Recordsetオブジェクトが後方スクロールをサポートしていない場合に MoveLastを呼び出すとエラーが発生します。

●AddNewメソッド

【機能】新規レコードを作成します。

【書式】rs.AddNew

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strTBL, cn, adOpenForwardOnly, adLockOptimistic, adCmdTable  'テーブルを開く

   rs.AddNew                '新規レコードを追加
   rs!HINMEI = "あんぱん"   '「商品名」をセット
   rs!HINCODE = "100"       '「商品コード」をセット
   rs!PRICE = 70            '「価格」をセット
   rs.Update                '更新(保存)

   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	AddNewメソッドを呼び出した後は、新規レコードがカレントレコードになり、Updateメソッドを呼び出した後もそのままカレントレコードになります。
※	カレントレコードの編集中、または新規レコードの追加中に AddNewメソッドを呼び出すと、Updateメソッドが呼び出されてすべての変更を保存して新規レコードを作成します。

●Updateメソッド

【機能】カレントレコードへの変更内容を保存します。

【書式】rs.Update

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset

   strSQL = "SELECT * FROM HINMST WHERE HINCODE='100'"  'SQL文

   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText  'SQL文を実行

   If Not rs.EOF Then
      rs!PRICE = 120   '「価格」を変更
      rs.Update        '更新(保存)
   End If

   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	Updateメソッドを呼び出す前に追加または編集中のレコードから移動した場合、ADOは Updateを自動的に呼び出して変更を保存します。
※	Updateメソッドを呼び出した後、カレントレコードはカレントとして維持されます。

●Deleteメソッド

【機能】カレントレコードを削除します。

【書式】rs.Delete

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset

   strSQL = "SELECT * FROM HINMST WHERE HINCODE='100'"  'SQL文

   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText  'SQL文を実行

   If Not rs.EOF Then
      rs.Delete        'レコードを削除
   End If

   rs.Close
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	Recordsetオブジェクトでレコードの削除が使用できない場合はエラーが発生します。
※	即時更新モードではすぐにデータベースで削除が行われます。それ以外のモードでは、キャッシュから削除するレコードがマークされ、UpdateBatchメソッドを呼び出したときに実際に削除されます。
※	削除したカレントレコードは、別のレコードに移動するまでそのままカレントレコードになります。
※	トランザクションで削除レコードをネストしている場合、RollbackTransメソッドを使って削除レコードを復活することができます。

●Closeメソッド

【機能】オブジェクトを閉じます。

【書式】rs.Close

【例】
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   cn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\WK\TEST.mdb"
   rs.Open strSQL, cn, adOpenStatic, adLockOptimistic, adCmdText
   rs.Close   'レコードセットを閉じる
   cn.Close
   Set rs = Nothing
   Set cn = Nothing

※	即時更新モードで編集しているときに Close メソッドを呼び出すと、エラーが発生します。この場合は先に Update または CancelUpdate メソッドを呼び出してください。
※	オブジェクトを閉じてもメモリからは削除されません。メモリからオブジェクトを完全に削除するには、オブジェクト変数を Nothing に設定します。

【Ｑ】EXCELをデータベースにした場合に、次のエラーが出ます。
「選択された CollatingSequence は OS でサポートされていません。」

【Ａ】コンパイルした実行ファイルではエラーは出ないようです。

【Microsoftサポートページ引用】

	Visual Basic のデザイン環境で Excel を使用して ADO コードをテストしている場合、初めてレコードセットの処理を実行すると、次のエラーメッセージが表示されることがあります。
	選択された CollatingSequence は OS でサポートされていません。
	このエラーメッセージは無視できます。アプリケーションをコンパイルした後は、このエラーメッセージは表示されません。

【MicrosoftサポートページURL】
http://support.microsoft.com/kb/303814/ja