vba宏ORACLE连接

一种：

Sub test()
'
' test 宏
'

Dim cn       As ADODB.Connection
            Dim rs       As ADODB.Recordset
            Set cn = New ADODB.Connection
            cn.Open "Provider=OraOLEDB.Oracle.1;PassWord=XXXX;Persist Security Info=True;User ID=XXXX;Data Source=XXXX;Extended Properties=PLSQLRSet=1"
            strQuery = "select count(*) as rscount from 工作人员"
            Set rs = New ADODB.Recordset
            rs.Open strQuery, cn, adOpenStatic, adLockOptimistic
            MsgBox rs("rscount")
End Sub

另一种：

   Dim Con As New ADODB.Connection
   Dim rst As New ADODB.Recordset
   Dim Cmd As New ADODB.Command
   Dim rsSQL As String
   Dim PassId As Integer
   Dim State As Integer
   Dim Prm1 As New ADODB.Parameter
   Dim Prm2 As New ADODB.Parameter
Con.ConnectionString = "Provider=OraOLEDB.Oracle.1;PassWord=cqboa;Persist Security Info=True;User ID=cqboa;Data Source=nsoa;Extended Properties=PLSQLRSet=1"

Con.Open
rsSQL = "select t.aa from table t where t.编号 = " & "5484"
With Cmd
     .ActiveConnection = Con
     .CommandType = adCmdText
     .CommandText = rsSQL
      End With
     rst.CursorType = adOpenStatic
     rst.LockType = adLockReadOnly
     Set rst.Source = Cmd
     rst.Open
     Set PassId = rst.Fields("编号").Value
     Set State = 0      
     Set rst = Nothing
     Set Cmd = Nothing    
   Con.Close