http://www.developer.com/

Back to article

Visual Basic Database Tutorial - Part 7


November 19, 2002

Good morning fellow geeks and geekesses! Karl Moore here as usual, attempting to glide you smoothly through the rocky mountains of Visual Basic and database design. Ahh, paints a pretty picture, don't it?

[Ed: Beautiful]

If you're unfortunate enough to have missed the previous six slots (DOH!), check them out here:

This is the seventh and final part of our Visual Basic Database tutorial. Today, we'll be covering:

  • SQL Server, Oracle and all that jazz
  • The Groovy VB Database Tools
  • Where to go from here
  • Recommended reading
  • Maybe some other stuff I haven't thought of yet

Ready? Hold on tight...

How much information does your typical database hold? If it's as empty as a hermit's address book, you should have no problems using Access MDB files in your projects.

But there comes a time in every programmer's life when hair starts to grow on the chest (yes, women included) and a database change is required.

That unwanted hair can crop up for many reasons. For example, Access has many limitations. For a start, it can only store up to 2Gig of information.

Another disadvantage; let's say your Access database is on a network and you send it a query. Do you know all the records from related tables will be sent across to your computer for processing?

That's because Access is a file, a 'desktop database' that can't think so all the information is sent to your computer's brain to sort out. And that spells major network traffic. But other more powerful types, live 'database servers', send back only the information you need.

Also, when open to multiple users, corruption in Access databases in a distinct possibility/probability. Trust me, I've been there, done that, got the sack.

So what other types of 'database servers' are there? You may overhear some of the following mentioned at nerdy cocktail bars:

  • Sybase SQL Server
  • SQL Server by Microsoft (PC version of Sybase SQL Server)
  • SQL Anywhere by Sybase (cut-down version of Sybase SQL Server)
  • ORACLE by Oracle, surprisingly
  • DB2 by IBM

Which is the best? Whoah, don't ask me that question. If I told everyone I recommend the latest version of SQL Server, I'd be hit by a thousand flames and probably a whopping great Oracle lawsuit. Err DOH!

Top Tip: In a corporate multi-user environment, the need for a big boy database server such as SQL Server becomes apparent. If you're not convinced, delve into the Access help and search for 'Access specifications' which shows you the limitations of the .MDB format. Now visit the above database server websites and compare the two often the big ones do stuff Access can't even think about. You'll also find that many corporations disallow the use of Access databases to store mission-critical information, opting for a more powerful database server.

Choosing the database server to use can also take a little deciding. Consider:

  • Your data storage requirements
  • Your development tools (for example, Microsoft make it easier for Visual Basic programmers to connect with their own SQL Server, hint hint)
  • Training and maintenance issues

Wow, I'm getting slightly serious now... and that's not my scene. So I'll end this section by summarising in a sentence; there is life beyond Access... it ain't easy, but it pays.

If you've still got your Visual Basic 6 packaging, you may notice a huge son-of-a-gun green sticker on the box, proudly claiming "Includes Visual Database Tools".

And this isn't just another Department-of-Justice-type lie; Visual Basic really does include a few cool tools that let you mess around with databases within the development environment.

Microsoft created this groovy little feature to allow professional developers to alter the structure of major databases whilst remaining within VB so print out this section and take it to a machine, perhaps at work, where you can access SQL Server or one of the other database servers mentioned earlier.

If you only have Microsoft Access, you'll feel about as left out in this section as Pope John Paul in a French brothel.

So let's try it out. No, not the brothel, but rather more excitedly, the Visual Database tools:

  • Create a new Standard EXE project
  • Click the small 'Data View' icon on the toolbar

A popup window similar to the below should appear:

  • Hit the 'Add a New Data Link' button

We've seen the screen in front of you before, whilst connecting the ADO control to a database.

  • Select 'Microsoft OLE Provider for SQL Server' (or your database provider) from the list and click Next

