使用Excel VBA 64位的ODBC(不是OLEDB)的Oracle 11g ADO连接字符串(DSN Less和tnsnames) [英] Oracle 11g ado connection strings for ODBC (not OLEDB) using excel VBA 64 bit (DSN Less and tnsnames)

问题描述

请帮助.我已经研究了几个小时.我需要一些部分进行工作，而其他部分则没有.

Please help. I have researched this for hours. I get some parts to work but not others.

我想做的是在excel VBA中编写所有连接字符串以连接到Oracle 11g数据库.我不想在ODBC管理器中设置用户DSN，也不想维护tnsnames.ora文件.

What I am trying to do is write all the connection strings in excel VBA to connect to Oracle 11g database. I don't want to set up the User DSN in ODBC Administrator and I don't want to have to maintain a tnsnames.ora file.

我可以使它适用于OLEDB连接字符串，但我相信Oracle不再支持此功能，因此我只想使用Oracle ODBC Driver命令.

I can get this to work for OLEDB connection strings but I believe this is no longer supported by Oracle so I want to use the Oracle ODBC Driver commands only.

这就是我要工作的(需要tnsnames.ora文件)

This is what I have got to work (which requires a tnsnames.ora file)

DRIVER={Oracle in OraClient11g_home1};DBQ=MyTNSnamesALias;UID=xxxx;PWD=xxxx

我也尝试过此操作，但收到TNS协议错误

I have tried this also but I get a TNS Protocol error

DRIVER={Oracle in OraClient11g_home1};
Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=xxxx)))
(CONNECT_DATA=(SERVICE_NAME=xxx)(SERVER=DEDICATED)));UID=xxxx;PWD=xxxx

其他有用的信息可能是当我按照ODBC管理器使用DSN名称时，我的连接可以正常工作.

Other useful information might be that i get my connections to work just fine when I use the DSN name as per the ODBC Administrator.

任何建议将不胜感激

谢谢

推荐答案

仍支持来自Oracle(Provider=OraOLEDB.Oracle)的OLEDB提供程序，仅来自Microsoft(Provider=msdaora)的提供程序

OLEDB Provider from Oracle (Provider=OraOLEDB.Oracle) is still supported, just the provider from Microsoft (Provider=msdaora) is deprecated. Microsoft recommends to use the Oracle provider.

Microsoft提供程序msdaora甚至不存在64位.

Microsoft provider msdaora does even not exist for 64 Bit.

我认为您的Oracle ODBC连接字符串必须是以下字符串(不包含换行符):

I think your connection string for Oracle ODBC must be this (without the line breaks):

Driver={Oracle in OraClient11g_home1};
   DBQ=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=hostname)(PORT=xxxx)))(CONNECT_DATA=(SERVICE_NAME=xxx)(SERVER=DEDICATED)));
   Pwd=xxxx;
   Uid=xxxx

注意，对于Oracle驱动程序，必须使用DBQ而不是Server.

Note, for the Oracle driver you must use DBQ instead of Server.

Server是Microsoft ODBC驱动程序的属性(例如Driver={Microsoft ODBC for Oracle})

Server is the attribute for the Microsoft ODBC driver (e.g. Driver={Microsoft ODBC for Oracle})

这篇关于使用Excel VBA 64位的ODBC(不是OLEDB)的Oracle 11g ADO连接字符串(DSN Less和tnsnames)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！