Classic ADO / ODBC / Oracle 10g EZConnect的连接字符串语法 [英] Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect

问题描述

我正在尝试使用ADO（2.8）和没有TNS将各种VBA项目连接到Oracle 10g后端。经过多次尝试，我们决定了一个干净安装的最简单的步骤包括：

1. 设置Oracle Instant Client
2. 
   
3. 安装随附的ODBC驱动程序


4. （通过SQL Plus测试使用EZCONNECT的连接）


5. （测试连接通过创建一个Windows DSN）

到此处的所有内容都可以正常工作。问题是，我无法弄清语法来告诉ADO使用我的ODBC驱动程序列表中显示为MyTest中的Oracle（没有引号）的即时客户端 ODBC驱动程序。将EZConnect的MSFT ODBC驱动程序用作此帖子建议在建立即时客户端之前没有任何工作（也就是说，根本不是）。但是这篇文章似乎表明这是可能的，而无需说明如何和 connectionstrings.com 只告诉你字符串的数据源部分是什么样的，即
SomeUser / SomePassword @ AServer：PortNumber / InstanceName

简短版本：什么是引用即时客户端ODBC驱动程序的经典ADO连接字符串的确切语法？

提前感谢您的帮助。给我一个愚蠢的很长一段时间，以便... ...

解决方案

类似于'user1206604的答案 - 我设置了一个ODBC连接使用ODBC数据源管理员（例如，我们将其命名为DEMO）并连接如下：

  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.打开yourQueryString，conn，adOpenDynamic，adLockReadOnly 
'根据需要调整以上设置
 
而不是api.EOF 
'在这里做有趣的东西
 wend 
 
'清理资源
 api.Close 
 Set api = Nothing 
 
 conn.Close 
 Set conn = Nothing 
开始菜单>程序> Oracle  -  oraClient10g>配置中找到ODBC数据源管理员（在我的机器上）迁移工具> Microsoft ODBC管理员，如下所示：
 
 
 
  
 
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:

Set up an Oracle Instant Client
Install the accompanying ODBC driver
(Test the connection using EZCONNECT via SQL Plus)
(Test the connection by creating a Windows DSN)
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

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...
解决方案
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
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:



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