使用Insert Into语句可将Excel工作表中的数据添加到Access数据库的指定表中。
有如下数据库Northwind：
将上面“供应商”的字段名填充到工作表“供应商”中，代码如下：
Sub 生成表单()
Dim cnn As New Connection, rs As New Recordset, fd As Field
Dim strSql As String, i As Long
On Error Resume Next
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & ThisWorkbook.Path & "\Northwind.mdb"
rs.Open "select * from 供应商", cnn
With Worksheets("供应商")
For i = 1 To rs.Fields.Count
.Cells(i, 1) = rs.Fields(i).Name
Next
End With
cnn.Close
Set cnn = Nothing
Set rs = Nothing
End Sub
代码运行后，“供应商”的字段名填充到工作表“供应商”中，效果如下：
按字段名录入数据：
需要将上面的内容添加到数据库对应的表“供应商”中，编写代码如下：
Sub 添加供应商信息()
Dim cnn As New Connection
Dim strSql As String, i As Long, c As Long
On Error Resume Next
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & ThisWorkbook.Path & "\Northwind.mdb"
strSql = "INSERT INTO 供应商("
With Worksheets("供应商")
c = .Range("A1").End(xlDown).Row
For i = 1 To c '生成字段列表
strSql = strSql & .Cells(i, 1) & ","
Next
strSql = Left(strSql, Len(strSql) - 1) & ") VALUES("
For i = 1 To c '生成插入的值列表
strSql = strSql & "'" & .Cells(i, 2) & "',"
Next
strSql = Left(strSql, Len(strSql) - 1) & ")"
End With
cnn.Execute strSql ' 执行SQL语句
cnn.Close
Set cnn = Nothing
End Sub
上面的代码运行后，在工作表中的数据就添加到了数据库中，效果如下：
－End－