vba 经典 ADO/ODBC/Oracle 10g EZConnect 的连接字符串语法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3398272/
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
Connection string syntax for Classic ADO / ODBC / Oracle 10g EZConnect
提问by downwitch
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:
我正在尝试使用 ADO (2.8) 而没有 TNS 将各种 VBA 项目连接到 Oracle 10g 后端。经过各种尝试，我们决定全新安装的最简单的一系列步骤包括：
- 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)
- 设置 Oracle Instant Client
- 安装随附的 ODBC 驱动程序
- （通过 SQL Plus 使用 EZCONNECT 测试连接）
- （通过创建 Windows DSN 测试连接）
Everything up to here works fine. Problem is, I cannot figure out the syntax to tell ADO to use the instant clientODBC driver, which appears in my ODBC driver list as "Oracle in MyTest" (no quotes). Using the MSFT ODBC driver with EZConnect as this postsuggests does not work any better than it did prior to setting up the instant client (which is to say, not at all). But this postseems to suggest it's possible, without stating exactly how, and connectionstrings.comonly tells you what the Data Source portion of the string looks like, i.e. SomeUser/SomePassword@AServer:PortNumber/InstanceName
到这里为止一切正常。问题是，我无法弄清楚告诉 ADO 使用即时客户端ODBC 驱动程序的语法，该驱动程序在我的 ODBC 驱动程序列表中显示为“Oracle in MyTest”（无引号）。正如本文所建议的那样，将 MSFT ODBC 驱动程序与 EZConnect 一起使用并没有比设置即时客户端之前的效果更好（也就是说，根本没有）。但是这篇文章似乎暗示这是可能的，没有说明具体如何，并且connectionstrings.com只告诉您字符串的数据源部分是什么样的，即 SomeUser/SomePassword@AServer:PortNumber/InstanceName
Short version: What is the exact syntax of a classic ADO connection string referencing an instant client ODBC driver?
简短版本：引用即时客户端 ODBC 驱动程序的经典 ADO 连接字符串的确切语法是什么？
Thanks in advance for your help. Took me a stupid long time to get going with SO...
在此先感谢您的帮助。我花了很长时间才开始使用 SO...
回答by Richard
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:
类似于 'user1206604's answer - 我使用 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.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:
ODBC 数据源管理员位于开始菜单 > 程序 > Oracle - oraClient10g > 配置和迁移工具 > Microsoft ODBC 管理器（在我的机器上），如下所示：
回答by JamieSee
Try this and replace the values as appropriate:
试试这个并根据需要替换值：
Set Connection = CreateObject("ADODB.Connection")
blnTest = Connection.Open("Driver={Oracle in instantclient};Dbq=127.0.0.1:1521/SERVICENAMEHERE", "USERNAME", "PASSWORD")
If Oracle in instantclient doesn't work check the HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers registry key to see what the value is for the Oracle Instant Client (there may be a version number appended).
如果 Oracle 在 InstantClient 中不起作用，请检查 HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers 注册表项以查看 Oracle Instant Client 的值（可能附加了版本号）。
If this still doesn't work for you. Leave a comment with the details of what happened and I'll try to adjust the answer for you.
如果这仍然不适合您。对发生的事情的详细信息发表评论，我会尽力为您调整答案。
回答by JamieSee
' Create a connection object.'
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
' Create a recordset object.'
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
' Provide the connection string.'
Dim strConn As String
Dim str As String
'Use the SQL Server OLE DB Provider.'
strConn = "Driver=(Oracle in OraHome92);" & "Data Source=;Uid=;Pwd=;"
'Now open the connection.'
cn.Open strConn
With rs
' Assign the Connection object.'
ActiveConnection = cn
' Extract the required records.'
.Open "SELECT ", cn
End With
