Accessing Databases

    Databases play a very important role in our day computing. They are everywhere. It's important, that you as a programmer, have at least some understanding of databases and how to access them from your applications.

   In this article, I'll show you how to access Microsoft (r) (td) Access database and view its contents right from VB program. You can use this article to enable your applications to access databases for storing and retrieving data.

ActiveX Data Objects (ADO) data access technology will be used here. If you are not familiar with ADO, here is some information on it. ADO is a technology used to access relational, non-relational databases, email and file structures (e.g. Excel), graphics, and data sources as well. ADO can work with Microsoft as well as non-Microsoft technologies. If you have VB installed, some version of ADO is present on your machine. From the programmer's perspective, ADO is a provider of objects through which databases (or some other data) can be accessed.

Fine points to remember when working with ADO:

1. Create Connection object first

2. Execute the command through this connection on some data

3. Retrieve the results and do with them whatever you wish (e.g. present to the user)

Just how to do that, I'll show you next. Before you start, create a Microsoft Access database with a table named "Main" containing five fields: ID, First_Name, Last_Name, Age, Occupation and add several entries to these fields. You can download the sample database following the link at the end of the article. Save it to some location you'll remember. Now let's create a VB app that will get and manipulate the data from this database.

Start new VB EXE Project. Go to Project menu and click on References ... . Scroll down the list and check any "Microsoft ActiveX Data Objects 2.x" entry. I said "any" because there might be multiple entries, so select the latest one. After you do that, create this user interface:

Connect (cmdConnect) will connect to the database (thus creates a Connection object), Get Column Names (cmdGetNames) will get the names of the columns from the database (in our case that will be "ID, First_Name, Last_Name, Age, Occupation), Get Values (cmdGetValues) will get the entries you entered for First_Name, and Disconnect (cmdDisconnect) will disconnect the connection. Before you run the program, don't forget to enter the path to the database in File path text box.

In general declaration area write this:

Option Explicit
Public cnn As Connection 'connection to the database
Public rcst As Recordset 'data keeper

cnn is ADODB.Connection object and is used to connect to the database. rcst Recordset will be used to manipulate and retrieve data.

Here is the code for connecting to the database and disconnecting from the database:

Private Sub cmdConnect_Click()
'connects to the database
On Error GoTo er

Dim strCnn As String   'connection string
Dim intErr As Integer   'iteration var

   'every connection needs connection string:
   strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & txtFilePath.Text & ";"

   Set cnn = New Connection 'create new connection
   cnn.ConnectionString = strCnn 'specify the connection string
   cnn.Open 'open the connection
   frmDataAccess.Caption = "Data Access - Connected"
   Exit Sub
er:
   If cnn.Errors.Count > 0 Then 'process all connection errors
      For intErr = 0 To cnn.Errors.Count - 1
         MsgBox "Error in Connection: " & cnn.Errors(intErr).Description, vbCritical, "Error"
      Next intErr
   Else 'process other errors
      MsgBox "Error in cmdConnect", vbCritical, "Error"
   End If
End Sub

Private Sub cmdDisconnect_Click() 'disconnects the connection
On Error Resume Next
   cnn.Close        'try to close the connection
   Set cnn = Nothing  'clear the object
   Set rcst = Nothing  'clear the recordset if it was initialized before
   frmData.Caption = "Data Access - Disconnected"
End Sub

Everything so far is pretty straightforward. Use Set keyword to create new connection and later on use Set again to destroy connection. Here comes the procedure for outputting the names of the fields:

Private Sub cmdGetFieldNames_Click()
'outputs the names of the fields onto the screen

Dim strSql As String   ' use SQL syntax to obtain info
Dim intIter As Integer   ' iteration variable

'create sql statement that would select all the fields (marked as *)
'from table Main
strSql = "SELECT * FROM Main"

Set rcst = New Recordset ' create recordset to store the data from
'database
rcst.Open strSql, cnn 'fill the recordset with data we specified in strSql

'output the info
txtStatus.Text = "Fields in Main: " & Chr(13) & Chr(10)

For intIter = 0 To rcst.Fields.Count - 1
txtStatus.Text = txtStatus.Text & rcst.Fields(intIter).Name & Chr(13) & Chr(10)
Next intIter

End Sub

Couple things I would like to mention about this procedure. Notice the way I specify what data I want from the database. I use SQL, which is the programming language of the databases. SELECT statement will select the records from the specified table (in this case Main) and specified fields (in this case all, marked with *). To get that data, new Recordset object is created and opened passing SQL statement and the connection. This is not the only way to obtain data from the database, but so far, the simplest to program. Try to modify the code to SELECT only First_Name field by modifying SQL statement: "SELECT First_Name FROM Main". Play with this code some more to get better understanding of SQL and Recordset object. Now  let's look how to obtain other data from the database by getting the First Names:

Private Sub cmdGetValues_Click()
'gets the First_Name field from the main table

Dim strSql As String ' use SQL syntax to obtain info

   'sql statement that selects First_Name column from Main table
   strSql = "SELECT First_Name FROM Main"

   Set rcst = New Recordset 'recorset that will contain the data
   rcst.Open strSql, cnn 'gets the data

   'output the info
   txtStatus.Text = "Data from First_Name: " & Chr(13) & Chr(10)

   'notice iteration with recordset
   Do While Not rcst.EOF 'check for the end
      txtStatus.Text = txtStatus.Text & rcst.Fields(0).Value & Chr(13) & Chr(10)
      rcst.MoveNext 'goto the next record
   Loop

   rcst.Close
   Set rcst = Nothing 'release the memory

End Sub

Pay close attention at the iteration technique used. Complete code can be obtained here. This article shows you the very basic data access. There are more topics to look at, but that we will leave for the future. Play around with what you have right now and soon we will bring more advanced articles on data access.