使用 VBA 连接到 Oracle 数据库的驱动程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34594705/
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
Drivers to connect to an Oracle Database using VBA
提问by Firilou
I'm trying to connect to an Oracle Database from an Excel application and whithout a DNS. I found on a website that it's possible to use ADO, so this is why I tried to do. I'm new to this so I juste copied what I found on this website.
我正在尝试从 Excel 应用程序连接到 Oracle 数据库，而没有 DNS。我在一个网站上发现可以使用 ADO，所以这就是我尝试这样做的原因。我是新手，所以我只是复制了我在本网站上找到的内容。
Here is my code so far :
到目前为止，这是我的代码：
Sub ADOtest()
Dim connection As New ADODB.connection
connection.ConnectionString = "UID = user1; PWD= my_pwd; DRIVER = {Microsoft ODBC for Oracle; Server= localhost; Database= orcl.my_domain;"
connection.Open
End sub
When I run this code, I get an error saying that the driver was not found.
当我运行此代码时，我收到一条错误消息，指出未找到驱动程序。
The problem is that I have no idea of what I have to do with the driver (how to install it and configure it). Plus, I don't know which one I should use : I've read that there is a driver from Microsoft, another one from Oracle and also I've seen something about providers like msdaora.
问题是我不知道我与驱动程序有什么关系（如何安装和配置它）。另外，我不知道应该使用哪个驱动程序：我读过有一个来自 Microsoft 的驱动程序，另一个来自 Oracle 的驱动程序，而且我还看到了一些关于 msdaora 之类的提供程序的信息。
The program will be used by many users, so I would like to choose the solution that is the lightest (not much to install on computers).
该程序将被许多用户使用，因此我想选择最轻的解决方案（在计算机上安装不多）。
Thank you !
谢谢 ！
回答by Wernfried Domscheit
For COM based ADO (ADODB) you can use the OLE DB Providers.
对于基于 COM 的 ADO ( ADODB)，您可以使用 OLE DB 提供程序。
One is from Oracle, called "Oracle Provider for OLE DB". You can download it from 32-bit Oracle Data Access Components (ODAC) and NuGet Downloads(assuming your Excel is 32-bit). The connection string would be
一种来自 Oracle，称为“Oracle Provider for OLE DB”。您可以从32 位 Oracle 数据访问组件 (ODAC) 和 NuGet 下载（假设您的 Excel 是 32 位）下载它。连接字符串将是
"Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=myUsername;Password=myPassword"
The other one is from Microsoft. Please note, this provider is deprecated, you should not use it for new projects. Usually it should be available on your Windows. Be aware, like the provider from Oracle it also requires an Oracle Client to be installed on the PC! The connection string would be
另一个来自微软。请注意，此提供程序已弃用，您不应将其用于新项目。通常它应该在您的 Windows 上可用。请注意，与 Oracle 的提供程序一样，它也需要在 PC 上安装 Oracle 客户端！连接字符串将是
"Provider=MSDAORA;Data Source=orcl;User ID=myUsername;Password=myPassword"
The data source is usually defined in tnsnames.orafile or at a LDAP server, for example:
数据源通常在tnsnames.ora文件或 LDAP 服务器中定义，例如：
orcl.my_domain =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(Host = localhost)(Port = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
If you don't have such entry you can put everything into the connection string, e.g.
如果您没有这样的条目，您可以将所有内容放入连接字符串中，例如
"Provider=OraOLEDB.Oracle;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)));User ID=myUsername;Password=myPassword"
Perhaps you have to enclose the data source value by double-quotes ("), I am not sure.
也许您必须用双引号 ( ")将数据源值括起来，我不确定。
So, in any case you would have to install an Oracle Client at all PC's.
因此，在任何情况下，您都必须在所有 PC 上安装 Oracle Client。
Where is your database server hosted? In your question you say Server=localhost;, this would be quite unlikely, i.e. it is in contradiction to The program will be used by many users. I doubt everybody has an Oracle Database server installed on his local host.
您的数据库服务器托管在哪里？在您的问题中，您说Server=localhost;这不太可能，即它与该程序将被许多用户使用相矛盾。我怀疑每个人的本地主机上都安装了 Oracle 数据库服务器。
