http://www.developer.com/

Back to article

Visual Basic Database Tutorial - Part 1


November 19, 2002

Welcome to the very first part in this wizzy Visual Basic Database tutorial!

I'm your crackingly cool host, Karl Moore and over the next few weeks we'll be covering everything you ever wanted to know about databases, but were too afraid to ask.

Whether youre wanting a Christmas bonus or just need to boost your Visual Basic knowledge I'm here to help.

Don't forget that I love to hear feedback! Feel free to send me a message, abusive or otherwise by clicking on the "Post Feedback" link at the end of the page.

Now let's get that wizzo brain cap on as we prepare to answer the following searching questions:

  • Erm so what's a database?
  • Why don't those tables have any legs?
  • What's Microsoft Access got to do with it?
  • Why is the grass green?
  • How do I build my own mini database program in VB?
  • What is the meaning of life?

Well, let's get the philosophical stuff out of the way first. The meaning of life is 42 and grass is green 'cause all the other rainbow colours are absorbed and only the green reflected, a process known as subtractive colour mixing.

Ha - and they said this tutorial wasn't going to be interesting!

(Ed groans)

When I first entered the geeky database scene, I shivered at the "d" word. Eugh, who wants to play around with databases? Certainly not me, I just wanted to program.

But you soon realise no matter what type of program you're creating, databases can be pretty cool things.

A database is essentially just a store of information. They usually come in the form of a simple file (just like a Microsoft Word file, say). You can shove information into this store or retrieve it from the store, with virtually no code at all.

Top Tip: You can make friends and impress the opposite sex at geeky cocktail parties by saying "DB" instead of database.

Hmm, doesn't sound terribly complex does it? Erm, thats because it's not. Most database wizards just like to overcomplicate things in a bid to scare off any programming newbies.

Now, listen up. A database may include many different tables. You can imagine a table as one worksheet in an Excel workbook.

Each column of the worksheet may hold something different. Column A, for example, could hold a customer name, column B may hold the customer postal code and column C may hold the customer telephone number.

That's all a table is a set of pre-defined "slots" or "boxes" into which you throw information. Each of those boxes has a descriptive name, such as "TelNumber".

Because we're supercool geeks, were not allowed to call those boxes err, boxes. We need to call them "fields", because it's the done thing and boosts your street-cred.

Just like an Excel worksheet, you can add new entries to the table by simply filling in a new set of fields. So each time you need to add a new customer, you just fill in the "CustomerName", "CustomerPostCode" and "CustomerTelephoneNumber" fields and Bob's your Uncle!

Actually, he is my uncle but thats totally irrelevant to the current discussion.

Top Tip: You cannot eat your dinner off a database table. This is considered highly uncool in the database world. Also, it's inadvisable to graze sheep in a table field.

OK, lets run over those wizzy terms once more:

  • Database a bunch of tables
  • Tables store numerous rows of information
  • Fields the little boxes inside a table

A database can also contain relationships and queries. You can even have a relationship with a query, but the Church doesn't commend it. Either way, that's pretty geeky stuff so we'll cover it later.

Don't tell me, your boss has asked you to develop a supercool database system and handed you a deadline of yesterday.

You don't want to hear me babble about database theory - you need to get stuck in! Well, I can take a hint so let's get down and dirty, and develop our first Visual Basic database-integrated application with absolutely no code at all!

1. Start Visual Basic if youre unsure about this, check out my Visual Basic tutorial here.

2. Choose the "Standard EXE" option and click OK

3. A regular blank form should appear on your screen. Double-click on the data control in your Toolbox the one that looks like this . It should appear on your form.

4. Change the Name property of the data control to "datCustomers".

5. Click on the ellipsis next to the DatabaseName property of the data control and select the "Nwind.mdb" file. Mine is located at C:\Program Files\Microsoft Visual Basic\VB98\Nwind.mdb though yours may be different. This is a sample database file that is distributed with Visual Basic!

6. Move a little further down in the Properties window and click on the RecordSource button. After a few seconds, a list will appear these are all the tables and queries in your database. The one entitled "Customers" is a table. Click it!

7. Now add three text boxes to your form name them txtCompanyName, txtContactName and txtPostalCode respectively.

