в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:47Z

1 версия

Новая страница

==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>

==Navigate through a recordset==



<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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Access/Recordset Cursor - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия