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:
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:
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()
cmdDisconnect_Click() 'disconnects the connection
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:
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()
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.