使用 VBA 将数据从 Excel 导出到 Access
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11232619/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Export data from Excel to Access using VBA
提问by meikse
I have a table in an Excel file with some data, and I want to export these data to my database on Access (in a concrete table on my database called Water Quality) with a VBA code to avoid to open my Database every time that I want to introduce more data on it.
我在 Excel 文件中有一个包含一些数据的表格，我想使用 VBA 代码将这些数据导出到我的 Access 数据库（在我的数据库中名为 Water Quality 的具体表格中），以避免每次打开我的数据库时想介绍更多关于它的数据。
At the moment I have this code but it's not working...
目前我有这个代码，但它不起作用......
Sub Button14_Click()
' Macro purpose: To add record to Access database using ADO and SQL
' NOTE: Reference to Microsoft ActiveX Data Objects Libary required
' Exports data from the active worksheet to a table in an Access database
'Dim cnt As New ADODB.Connection
'Dim rst As New ADODB.Recordset
Dim cnt As DAO.Database
Dim rst As Recordset
Dim dbPath As String
Dim tblName As String
Dim rngColHeads As Range
Dim rngTblRcds As Range
Dim colHead As String
Dim rcdDetail As String
Dim ch As Integer
Dim cl As Integer
Dim notNull As Boolean
Dim sConnect As String
'Set the string to the path of your database as defined on the worksheet
dbPath = "C:\Documents and Settings\Administrador\Mis documentos\MonEAU\modelEAU Database V.2.accdb"
tblName = "Water Quality"
Set rngColHeads = ActiveSheet.Range("tblHeadings")
Set rngTblRcds = ActiveSheet.Range("tblRecords")
'Set the database connection string here
sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbPath & "';" 'For use with *.accdb files
' sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath & ";" 'For use with *.mdb files
'Concatenate a string with the names of the column headings
colHead = " ("
For ch = 1 To rngColHeads.Count
colHead = colHead & rngColHeads.Columns(ch).Value
Select Case ch
Case Is = rngColHeads.Count
colHead = colHead & ")"
Case Else
colHead = colHead & ","
End Select
Next ch
'Open connection to the database
cnt.Open sConnect
'Begin transaction processing
On Error GoTo EndUpdate
cnt.BeginTrans
'Insert records into database from worksheet table
For cl = 1 To rngTblRcds.Rows.Count
'Assume record is completely Null, and open record string for concatenation
notNull = False
rcdDetail = "('"
'Evaluate field in the record
For ch = 1 To rngColHeads.Count
Select Case rngTblRcds.Rows(cl).Columns(ch).Value
'if empty, append value of null to string
Case Is = Empty
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL)"
Case Else
rcdDetail = Left(rcdDetail, Len(rcdDetail) - 1) & "NULL,'"
End Select
'if not empty, set notNull to true, and append value to string
Case Else
notNull = True
Select Case ch
Case Is = rngColHeads.Count
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "')"
Case Else
rcdDetail = rcdDetail & rngTblRcds.Rows(cl).Columns(ch).Value & "','"
End Select
End Select
Next ch
'If record consists of only Null values, do not insert it to table, otherwise
'insert the record
Select Case notNull
Case Is = True
rst.Open "INSERT INTO " & tblName & colHead & " VALUES " & rcdDetail, cnt
Case Is = False
'do not insert record
End Select
Next cl
EndUpdate:
'Check if error was encounted
If Err.Number <> 0 Then
'Error encountered. Rollback transaction and inform user
On Error Resume Next
cnt.RollbackTrans
MsgBox "There was an error. Update was not succesful!", vbCritical, "Error!"
Else
On Error Resume Next
cnt.CommitTrans
End If
'Close the ADO objects
cnt.Close
Set rst = Nothing
Set cnt = Nothing
On Error GoTo 0
End Sub
At the moment, the problem is when I debug the code, there appears the compiling error: "Method or data member not found" on the function "cnt.Open sConnect".
目前，问题是在调试代码时，在函数“cnt.Open sConnect”上出现编译错误：“Method or data member not found”。
If this is possible, any help would be greatly appreciated.
如果这是可能的，任何帮助将不胜感激。
Note: I'm using Office 2010.
注意：我使用的是 Office 2010。
回答by HansUp
Your compile error is due to these 2 lines:
您的编译错误是由于这两行：
Dim cnt As DAO.Database
cnt.Open sConnect
A DAO.Databaseobject does not have an .Openmethod, which explains "Method or data member not found". Too often error messages can be somewhat vague and just not very helpful. However, in this case, I can't think how the error message could be any more clear.
甲DAO.Database对象不具有一个.Open方法，其中解释“未找到方法或数据成员”。错误消息往往有些含糊，而且不是很有帮助。但是，在这种情况下，我想不出错误消息如何更清晰。
There is something more which I don't understand. sConnectlooks like an ADO connection string. But cntis a DAO(database) object. You can't mashup the two object models like that in one statement.
还有一些我不明白的东西。 sConnect看起来像一个 ADO 连接字符串。但是cnt是一个DAO（数据库）对象。您不能像这样在一个语句中混搭两个对象模型。
You have this just before your active variable declarations:
你在你的活动变量声明之前有这个：
'Dim cnt As New ADODB.Connection
Then later in your procedure, you have:
然后在您的程序稍后，您有：
'Close the ADO objects
cnt.Close
So perhaps you originally intended cntto be an ADO.Connectionobject and didn't adapt the rest of your code after you switched it to a DAO.Databaseobject.
因此，也许您最初打算cnt成为一个ADO.Connection对象，但在将其切换为DAO.Database对象后并未调整其余代码。
I suggest you revise your code to sort out the DAO vs. ADO confusion, then show us the new code if you have any remaining problems. And please show us only the minimum tested code required to reproduce the problem you're hoping to solve. TIA for your consideration.
我建议您修改您的代码以解决 DAO 与 ADO 的混淆，然后如果您有任何遗留问题，请向我们展示新代码。并且请仅向我们展示重现您希望解决的问题所需的最少测试代码。TIA 供您考虑。
回答by Colophi
I only have Access databases that open the excel file (instead of the other way around) but from looking through my code I think you should go straight to this:
我只有打开 excel 文件的 Access 数据库（而不是相反），但是通过查看我的代码，我认为您应该直接进入：
`Set cnt = OpenDatabase_
(dbPath, False, True, "Access 8.0;")
Found this on http://support.microsoft.com/kb/190195too.
在http://support.microsoft.com/kb/190195 上也找到了这个。
Does this help?
这有帮助吗？
