Visual Basic Code of the Week (COTW)
Issue #68
Online Version at (paid subscribers only)
All content and source code is Copyright (c) 1999 by C&D Programming Corp. No part of this issue can be reprinted or distributed in any manner without express written permission of C&D Programming Corp.

Happy New Year

We hope everyone had a Happy New Year.

Free Software Giveaway

A new software giveaway has begun this month. Be sure to enter at now! If you entered last month, please be sure to enter again because your entry from last month does not count towards the prize this month.

In this Issue

This issue we are introducing a class module to enable easy access to Oracle database tables.

This source code is designed for VB 5.0 and up. Questions? Email us at


The Oracle Controller allows you to set a few properties and open a recordset much the same way as you would a Microsoft Access database. This class can probably be used to access any ODBC data source with no additional changes. We have only tested it while accessing Oracle data sources.

There are several properties, functions and methods that will be documented below.


Public Property Let Username(sUsername As String)
Public Property Get Username() As String
Public Property Let Password(sPassword As String)
Public Property Get Password() As String
Public Property Let ConnectionName(sConnectionName As String)
Public Property Get ConnectionName() As String

The Username and Password properties define the username and password to use when accessing the databases on the Oracle server. They default to null. ConnectionName is the ODBC data source name that was defined in the 32bit ODBC control panel object. There are ways to create data sources programmatically, but we are not going to cover that in this issue.

Public Function Connect() As Boolean
Public Sub Disconnect()

Connect is the method that will connect to the remote data source. If it returns True everything went okay. If it raises an error or returns False there was a problem connecting to the data source. Disconnect will disconnect from the remote data source.

Public Property Set Workspace(oWorkspace As Workspace)
Public Property Get Workspace() As Workspace

The Workspace property defines the reference to the Workspace object that was created to manage the database connections. This is not valid until the Connect method is called.

Public Property Set Connection(oConnection As Connection)
Public Property Get Connection() As Connection

The Connection property defines the connection that was made when the Oracle database was connected to with the Connect method.

Public Sub OpenRecordset(sSQL As String, dbType As RecordsetTypeEnum)
Public Property Get Recordset() As Recordset
Public Sub CloseRecordset()

OpenRecordset will create a recordset object based upon a SQL string that is passed to it. Refer to the help file for details on the RecordsetTypeEnum enumerator.

The Recordset property is how you access the recordset that was created with OpenRecordset.

CloseRecordset will close a recordset created with the OpenRecordset method.

Sample Usage

This sample opens the Business data source with the username ALLOC and the password ALLOC. It then opens a recordset looking for all the records in the Inventory table that have a price greater than 100.

    Dim Oracle As New OracleController
    Dim rsSrc As Recordset

    Oracle.Username = "ALLOC"
    Oracle.Password = "ALLOC"
    Oracle.ConnectionName = "Business"
    Oracle.OpenRecordset "select * from inventory WHERE price > 100.00", dbOpenSnapshot
    Set rsSrc = Oracle.Recordset
    Debug.Print "List of items with a price greater than 100."
    While Not rsSrc.EOF
        Debug.Print rsSrc.Fields("InvNum"), rsSrc.Fields("Description")
    Set Oracle = Nothing

Source Code

To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address:

This document is available on the web

Paid subscribers can view this issue in HTML format. There is no additional source or information in the HTML formatted document. It just looks a little better since we have included some HTML formatting. Just point your browser to link at the top of this document.

Other links

Contact Information

C&D Programming Corp.
PO Box 20128
Floral Park, NY 11002-0128
Phone or Fax: (212) 504-7945