对于经典的ADO / ODBC / Oracle 10g中EZCONNECT连接字符串语法 [英] Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

问题描述

我想不同的VBA项目连接到Oracle 10g的后端使用ADO（2.8），无TNS。各种尝试之后，我们决定最简单的一系列的全新安装步骤包括：

I'm trying to connect various VBA projects to an Oracle 10g back end using ADO (2.8) and no TNS. After various attempts, we've decided that the simplest series of steps for a clean installation include:

1. 设置了一个Oracle即时客户端
2. 安装附带的ODBC驱动程序
3. （测试通过SQL Plus中使用EZCONNECT连接）
4. （测试通过创建连接的Windows DSN）

一切都到这里工作正常。问题是，我不能找出语法告诉ADO使用的即时客户端的ODBC驱动程序，它会出现在我的ODBC驱动程序列表，（不包括引号），甲骨文在MyTest的。使用与EZCONNECT的MSFT ODBC驱动程序的<一个href="http://stackoverflow.com/questions/782181/connect-to-an-oracle-10g-database-with-microsoft-odbc-for-oracle">this帖子建议不起作用任何比它之前建立即时客户端（这是说，一点都没有）更好。但是，<一个href="http://stackoverflow.com/questions/1272998/do-i-still-need-to-install-oracle-client-if-using-microsoft-odbc-for-oracle-to-co">this帖子似乎表明这是可能的，没有说明究竟是如何和 connectionstrings.com 只告诉你什么数据字符串的源部的样子，即     对待SomeUser / SomePassword @ Aserver的：端口号/实例名称

Everything up to here works fine. Problem is, I cannot figure out the syntax to tell ADO to use the instant client ODBC driver, which appears in my ODBC driver list as "Oracle in MyTest" (no quotes). Using the MSFT ODBC driver with EZConnect as this post suggests does not work any better than it did prior to setting up the instant client (which is to say, not at all). But this post seems to suggest it's possible, without stating exactly how, and connectionstrings.com only tells you what the Data Source portion of the string looks like, i.e. SomeUser/SomePassword@AServer:PortNumber/InstanceName

短版：什么是一个典型的ADO连接字符串中引用的即时客户端ODBC驱动程序的确切语法

Short version: What is the exact syntax of a classic ADO connection string referencing an instant client ODBC driver?

在此先感谢您的帮助。我花了一个愚蠢的很长一段时间来获取与那么回事......

Thanks in advance for your help. Took me a stupid long time to get going with SO...

推荐答案

类似user1206604的答案 - 我设置使用ODBC数据源管理ODBC连接（例如起见，我们将其命名为DEMO），并连接如这样的：

Similar to 'user1206604's answer - I set up an ODBC connection using ODBC Data Source Administrator (for example's sake we'll name it 'DEMO') and connect like this:

Dim conn As New adodb.Connection
Set conn = New adodb.Connection

connStr = "Provider=OraOLEDB.Oracle;Data Source=DEMO;User Id=yourUserID;Password=yourPassword;"
conn.Open connStr

Dim api As New adodb.Recordset
Set api = New adodb.Recordset

yourQueryString = "SELECT foo FROM bar"
api.Open yourQueryString, conn, adOpenDynamic, adLockReadOnly 
'adjust above setting as needed

while not api.EOF
  'do interesting stuff here
wend

'clean up resources
api.Close
Set api = Nothing

conn.Close
Set conn = Nothing

ODBC数据源管理员发现（我的机器上）在开始菜单>程序>甲骨文 - oraClient10g>配置和迁移工具>微软ODBC管理器，看起来像这样：

The ODBC data source administrator is found (on my machine) in start menu > Programs > Oracle - oraClient10g > Configuration and Migration Tools > Microsoft ODBC Administrator and looks like this:

这篇关于对于经典的ADO / ODBC / Oracle 10g中EZCONNECT连接字符串语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！