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=appsUser ID=appsData Source=demo" '
connt.ConnectionString = strconnt
connt.Open
End Sub
1、打开VBA编辑器,在菜单中点选“工具”,“引用”;2、确保“Microsoft ActiviteX Data Objects 2.8 Library”和“Microsoft ActiviteX Data ObjectS Recordset 2.8 Library”被勾选上。
建立连接过程,代码如下:
复制代码 代码如下:
Public Sub ConOra()
On Error GoTo ErrMsg:
Dim ConnDB As ADODB.Connection
Set ConnDB = New ADODB.Connection
Dim ConnStr As String
Dim DBRst As ADODB.Recordset
Set DBRst = New ADODB.Recordset
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID="Orcl" 'Oracle数据库的相关配置
OraUsr="user"
OraPwd="password"
ConnStr = "Provider = MSDAORA.1Password=" &OraPwd &_
"User ID=" &OraUsr &_
"Data Source=" &OraID &_
"Persist Security Info=True"
ConnDB.CursorLocation = adUseServer
ConnDB.Open ConnStr
OraOpen = True '成功执行后,数据库即被打开
'MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful"
DBRst.ActiveConnection = ConnDB
DBRst.CursorLocation = adUseServer
DBRst.LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst.MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail ,please check!", vbCritical, "Connect fail!"
End Function
Public Sub ConOra()
On Error GoTo ErrMsg:
Dim ConnDB As ADODB.Connection
Set ConnDB = New ADODB.Connection
Dim ConnStr As String
Dim DBRst As ADODB.Recordset
Set DBRst = New ADODB.Recordset
Dim SQLRst As String
Dim OraOpen As Boolean
OraOpen = False
OraID="Orcl" 'Oracle数据库的相关配置
OraUsr="user"
OraPwd="password"
ConnStr = "Provider = MSDAORA.1Password=" &OraPwd &_
"User ID=" &OraUsr &_
"Data Source=" &OraID &_
"Persist Security Info=True"
ConnDB.CursorLocation = adUseServer
ConnDB.Open ConnStr
OraOpen = True '成功执行后,数据库即被打开
'MsgBox "Connect to the oracle database Successful!", vbInformation, "Connect Successful"
DBRst.ActiveConnection = ConnDB
DBRst.CursorLocation = adUseServer
DBRst.LockType = adLockBatchOptimistic
SQLRst = "Select * From TstTab"
DBRst.Open SQLRst, ConnDB, adOpenStatic, adLockBatchOptimistic
DBRst.MoveFirst
Exit Function
ErrMsg:
OraOpen = False
MsgBox "Connect to the oracle database fail ,please check!", vbCritical, "Connect fail!"
End Function
1、把开excel,点击工具-->宏-->录制新宏 2、点击 数据-->导入外部数据-->新建数据库查询,然后根据向导选择oracle驱动,最后完成向导 3、点停止宏,再点 工具-->宏-->宏... 在对话框里选择刚才录制的宏,点编辑, 这样就可以看到连接字符串了。欢迎分享,转载请注明来源:内存溢出
评论列表(0条)