ADO Database access in VB6

To modify or view a database from VB6 is relatively easy, but there is no good method built into Visual Basic (there is a method, but I won't discuss it in this article). The best way is to use the COM based ADO objects.
Step 1: Reference ADO
The first thing to do is to add a reference to the ADO objects needed. This can be done by going to the menu and selecting Project -> References. At this point you will be presented with a screen, scroll though the list until you get to 'Microsoft ActiveX Data Access Objects x.x Library' On my computer I have several different versions of the library. I always select the highest version, which is v2.8 in my case. Even if you have a lower version, don't worry about it, what I'll be showing you will still work.


Step 2: Create a Form
I've created a simple form below that only contains 1 label and 1 textbox, the text box is named txtColor.


Step 3: Connection object
Now that we have referenced the ADO objects and a form for the project we can get started on the code. First thing to decide is which kind of database are you connecting to? In my example I will be connecting to a Microsoft Access Database, but if you wan to connect to another one, you can choose one of these below connection strings.
MS Access ODBC =  Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\DatabasePath\dbaccess.mdb;Uid=;Pwd=;
MS Access Jet (I will use this one) =  Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DatabasePath\MmDatabase.mdb;User Id=admin;Password=;
Oracle ODBC =  Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=MyDatabase;User=MyUserName;Password=MyPassword;Option=4;
DB2 = Provider=DB2OLEDB;Network Transport Library=TCPIP;Network Address=127.0.0.1;Initial Catalog=MyCatalog;Package Collection=MyPackageCollection;Default Schema=MySchema;User ID=MyUsername;Password=MyPassword;
SQL Server = Provider=sqloledb;Network Library=DBMSSOCN;Data Source=127.0.0.1,1433;Initial Catalog=MyDatabaseName;User ID=MyUserName;Password=MyPassword;
Step 4: Database
I created a sample database called 'db1.mdb'. It only contains 1 table called 'Table1'. This table has 2 fields, 'ID' (Number, Long Integer) & 'Color' (Text 50 characters). Obviously this table is only a sample so doesn't have much in it, but I added some dummy data so that we can perform a SELECT on the db.

Step 4: Code
Option Explicit

Private Sub Form_Load()

Dim C As New ADODB.Connection
C.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;User Id=admin;Password=;"

Dim Rec As New ADODB.Recordset
Dim SQL As String
SQL = "SELECT ID, Color FROM Table1 WHERE ID = 2"
Rec.Open SQL, C, adOpenForwardOnly, adLockReadOnly
Do Until Rec.EOF

Me.txtColor.Text = Rec.Fields("Color").Value
Rec.MoveNext

Loop
Rec.Close
Set Rec = Nothing 'Insert a new record
C.Execute("INSERT INTO Table1 (ID, Color) VALUES (4, 'Black')")
C.Close

 End Sub

Step 5: Run the app
Just press F5, and the application starts. And you will see the form below.