If you don't have SQL Server or similar on your computer nor on any other network machine you should ignore all my SQL-specific comments and fill in the screen just as you did for the Access database in previous instalments. However be aware that you can't use these Visual Database tools to change the structure of an Access database only for editing data.

  • On the next screen, enter a server name plus any login details then select a database name

Your completed screen should look a little like this:

  • Hit that 'Test Connection' button and cross your fingers. It should report back that everything is A-OK
  • Click OK
  • You should now be in a position to christen the Data Link I've called mine Colossi, after the server to which it is linked
  • Click the little + beside your data link

The Data View window should now look something like this:

And from here you can do virtually anything with your database define relationships, create new tables, knock out a couple of views, write stored procedures or do a few other weird things we haven't yet covered simply by right-clicking one of the folders and selecting the 'New...' option.

Clicking the + sign next to a folder will show its contents. For example, expanding the Tables tree will display all tables within my CIS database. I can then edit the table structure, add information to the table or even delete the table just by right-clicking and selecting the appropriate option.

In this section, I've shown you how to use the Visual Database tools to link direct into a major database format. But each type has its own little quirks, so I shan't delve too far into this subject for fear of excluding virtually all of our readers except perhaps Crazy Jo of Mississippi and my technically au fait pet iguana, Strangely Brown.

[Ed: I remember him!]

But you now know how to access the Visual Database tools. And you'll probably need them as you progress onwards in the wacky world of enterprise development.

So how do you jump to those upper echelons of the nerd-world? Read on...

So what's to do from here?

If I've inspired you to take your database development skills to the next level, read on. If not, perhaps it's time to backtrack to the advice of tutorial one - move into biology and study subtractive colour mixing. Or become a Welsh sheep farmer. Or something

But if you'd like more, your next step is to start really playing around with database code. Make sure you can retrieve figures from an Access database completely in code without referring to previous tutorials. Then get to know the Access query builder and its various cool uses... don't forget, it's your friend!

Your next stop is to start learning more about the more powerful databases such as SQL Server or ORACLE. As you walk down that road, you'll also bump into 'three-tier architecture' - which is just a fancy word that means splitting an application up into a few different bits, making it easier to maintain.

And if you're really geeky, you'll end up surrounded by inexplicable three-letter acronyms such as MTS, IIS, ASP and MSMQ. Hold on, the latter is a four-letter acronym, but I'll let it pass.

So where can you learn all this? I'd recommend books. I know, I know, it's difficult to find time, blah blah, you have to finish that assignment, blah blah, the toilet needs unblocking, zzzz...

Listen up, if you want to start earning mega-bucks in the world of Visual Basic and databases, it's time to put the needs of your bathroom to one side. Ideally, I'd recommend you purchase the following book:

Author Charles Williams knows what he's talking about and drags you through the ups-and-downs of SQL Server, Structured Query Language, three-tier architectures, classes, reports, data warehousing... and loads more.

If you really are keen on taking your skills to the next level, this is definitely the next step.

Alternatively, here are another couple of other bedtime reads from my bookshelf:

Good solid book based around three major projects. Excellent way to start tinkering and discover the secrets of SQL Server. Also puts a lot of emphasis on proper planning... something I never do... ;-))

Thick 900-page book that covers most of what we've discussed in this tutorial, but in more detail. Actually, about 800 pages more detail. Particularly useful if you're wanting to study more physical database code

This week, I babbled on about the different types of database out there, then we took a little look at the Visual Database tools which could come in handy at work or later in your programming career.

I finished by chatting about what you should do next and recommended a couple of suitable publications.

Don't forget to let me know how you get on. My personal e-mail address is Karl@karlmoore.com - I look forward to hearing from you!

And that just about ends this final instalment of the Visual Basic Database tutorial. Rumour has it I'll be in a couple of months covering the scary world of ActiveX, but I'm not so sure.

Still, until the next time, this is your supercool 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.

Sitemap | Contact Us

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