Visual Basic Database Tutorial - Part 7, Page 2
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.