Excel VBA连接ORACLE数据库 | 您所在的位置:网站首页 › vba连接excel数据库查找 › Excel VBA连接ORACLE数据库 |
'装好oracle客户端,不需引用直接粘贴下列格式运行 Sub connect() 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 = "IP地址" Db = "数据库名" user = "用户名" pwd = "密码" strconnt = "DRIVER={Microsoft ODBC for Oracle};Password=apps;User ID=apps;Data Source=demo" ' connt.ConnectionString = strconnt connt.Open End Sub '========================= Sub linkOracle() Dim strConn As String '连接字符串 Dim dbConn As Object '连接对象 Dim resSet As Object '查询结果集 Dim db_sid, db_user, db_pass As String 'sid,用户名,密码 '设置自己的链接数据 db_sid = "数据库名" db_user = "用户名" db_pass = "密码" '创建对象 Set dbConn = CreateObject("ADODB.Connection") Set resSet = CreateObject("ADODB.Recordset") '拼接链接字符串 下面两个选一个 strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True" strConn = "Provider=MSDAORA.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True" '-----打开数据库---- dbConn.Open strConn '执行查询 Set resSet = dbConn.Execute("select * from 表名") '打印表头 For j = 0 To resSet.Fields.COUNT - 1 Cells(1, j + 1) = resSet.Fields(j).Name Next '粘贴结果 Range("A2").CopyFromRecordset resSet '-----关闭连接---- dbConn.Close '关闭数据库 End Sub |
CopyRight 2018-2019 实验室设备网 版权所有 |