Question

vba 远程连接 Oracle 链接字符串

vba 远程连接 Oracle 链接字符串，我的代码如下，但是无论改IP为什么，没人都是链接本地数据源，请问应该如何改

'定义链接属性
Dim serverAdd As String
Dim oraUserId As String
Dim oraPwd As String
Dim oraDS As String
Dim oraSid As String

'初始化链接属性值
serverAdd = "127.0.0.1"
oraSid = "orcl"
oraUserId = "scott"
oraPwd = "tiger"
oraDS = "orcl"

On Error GoTo ConnectingError
'定义链接字符串并初始化
Dim connStr As String
connStr = "provider=ORAOLEDB.Oracle.1;Persist Security Info=True" & _
";USER ID=" & oraUserId & _
";PASSWORD=" & oraPwd & _
";SERVER=" & serverAdd & _
";DATA SOURCE=" & oraDS

'定义操作数据库 属性并初始化
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

'打开数据库
conn.Open connStr
rs.ActiveConnection = conn

'操作数据库
Dim sqlStr As String
sqlStr = "select * from empinfo"
rs.Open sqlStr, LockType:=adLockBatchOptimistic
正确答案如下
"Provider = OraOLEDB.Oracle;Persist Security Info=true;User ID = scott;Password = whg;Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=Orcl)))"

用的时候只需要把对应的host值改为目标地址即可

Answer 1

Dim strSql As String
strSql = "DRIVER={Microsoft ODBC for Oracle};UID="
strSql = strSql & [user].Value & ";PWD="
strSql = strSql & [Password].Value & ";SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST="
strSql = strSql & [ip].Value
strSql = strSql & ")(PORT="
strSql = strSql & [port].Value
strSql = strSql & "))(CONNECT_DATA=(SID="
strSql = strSql & [sid].Value
strSql = strSql & ")));"

 

将中括号中的内容通过设定名称指定对应单元格即可。另外，一定得安装好ORACLE客户端才行。

Answer 2

参考这个连接串
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;Integrated Security=no;"

追问

这个不行，Provider=Microsoft.Jet.OLEDB.4.0，找不到这个驱动，
另外，我说的是远程连接，需要远程机器的IP的，你这个串也没有啊

追答

HI我

Answer 3

需要改oracle的配置文件。