Basic Database Programming

What you will learn:

This tutorial will teach you the basics of database programming. Learn how to build a connection string, connect to an access database, scroll through the database's entries, add, delete, and edit database entries, and display all the entries and database data in a table-like chart. This tutorial includes links to many different screenshots that will help you along the way to understanding ADO database programming with Microsoft Access.


Part One: Obtaining the Underlying Information:

Before you begin understanding the system of database programming with access, you must understand basic vocabulary. Below are some listings of words that will provide you with a basis for programming with Access and ADO databases:


Connectionstring: The connectionstring tells the Visual Basic compiler with which system or engine to connect to the database, what database to connect to, and any security issues to handle. You can either write your own, or, like in the example in this tutorial, you can use Visual Basic's own system of building a connection string automatically.

Command Type: As you should know, a database includes tables, forms, and text. The Command Type is a property that tells Visual Basic which one of those three it should connect to. In this example, we shall be editing and receiving data from the Customers table, and so Command Type will be set to adCmdTable (the VB compiler should look for a table from which to receive its data.)

ADODC: This is an OCX control for Visual Basic that handles ODBC or Connectionstring connections. We shall be using one of these controls to connect to our database.

ODBC: This is a standard. This standard was invented to work with Microsoft Visual Basic programs, Microsoft Visual C++ programs, and of course, all SQL systems.

OLE DB Provider: These are applications that run databases. They are what interprets your commands in SQL and then sends those commands off to a database which performs your request and spits out a result.


Part Two: Starting the Program:

Now that you have a basic understanding of the words you will need to know, you can begin the program. Start a normal Standard EXE project in Visual Basic and right click on the toolbar. Select Components.

Scroll down in the components window until you see an entry called: "Microsoft ADO Data Control." Check off the box to this entry's left and click OK. Add an ADODC control to the form wherever you like and resize it to fit your creative needs. Note that its name is ADODC1. Change this name to: "adoConnect."

Now, you shall setup its connectionstring. We shall be looking at the NWIND.mdb file in the Visual Basic folder (c:\program files\microsoft visual studio\vb98\NWIND.mdb is standard) because of its setup. This file, NWIND.mdb is great for learning basic database programming because it was written in Microsoft Access 97, the native version for the ADODC control, and it was written in a business-like, real-world sort of way. This database will give you practice and greatly-needed expertise when it comes to programming business database applications.

To setup the connectionstring, right-click on adoConnect. Select "ADODC Properties." In the window that appears, click on the option box that reads "Use Connectionstring." Click "Build."

This step can get a bit complex to explain. To build a connectionstring, you must tell the computer what PROVIDER you are using. All Microsoft Access databases use the Microsoft Jet OLE DB Provider. If you were running a database from Oracle, you would use their database provider. To select the Microsoft Jet OLE DB Provider, make sure the window that appears after you clicked "Build" is sitting with the "Provider" tab open. There should be a list of providers, and among them should be the Microsoft Jet 4.0 OLE DB Provider. Select this and click on the Connection tab. Now you have to select the database. Click on the "..." button and navigate to your Visual Basic directory. The VB directory, by standard, is "c:\program files\microsoft visual studio\vb98." Once there, click on the NWIND icon and click "Open." The NWIND database is NOT passworded, so you can leave the textboxes alone. If you want to do this, however, with a database you create and password, you would have to add your administrative username and password in the textboxes below the "Enter information to log on to your datbase" line.

Now, click on the "OK" button at the bottom of the screen. A line of text should appear in the textbox under the "Use Connectionstring" optionbox. Let's look at this line:


Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.MDB;Persist Security Info=False


Note the syntax. Provider=Microsoft.Jet.OLEDB.4.0; tells the PC to use Microsoft Jet 4.0 OLE DB Database Provider. The ";" is used as a separator between attributes. Data Source= tells the computer where the database is located. Persist Security Info=False tells the computer you set no security attributes; that is, the database is not passworded, nor does it do any kind of security check before allowing editing, adding, removal, etc. of its data.

Now, you have to tell the PC to get its data from a certain table. Click on the "RecordSource" tab. Select from the first drop-down box 2-adCmdTable, and from the second drop-down box, select "Customers." Now you are ready to create textboxes to display this data. At the end of this tutorial you will be taught how to display the data in a datagrid. Click on the "OK" button. Your program is now ready to connect to the database NWIND.mdb.

