Access Knowhow Modules - Clinet/Server Recordset

ADO 2.1のRecordsetオブジェクトの使い方

ADO 2.1のRecordsetオブジェクトとプロパティの使い方を解説します。
使用例

ここでは、RecordsetオブジェクトのOpen,GetString,MoveNext,Find,Seek,Filter,Sort,AddNew,Updateメソッドの使い方を紹介します。
Sub RsOpenGetString()
  Dim rs As New ADODB.Recordset
  
  With rs
    .Open "得意先", _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb;"
    Debug.Print .GetString(adClipString, 5, "; ")
    .Close
  End With
End Sub

Sub RsOpenGetString2()
  Dim rs As New ADODB.Recordset
  
  With rs
    .Open "Select * From 得意先;", _
      "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb;"
    Debug.Print .GetString(adClipString, 5, "; ")
    .Close
  End With
End Sub

Sub RsOpenGetString3()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "Select * From 得意先;", cnn, _
      adOpenForwardOnly, adLockReadOnly
    Debug.Print .GetString(adClipString, 5, "; ")
    .Close
  End With
End Sub

Sub RsOpenMoveNext()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先", cnn, adOpenKeyset, adLockOptimistic
    Do Until .EOF
      For Each fld In .Fields
        Debug.Print fld.Value & ";";
      Next fld
      Debug.Print
      .MoveNext
    Loop
    .Close
  End With
  
End Sub

Sub RsOpenMoveNext2()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "Select * from 得意先 Where 都道府県='東京都';", _
      cnn, adOpenForwardOnly, adLockReadOnly
    Do Until .EOF
      For Each fld In .Fields
        Debug.Print fld.Value & ";";
      Next fld
      Debug.Print
      .MoveNext
    Loop
    .Close
  End With
End Sub

Sub RsOpenFindForward()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先;", cnn, adOpenKeyset, adLockReadOnly
    .Find "都道府県='東京都'"
    Do While Not .EOF
        Debug.Print .Fields("得意先コード").Value & vbTab;
        Debug.Print .Fields("得意先名").Value & vbTab;
        Debug.Print .Fields("都道府県").Value;
        Debug.Print
        .Find "都道府県='東京都'", 1
    Loop
    .Close
  End With
End Sub

Sub RsOpenFindBackward()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先;", cnn, adOpenKeyset, adLockReadOnly
    .MoveLast
    .Find "都道府県='東京都'", 0, adSearchBackward
    Do While Not .BOF
        Debug.Print .Fields("得意先コード").Value & vbTab;
        Debug.Print .Fields("得意先名").Value & vbTab;
        Debug.Print .Fields("都道府県").Value;
        Debug.Print
        .Find "都道府県='東京都'", 1, adSearchBackward
    Loop
    .Close
  End With
End Sub


Sub RsOpenSeek()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Index = "PrimaryKey" ' OrderID
    .Open "受注", cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
    .Seek 1050, adSeekFirstEQ
    If Not (.EOF Or .BOF) Then
      For Each fld In .Fields
        Debug.Print fld.Value & ";";
      Next fld
      Debug.Print
    End If
    .Close
  End With
End Sub


Sub RsOpenSeek2()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Index = "PrimaryKey"   ' OrderID, ProductID
    .Open "受注明細", cnn, adOpenKeyset, adLockReadOnly, adCmdTableDirect
    .Seek Array(1050, 102), adSeekFirstEQ
    If Not (.EOF Or .BOF) Then
      For Each fld In .Fields
        Debug.Print fld.Value & ";";
      Next fld
      Debug.Print
    End If
    .Close
  End With
End Sub

Sub RsOpenFilter()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先", cnn, adOpenKeyset, adLockReadOnly
    .Filter = "都道府県='東京都'"
    Debug.Print "RecordCount:", .RecordCount
    Do Until .EOF
      For Each fld In .Fields
        Debug.Print fld.Value & ";";
      Next fld
      Debug.Print
      .MoveNext
    Loop
    .Close
  End With
End Sub

Sub RsOpenSort()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .CursorLocation = adUseClient
    .Open "得意先", cnn, adOpenKeyset, adLockReadOnly
    .Sort = "都道府県"
    Do Until .EOF
      Debug.Print .Fields("都道府県").Value & ";";
      Debug.Print .Fields("得意先コード").Value & ";";
      Debug.Print .Fields("得意先名").Value & ";";
      Debug.Print
      .MoveNext
    Loop
    .Close
  End With
End Sub

Sub RsOpenAdd()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先", cnn, adOpenKeyset, adLockOptimistic
    .AddNew
    !得意先名 = "フレンドリーソフト"
    !郵便番号 = "340-9999"
    !都道府県 = "埼玉県"
    !住所1 = "＊＊＊＊"
    !電話番号 = "(0480)40-9999"
    .Update
    Debug.Print .Fields("得意先コード").Value
    .Close
  End With
End Sub

Sub RsOpenAdd2()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Open "得意先", cnn, adOpenKeyset, adLockOptimistic
    .AddNew Array("得意先名", "郵便番号", "都道府県", "住所１"), _
      Array("フレンドリーソフト", "340-9999", "埼玉県", "＊＊＊＊")
    .Update
    Debug.Print .Fields("得意先コード").Value
    .Close
  End With
End Sub

Sub RsOpenUpdate()
  Dim cnn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  Dim fld As ADODB.Field
  
  cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=D:\Temp\Northwind.mdb"
  With rs
    .Index = "PrimaryKey"   ' CustomerID
    .Open "得意先", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    .Seek 53, adSeekFirstEQ
    !担当者名 = "平成　花子"
    .Update
    .Close
  End With
End Sub