1.2. Is Access the Only Database I'll Ever Need?

The simple answer to this question is no. Access is not the only database product on the market, nor is it the only database product available from Microsoft. There are times when you might want to use a different type of database such as SQL Server or Microsoft Development Environment (MSDE). If you've only ever used Microsoft Access for your database needs you might be wondering why you'd need another type of database program. Well, there are several reasons that are detailed in the following paragraphs.

1.2.1. Microsoft Access

Microsoft Access works very well for single-user applications. You can get by with Access for multiuser applications; however, you need to be aware of how record-locking options affect your data. It's also possible that you'll encounter users who like to open the database exclusively, thus locking out other users from the application. If you have multiple users with differing versions of Access trying to use the same database, you'll probably end up with some compatibility issues as well. In addition to the single-user and multiple-user scenarios, Access works very well when you have multiple users but a single code base that makes all of the requests to the database. For example, a front-end application written in Visual Basic can take advantage of ADO to make calls to the back-end Access database. This type of application works very well in a single- or multiuser environment as the only time the data is touched is when an ADO call is made to the database.

1.2.2. MSDE

The Microsoft SQL Server Desktop Engine (MSDE) is actually a database server, very much like SQL Server. In fact, it's actually a scaled-down SQL Server installation aimed at desktop environments. If you have a desktop application that needs the features of SQL Server, you can use MSDE in place of a full SQL Server installation. Designing your application against MSDE is also a good idea if you think eventually your application will grow large enough to need the full features of SQL Server. If you're designing a desktop application that needs a database and have the MSDE package that's included with Visual Studio, you can freely distribute MSDE. The redistribution license is included with Visual Studio. You can also redistribute MSDE with the Access Runtime included with Access 2003. There are some distinct advantages to MSDE. It's more robust, accommodating databases up to 2GB in size. Jet databases are limited to 2GB in size; however, databases larger than 1GB often suffer performance issues. It is possible for a Jet database to grow to 2GB; however, if you're designing a Jet database that large, you'll need to be very careful with building proper indexes and building in a regular compact and repair cycle. So, why not use MSDE for everything? Well, for one thing, you have more administrative and security concerns with MSDE than with Access. MSDE is a database server. Thus it can often behave in ways the users won't understand. For example, if disk space becomes an issue, MSDE will often take a table offline. A typical computer user won't know how to remedy that situation.

Along with administrative issues, you also need to worry about security. As a slightly scaled-down version of SQL Server, MSDE suffers the same security vulnerabilities as SQL Server. MSDE was recently vulnerable to the SQL Slammer worm. If you're writing an application that installs MSDE, you'll need to keep on top of security issues that affect SQL Server and MSDE.

1.2.3. SQL Server

If you're designing a large-scale professional application that requires a back-end database, you should consider using SQL Server as your database. SQL Server is the most robust of the databases listed in this chapter, but it also costs the most and has the highest learning curve. You cannot distribute SQL Server freely, nor can it be installed on a typical desktop class machine. SQL Server requires Windows NT, Windows 2000 Server, or Windows 2003 Server. One of the advantages to both SQL Server and MSDE is that code written for one will work equally well with the other. The two products use the same API and SQL language. However, there are several distinct advantages to SQL Server. SQL Server supports larger databases and more users. In addition, it has many features that are not found in MSDE, such as full-text searching, replication, failover, and Query Analyzer. From a developer's perspective, SQL Server allows you to script the creation of a database. MSDE doesn't support this functionality.

If you're not sure which type of database to create for your application, ask yourself the following questions:

• Does your application need to be accessed by multiple users?

• Does your application need to create databases via code?

• Will your database grow beyond 2GB?

• Will your application need replication or full-text searching?

Even answering these questions won't tell you for sure every time which type of database you should use for your application. You'll have to use the answers to these questions as well as some common sense and research to determine which type of database application to use. For example, if you need a database that promises to grow to around 1.5GB and needs to be accessed by multiple users on a central server, you can utilize either MSDE or SQL Server. However, using SQL Server gives you some added benefits, including the capacity beyond 2GB in size for future growth. If money is an issue, you'll want to lean toward using an MSDE database, as the license is included with Visual Studio.

Whatever database you choose, be sure to research your options before making your decision. If you do choose SQL Server, you'll need to do some research on server sizing and performance based on your application's size and number of users.