Next, we shall create three textboxes to hold the data of the ContactName, Address, and City of the customers stored in the customer table of the NWIND.mdb database. Create three textboxes, text1, text2, and text3 and align them like this: Note, though, that your textboxes will not yet have any data in them. That step comes later.



Click on text1 and goto the properties window (F4 to bring this window to the front.) Find its DataSource property and set it to adoConnect. Find its DataField property and set it to ContactName. Click on text2 and set its DataSource property to adoConnect and its DataField property to Address. Click on text3 and set its DataSource property to adoConnect and its DataField property to City. Run your project (F5) and see if "Maria Anders" appears in text1, "Obere Str. 57" appears in text2, and "Berlin" appears in text3. If it has you have successfully written your first database program. Try clicking on the ">" button on adoConnect to goto the next record and watch the new names popup.




But don't you want to do more with your project? Don't you want to edit, delete, and add records to your database?

Adding Records:

We will first learn how to add records. To do this, we must first finish the project. We must create a textbox for each field of the database. Therefore, create eight more textboxes for the following fields:

ContactID
CompanyName
ContactTitle
Postal Code
Phone
Region
Country
Fax
Once you have those and they are carrying the correct information, add two commandbuttons. These will complete the two step process of adding a record. Set the caption of the first commandbutton to "Add" and the second's caption to "Save." You see, to add a record, you must first make a blank record, give the user time to input data, then save the edited record into the table.

In the commandbutton "Add"'s _Click() event, put:


adoConnect.Recordset.Addnew


In the commandbutton "Save"'s _Click() event, put:


adoConnect.Recordset.Save


Run the program and click on "Add." All textboxes should go blank. Input information - any info you want and click Save. All textboxes should return to the old data. Then, click on the "|>" button on adoConnect. You should see the data you have just entered. You have created a new record!


Deleting Records:


To delete records, you must create a commandbutton with the caption of "Remove." In its Click() event, put:


adoConnect.Recordset.Delete


Then, run the program and use the "<" and ">" buttons to navigate to the record you created in instructions on how to add records. DO NOT TRY TO DELETE ANY OTHER RECORDS! If you do, it will give you an error that another table has "related entries." This means that some of the data other tables are holding are directly dependent on the data in the Customers table (for example if I have Orders and Customers, I may say that John Doe has ordered a pair of pants. The entry for a pair of pants in the Orders table is direclty dependent on the data on John Doe that the Customers table holds.) The record that you created yourself is 100% free of this hassle. Click on the "Remove" button. If nothing happens, you are on the right track. Now, end the program and start it up again. Your entry should now have been deleted. Congradulations. If you want to go a step further refresh the database after you delete the record, simply put this in the "Remove" button's Click() event under "adoConnect.RecordSet.Delete":


adoConnect.Refresh


Editing Records:

Editing records is easy! Just navigate to the record you wish to edit, change something, and click the "Save" button. You're done!


Viewing Data in a DataGrid:

This part of the tutorial is the best part, for you can use your skills which you learned above to create a project that makes the database look exactly like Excel! To begin, right click on the toolbar and select Components.

In the components window, check off Microsoft Chart Control and DataList Control. Click "OK." Add the DataGrid control to your form. Set the DataGrid control's DataSource property to adoConnect. Run the program and see all the data in a night neat grid. You can now select, edit, delete, etc. records as you please!

Add a DataChart control to the form and set its DataSource property to adoConnect. Unfortunately, though, the DataChart, by nature, requires some sort of charable data. No table in NWIND.mdb has such data. Therefore, I (being the author of this tutorial,) suggest that you create your own database with a table called numbers with fields Name and Amount. This is a perfect graphable value. Enter a bunch of names into the Name field like Bob, Donald, Jim, John, Hal, etc. and random numbers in the Amounts field. Then, rebuild the connection string (see above) to fit your database with your table. Then, run your app and see the DataChart control display your data in a bar chart.

To change the type of chart the DataChart will display, right-click on it and click "Properties." Select whether you want a 2D or a 3D graph and then select a type of graph from the list. When done, click OK and run the program. See your new graph displayed there.