Make your own free website on Tripod.com

CONNECTING TO ORACLE
FROM VB 6.0 WITH ADO


The VB application I've created consists of a Main Form and five individual forms. In a .BAS Module, I have declared my connection variables and created a connection object in a Sub Procedure within the Module. These items are Public to enable the same connection to be used on all forms in the application. The recordsets used are opened and closed as their respective forms are opened and closed. However, the connection is opened when the Main Form is loaded and remains opened to be used as the Active Connection for all subsequent Recordsets on their respective forms. The Connection is not closed until the user exits the application via the Main Form. This eliminates having multiple connections and thus reduces network traffic:


THE MODULE AND SUB PROCEDURE FOR THE OPEN CONNECTION:

Option Explicit

Public rs As New ADODB.Recordset

Public mdkcmd As New ADODB.Command

Public conn As New ADODB.Connection

Public myobj As Object


Public Sub OpenConn()

With conn

.CursorLocation = adUseClient

.ConnectionString = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"

.Open

End With

End Sub


In the Load Event of my Main Form, I open the Connection:

Private Sub Form_Load()

OpenConn

End Sub



THE MAIN FORM:

My main form consists of five Command Buttons and an Exit button. Each Command Button will open the subsequent forms within the application. In the Click Event of these buttons, I simply show the respective form.

Private Sub cmdKirbyCustomer_Click()

frmKirbyCustomer.Show

End Sub



THE APPLICATION FORMS:

CUSTOMER, STOCK, ORDERS, ITEM AND HISTORY

In the Form Load Event of each subsequent form I choose my recordset options, open my Recordset off of the established Connection and fill the Text Boxes on the form with the appropriate data from the Fields in my Recordset using a sub procedure called "FillFields":


Private Sub Form_Load()

With rs

.CursorLocation = adUseClient

.CursorType = adOpenKeyset

.LockType = adLockOptimistic

.CacheSize = 50

.Source = "Select * from Customer"

.ActiveConnection = "dsn=MizMoORCL;uid=MizMo;pwd=Pooh"

.Open

End With

FillFields

End Sub


THE FILLFIELDS PROCEDURE:

NOTE: In order for the Text Boxes to accept Null Values, I have set their Text Properties equal to the Recordset's Field Value and an Empty String:

Public Sub FillFields()

txtCustomerID.Text = rs.Fields("CustomerID") & ""

txtCompanyName = rs.Fields("CompanyName") & ""

txtLastName = rs.Fields("LastName") & ""

txtFirstName = rs.Fields("FirstName") & ""

txtAddress = rs.Fields("Address") & ""

txtCity = rs.Fields("City") & ""

txtState = rs.Fields("State") & ""

txtZip = rs.Fields("ZipCode") & ""

txtPhone = rs.Fields("Phone") & ""

txtFax = rs.Fields("Fax") & ""

End Sub


RETURN TO HOME PAGE