Article Options
Premium Sponsor
Premium Sponsor

 »  Home  »  .NET Newbie  »  My First Access Database Program. Part 1
 »  Home  »  Data Programming  »  My First Access Database Program. Part 1
 »  Home  »  Data Programming  »  Microsoft Access  »  My First Access Database Program. Part 1
My First Access Database Program. Part 1
by George Poth | Published  01/20/2004 | .NET Newbie Data Programming Microsoft Access | Rating:
George Poth

I have been teaching English in Brazil since 1994 and always wanted to do more for learners than common textbooks can offer. This started with web sites that couldn't reach most students as computers and the Internet are not standard for most people in this country.

Computer tools to help Brazilian students learn a complex language like English are practically non-existent and so I sent some suggestions to software companies. Since Brazil is neither a target market for English textbooks nor for software of this kind, the rejection seemed natural.

As a result, I tried some free developer tools such as Borland's free C++ compiler, Free Pascal, and Envelope's Visual Basic. Envelope's Visual Basic, which is a Microsoft Visual Basic 1.0 clone and still available, suited my taste but I knew it was obsolete technology. In March 2003, I bought a copy of Microsoft Visual Basic .NET Standard and have been hopelessly contaminated with the programming virus ever since.

I mostly write programs for educational purposes. Having discovered the wonderful world of DirectX recently, I am diving into the most entertaining part of programming: games. One can connect teaching with pure entertainment, learning, and culture.

 

View all articles by George Poth...
My First Access Database Program. Part 1

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

How would you rate the quality of this article?
1 2 3 4 5
Poor Excellent
Tell us why you rated this way (optional):

Article Rating
The average rating is: No-one else has rated this article yet.

Article rating:4.02898550724639 out of 5
 69 people have rated this page
Article Score43777
Sponsored Links