VBA/Excel/Access/Word/Access/SQL Update
Содержание
Deleting a Stored Query
<source lang="vb">
Sub Delete_Query()
Dim cat As New ADOX.Catalog Dim strPath As String On Error GoTo ErrorHandler strPath = CurrentProject.Path & "\mydb.mdb" cat.ActiveConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _ "Data Source= " & strPath cat.Views.Delete "London Employees"
ExitHere:
Set cat = Nothing Exit Sub
ErrorHandler:
If Err.Number = 3265 Then MsgBox "Query does not exist." Else MsgBox Err.Number & ": " & Err.Description End If Resume ExitHere
End Sub
</source>
Executing an Update Query
<source lang="vb">
Sub Execute_UpdateQuery()
Dim conn As ADODB.Connection Dim NumOfRec As Integer Dim strPath As String strPath = CurrentProject.Path & "\mydb.mdb" Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath conn.Execute "UPDATE Products SET UnitPrice = UnitPrice + 1" & _ " WHERE CategoryId = 8", NumOfRec, adExecuteNoRecords Debug.Print NumOfRec & " records were updated." conn.Close Set conn = Nothing
End Sub
</source>
Executing an Update Query using the Command Object
<source lang="vb">
Sub Execute_UpdateQuery2()
Dim cmd As ADODB.rumand Dim NumOfRec As Integer Dim strPath As String strPath = CurrentProject.Path & "\mydb.mdb" Set cmd = New ADODB.rumand With cmd .ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath .rumandText = "Update Products Set UnitPrice = UnitPrice *1.1" .Execute NumOfRec, adExecuteNoRecords End With MsgBox NumOfRec Set cmd = Nothing
End Sub
</source>
Get the updated record count
<source lang="vb">
Sub Execute_UpdateQuery()
Dim conn As ADODB.Connection Dim NumOfRec As Integer Dim strPath As String strPath = CurrentProject.Path & "\mydb.mdb" Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath conn.Execute "UPDATE Products SET UnitPrice = UnitPrice + 1" & _ " WHERE CategoryId = 8", NumOfRec, adExecuteNoRecords Debug.Print NumOfRec & " records were updated." conn.Close Set conn = Nothing
End Sub
</source>
Listing All Saved Queries in a Database
<source lang="vb">
Sub List_SavedQueries()
Dim cat As New ADOX.Catalog Dim v As ADOX.View Dim strPath As String strPath = CurrentProject.Path & "\mydb.mdb" cat.ActiveConnection = "Provider=Microsoft.Jet.OleDb.4.0;" & _ "Data Source= " & strPath For Each v In cat.Views Debug.Print v.Name Next Set cat = Nothing
End Sub
</source>