Public Sub ExecuteX() Dim strSQLChange As String Dim strSQLRestore As String Dim strCnn As String Dim cnn1 As ADODB.Connection Dim cmdChange As ADODB.Command Dim rstTitles As ADODB.Recordset Dim errLoop As ADODB.Error ' Define two SQL statements to execute as command text. strSQLChange = "UPDATE Titles SET Type = " & _ "'self_help' WHERE Type = 'psychology'" strSQLRestore = "UPDATE Titles SET Type = " & _ "'psychology' WHERE Type = 'self_help'" 'myodbc3-test 是你的mysql的ODBC数据源. strCnn = "Provider=MSDASQL.1;" & _ "Data Source=myodbc3-test;User Id=sa;Password=; " Set cnn1 = New ADODB.Connection cnn1.Open strCnn ' Create command object. Set cmdChange = New ADODB.Command Set cmdChange.ActiveConnection = cnn1 cmdChange.CommandText = strSQLChange
' Open titles table. Set rstTitles = New ADODB.Recordset rstTitles.Open "titles", cnn1, , , adCmdTable ' Print report of original data. Debug.Print _ "Data in Titles table before executing the query" PrintOutput rstTitles ' Clear extraneous errors from the Errors collection. cnn1.Errors.Clear ' Call the ExecuteCommand subroutine to execute cmdChange command. ExecuteCommand cmdChange, rstTitles
' Print report of new data. Debug.Print _ "Data in Titles table after executing the query" PrintOutput rstTitles ' Use the Connection object's execute method to ' execute SQL statement to restore data. Trap for ' errors, checking the Errors collection if necessary. On Error GoTo Err_Execute cnn1.Execute strSQLRestore, , adExecuteNoRecords On Error GoTo 0 ' Retrieve the current data by requerying the recordset. rstTitles.Requery ' Print report of restored data. Debug.Print "Data after executing the query " & _ "to restore the original information" PrintOutput rstTitles rstTitles.Close cnn1.Close
Exit Sub
Err_Execute: ' Notify user of any errors that result from ' executing the query. If rstTitles.ActiveConnection.Errors.Count >= 0 Then For Each errLoop In rstTitles.ActiveConnection.Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If Resume NextEnd SubPublic Sub ExecuteCommand(cmdTemp As ADODB.Command, _ rstTemp As ADODB.Recordset) Dim errLoop As Error
' Run the specified Command object. Trap for ' errors, checking the Errors collection if necessary. On Error GoTo Err_Execute cmdTemp.Execute On Error GoTo 0 ' Retrieve the current data by requerying the recordset. rstTemp.Requery
Exit SubErr_Execute: ' Notify user of any errors that result from ' executing the query. If rstTemp.ActiveConnection.Errors.Count > 0 Then For Each errLoop In Errors MsgBox "Error number: " & errLoop.Number & vbCr & _ errLoop.Description Next errLoop End If
Resume NextEnd SubPublic Sub PrintOutput(rstTemp As ADODB.Recordset) ' Enumerate Recordset. Do While Not rstTemp.EOF Debug.Print " " & rstTemp!Title & _ ", " & rstTemp!Type rstTemp.MoveNext LoopEnd Sub
添加新记录 Public Sub AddNewX() Dim cnn1 As ADODB.Connection Dim rstEmployees As ADODB.Recordset Dim strCnn As String Dim strID As String Dim strFirstName As String Dim strLastName As String Dim booRecordAdded As Boolean 'myodbc3-test 是你的mysql的ODBC数据源. ' Open a connection. Set cnn1 = New ADODB.Connection strCnn = "Provider=MSDASQL.1;" & _ "Data Source=myodbc3-test;User Id=sa;Password=; " cnn1.Open strCnn
' Open Employee table. Set rstEmployees = New ADODB.Recordset rstEmployees.CursorType = adOpenKeyset rstEmployees.LockType = adLockOptimistic rstEmployees.Open "employee", cnn1, , , adCmdTable ' Get data from the user. The employee ID must be formatted as ' first, middle, and last initial, five numbers, then M or F to ' signify the gender. For example, the employee id for Bill Sornsin ' would be "B-S55555M". strID = Trim(InputBox("Enter employee ID:")) strFirstName = Trim(InputBox("Enter first name:")) strLastName = Trim(InputBox("Enter last name:")) ' Proceed only if the user actually entered something ' for both the first and last names. If (strID <> "") And (strFirstName <> "") _ And (strLastName <> "") Then rstEmployees.AddNew rstEmployees!emp_id = strID rstEmployees!fname = strFirstName rstEmployees!lname = strLastName rstEmployees.Update booRecordAdded = True ' Show the newly added data. MsgBox "New record: " & rstEmployees!emp_id & " " & _ rstEmployees!fname & " " & rstEmployees!lname Else MsgBox "Please enter an employee ID, " & _ "first name, and last name." End If
' Delete the new record because this is a demonstration. cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
Dim strSQLRestore As String
Dim strCnn As String
Dim cnn1 As ADODB.Connection
Dim cmdChange As ADODB.Command
Dim rstTitles As ADODB.Recordset
Dim errLoop As ADODB.Error ' Define two SQL statements to execute as command text.
strSQLChange = "UPDATE Titles SET Type = " & _
"'self_help' WHERE Type = 'psychology'"
strSQLRestore = "UPDATE Titles SET Type = " & _
"'psychology' WHERE Type = 'self_help'" 'myodbc3-test 是你的mysql的ODBC数据源.
strCnn = "Provider=MSDASQL.1;" & _
"Data Source=myodbc3-test;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn ' Create command object.
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = cnn1
cmdChange.CommandText = strSQLChange
' Open titles table.
Set rstTitles = New ADODB.Recordset
rstTitles.Open "titles", cnn1, , , adCmdTable ' Print report of original data.
Debug.Print _
"Data in Titles table before executing the query"
PrintOutput rstTitles ' Clear extraneous errors from the Errors collection.
cnn1.Errors.Clear ' Call the ExecuteCommand subroutine to execute cmdChange command.
ExecuteCommand cmdChange, rstTitles
' Print report of new data.
Debug.Print _
"Data in Titles table after executing the query"
PrintOutput rstTitles ' Use the Connection object's execute method to
' execute SQL statement to restore data. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cnn1.Execute strSQLRestore, , adExecuteNoRecords
On Error GoTo 0 ' Retrieve the current data by requerying the recordset.
rstTitles.Requery ' Print report of restored data.
Debug.Print "Data after executing the query " & _
"to restore the original information"
PrintOutput rstTitles rstTitles.Close
cnn1.Close
Exit Sub
Err_Execute: ' Notify user of any errors that result from
' executing the query.
If rstTitles.ActiveConnection.Errors.Count >= 0 Then
For Each errLoop In rstTitles.ActiveConnection.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume NextEnd SubPublic Sub ExecuteCommand(cmdTemp As ADODB.Command, _
rstTemp As ADODB.Recordset) Dim errLoop As Error
' Run the specified Command object. Trap for
' errors, checking the Errors collection if necessary.
On Error GoTo Err_Execute
cmdTemp.Execute
On Error GoTo 0 ' Retrieve the current data by requerying the recordset.
rstTemp.Requery
Exit SubErr_Execute: ' Notify user of any errors that result from
' executing the query.
If rstTemp.ActiveConnection.Errors.Count > 0 Then
For Each errLoop In Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End If
Resume NextEnd SubPublic Sub PrintOutput(rstTemp As ADODB.Recordset) ' Enumerate Recordset.
Do While Not rstTemp.EOF
Debug.Print " " & rstTemp!Title & _
", " & rstTemp!Type
rstTemp.MoveNext
LoopEnd Sub
Public Sub AddNewX() Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim booRecordAdded As Boolean 'myodbc3-test 是你的mysql的ODBC数据源.
' Open a connection.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=MSDASQL.1;" & _
"Data Source=myodbc3-test;User Id=sa;Password=; "
cnn1.Open strCnn
' Open Employee table.
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable ' Get data from the user. The employee ID must be formatted as
' first, middle, and last initial, five numbers, then M or F to
' signify the gender. For example, the employee id for Bill Sornsin
' would be "B-S55555M".
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:")) ' Proceed only if the user actually entered something
' for both the first and last names.
If (strID <> "") And (strFirstName <> "") _
And (strLastName <> "") Then rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True ' Show the newly added data.
MsgBox "New record: " & rstEmployees!emp_id & " " & _
rstEmployees!fname & " " & rstEmployees!lname Else
MsgBox "Please enter an employee ID, " & _
"first name, and last name."
End If
' Delete the new record because this is a demonstration.
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
rstEmployees.Close
cnn1.CloseEnd Sub