(1条消息) Excel中VBA编程学习笔记（十五）

【例1】使用Connection对象的Execute方法删除数据

数据库格式:

Sub test()

Dim conString$, sqlString$

Dim cnn, rst

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

Dim i%, sex$, Address$, Name$, birthDay$

conString = "provider=Microsoft.ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.accdb;"

cnn.Open conString

sqlString = "delete from students where sName='张三'"

cnn.Execute sqlString

MsgBox "删除成功"

cnn.Close

End Sub

【例2】使用Recordset删除数据

数据表字段如下：

Sub exercise()

Dim cnn ', rst

Set cnn = CreateObject("ADODB.connection")

'Set rst = CreateObject("ADODB.recordset")

Dim rst As New ADODB.Recordset

Dim sqlStr$, conStr$

conStr$ = "provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\test.mdb"

sqlStr = "select * from students where 总分 <161"

cnn.Open conStr$

rst.Open sqlStr, cnn, adOpenDynamic, adLockOptimistic

Do Until rst.EOF

rst.Delete

rst.MoveNext

Loop

cnn.Close

End Sub