vba 通过 VBScript 连接到 Oracle

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/1322829/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 10:43:54  来源:igfitidea点击:

Connection to Oracle via VBScript

ms-accessvbaodbcadodb

提问by chinnagaja

I have two databases on the same server with the same name and different port number. The tnxnames.oraentry looks like (actually one database is a clone of the other one.)

我在同一台服务器上有两个具有相同名称和不同端口号的数据库。该tnxnames.ora条目看起来像(实际上一个数据库是另一个数据库的克隆。)

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

I am using MS-Access to develop the application and using ADODB connectivity to access the server. I am try to access the data from the server. My connection string is look like:

我使用 MS-Access 开发应用程序并使用 ADODB 连接访问服务器。我试图从服务器访问数据。我的连接字符串如下所示:

sConStr = "Driver={oracle in oraclient10g_home1};SERVER=SERVER_NAME;DBQ=AAAA;UID=username;Pwd=pswd"

Set oConn = New ADODB.Connection

oConn.connectionstring = sConStr
oConn.open

My problem is, my ADODB always connect to database port number 1530. I am not able to set it in the connetion string to access the database in the port 1521.

我的问题是,我的 ADODB 总是连接到数据库端口号 1530。我无法在连接字符串中设置它以访问端口 1521 中的数据库。

Is it possible to mention the port number in the connection string to access different database with the same name and in the same server?

是否可以在连接字符串中提及端口号以访问同一服务器中具有相同名称的不同数据库?

I need to prepare the application to access the cloned database instead of original one. Nothing but the port number is different between the databases.

我需要准备应用程序来访问克隆的数据库而不是原始数据库。数据库之间只有端口号不同。

回答by Fionnuala

You may find this useful: http://www.connectionstrings.com/oracle

您可能会发现这很有用:http: //www.connectionstrings.com/oracle

回答by Mark Nold

This may be a dumb question... but are both of those entries in your tnsnames.ora?

这可能是一个愚蠢的问题……但是这两个条目都在您的 tnsnames.ora 中吗?

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AAAA.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Try changing it so each name is unique.

尝试更改它,以便每个名称都是唯一的。

AAAA.FSA.GOV.UK =
(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1530)))
(CONNECT_DATA = (SID = AAAA)))


AA21.FSA.GOV.UK = 
(DESCRIPTION = 
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = SERVERNAME)(Port = 1521)))
(CONNECT_DATA = (SID = AAAA)))

Then on the command line try tnsping AAAA.FSA.GOV.UKand tnsping AAAA21.FSA.GOV.UK

然后在命令行上尝试tnsping AAAA.FSA.GOV.UKtnsping AAAA21.FSA.GOV.UK

回答by David Walker

Try adding your port number to the end of the server name separated by a colon.

尝试将您的端口号添加到以冒号分隔的服务器名称的末尾。

SERVER=SERVER_NAME:1521

I'm not an Oracle user, but that will work with MS SQL Server.

我不是 Oracle 用户,但这将适用于 MS SQL Server。