Move メソッドの使用例 (VB)

適用先: Access 2013、Office 2013

この例では、Move メソッドを使用して、ユーザーの入力に基づいてレコード ポインターを配置します。

 
'BeginMoveVB 
 
 'To integrate this code 
 'replace the data source and initial catalog values 
 'in the connection string 
 
Public Sub Main() 
 On Error GoTo ErrorHandler 
 
 ' connection and recordset variables 
 Dim rstAuthors As ADODB.Recordset 
 Dim Cnxn As ADODB.Connection 
 Dim strCnxn As String 
 Dim strSQLAuthors As String 
 ' record variables 
 Dim varBookmark As Variant 
 Dim strCommand As String 
 Dim lngMove As Long 
 
 ' Open connection 
 Set Cnxn = New ADODB.Connection 
 strCnxn = "Provider='sqloledb';Data Source='MySqlServer';" & _ 
 "Initial Catalog='Pubs';Integrated Security='SSPI';" 
 Cnxn.Open strCnxn 
 
 ' Open recordset from Authors table 
 Set rstAuthors = New ADODB.Recordset 
 rstAuthors.CursorLocation = adUseClient 
 ' Use client cursor to allow use of AbsolutePosition property 
 strSQLAuthors = "SELECT au_id, au_fname, au_lname, city, state FROM Authors ORDER BY au_lname" 
 rstAuthors.Open strSQLAuthors, strCnxn, adOpenStatic, adLockOptimistic, adCmdText 
 
 rstAuthors.MoveFirst 
 
 Do 
 ' Display information about current record and 
 ' ask how many records to move 
 
 strCommand = InputBox( _ 
 "Record " & rstAuthors.AbsolutePosition & _ 
 " of " & rstAuthors.RecordCount & vbCr & _ 
 "Author: " & rstAuthors!au_fname & _ 
 " " & rstAuthors!au_lname & vbCr & _ 
 "Location: " & rstAuthors!city & _ 
 ", " & rstAuthors!State & vbCr & vbCr & _ 
 "Enter number of records to Move " & _ 
 "(positive or negative).") 
 
 ' this is for exiting the loop 
 'lngMove = CLng(strCommand) 
 
 lngMove = CLng(Val(strCommand)) 
 If lngMove = 0 Then 
 MsgBox "You either entered a non-number or canceled the input box. Exit the application." 
 Exit Do 
 End If 
 
 ' Store bookmark in case the Move goes too far 
 ' forward or backward 
 varBookmark = rstAuthors.Bookmark 
 
 ' Move method requires parameter of data type Long 
 rstAuthors.Move lngMove 
 
 ' Trap for BOF or EOF 
 If rstAuthors.BOF Then 
 MsgBox "Too far backward! Returning to current record." 
 rstAuthors.Bookmark = varBookmark 
 End If 
 If rstAuthors.EOF Then 
 MsgBox "Too far forward! Returning to current record." 
 rstAuthors.Bookmark = varBookmark 
 End If 
 Loop 
 
 ' clean up 
 rstAuthors.Close 
 Cnxn.Close 
 Set rstAuthors = Nothing 
 Set Cnxn = Nothing 
 Exit Sub 
 
ErrorHandler: 
 ' clean up 
 If Not rstAuthors Is Nothing Then 
 If rstAuthors.State = adStateOpen Then rstAuthors.Close 
 End If 
 Set rstAuthors = Nothing 
 
 If Not Cnxn Is Nothing Then 
 If Cnxn.State = adStateOpen Then Cnxn.Close 
 End If 
 Set Cnxn = Nothing 
 
 If Err <> 0 Then 
 MsgBox Err.Source & "-->" & Err.Description, , "Error" 
 End If 
End Sub 
'EndMoveVB