Connecting vb6 to oracle using ADO object

Inorder to use ADO object, first you have to add a reference to the ADO object library.To add a reference select Project->References from the visual basic menubar.then select Microsoft ActiveX Data Objects Library.There are many versions available like 2.6,2.7 etc.,You may select any version supported by your computer.

Then you have to include a ADO Data Grid Control to display your oracle table or any recordset.Inorder to do that ,select Project->Components from Visual Basic menubar and select Microsoft Data Grid Control 6.0.When you click the OK button,the DataGrid Control is added to the Visual Basic toolbox.

With the DataGrid control in your Toolbox, now it's time to add it to your form.

Let's place this code in the Load Event Procedure of the Form.

Dim oconn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM EMPLOYEES"
Set oconn = New ADODB.Connection
oconn.Open "Provider=msdaora;Data Source=local host;User Id=paul;
Password=yourpassword;"
rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic
rs.Open strSQL, oconn, , , adCmdText
Set DataGrid1.DataSource = rs

Let me explain. These first two lines of code declare 2 object variables.The first is an ADO Connection Object

Dim oconn As New ADODB.Connection

followed by an ADO Recordset Object

Dim rs As New ADODB.Recordset

Now we have to declare a string variable to 'hold' the SQL statement.

Dim strSQL As String

Now we assign a SQL statement to the string variable.Let us assign a SQL statement to display all the records of the EMPLOYEES table.

strSQL = "SELECT * FROM EMPLOYEES"

Our next step is to open our ADO Connection. We do that by executing the Open method of our Connection Object. The Open method is looking for four parameters: the Provider name, the Data Source (or HostName), the User ID and the Password of the database.

oconn.Open "Provider=msdaora;Data Source=local host;User Id=paul Password=yourpassword;"

This is actually the connection string that we built while using ADO Data Control.You can also build the connection string using the ADO Data Control and then copy and paste it here.

Before we build the Recordset object we need to adjust three properties of the Recordset object the CursorType,CursorLocation and LockType.

rs.CursorType = adOpenStatic
rs.CursorLocation = adUseClient
rs.LockType = adLockOptimistic

Now it's time to open the Recordset.

rs.Open strSQL, oconn, , , adCmdText

Now we can use the Set statement to assign the Recordset object to the DataSource property of our DataGrid.

Set DataGrid1.DataSource = rs

That's all,now it's time to run your program.The code in the load event procedure gets executed.A Connection object is created, initiating the Connection to your Oracle Database.Then a Recordset object is created, retrieving Employee records.Finally, the DataSource property of the DataGrid is set to point to the Recordset object.Thus all the records of the EMPLOYEES table gets displayed in the Data Grid.