With a zip and a zap and a zibidy-doink, welcome to the third part of this wizzy Visual Basic database tutorial.
Oh, and don’t forget to tell us how we’re doing. If you love the series, tell us using the below feedback form. If you hate the series, tell us using the feedback form below. If you’re after a date, e-mail me – Karl@karlmoore.com ;-))
But hey-ho, enough patter – time to get on with the real stuff! This week, we’ll be covering:
- Creating SQL statements in Access
- How old the earth is
- Storing statements as "queries"
- Making a Real Visual Basic Database Application!
Let’s start with geographical affairs; the earth is approximately 4.6 billion years old. Or is it 6.4 billion years? Either way, it’s about the same age as my Grandma. Err, just kiddin’ Grandma!
[Ed: No sweets for you this weekend, Karl…]
Enough of this rambling – what about the nerdy stuff? Corr, I can see you getting excited already. So fellow geeks and geekesses, lend me your ears (plus any money you have lying about)… and let us proceed!
I love taking shortcuts; using super-glue to attach those new shelves, stealing code from the Web for my latest project… and using Microsoft Access to generate my SQL statements.
You may remember that we finished the last instalment telling you how to generate simple SQL statements in Microsoft Access, as opposed to writing them by hand (boring!).
Top Tip: Remember, an "SQL statement" is just a database question – for example, "select ContactName from Customers" means "Show me the contents of every ‘ContactName’ field in the Customers table"
Well, in this section, I’ll be giving you a few brief pointers on how to go that little bit further. I shan’t drone on about this for ages and pages, but the tips you learn now will help in our later projects. So let’s continue where we left off…
Sometimes you don’t want a database to return every single record. For example, you may simply want to show the orders for one particular customer.
In Microsoft Access, you can specify criteria for a particular field by placing the value underneath the fieldname.
For example, the below query on our Nwind.mdb database shows the ContactName for records where the CompanyName is equal to "France restauration"
When we enter Datasheet View, we get this:
And when we nip into SQL View, we get this:
SELECT Customers.CompanyName, Customers.ContactName FROM Customers WHERE (((Customers.CompanyName)="France restauration"));
And that’s just saved us having to write and debug our own SQL statement. Access did all that hard work for us.
Top Tip: You will often use SQL statements in your Visual Basic applications. We’ll be using them later in this week’s tutorial…
OK, so you’ve produced that list of customers – and now your boss wants them in alphabetical order. Argh! Don’t you just want to murder picky users?
Still, once again, Microsoft Access can help out.
Change the query we created in the last section to look like the below. I’ve simply removed the "France restauration" criterion and changed the "Sort" box to "Ascending"
Now run the query by entering into Datasheet View. You should find that all the companies are now listed in alphabetical order.
The SQL statement for this looks like:
SELECT Customers.CompanyName, Customers.ContactName FROM Customers ORDER BY Customers.CompanyName;
You can even reverse that order by changing ‘Ascending’ to ‘Descending’.
To return all the fields in a table, we can double click on the asterisk in the Customer table, like so:
This tells Access you want to return every field (*) from the table. You could then perhaps add criteria to the query, like this:
The SQL statement for this query is:
SELECT Customers.* FROM Customers WHERE (((Customers.CompanyName)="Lazy K Kountry Store"));
OK, enough babbling about Structured Query Language – if you’re interested in how Access can help you design queries that count records, sum field values and so on, press F1 whilst designing your query to launch the help.
Go on, press it! I promise I won’t tell anyone. Except perhaps my Uncle Bob. And his wife. And his wife’s personal assistant. And his wife’s personal assistant’s pet snail, Strangely Brown. And his wife’s personal assistant’s pet snail’s in-house lover, Also Curiously Brown.
On a related note, try looking up the "=Date()" function and using it as criteria in a query.
Hmm, queries, queries, queries. Remember that SQL program we created just last week? The one into which we type a few "select * from customers" statements and clicked a button to view the results?
Well, a query is basically one of those SQL questions… stored inside a database, under a particular name.
If you’ve still got the last Access query open, hit File and Save. Tap in the name "qryContacts" and click OK.
Erm, there we have it. You’ve just created your first query.
Close the current window and returned to the "Nwind: Database" screen. Take a peek under the ‘Queries’ tab and double-click on your query. Wallah!
A query is just like a table – although in realistic terms, it probably only grabs a few elite records matching your criteria. But it can be treated just like a table.
For example, if you pop back into that SQL program we created and type in "select * from qryContacts" – you’ll find it will display everything returned by the qryContacts query.
Or you could try simply stating, "select ContactName from qryContacts" – which would only return the ContactName fields from the query. Go on, try it!
Top Tip: Some database whizz-kids also call queries, ‘views’. Just nod and ignore them… after all, we know who’s right, don’t we?
The advantage of queries is they allow you to store SQL statements outside of Visual Basic. Let’s say you had a customer order table with a True/False value depending on whether payment had been received. You could create a query that shows you all the customers that haven’t paid – and display that in your program – without having to store all the SQL in Visual Basic.
And if you ever need to make changes to the query – you can simply edit it. No recompiling required!
Well that’s enough of that. Next, let’s dive into Visual Basic and create our first real database-integrated application!
In this section, we’ll be putting everything we’ve learned so far into practice. Except the bit about tectonic plates. Yessiree, we’ll be creating our very own supercool customer browser application!
Before we jump straight into the dirt, let me outline the project. We’ll be creating an application that allows you to browse through each customer and view general details of each order they have placed.
On top of this, we’ll add functionality to add, delete and search for customers.
Anyway, you want to design a form that looks something like this:
OK, so it ain’t gonna win any design awards, but I’m no Christian Dior.
Now you could design and set all the properties for this project from scratch, but I wouldn’t advise it – particularly when you can download the form by simply clicking here.
Let me explain how I created the Customer Browser. Well, it all started when I bought a computer and installed Windows 98. Then I purchased Visual Basic and played around a bit. Then I read a few books, sat a few courses and watched a few videos. Then I did some other things.
And then I created the Customer Browser. How?
First off, I added a Data control and changed the DatabaseName property to point to my Nwind.mdb database. I also changed the RecordSource property to the Customers table.
Then I added individual text boxes and "bound them" direct to the Data control. I did this by changing the DataSource property to the name of the above Data control, then changing the DataField property to the table field I wanted to ‘connect’ this text box with.
Next, I added six boring (really boring!) command buttons and changed their Caption properties. I’ll add code behind these later.
So far, I’ve created everything I need to browse the entire Customers table. But what about the orders?
First, I added another Data control to the project. The main control above will handle the customers, whilst this one will retrieve the orders.
Next, I added the DBGrid control to my Toolbox by clicking Project, Components and selecting ‘Microsoft Data Bound Grid Control 5.0’.
Then I added an instance of the control to my form, as above – changing its DataSource property to the name of my second Data control.
Now the design is over and it’s time to code, geek-to-geek. Here’s the code you’ll find underneath each command button:
datCustomers.Recordset.AddNew ' Go on, add a new customer! ' Just fill in the customer fields ' and move to another record. Then ' try using the Search button to ' find the customer again!
Due to the way Microsoft have setup the Nwind.mdb database, you can’t delete customers that have orders. If you do, you’ll receive an error. You must first delete all the orders before deleting a customer. This type of database integrity is maintained using "relationships", which we’ll cover later in this tutorial.
On Error Resume Next datCustomers.Recordset.Delete ' Delete the record datCustomers.Recordset.Requery ' Then 'requery' the Recordset - ' which will 'refresh' it. ' It should show everything it did ' before, minus the deleted record
Dim CompName as String CompName = _ InputBox("Which company would you like to search for?") ' Get name from user, via InputBox datCustomers.Recordsource = "select * from Customers " & _ "where CompanyName = '" & CompName & "'" ' Merge that name with an SQL string datCustomers.Refresh ' Refresh the customer Data control
You’ll notice that for the majority of buttons, we’re simply using a method of the Data control’s Recordset object, such as datCustomers.Recordset.Delete
The Recordset object in datCustomers is the bit that actually holds all the records. As its name implies, a Recordset is simply a "set of records". So when you want to delete a record, you simply fire that method of the Recordset.
Top Tip: You know how you can declare and work with strings in Visual Basic? Well, you can also declare and work direct with Recordset objects in code, something we’ll be covering later in this tutorial
Of course, you can always implement error handling and all that jazz into your command button code, but I haven’t bothered in other to keep things simple. And because I’m a complete sloth.
Now things may seem a little sticky when it comes down to the Search button code, but it’s really pretty straight-forward. The code is doing this:
- Grabs the company name from the user (InputBox stuff)
- Changes the RecordSource property to an SQL string, with the company name inserted in the middle
- Refreshes the Data control to display the new bunch of records in the Recordset
There, that wasn’t too bad, was it?
Well that’s all fine and dandy, but we’ve still not thought about the customer orders section at the bottom of our form. In English, we want it to display both the order date and the actual shipping date. But how do we do that in code?
The Reposition event of the Data control fires every time the user moves to another record. And that’s just what we want – to update the Orders section every time we move to another Customer. So, our code looks like this:
Private Sub datCustomers_Reposition() datOrders.RecordSource = "select OrderDate, " & _ "RequiredDate from Orders where " & _ "CustomerID = '" & _ datCustomers.Recordset.Fields("CustomerID") & "'" datOrders.Refresh End Sub
This code fires every time our first Data control – datCustomers, the one that holds customer information – is "repositioned". In the code, we’re changing the RecordSource property of our second Data control to retrieve the OrderDate and RequiredDate fields for all Orders that have a CustomerID same as the one in the datCustomers.
The code – datCustomers.Recordset.Fields(“CustomerID”) – allows us to retrieve a singular field within our Recordset. In this case, we’re asking it to give us the CustomerID field.
So, in brief, every time the user moves from one record to the next, the Reposition event occurs. You respond to this by refreshing the Orders Section and filling it with all OrderDate and RequireDate fields that have the same CustomerID as the current Customer record.
This week we entered real nerd mode. We learned a few neat tips for generating SQL statements using Microsoft Access, figured out what queries are all about – and even built our own customer order browsing application.
Next week, we’ll be adding greater functionality to that application – with database theory and query joins all on the menu. But I promise it won’t get geeky nor boring. Really.
So join us next week for the fourth instalment of this Visual Basic Database tutorial. Until then, this is your surprisingly handsome host, Karl Moore, saying goodnight 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.