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

数据库表如下:

【例1】使用Recordset插入数据

Excel表格内容如下:

Sub test()

'Dim cnn As New ADODB.Connection

'Dim rst As New ADODB.Recordset

Dim conString$, sqlString$

Dim cnn, rst

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

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

cnn.Open conString

rst.Open "select * from students where 1=2", cnn, adOpenDynamic, adLockOptimistic

With Worksheets("Sheet1")

Set arrAll = .Range("A1:E2")

arrFileds = Array("姓名", "数学", "语文", "总分", "计算日期")

datas = Array("张三", 88, 92, 180, "2015/12/23")

rst.AddNew arrFileds, datas

End With

cnn.Close

End Sub

【例2】使用Recordset插入数据

Option Explicit

Sub test()

Dim conString$, sqlString$

Dim cnn, rst

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

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

cnn.Open conString

rst.Open "select * from students where 1=2", cnn, adOpenDynamic, adLockOptimistic

With Worksheets("Sheet1")

rst.AddNew

rst!姓名 = "李四"

rst!数学 = 90

rst!语文 = 80

rst!计算日期 = Format(Now(), "yyyy-MM-dd")

rst.Update

'

End With

cnn.Close

End Sub

注意:rst!姓名与rst(“姓名”)等价,也可以用rst(0)表示第一个字段。

【例3】使用Connection对象的Execute方法添加数据

Excel数据如下:

Sub test()

'Dim cnn As New ADODB.Connection

'Dim rst As New ADODB.Recordset

Dim conString$, sqlString$

Dim cnn, rst

Dim i%, Math%, chinese%, Total%, Name$, Calculatedate$

Set cnn = CreateObject("ADODB.Connection")

Set rst = CreateObject("ADODB.Recordset")

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

cnn.Open conString

With Worksheets("Sheet1")

For i = 2 To .UsedRange.Rows.Count

Name = .Range("A" & i)

Math = .Range("B" & i)

chinese = .Range("C" & i)

Total = .Range("D" & i)

Calculatedate = .Range("E" & i)

sqlString = "insert into students(姓名,数学,语文,总分,计算日期) values('" & Name & "'," & Math & "," & chinese & "," & Total & ",'" & Calculatedate & "')"

cnn.Execute sqlString

Next

'

End With

cnn.Close

End Sub

(0)

相关推荐