Getting Started with MSDE and VB .NET

• By Rod Stephens
• May 17, 2002
• Article is provided courtesy of Que

From the author of
Prototyping with Visual Basic

Access versus SQL Server

Access databases are relatively inexpensive. In fact, you can open an Access database, modify the database structure using SQL, fetch records, modify and update data, and delete records using a Visual Studio application without buying the Access product itself.

Access databases have their disadvantages, however. They have some size restrictions (at most around 2GB per database), provide little in the way of user access control (only database-level passwords), and have little support for multiple concurrent users.

SQL Server (usually pronounced "sequel server"), on the other hand, provides support for huge databases (up to 1TB = 1,024GB per database), multiple simultaneous users, and very detailed user access controls. SQL Server's big drawback is its price. Depending on your installation, SQL Server licenses can cost thousands of dollars.

Unfortunately, there is no simple migration path from Access to SQL Server. Ideally, you would develop an application using an inexpensive Access database. Later, if you decided the application required the greater size, multi-user, or security features provided by SQL Server, you could upgrade the database. Sadly, Access and SQL Server are too different to make that easy. Their user security schemes are quite different and the basic architectures make a quick upgrade problematic.

Figure 1 shows a schematic representation of the architecture used by Access applications. Different applications use database access routines to interact with each database individually.

Figure 1 In Access, different applications access the database independently.

Figure 2 shows the architecture used by SQL Server. Here, applications connect to a server that contains one or more databases. The server coordinates interactions between the applications and the databases. This makes such tasks as managing user privileges easier.

Figure 2 In SQL Server, different applications access the database using a shared server.

Migrating from the Access architecture to the SQL Server architecture usually requires many changes in the way an application interacts with databases. Fortunately, MSDE offers a possible alternative.