Article source code: my1st_accessdb.zip

Introduction

No, no, this isn't going to be some "Use-the-Data-Form-Wizard" nonsense. Neither is this going to be a real-world program. But before you grumble, curse, and look elsewhere, you should know that the code here will help you to write that real-world program you're probably after. Of course, you will have to make the necessary changes, and that's why I start with something very general and simple.

I don't want to bother you with things such as writing the Access database or starting your design totally from scratch. It's not that I'm lazy; it's more in consideration of those who have such an awfully slow dial-up connection. Such a connection can be a spoilsport when it takes ages to load the page. Since this tutorial is going to be a bit - but just a bit - large, I'm happy when I can cut down on things. Believe me, you'll be happy about that, too - unless you have one of those speedy connections.

Before you start

Before you get going, download the Starter Solution. This Starter Solution doesn't come with a single line of relevant code, but it does come with the Access database, including some made-up entries, a ready-to-use interface, and all icons used in the toolbar. Before you continue, keep an unchanged copy of this downloaded folder in case things go terribly wrong.

Unzip the folder and then open any folders until you see the Database Sample Program solution file. Depending on your operating system, you either have to look for the file extension .sln or for the "Visual Studio Solution" file. Double click this file to open the solution. In the Solution Explorer, double click the frmSample.vb and you should see the form as shown in Figure 01.

(Figure 01)

Study the form, controls, and the properties. If you really don't care about what I've done so far, study at least the toolbar. Click the toolbar once and find the property Buttons in the Properties window. Click where it reads "Collections". Look for the property "Tag". You will see that I have typed keywords there. You should be familiar with those tags by the time we start coding the toolbar.

Connecting to the database

Open the "Data" tab in the toolbox and double click "OleDbDataAdapter" as Figure 02 indicates.

(Figure 02)

This will bring up the "Data Adapter Configuration Wizard" as shown in Figure 03.

(Figure 03)

When you click "Next", you will be asked to "Choose Your Data Connection" as you can see in Figure 04.

(Figure 04)

I assume that this is the first time you are using the connection wizard, so the combo box should be empty. Click "New Connection ..." to get to the properties window shown in Figure 05.

(Figure 05)

By default, people at Microsoft assume you're rich and have SQL, even if you're using the standard edition. So you will see the window asking you to connect to an SQL server. Since I didn't have the necessary change to buy SQL, neither the necessary patience for MSDE, I'll try to make do with Access. So click the Provider tab.

As you can see in Figure 06, the Microsoft guys insist on you having more cash in the bank as I do. Since my name isn't William or Bill for short, I'll insist on using an Access data connection.

(Figure 06)

Click "Microsoft Jet 4.0 OLE DB Provider" as suggested in Figure 07 and then "Next" or simply double click this item.

(Figure 07)

In the connection tab, shown in Figure 08, click the ellipsis and browse to the Access database in the bin folder of the Starter Solution.

(Figure 08)

Double click the "dbSample.mdb". As you can see in Figure 09, the connection path will appear in the text box. Click the "Test connection" button. A message box should tell you that the connection is okay. If this isn't the case, you will have to start all over and see where you have gone wrong.

(Figure 09)

Let's assume that everything went just fine. Click the "OK" button. This will bring you to the window shown in Figure 10. Yes, it's the same window you saw in Figure 04, but this time, the connection path is displayed. It's probably a good idea to write the connection path down; it will help you later to understand why we have to change the connection string (path) inside the code.

(Figure 10)

In Figure 11, you will be asked to "Choose a Query Type". Depending on the software you have installed on your machine, you might have more than one option. Important is that the "Use SQL statements" radio button is selected. If this is so, click "Next" to continue.

(Figure 11)

This will bring you to the "Generate the SQL statements" window as shown in Figure 12. There are different ways to generate those statements, but we will use the simplest way. Click the "Query Builder ..." button.

(Figure 12)

This will bring up the "Add Table" window on top of the "Query Builder" window. The "Add Table" window should look like the one shown in Figure 13.

(Figure 13)

You should see the "tblContacts" which is the name I gave the table. Click the "Add" button and then "Close". You will now see the "Query Builder" window as shown in Figure 14.

(Figure 14)

Check "*(All Columns)" in the tblContacts window as shown in Figure 15.

(Figure 15)

Click the "OK" button. This will bring you back to the "Generate the SQL statements" window as shown in Figure 16. This time, the statements are added.

(Figure 16)

Click the "Next" button. This will bring you to the "View Wizard Results" window as shown in Figure 17. There should be a blue check mark before each item. If this is not so, then you have gone wrong earlier. The error might persist even if you start everything from scratch. You will have to edit the "Windows Form Designer generated code" to correct such an error. If you don't have the courage to do that, delete the entire project and unzip the backup copy you - hopefully - have.

(Figure 17)

If everything went just fine, click the "Finish" button. It might take some moments before you can continue, so be patient. Your component tray, just below your form, should look as shown in Figure 18.

(Figure 18)

Click the "OleDbDataAdapter1" once and go to the Properties window. Change the name, OleDbDataAdapter1, to "odaContacts". The prefix "oda" stands for "OleDbDataAdapter" followed by the name of the table. Change the name of the "OleDbConnection1" to "odcContacts", too. As you certainly guessed, the prefix "odc" stands for "OleDbConnection". Your component tray should now look as shown in Figure 19.

(Figure 19)

Right click the "odaContacts" and choose "Generate Dataset" from the context menu. This will bring up the "Generate Dataset" window as shown in Figure 20.

(Figure 20)

Change "DataSet1" to "pdsContacts". The prefix "pds" stands for "Persistent Data Set". Just make sure the "New" radio button is selected and that the checkboxes for the "tblContacts" and "Add this dataset to the designer" are checked. Figure 21 shows you what this should look like.

(Figure 21)

When this is so, click the "OK" button to continue. Your component tray should now look like the one shown in Figure 22.

(Figure 22)

Click the "PdsContacts1" once to select it if it isn't already selected and change the name in the Properties window to "dsContacts". The prefix "ds" stands for "Data Set". When you're finished, your component tray should look as shown in Figure 23.

(Figure 23)

I think I didn't overstate it when I said that this one's going to be a bit large, did I? Hmm, fact is that we haven't even started yet.

Continued in My First Access Database Program. Part 2