We hope everyone had a Happy New Year.
A new software giveaway has begun this month. Be sure to enter at http://www.codeoftheweek.com/contest.html 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.
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 questions@codeoftheweek.com.
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.
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.Connect
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")
rsSrc.MoveNext
Wend
Oracle.CloseRecordset
Oracle.Disconnect
Set Oracle = Nothing
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: http://www.codeoftheweek.com/membersonly/bi/0068.html