OLEDB/ODBC在Python和MS Access VBA之间的相反工作 [英] Oppposite Workings of OLEDB/ODBC between Python and MS Access VBA

查看：78
本文介绍了OLEDB/ODBC在Python和MS Access VBA之间的相反工作的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

资深程序员:

如果这看起来像是疲惫的Python 32位/64位ODBC/OLEDB Windows问题，请原谅我，但我尝试搜索论坛，但找不到引起我问题的原因.

基本上，我尝试首先使用pypyodbc模块通过ODBC连接一个非常简单的Python脚本，然后尝试使用adodbapi模块通过OLEDB连接一个非常简单的Python脚本，两者均尝试连接到MS Access 2010 .accdb数据库.但是，对于OLEDB，我始终得到未找到'Provider的信息.它可能未正确安装".错误.对于ODBC，我始终收到未找到数据源名称且未指定默认驱动程序" .

深入挖掘，虽然发生了一些非凡的事情.在Python中，JET.OLEDB.4.0适用于.mdb文件，但不适用于ACE.OLEDB.12.0适用于.accdb文件.但是，当我运行Access VBA ADODB连接时，情况恰好相反！

我的环境包括:

  • Python3.4 -32位
  • pywin32-219(随安装后安装)
  • Microsoft Office 2010 64位
  • Windows 7

是的，我下载并成功安装了AccessDatabaseEngine_x64.exe.是的，我将ODBC数据源指向％Win％/SysWOW64.是的，我在上述文件夹的odbcad32.exe中看到了mdb和accdb的访问驱动程序和数据源.是的，我在regedit中看到Access(* mdb)和Access(* mdb，* accdb)数据源以及Access驱动程序的注册表项.是的，我都重新启动并关闭了机器.

OLEDB
以下是我的连接字符串，该字符串返回找不到提供者错误..." :

import adodbapi
databasename = 'D:\directorypath\DatabaseName.accdb'  
constr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s'  % databasename 
db = adodbapi.connect(constr)

但是，值得注意的是，以下连接字符串可以完美运行，但当然仅适用于.mdb文件:

import adodbapi
databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb' 
constr = 'Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s'  % databasename 
db = adodbapi.connect(constr)


ODBC
以下是我的连接字符串，该字符串返回'找不到数据源名称... ':

import pypyodbc
databasename = 'D:\directorypath\DatabaseName.accdb'
constr = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;'  % databasename
db = pypyodbc.connect(constr)

像上面一样，以下内容理想地工作，但仅适用于.mdb文件:

import pypyodbc
databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb'
constr = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;'  % databasename
db = pypyodbc.connect(constr)


访问VBA
有趣的是，在EXACT VBA模块(当然带有ActiveX数据对象库引用)中，使用驱动程序和提供程序使用相同的连接字符串时，会发生完全相反的情况.

代码对于.mdb和.accdb文件都非常有效:

Dim constr As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"

' OR constr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"

Set CN = New ADODB.Connection
CN.Open (constr)

代码返回错误-找不到提供程序" 或无法加载指定的驱动程序" :

Dim constr As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"

' OR constr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"

Set CN = New ADODB.Connection
CN.Open (constr)


ACE.OLEDB.12.0与ACE.OLEDB.14.0
最后，仍然在Access VBA中，只有提供程序:Microsoft.ACE.OLEDB.12.0可以正常工作，我认为它对应于MS Access2007.但是，提供程序:Microsoft.ACE.OLEDB.14.0不适用于我安装的MS Access 2010.有什么原因吗?

结论
我知道ActiveX库与Python的模块库是一种不同的技术，并且Microsoft不允许同时使用x32位和x64位组件，但是为什么我无法获得预期的结果.我考虑过AccessDatabaseEngine_x64.exe/passive，但是我听到奇怪的Office结果沿这条路线走了.我需要为64位安装Python3.4吗?请帮忙或建议！谢谢.

解决方案

简短答案:

是的，如果您安装了64位Office并希望通过Python处理Access数据库，则应尽量使用64位版本的Python，以最大程度地减少头痛.

更长的答案:

较旧的"Jet"引擎/驱动程序和较新的Access Database Engine(也称为"ACE")引擎/驱动程序是完全独立的实体.

较旧的"Jet"驱动程序... 

  ODBC: Driver={Microsoft Access Driver (*.mdb)}
OLEDB: Provider=Microsoft.Jet.OLEDB.4.0

...已安装为Windows操作系统的组成部分，但仅适用于32位应用程序.

您已经安装了64位Office，因此您拥有64位版本的较新的"ACE"驱动程序... 

  ODBC: Driver={Microsoft Access Driver (*.mdb, *.accdb)}
OLEDB: Provider=Microsoft.ACE.OLEDB.12.0

...，它们仅适用于64位应用程序.

您当前的32位Python环境可以使用Jet驱动程序，但不能使用ACE驱动程序

您具有Access的64位版本，因此您的VBA代码在64位Universe中运行，并且可以使用ACE驱动程序，但不能使用Jet驱动程序.