8. Change the DataSource property for each of the text boxes to the name of your data control (probably datCustomers, if you've been good!)

9. For each of the separate text boxes, click the DataField property you will see a list of words appear. These are all the fields within the 'Customers' database table. Change this property for each of your three text boxes as so:

  • txtCompanyName - CompanyName
  • txtContactName - ContactName
  • txtPostcalCode - PostalCode

10. Now hit F5 to run your program!

Congratulations! Youve just created your very first database application!

Try clicking those buttons on the data control. They'll move you backwards and forwards among the rows in that Customers table.

If you're likening this to an Excel spreadsheet, you can imagine that with each click you're moving down or up one row and displaying all the customer information on that line.

Try changing one of the company names and moving forward a few records then moving back. You should notice that your changes have been saved!

That's what a database table is all about. They allow you to add "rows" of information to a table, edit stuff currently sitting in the table, remove entire rows or bunches of rows from a table even find rows in a table!

Top Tip: Instead of using the word "row" to describe a line of information in a table, try using the term "record" instead. Dont ask why, just trust me I'm a programmer.

That's all fine and dandy, but how do you actually create a database?

Unfortunately, just to confuse us all, there are many different types of databases. First off, there's an expensive whopper of a database system called SQL Server, which is used in corporations that need to store huge wads of information. Theres also that thing they call Oracle, which is another database format.

But one of the most exciting (and cheap!) types of database is an Access database. Remember when we changed the DatabaseName property of our data control to "Nwind.mdb"?

That Nwind.mdb file is actually an Access database.

The best way to create your own Access database is by using, unsurprisingly, Microsoft Access. This tool ships with the professional editions of Office 97 and enables you to graphically design your own tables and individual fields.

If you dont already have Microsoft Access, add it to your Christmas list and be a good boy. If you do have it, brilliant!

We'll delve into the intricacies of creating your own database next week but for now let's explore the existing Nwind.mdb database:

1. Click on Start, Programs, Microsoft Access
2. Select "More Files" then click OK
3. At the Open dialog box, select the Nwind.mdb file dont forget, by default it is installed at C:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb
4. Click OK when the boring "Welcome to Northwind" screen appears
5. A box should appear on your screen, looking a little like this:

6. Click on the tab entitled "Tables". These are all the tables in your database including the Customers one we browsed earlier in this tutorial
7. Double-click on the Customers table. You should see something like this:


Blow Up!

That's a table! The names at the very top of the screen are field names. Under that, youll find all the actual information. Each singular row of information is known as a record. If you move right down to the bottom of the table, you can also add new customers to the Customers table.

Try it now!

1. Click on the "Add New" button on the frame around the customer data

2. Fill in all the boxes for the new record, inventing wacky company names and telephone numbers as you go along

3. Now close the table and re-open it
4. Scour down the list and you should now see your customer listed alphabetically
5. Click on any field in your record, then click Edit, Delete Record6. You should be asked if you want to delete the selected record. Click Yes and it should disappear!

Well done you've just looked behind the scenes of a real database, added a record and then manually removed it!

This week, we've figured out exactly what a database is and how to create a simple link between an Access file and your Visual Basic application. We also edited a record, added a new record and deleted a record! Heck, we even learned about subtractive colour mixing.

Next week, the tutorial will get even juicier. Yessiree, we'll:

  • Create our own database
  • Knock together our own tables
  • Define our own fields
  • Figure out the real meaning of life
  • Discover what 'Structured Query Language' is all about
  • Plus.. take a peek at queries!

Until then, I'll say cheerio and toodle-pip - this is Karl Moore signing off for tonight. Goodnight!

Karl's Database Tutorial Index

Visual Basic Database Tutorial - Part 1
What do VB-World.net and databases have in common? Well for a start, the letters "b", "e" and "t". But more relevantly, over the next couple of months, it'll be hosting a weekly Visual Basic database tutorial with head geek, Karl Moore.

Visual Basic Database Tutorial - Part 2
Wondering how to use databases in Visual Basic? Ponder no more. This week, head geek Karl Moore returns with the second part in his Visual Basic Database tutorial. That's right, he's back... and he's geekier than ever.

Visual Basic Database Tutorial - Part 3
The Head Geek is back with the third part in his Visual Basic Database tutorial. Join Karl Moore as he takes you on a wobbly ride through the wacky world of Access, queries, grids and controls. And that's just this week!

Visual Basic Database Tutorial - Part 4
Head geek Karl Moore returns with another extra helping of juicy database info. This week, Karl explains how to access databases completely in code and build a complete working mini db application!

Visual Basic Database Tutorial - Part 5
Database diva Karl Moore continues his epic series by taking a look at good db design, primary keys, relationships and more.

Visual Basic Database Tutorial - Part 6
Karl Moore continues his epic database tutorial by taking a sneak geek peek at good database design and validation, before moving on to create a simple VB-integrated report.

Visual Basic Database Tutorial - Part 7
VB-World database dude Karl Moore concludes his epic tutorial series by uncovering the mysteries of Oracle, SQL Server and the oh-so-groovy VB database tools.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date