VB 访问Oracle方法汇总实例

最近学习使用Oracle.收集了一个VB访问Oracel数据库的方法。自已全部测试了一下，均无问题

Private Sub FetchDAO()

    Dim dbMovies As DAO.Database

    Dim rsMovies As DAO.Recordset

    Dim strSQL As String

 

    Dim iField As Integer

 

   Set dbMovies = Workspaces(0).OpenDatabase("", False, False, "DSN=orcl;UID=store;pwd=store_password")

 

 

 

'    strSQL = "select * from [STORE.CUSTOMERS] order by customer_id"

'

'    Set rsMovies = dbMovies.OpenRecordset(strSQL, dbOpenSnapshot)

 

    strSQL = "select * from CUSTOMERS order by customer_id "

    Set rsMovies = dbMovies.OpenRecordset(strSQL, dbOpenSnapshot, dbSQLPassThrough)

   

'    Dim tb As DAO.TableDef

'    For Each tb In dbMovies.TableDefs

'        Debug.Print tb.Name

'

'    Next

    While Not rsMovies.EOF

        For iField = 0 To rsMovies.Fields.Count - 1

            Debug.Print rsMovies.Fields(iField).Value

        Next

        rsMovies.MoveNext

    Wend

End Sub

 

'Private Sub FetchRDO()

'    Dim rs As RDO.rdoResultset

'    Dim strSQL As String

'    Dim iColumn As Integer

'    Dim strConnection As String

'    Dim cn As rdoConnection

'

'

'    With rdoEngine

'        .rdoDefaultCursorDriver = rdUseClientBatch

'        .rdoDefaultLoginTimeout = 1

'    End With

'

'

'    strConnection = "dsn=orcl;uid=store;pwd=store_password"

'    Set cn = rdoEngine.rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, False, strConnection)

'

'    strSQL = "select * from customers order by customer_id"

'

'    Set rs = cn.OpenResultset(strSQL, rdOpenForwardOnly, rdConcurReadOnly)

'

'    While Not rs.EOF

'         For iColumn = 0 To rs.rdoColumns.Count - 1

'            Debug.Print rs(iColumn).Value

'         Next

'        rs.MoveNext

'    Wend

'

'    Set rs = Nothing

'    Set cn = Nothing

'

'

'End Sub

 

Private Sub FetchODBCDirect()

    Dim cn As Connection

    Dim rs As Recordset

    Dim strSQL As String

   

    DBEngine.DefaultType = dbUseODBC

   

    Dim strConn As String

    Dim iField As Integer

   

    strConn = "ODBC;dsn=orcl;uid=store;pwd=store_password"

   

    Workspaces(0).DefaultCursorDriver = dbUseNoCursor

    Set cn = Workspaces(0).OpenConnection("orcl", dbDriverNoPrompt, False, strConn)

   

    strSQL = "select * from customers order by customer_id"

    Set rs = cn.OpenRecordset(strSQL, dbOpenForwardOnly, 0, dbReadOnly)

   

    While Not rs.EOF

        For iField = 0 To rs.Fields.Count - 1

        

            Debug.Print rs(iField).Value

        Next

   

        rs.MoveNext

    Wend

   

 

End Sub

 

Private Sub Fetchoo4o()

    Dim OraSession As Object

    Dim OraDatabase As Object

    Dim rs As Object

    Dim strSQL As String

   

    Set OraSession = CreateObject("OracleInProcServer.XOraSession")

    Set OraDatabase = OraSession.OpenDatabase("orcl", "store/store_password", oradb_defalust)

   

   

    strSQL = "select * from customers order by customer_id"

    Set rs = OraDatabase.CreateDynaset(strSQL, oradyn_readonly)

    Dim iField As Integer

       

    While Not rs.EOF

        For iField = 0 To rs.Fields.Count - 1

       

            Debug.Print rs(iField).Value

        Next

   

        rs.MoveNext

    Wend

   

 

End Sub

 

 

private sub FetchADO()

      Dim strCnn As String
    strCnn = "Provider=OraOLEDB.Oracle.1;Password=test1;Persist Security Info=True;User ID=test1;Data Source=orcl;"
   
    Dim rst As New ADODB.Recordset
   
    Dim strSQL As String
    strSQL = "select sysdate from dual"
   
    rst.Open strSQL, strCnn, adOpenDynamic, adLockOptimistic
   
    If Not rst.EOF Then
        Debug.Print rst.Fields(0).Value
    End If
   

 

end sub