vba如何查询oracle

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

给你个连oracle的function

function中你需要提供5个数据：

oracle host name

oracle database name

SQL query （比如：select * from table1）

访问数据库的用户名

密码

Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)

 Dim strConOracle, oConOracle, oRsOracle

 Dim StrResult As String

 StrResult = ""

 strConOracle = "Driver={Microsoft ODBC for Oracle}" &_

        "CONNECTSTRING=(DESCRIPTION=" &_

        "(ADDRESS=(PROTOCOL=TCP)" &_

        "(HOST=" &strHost &")(PORT=1521))" &_

        "(CONNECT_DATA=(SERVICE_NAME=" &strDatabase &")))uid=" &strUser &" pwd=" &strPassword &""

 Set oConOracle = CreateObject("ADODB.Connection")

 Set oRsOracle = CreateObject("ADODB.Recordset")

 oConOracle.Open strConOracle

 Set oRsOracle = oConOracle.Execute(strSQL)

 MsgBox (oRsOracle.Fields(0).Value)

 varResult = oRsOracle.GetRows

 Do While Not oRsOracle.EOF

     If StrResult <>"" Then

       StrResult = StrResult &Chr(10) &oRsOracle.Fields(0).Value

     Else

       StrResult = oRsOracle.Fields(0).Value

     End If

   oRsOracle.MoveNext

 Loop

 oConOracle.Close

 Set oRsOracle = Nothing

 Set oConOracle = Nothing

 ORAQUERY = StrResult

End Function

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 = "****"

Db = "****"

user = "****"

pwd = "****"

strconnt = "DRIVER={Microsoft ODBC for Oracle}Password=appsUser ID=appsData Source=demo" '

connt.ConnectionString = strconnt

connt.Open

End Sub

---