Overview

The purpose of this program is to access a Legacy Database, in Oracle format, and extract the necessary data into a Comma Seperated Variable File (.csv). Although this program has been designed for a specific purpose, it may be of use to developers who are looking for an example of accessing a database using Visual Basic 6.

In laymans terms, the basics of the program are as follows:

To reference a database from VB you first have to go to Project > References and check the box next to Microsoft DAO 3.6 Object Library

The following code snippet is the connection string which tells the program where your data is stored. You must change YourPathName.mdb to wherever you stored the db you created:

Public Sub OpenAccessDB()
    With conAccess 
       .Provider = "Microsoft.Jet.OLEDB.4.0" 
       .ConnectionString = "Data Source= YourPathName.mdb" 
       .CursorLocation = adUseClient 
       .Open() 
End With
End Sub 

End Sub

Before you do anything you must declare the database connection and the recordset object:

Dim conAccess As New ADODB.Connection 
Dim myrecordset As New ADODB.Recordset 

To access your data type the following in the Form_Load() event

Call OpenAccessDB 
sqlstring = "select name, address from person" 
Set myrecordset = conAccess.Execute(sqlstring) 

Note, this will select all records in the person table, to specify criteria try:

dim searchcriteria as string 
searchcriteria = "Bob" 
sqlstring = "select name, address from person where name = '" & searchcriteria & "'" 

Now that you have created a recordset which contains the data that you have specified, this data can be displayed on the screen by using:

 ' note the reference starts from 0 not 1 
Text1.Text = myrecordset!name or Text.Text = myrecordset.Fields(0)

to navigate through the recordset use:

myrecordset.movenext / myrecordset.movefirst etc. 

A useful tip for learning the syntax of a new language is to type something like "myrecordset." and wait for the automatic list to appear, scrolling through the list will familiarise you with the various operations associated with the various components!

Once you have displayed the data and navigated thru a recordset, the following code may be used to add a new record or ammend a record:

To add a new record:

   myrecordset.AddNew 
   myrecordset.Fields("name") = "James" 
   myrecordset.Fields("address") = "earth" 
   myrecordset.Update 
   myrecordset.Close 

To alter a record

    myrecordset.Update ("name"), "Peter"
   myrecordset.Update ("address"), "Mars" 

That is basically it!