VBA/Excel/Access/Word/Access/Recordset Cursor
Содержание
- 1 Check after using the MoveNext method whether the end of the file has been reached
- 2 Designating the Cursor Location
- 3 Move cursor in a Recordset with MoveNext
- 4 moves 5 records backward from the current record in a DAO recordset:
- 5 Move the resultset cursor with MoveNext method
- 6 Moving Around in a Recordset
- 7 Moving Through the Records in a Recordset:MoveFirst - To the first record in a recordset
- 8 Moving Through the Records in a Recordset:MoveLast - To the last record in a recordset
- 9 Moving Through the Records in a Recordset:MoveNext - To the next record in a recordset
- 10 Moving Through the Records in a Recordset:MovePrevious - To the previous record in a recordset
- 11 Navigate through a recordset
- 12 Set Cursor Location to adUseClient
- 13 Set CursorLocation to adUseServer
- 14 Set CursorType to adOpenDynamic
- 15 Set CursorType to adOpenForwardOnly
- 16 Set CursorType to adOpenKeyset
- 17 Set CursorType to adOpenStatic
- 18 Supplying the CursorType as a Parameter of the Open Method
- 19 Supplying the CursorType as a Property of the Recordset Object
- 20 Using the Recordset Movements() Methods on a Recordset Object
Check after using the MoveNext method whether the end of the file has been reached
<source lang="vb">
Sub moveNext()
Dim myDatabase As dao.Database Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb") Dim myRecordset As dao.Recordset Set myRecordset = myDatabase.OpenRecordset(Name:="SELECT * FROM Customers WHERE Country="Germany"", Type:=dbOpenDynaset) myRecordset.moveNext If myRecordset.EOF Then myRecordset.MovePrevious
End Sub
</source>
Designating the Cursor Location
<source lang="vb">
Sub CursorLocation()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.CursorLocation = adUseServer rst.Open Source:="Select * from Employees ", _ Options:=adCmdText rst("City") = "New City" rst.Update Debug.Print rst("City") rst.Close Set rst = Nothing
End Sub
</source>
Move cursor in a Recordset with MoveNext
<source lang="vb">
Sub PropNulls()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.Open "Employees", CurrentProject.Connection Do Until rst.EOF Debug.Print rst!EmployeeID, rst!Region rst.MoveNext Loop
End Sub
</source>
moves 5 records backward from the current record in a DAO recordset:
<source lang="vb">
Sub MoveRows()
Dim myDatabase As dao.Database Set myDatabase = OpenDatabase(CurrentProject.Path & "\mydb.mdb") Dim myRecordset As dao.Recordset Set myRecordset = myDatabase.OpenRecordset(Name:="SELECT * FROM Customers WHERE Country="Germany"", Type:=dbOpenDynaset) myRecordset.Move Rows:=-5
End Sub
</source>
Move the resultset cursor with MoveNext method
<source lang="vb">
Sub cursorMove()
Dim conn As ADODB.Connection Dim myRecordset As ADODB.Recordset Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb" Set myRecordset = New ADODB.Recordset With myRecordset .Open "Customers", conn, adOpenKeyset, adLockOptimistic .Filter = "City="Madrid" and Country="Spain"" End With Do Until myRecordset.EOF Debug.Print myRecordset.Fields(1).Value myRecordset.MoveNext Loop myRecordset.Close Set myRecordset = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Moving Around in a Recordset
<source lang="vb">
Sub MoveAround()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim fld As ADODB.Field Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set rst = New ADODB.Recordset rst.Open "Select * from Customers where ContactTitle = "Owner"", _ conn, adOpenForwardOnly, adLockReadOnly Do While Not rst.EOF For Each fld In rst.Fields Debug.Print fld.Name & " = " & fld.Value Next rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Moving Through the Records in a Recordset:MoveFirst - To the first record in a recordset
<source lang="vb">
Sub RecordsetMovements()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst("EmployeeID") rst.MoveNext Debug.Print rst("EmployeeID") rst.MoveLast Debug.Print rst("EmployeeID") rst.MovePrevious Debug.Print rst("EmployeeID") rst.MoveFirst Debug.Print rst("EmployeeID") rst.Close Set rst = Nothing
End Sub
</source>
Moving Through the Records in a Recordset:MoveLast - To the last record in a recordset
<source lang="vb">
Sub RecordsetMovements()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst("EmployeeID") rst.MoveNext Debug.Print rst("EmployeeID") rst.MoveLast Debug.Print rst("EmployeeID") rst.MovePrevious Debug.Print rst("EmployeeID") rst.MoveFirst Debug.Print rst("EmployeeID") rst.Close Set rst = Nothing
End Sub
</source>
Moving Through the Records in a Recordset:MoveNext - To the next record in a recordset
<source lang="vb">
Sub RecordsetMovements()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst("EmployeeID") rst.MoveNext Debug.Print rst("EmployeeID") rst.MoveLast Debug.Print rst("EmployeeID") rst.MovePrevious Debug.Print rst("EmployeeID") rst.MoveFirst Debug.Print rst("EmployeeID") rst.Close Set rst = Nothing
End Sub
</source>
Moving Through the Records in a Recordset:MovePrevious - To the previous record in a recordset
<source lang="vb">
Sub RecordsetMovements()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst("EmployeeID") rst.MoveNext Debug.Print rst("EmployeeID") rst.MoveLast Debug.Print rst("EmployeeID") rst.MovePrevious Debug.Print rst("EmployeeID") rst.MoveFirst Debug.Print rst("EmployeeID") rst.Close Set rst = Nothing
End Sub
</source>
<source lang="vb">
Public Sub RecordsetNavigation()
Const SQL As String = "SELECT * FROM Customers" Const ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + _ "Data Source=C:\mydb.mdb;Persist Security Info=False" Dim Recordset As Recordset Set Recordset = New Recordset Call Recordset.Open(SQL, ConnectionString, adOpenDynamic) Recordset.MoveLast While Not Recordset.BOF Debug.Print Recordset.Fields("CompanyName") Recordset.MovePrevious Wend
End Sub
</source>
Set Cursor Location to adUseClient
<source lang="vb">
Sub SortRecordset()
Dim intCounter As Integer Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorLocation = adUseClient rst.Open "Select * from Employees" Debug.Print "NOT Sorted!!!" Do Until rst.EOF Debug.Print rst("EmployeeID") rst.MoveNext Loop Debug.Print "Now Sorted!!!" rst.Sort = "[EmployeeID]" Do Until rst.EOF Debug.Print rst("EmployeeID") rst.MoveNext Loop rst.Close Set rst = Nothing
End Sub
</source>
Set CursorLocation to adUseServer
<source lang="vb">
Sub SupportsMethod()
"Declare and instantiate a Recordset object Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.LockType = adLockOptimistic rst.CursorLocation = adUseServer "Open the recordset, designating that the source "is a SQL statement rst.Open Source:="Select * from Employees ", _ Options:=adCmdText "Determine whether the recordset supports certain features Debug.Print "Bookmark " & rst.Supports(adBookmark) Debug.Print "Update Batch " & rst.Supports(adUpdateBatch) Debug.Print "Move Previous " & rst.Supports(adMovePrevious) Debug.Print "Seek " & rst.Supports(adSeek) rst.Close Set rst = Nothing
End Sub
</source>
Set CursorType to adOpenDynamic
<source lang="vb">
Sub StaticRecordset1()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees", CursorType:=adOpenDynamic Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Set CursorType to adOpenForwardOnly
<source lang="vb">
Sub StaticRecordset1()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees", CursorType:=adOpenForwardOnly Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Set CursorType to adOpenKeyset
<source lang="vb">
Sub StaticRecordset1()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees", CursorType:=adOpenKeyset Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Set CursorType to adOpenStatic
<source lang="vb">
Sub StaticRecordset1()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees", _ CursorType:=adOpenStatic Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Supplying the CursorType as a Parameter of the Open Method
<source lang="vb">
Sub StaticRecordset1()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.Open "Select * from Employees", CursorType:=adOpenStatic Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Supplying the CursorType as a Property of the Recordset Object
<source lang="vb">
Sub StaticRecordset2()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst.RecordCount rst.Close Set rst = Nothing
End Sub
</source>
Using the Recordset Movements() Methods on a Recordset Object
<source lang="vb">
Sub RecordsetMovements()
Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" Debug.Print rst("EmployeeID") rst.MoveNext Debug.Print rst("EmployeeID") rst.MoveLast Debug.Print rst("EmployeeID") rst.MovePrevious Debug.Print rst("EmployeeID") rst.MoveFirst Debug.Print rst("EmployeeID") rst.Close Set rst = Nothing
End Sub
</source>