vba 在 Office 2013 64 位上使用 OLEDB 连接
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26515823/
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
Working with OLEDB connection on Office 2013 64 bit
提问by dsauce
I know there are a number of threads related to this. However, none of them seemed to solve my problem. I am putting down my exact problem here. Can someone help me with this?
我知道有许多与此相关的主题。但是，它们似乎都没有解决我的问题。我把我的确切问题放在这里。有人可以帮我弄这个吗？
Environment- Windows 7 64 bit, Office 2013 64 bit
环境- Windows 7 64 位，Office 2013 64 位
Automation- A macro in VBA in Excel that will import some data to an .mdb file by connecting to it using the below mentioned driver
自动化- Excel 中 VBA 中的一个宏，它将通过使用下面提到的驱动程序连接到 .mdb 文件来将一些数据导入到 .mdb 文件中
Issue - Runtime error 3706, provider cannot be found. it may not be properly installed
问题 - 运行时错误 3706，找不到提供程序。它可能没有正确安装
Attempt 1- I checked the error code online and most of the threads pointed to syntax errors and way of connecting. I tried implementing the suggestions but there was no impact
尝试 1- 我在线检查了错误代码，大多数线程都指向语法错误和连接方式。我尝试实施这些建议，但没有任何影响
Attempt 2- I then found that this is because it was trying to use old driver with 64 bit office. Therefore, I then tried installing the most popular suggestion - http://www.microsoft.com/en-us/download/details.aspx?id=13255This also didn't work
尝试 2- 然后我发现这是因为它试图使用带有 64 位办公室的旧驱动程序。因此，我然后尝试安装最受欢迎的建议 - http://www.microsoft.com/en-us/download/details.aspx?id=13255这也不起作用
Code -
代码 -
database_path = Application.ActiveWorkbook.path & "\" & "mydb.mdb"
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & database_path
End With
cn.Open
Any solution is welcome as long as it works on all - office 2007, 2010 and 2013.
只要适用于所有解决方案 - Office 2007、2010 和 2013，任何解决方案都是受欢迎的。
Thanks!
谢谢！
回答by Tom
If you downloaded and installed the driver from the link you have in your question, you will notice that the "Install Instructions" has a section called "To use this download:". #2 under that section states that you need to change the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0”instead of "Microsoft.Jet.OLEDB.4.0".
如果您从问题中的链接下载并安装了驱动程序，您会注意到“安装说明”有一个名为“使用此下载：”的部分。该部分下的 #2 指出您需要将 ConnectionString 属性的 Provider 参数更改为“Microsoft.ACE.OLEDB.12.0”而不是"Microsoft.Jet.OLEDB.4.0"。
I ran your modified code with the replaced argument in Excel 32-bit and 64-bit and it worked:
我在 32 位和 64 位 Excel 中使用替换的参数运行了您修改后的代码，它工作正常：
Set cn = CreateObject("ADODB.Connection")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & database_path & ";"
cn.Open
End With
The ACE driver you downloaded can be used in Office 2007, 2010 and 2013 according to this article:
根据这篇文章，您下载的ACE驱动程序可以在Office 2007、2010和2013中使用：
Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
