Accessing Oracle from Visual Basic

Accessing Oracle from Visual Basic

Connectivity to Oracle is provided via ODBC or OO4O (Oracle Objects for OLE). For more information about ODBC, read the ODBC FAQ. For information about OO4O, read the OO4O FAQ.

Look at these examples:

DAO Example (Data Access Objects):

Dim wstemp As Workspace

Dim dbtemp As Database

Dim rstemp As Recordset

Set wstemp = DBEngine.Workspaces(0)

Set dbtemp = wstemp.OpenDatabase("", False, False, "ODBC;DSN=Oracle;USR=scott;PWD=tiger")

Set rstemp = dbtemp.OpenRecordset(myquery.Text, dbOpenDynaset, dbSQLPassThrough)

howmany = 0

Combo1.Clear

Do Until rstemp.EOF

        msgbox rstemp(0)

        rstemp.MoveNext

        howmany = howmany + 1

Loop

RDO example (Remote Data Objects):

Dim contemp As New rdoConnection

Dim rstemp  As rdoResultset

Dim envtemp As rdoEnvironment

Set envtemp = rdoEngine.rdoEnvironments(0)

envtemp.CursorDriver = rdUseServer

                  ' or rdUseOdbc, rdUseNone, rdUseIfNeeded, rdUseClientBatch

With contemp

  .Connect = "ODBC;DSN=Oracle;USR=scott;PWD=tiger"

  .EstablishConnection rdDriverNoPrompt, false, rdoForwardOnly

                                           ' or rdoStatic, rdoKeyset, rdoDynamic

End With

Set rstemp = contemp.OpenResultset("select ...") ' Your SQL here

howmany = 0

With rstemp

Do Until .EOF Or howmany > 2000

   msgbox .rdoColumns(0) ' Popup a message box showing the 1st column

   .MoveNext

   howmany = howmany + 1

Loop

ADO example:

Option Explicit

Public m_adoCnn As New ADODB.Connection

Public m_adoRst As New ADODB.Recordset

Public Sub Command1_Click()

    m_adoCnn.ConnectionString = "Provider=MSDAORA;Password=tiger;User ID=scott;Data Source=database"

    m_adoCnn.Open

End Sub

Private Sub Form_Unload(Cancel As Integer)

    m_adoCnn.Close

End Sub

OO4O example:

Option Explicit

Dim m_oraSession  As Object

Dim m_oraDatabase As Object

Private Sub Command1_Click()

    Set m_oraSession = CreateObject("OracleInProcServer.XOraSession")

    Set m_oraDatabase = m_oraSession.DBOpenDatabase("Database", "user/password", 0&)

   

    MsgBox "Connected to " & m_oraDatabase.Connect & "@" & m_oraDatabase.DatabaseName

End Sub

Private Sub Form_Unload(Cancel As Integer)

    Set m_oraDatabase = Nothing

    Set m_oraSession = Nothing

End Sub

Counting records in a recordset

When you do a recordcount and it return only one record in the recordset, while you know there are more records, you need to move to the last record before doing the count. Look at this example.

Dim rs As Recordset

rs.MoveLast

TxtNumRows.Text = rs.RecordCount