Excel中VBA编程学习笔记（⼗四）--Access数据库录⼊操作数据库表如下：
【例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()
vba自学好学吗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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。