作者:乔山办公网日期:
返回目录:excel表格制作
如果你电脑上的PL/SQL能连上ORACEL数据库,那你的EXCEL就应该能连上,因为你已经安装过oracle客户端了。,连接不上可能是因为你的VBA中的数据库连接参数写的有问题。
Dim cn As New ADODB.Connection '定义数据链接对象e799bee5baa6e78988e69d83332 ,保存连接数据库信息;请先添加ADO引用
Dim rs As New ADODB.Recordset '定义记录集对象,保存数据表
Dim strCn As String, strSQL As String '字符串变量
Dim i As Integer, j As Integer, sht As Worksheet
strCn = "Provider=MSDAORA.1;Password="psw";User ID="userid";Data Source="DB";Persist Security Info=True" strSQL = "select * from tab"
cn.Open strCn
rs.Open strSQL, cn
xrow = 1
Do While (Not rs.EOF)
Sheet1.Cells(xrow, 1) = rs.Fields().Item(0)
....
xrow = xrow + 1
rs .MoveNext
Loop
End Sub
连接数据库代百码:
Private Sub btn登陆度_Click()
Dim conn As New ADODB.Connection
Dim strConn As String
strConn = "Provider=MSDAORA.1; user id="
strConn = strConn & txt用户名.Value
strConn = strConn & "; password=" & txt密码.Value
strConn = strConn & "; data source = " & txt数据库回.Value
strConn = strConn & "; Persist Security Info=True"
conn.Open strConn '打开答数据库
OraOpen = True
MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful"
Exit Sub
Sub connect_数据zd库()
Dim strconnt As String
Set connt = New ADODB.Connection
Dim rs As Object
Set rs = New ADODB.Recordset
Dim sevip, Db, user, pwd As String
'设服务回器地址、所连数据,及登答录用户密码
sevip = "****"
Db = "****"
user = "****"
pwd = "****"
strconnt = "DRIVER={Microsoft ODBC for Oracle};Password=apps;User ID=apps;Data Source=demo" '
connt.ConnectionString = strconnt
connt.Open
End Sub