偶遇到Excel向Access批量插入數(shù)據(jù)的題目，就做了一下總結(jié)堡掏。
ACCESS表
EXCEL表
要求：批量將Excel里的數(shù)據(jù)插入到Access對(duì)應(yīng)的表格中应结。
addnew和update方法（循環(huán)）
Public Sub addnew和update方法()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim mydata As String, myTable As String
Dim myFields As Variant, myValue As Variant
Dim arr
mydata = ThisWorkbook.Path & "\交叉錄入.accdb" '指定數(shù)據(jù)庫
myTable = "錄入" '指定數(shù)據(jù)表名稱
With cnn '建立與數(shù)據(jù)庫的連接
.Provider = "microsoft.ace.oledb.12.0"
.Open mydata
End With
arr = [a11:d15]
'創(chuàng)建指定數(shù)據(jù)表的記錄集
rs.Open myTable, cnn, adOpenKeyset, adLockOptimistic
'開始添加新記錄
For i = 1 To UBound(arr) '添加數(shù)據(jù)
myFields = Array("款號(hào)", "顏色", "尺碼", "數(shù)量")
myValues = Array(arr(i, 1), arr(i, 2), arr(i, 3), arr(i, 4))
With rs
.AddNew myFields, myValues
.Update
End With
Next
MsgBox "數(shù)據(jù)添加完畢！", vbInformation
'關(guān)閉記錄集和數(shù)據(jù)庫連接泉唁，釋放變量
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
insert方法（循環(huán)）
Public Sub insert方法()
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim arr
arr = [a11:d15].Value
'建立與建數(shù)據(jù)庫的連接
With cnn
.Provider = "microsoft.ace.oledb.12.0;"
.Open ThisWorkbook.Path & "\交叉錄入.accdb"
End With
'開始添加數(shù)據(jù)
For i = 1 To UBound(arr)
sr = "'" & arr(i, 1) & "','" & arr(i, 2) & "','" & arr(i, 3) & "','" & arr(i, 4) & "'"
SQL = "insert into 錄入(款號(hào),顏色,尺碼,數(shù)量) values( " & sr & ")"
cnn.Execute SQL
Next
MsgBox "成功添加" & UBound(arr) & " 條記錄鹅龄！", vbInformation, "添加記錄"
'釋放變量
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub
insert into ...select方法
'//下面程序可插入超過65536行記錄:
Sub 從Excel工作表中向數(shù)據(jù)表添加紀(jì)錄()
Dim cnn As New ADODB.Connection
Dim myTable As String
myTable = "錄入"
cnn.Open "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\交叉錄入.accdb"
SQL = "INSERT INTO " & myTable & " SELECT * FROM [Excel 12.0;Database=" & ThisWorkbook.FullName & ";].[" & ActiveSheet.Name & "$a10:d15]"
cnn.Execute SQL
MsgBox "紀(jì)錄添加成功。", vbInformation, "添加紀(jì)錄"
cnn.Close
Set cnn = Nothing
End Sub
示例文件下載：
鏈接: http://pan.baidu.com/s/1o7PJjy6 密碼: 7ixu
