AddNew 方法添加记录:使用 AddNew 方法创建具有指定名称的新记录。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 ' 打开连接。 Set cnn1 = New ADODB.Connection strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;" cnn1.Open strCnn
' 打开雇员表。 Set rstEmployees = New ADODB.Recordset rstEmployees.CursorType = adOpenKeyset rstEmployees.LockType = adLockOptimistic rstEmployees.Open "employee", cnn1, , , adCmdTable ' 从用户获取数据,雇员 ID 的格式必须为: ' 名、中间名和姓的三个首字母, ' 五位数字,以及性别标识 M 或 F。 ' 例如,Bill Sornsin 的雇员 ID 为:B-S55555M。 strID = Trim(InputBox("Enter employee ID:")) strFirstName = Trim(InputBox("Enter first name:")) strLastName = Trim(InputBox("Enter last name:")) ' P只在用户输入姓和名之后进行。 If (strID <> "") And (strFirstName <> "") _ And (strLastName <> "") Then rstEmployees.AddNew rstEmployees!emp_id = strID rstEmployees!fname = strFirstName rstEmployees!lname = strLastName rstEmployees.Update booRecordAdded = True ' 显示新添加的数据。 MsgBox "New record: " & rstEmployees!emp_id & " " & _ rstEmployees!fname & " " & rstEmployees!lname Else MsgBox "Please enter an employee ID, " & _ "first name, and last name." End If
' 删除新记录,因为这只是演示。 cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
rstEmployees.Close cnn1.CloseEnd Sub
查询记录用SQL语句这个不好教你,你只有买一本数据库的书看吧! 删除也可以用sql语句方法也可以用Delete 方法: 使用 Delete 方法从 Recordset 删除指定的记录。Public Sub DeleteX() Dim rstRoySched As ADODB.Recordset Dim strCnn As String Dim strMsg As String Dim strTitleID As String Dim intLoRange As Integer Dim intHiRange As Integer Dim intRoyalty As Integer ' 打开 RoySched 表。 strCnn = "Provider=sqloledb;" & _ "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; " Set rstRoySched = New ADODB.Recordset rstRoySched.CursorLocation = adUseClient rstRoySched.CursorType = adOpenStatic rstRoySched.LockType = adLockBatchOptimistic rstRoySched.Open "SELECT * FROM roysched " & _ "WHERE royalty = 20", strCnn, , , adCmdText ' 提示删除记录。 strMsg = "Before delete there are " & _ rstRoySched.RecordCount & _ " titles with 20 percent royalty:" & vbCr & vbCr Do While Not rstRoySched.EOF strMsg = strMsg & rstRoySched!title_id & vbCr rstRoySched.MoveNext Loop strMsg = strMsg & vbCr & vbCr & _ "Enter the ID of a record to delete:" strTitleID = UCase(InputBox(strMsg)) ' 移动到记录并保存数据以使其可被恢复。 rstRoySched.Filter = "title_id = '" & strTitleID & "'" intLoRange = rstRoySched!lorange intHiRange = rstRoySched!hirange intRoyalty = rstRoySched!royalty ' 删除记录。 rstRoySched.Delete rstRoySched.UpdateBatch ' 显示结果。 rstRoySched.Filter = adFilterNone rstRoySched.Requery strMsg = "" strMsg = "After delete there are " & _ rstRoySched.RecordCount & _ " titles with 20 percent royalty:" & vbCr & vbCr Do While Not rstRoySched.EOF strMsg = strMsg & rstRoySched!title_id & vbCr rstRoySched.MoveNext Loop MsgBox strMsg ' 恢复数据,因为这只是演示。 rstRoySched.AddNew rstRoySched!title_id = strTitleID rstRoySched!lorange = intLoRange rstRoySched!hirange = intHiRange rstRoySched!royalty = intRoyalty rstRoySched.UpdateBatch rstRoySched.CloseEnd Sub
还有操作数据库使用ADO对象,需要引用ADO对象库!!! 你首先要在VB菜单中: “工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library” 注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的。
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 ' 打开连接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
cnn1.Open strCnn
' 打开雇员表。
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable ' 从用户获取数据,雇员 ID 的格式必须为:
' 名、中间名和姓的三个首字母,
' 五位数字,以及性别标识 M 或 F。
' 例如,Bill Sornsin 的雇员 ID 为:B-S55555M。
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:")) ' P只在用户输入姓和名之后进行。
If (strID <> "") And (strFirstName <> "") _
And (strLastName <> "") Then rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True ' 显示新添加的数据。
MsgBox "New record: " & rstEmployees!emp_id & " " & _
rstEmployees!fname & " " & rstEmployees!lname Else
MsgBox "Please enter an employee ID, " & _
"first name, and last name."
End If
' 删除新记录,因为这只是演示。
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"
rstEmployees.Close
cnn1.CloseEnd Sub
删除也可以用sql语句方法也可以用Delete 方法:
使用 Delete 方法从 Recordset 删除指定的记录。Public Sub DeleteX() Dim rstRoySched As ADODB.Recordset
Dim strCnn As String
Dim strMsg As String
Dim strTitleID As String
Dim intLoRange As Integer
Dim intHiRange As Integer
Dim intRoyalty As Integer ' 打开 RoySched 表。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstRoySched = New ADODB.Recordset
rstRoySched.CursorLocation = adUseClient
rstRoySched.CursorType = adOpenStatic
rstRoySched.LockType = adLockBatchOptimistic
rstRoySched.Open "SELECT * FROM roysched " & _
"WHERE royalty = 20", strCnn, , , adCmdText ' 提示删除记录。
strMsg = "Before delete there are " & _
rstRoySched.RecordCount & _
" titles with 20 percent royalty:" & vbCr & vbCr
Do While Not rstRoySched.EOF
strMsg = strMsg & rstRoySched!title_id & vbCr
rstRoySched.MoveNext
Loop
strMsg = strMsg & vbCr & vbCr & _
"Enter the ID of a record to delete:"
strTitleID = UCase(InputBox(strMsg)) ' 移动到记录并保存数据以使其可被恢复。
rstRoySched.Filter = "title_id = '" & strTitleID & "'"
intLoRange = rstRoySched!lorange
intHiRange = rstRoySched!hirange
intRoyalty = rstRoySched!royalty ' 删除记录。
rstRoySched.Delete
rstRoySched.UpdateBatch ' 显示结果。
rstRoySched.Filter = adFilterNone
rstRoySched.Requery
strMsg = ""
strMsg = "After delete there are " & _
rstRoySched.RecordCount & _
" titles with 20 percent royalty:" & vbCr & vbCr
Do While Not rstRoySched.EOF
strMsg = strMsg & rstRoySched!title_id & vbCr
rstRoySched.MoveNext
Loop
MsgBox strMsg ' 恢复数据,因为这只是演示。
rstRoySched.AddNew
rstRoySched!title_id = strTitleID
rstRoySched!lorange = intLoRange
rstRoySched!hirange = intHiRange
rstRoySched!royalty = intRoyalty
rstRoySched.UpdateBatch rstRoySched.CloseEnd Sub
你首先要在VB菜单中:
“工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library”
注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的。