Fellow more advanced programmers:

Please forgive me if this seems like the tired Python 32-bit/64-bit ODBC/OLEDB Windows issue but I tried searching the forums and can't quite find the reason to my issue.

Basically, I tried to connect a very simple Python script first via ODBC using the pypyodbc module, and then second via OLEDB using the adodbapi module, both attempts to an MS Access 2010 .accdb database. However, for OLEDB I consistently get the 'Provider not found. It may not be properly installed' error. And for ODBC I consistently receive the 'Data source name not found and no default driver specified'.

Digging deeper, something remarkable happened though. In Python, JET.OLEDB.4.0 works for .mdb files but not ACE.OLEDB.12.0 for .accdb files. Yet, the exact reverse happens when I ran an Access VBA ADODB connection!

My environment includes:

  • Python3.4 -32-bit
  • pywin32-219 (installed with post-install)
  • Microsoft Office 2010 64-bit
  • Windows 7

Yes, I downloaded and successfully installed the AccessDatabaseEngine_x64.exe. Yes, I pointed ODBC data sources to %Win%/SysWOW64. Yes, I see the Access driver and datasources for mdb and accdb in odbcad32.exe in aforementioned folder. Yes, I see the registry keys in regedit for Access (*mdb) and Access (*mdb, *accdb) data sources and Access driver. And yes, I both restarted and shut off/on my machine.

OLEDB
The following is my connection string that returns the 'Provider not found error...':

import adodbapi
databasename = 'D:\directorypath\DatabaseName.accdb'  
constr = 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%s'  % databasename 
db = adodbapi.connect(constr)

Remarkably though, the following connection string works perfectly but of course only with .mdb files:

import adodbapi
databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb' 
constr = 'Provider=Microsoft.JET.OLEDB.4.0;Data Source=%s'  % databasename 
db = adodbapi.connect(constr)


ODBC
The following is my connection string that returns the 'Data source name not found...':

import pypyodbc
databasename = 'D:\directorypath\DatabaseName.accdb'
constr = 'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s;'  % databasename
db = pypyodbc.connect(constr)

Like above, the following works ideally but only for .mdb files:

import pypyodbc
databasename = 'D:\otherdirectorypath\OtherDatabaseName.mdb'
constr = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;'  % databasename
db = pypyodbc.connect(constr)


Access VBA
Interestingly, the EXACT reverse happens in an Access VBA module (of course with the ActiveX Data Object library reference) using the same connections strings both Driver and Provider.

Code works perfectly for both .mdb and .accdb files:

Dim constr As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"

' OR constr = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\databasedirectory\DatabaseName.accdb;Persist Security Info=False"

Set CN = New ADODB.Connection
CN.Open (constr)

Code returns error - 'Provider not found' or 'Specified driver could not be loaded':

Dim constr As String
Dim CN As ADODB.Connection
Dim RS As ADODB.Recordset

constr = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"

' OR constr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=D:\otherdatabasedirectory\OtherDatabaseName.mdb;Persist Security Info=False"

Set CN = New ADODB.Connection
CN.Open (constr)


ACE.OLEDB.12.0 VS ACE.OLEDB.14.0
Finally, still in Access VBA, only the Provider: Microsoft.ACE.OLEDB.12.0 works which I thought corresponded to MS Access 2007. However, the Provider: Microsoft.ACE.OLEDB.14.0 does not work for my installed MS Access 2010. Any reasons for this?

Conclusion
I understand ActiveX library is a different technology than Python's module library and Microsoft does not allow simultaneous use of components in x32-bit and x64-bit but why can't I get my intended result. I thought about the AccessDatabaseEngine_x64.exe /passive but I hear weird Office results going that route. Do I need to install Python3.4 for 64-bit? Please help or suggest! Thanks.

解决方案

Short answer:

Yes, to minimize headaches you should be running a 64-bit version of Python if you have 64-bit Office installed and you want to manipulate Access databases from Python.

Longer answer:

The older "Jet" engine/drivers and the newer Access Database Engine (a.k.a. "ACE") engine/drivers are completely separate entities.

The older "Jet" drivers... 

 ODBC: Driver={Microsoft Access Driver (*.mdb)}
OLEDB: Provider=Microsoft.Jet.OLEDB.4.0

... are installed as an integral part of the Windows OS but they only available to 32-bit applications.

You have 64-bit Office installed, so you have the 64-bit version of the newer "ACE" drivers ...

 ODBC: Driver={Microsoft Access Driver (*.mdb, *.accdb)}
OLEDB: Provider=Microsoft.ACE.OLEDB.12.0

... and they are only available to 64-bit applications.

Your current 32-bit Python environment can use the Jet drivers but not the ACE drivers

You have the 64-bit version of Access, therefore your VBA code is running in the 64-bit universe and it can use the ACE drivers but not the Jet drivers.

这篇关于OLEDB/ODBC在Python和MS Access VBA之间的相反工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Python最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