в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:46:50Z

1 версия

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

==Adding a New Record to a Recordset==



<source lang="vb">

Sub cmdAddADO()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open "Select * from Employees Where EmployeeID = 0"
.AddNew
!FirstName = "newF"
!LastName = "newL"
!Region = "new"
.Update
End With
End Sub
</source>

==Adding a New Record to a Table==



<source lang="vb">

" Use the References dialog box to set up a reference to the Microsoft ActiveX Data Objects Library
Sub Add_Record()
Dim conn As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
Set myRecordset = New ADODB.Recordset
With myRecordset
.Open "Select * from Employees", _
strConn, adOpenKeyset, adLockOptimistic
.AddNew
!LastName = "Marco"
!FirstName = "Paulo"
!City = "Boston"
.MoveFirst
.Close
End With
Set myRecordset = Nothing
Set conn = Nothing
End Sub
</source>

==Add new a row to recordset==



<source lang="vb">

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

Set db = CurrentDb
Set rs = db.OpenRecordset("Books")
Debug.Print "Current title: " & rs!Title
With rs
.AddNew " Add new record
!ISBN = "0-000" " Set fields
!Title = "New Book"
!PubID = 1
!Price = 100
.Update " Save changes.
.Bookmark = rs.LastModified " Go to new record
Debug.Print "Current title: " & rs!Title
End With

rs.Close
End Sub
</source>

==Call delete method in Recordset==



<source lang="vb">

Public Sub RemoveCompany()
Dim rst As ADODB.Recordset
Dim strSQL As String
Set rs = New ADODB.Recordset
rs.Open "SELECT * FROM tblCompany WHERE CompanyID=14", CurrentProject.Connection, adOpenStatic, adLockOptimistic
With rs
If .RecordCount > 0 Then
.Delete
End If
End With
rs.Close
Set rs = Nothing
End Sub
</source>

==Call update method from Recordset==



<source lang="vb">

Public Sub ADOUpdate()
Dim rs As ADODB.Recordset
Dim strSQL As String

Set rs = New ADODB.Recordset
strSQL = "SELECT CompanyName, Address, City FROM tblCompany WHERE (CompanyName = "Liams Diner")"

rs.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

With rs
!CompanyName = "Diner"
.Update
End With
rs.Close
End Sub
</source>

==Check the Recordset affected==



<source lang="vb">

Sub exaCreateAction2()
Dim ws As Workspace
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Set ws = DBEngine(0)
Set db = CurrentDb
strSQL = "UPDATE BOOKS SET Price = Price*1.1 WHERE Price > 20"
Set qdf = db.CreateQueryDef("PriceInc", strSQL)
ws.BeginTrans
qdf.Execute
If qdf.RecordsAffected > 15 Then
Debug.Print qdf.RecordsAffected
ws.Rollback
Else
Debug.Print qdf.RecordsAffected
ws.rumitTrans
End If

End Sub
</source>

==Delete row for a certain criteria==



<source lang="vb">

Sub exaRecordsetDelete()
Dim db As Database
Dim rs As Recordset
Dim DeleteCt As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Books Copy")

DeleteCt = 0

rs.MoveFirst
Do While Not rs.EOF
If rs!Price > 20 Then
rs.Delete
DeleteCt = DeleteCt + 1
End If
rs.MoveNext
Loop

rs.Close

Debug.Print Format$(DeleteCt) & " records deleted."
End Sub
</source>

==Deleting a Record==



<source lang="vb">

Sub Delete_Record()
Dim conn As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strConn As String
Dim strCriteria As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
Set conn = New ADODB.Connection
Set myRecordset = New ADODB.Recordset
With myRecordset
.Open "Select * from Employees Where LastName ="Marco"", _
strConn, adOpenKeyset, adLockOptimistic
.Delete
.Close
End With
Set myRecordset = Nothing
Set conn = Nothing
End Sub
</source>

==Modifying a Record==



<source lang="vb">

Sub Update_Record()
Dim conn As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
Set myRecordset = New ADODB.Recordset
With myRecordset
.Open "Select * from Employees Where LastName = "Marco"", _
strConn, adOpenKeyset, adLockOptimistic
.Fields("FirstName").Value = "A"
.Fields("City").Value = "D"
.Fields("Country").Value = "USA"
.Update
.Close
End With
Set myRecordset = Nothing
Set conn = Nothing
End Sub
</source>

==Use AddNew and specify the field information==



<source lang="vb">

Public Sub addCustomer()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable
With rs
.AddNew
.Fields("CompanyName") = "Diner"
.Fields("Address") = "Road"
.Fields("City") = "New York"
.Update
End With
End Sub
</source>

==Use AddNew method from Recordset and two arrays to add a new row==



<source lang="vb">

Public Sub addCustomerArray()
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Open "tblCompany", conn, adOpenDynamic, adLockOptimistic, adCmdTable

varfields = Array("CompanyName", "Address", "City")
varValues = Array("A", "Road", "B")
rs.AddNew varfields, varValues
rs.Update
End Sub
</source>

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

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

в 16:33, 26 мая 2010

Admin: 1 версия