в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:40Z

1 версия

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

==Change column data case==



<source lang="vb">

Sub exaRecordsetEdit()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Employees")

rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!Title = UCase$(rs!Title)
rs.Update
rs.MoveNext
Loop

rs.Close

End Sub
</source>

==Finding a Record Based on Multiple Criteria==



<source lang="vb">

Sub Find_WithFilter()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & _
"\mydb.mdb"
Set rst = New ADODB.Recordset
rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic
rst.Filter = "TitleOfCourtesy ="Ms." and Country ="USA""
Do Until rst.EOF
Debug.Print rst.Fields("LastName").Value
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub
</source>

==Finding a Specific Record in a Recordset==



<source lang="vb">

Sub FindProject()
Dim strSQL As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * from Employees"
"Attempt to find a specific project
strSQL = "[EmployeeID] = " & 1
rst.Find strSQL
"Determine if the specified project was found
If rst.EOF Then
msgBox lngValue & " Not Found"
Else
msgBox lngValue & " Found"
End If
rst.Close
Set rst = Nothing
End Sub
</source>

==Finding Records Using the Find Method==



<source lang="vb">

Sub Find_WithFind()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Set conn = New ADODB.Connection
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
Set rst = New ADODB.Recordset
rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic
rst.Find "TitleOfCourtesy ="Ms.""
Do Until rst.EOF
Debug.Print rst.Fields("LastName").Value
rst.Find "TitleOfCourtesy ="Ms."", SkipRecords:=1, _
SearchDirection:=adSearchForward
Loop
rst.Close
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub
</source>

==Finding the Record Position==



<source lang="vb">

Sub FindRecordPosition()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
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
With rst
.Open "Select * from Employees", conn, adOpenKeyset, _
adLockOptimistic, adCmdText
Debug.Print .AbsolutePosition
.Move 3 " move forward 3 records
Debug.Print .AbsolutePosition
.MoveLast " move to the last record
Debug.Print .AbsolutePosition
Debug.Print .RecordCount
.Close
End With
Set rst = Nothing
conn.Close
Set conn = Nothing
End Sub
</source>

==Find record by using Recordset.FindFirst==



<source lang="vb">

Sub findrecorder()
Dim dbNorthwind As DAO.Database
Dim dbPath As String
DbPath = CurrentProject.Path & "mydb.mdb"
Set dbNorthwind = OpenDatabase(dbPath)

Dim rsEmployees As DAO.Recordset
Dim rsCustomers As DAO.Recordset
Set rsEmployees = dbNorthwind.OpenRecordset("Employees", dbOpenTable)
Set rsCustomers = dbNorthwind.OpenRecordset("Customers", dbOpenTable)
rsCustomers.MoveLast
numCustomers = rsCustomers.RecordCount
With rsEmployees
.FindFirst "City = "Seattle""
If .NoMatch Then
MsgBox ("No Records Found!")
.MoveFirst
Else
MsgBox ("Found "& .Fields(2).Value & " "& .Fields(1).Value & _
"in Seattle")
End If
End With
End Sub
</source>

==NoMatch property in Recordset==



<source lang="vb">

Sub SeekByPrice(curPrice As Currency)
Dim db As Database
Dim rec As Recordset
Dim strSQL As String
strSQL = "tblSales"
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)
rec.Index = "AmountPaid"
rec.Seek "=", curPrice

If rec.NoMatch = True Then
Debug.Print "No orders cost " & FormatCurrency(curPrice)
Else
Debug.Print "Order No. " & rec("SalesID") & " placed on " & _
FormatDateTime(rec("DateOrdered"), vbLongDate) & _
" cost " & FormatCurrency(rec("AmountPaid"))
End If
rec.Close
End Sub
</source>

==Select specific column in select statement==



<source lang="vb">

Sub MyFirstConnection()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer"
Set myConnection = CurrentProject.Connection

Set myRecordset = New ADODB.Recordset
myRecordset.Open strSQL, myConnection

Do Until myRecordset.EOF
Debug.Print myRecordset.Fields("txtCustFirstName") & " " & _
myRecordset.Fields("txtCustLastName")
myRecordset.MoveNext
Loop
myRecordset.Close
myConnection.Close
Set myConnection = Nothing
Set myRecordset = Nothing
End Sub
</source>

==Simple Select statement==



<source lang="vb">

Sub MyFirstConnection()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT * FROM tblCustomer ORDER BY txtCustLastName"

Set myConnection = CurrentProject.Connection

Set myRecordset = New ADODB.Recordset
myRecordset.Open strSQL, myConnection

Do Until myRecordset.EOF
Debug.Print myRecordset.Fields("txtCustFirstName"), _
myRecordset.Fields("txtCustLastName")
myRecordset.MoveNext
Loop
myRecordset.Close
myConnection.Close
Set myConnection = Nothing
Set myRecordset = Nothing
End Sub
</source>

==SQL with where clause==



<source lang="vb">

Sub MyFirstConnection()
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strSQL As String
Dim strSearch As String

strSearch = "Joe"

strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer" & _
" WHERE txtCustLastName = " & " "" & strSearch & """

Set myConnection = CurrentProject.Connection

Set myRecordset = New ADODB.Recordset
myRecordset.Open strSQL, myConnection

Do Until myRecordset.EOF
Debug.Print myRecordset.Fields("txtCustFirstName"), _
myRecordset.Fields("txtCustLastName")
myRecordset.MoveNext
Loop
myRecordset.Close
myConnection.Close
Set myConnection = Nothing
Set myRecordset = Nothing
End Sub
</source>

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

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

в 16:33, 26 мая 2010

Admin: 1 версия