Microsoft Data Engine (MSDE) for Microsoft Visual Studio 6.0: An Alternative to Jet for Building Desktop and Shared Solutions
Clarity Consulting, Inc.
Updated June 2002
Summary: This article discusses the benefits of using the Microsoft Data Engine (MSDE) in creating desktop and shared database solutions; it covers options for accessing data within MSDE, including Microsoft's ActiveX® Data Objects (ADO) and Data Access Objects (DAO) technologies. (26 printed pages)
What Technologies are Involved?
How to Choose a Database Engine
Creating MSDE Solutions
Data Access Options in MSDE
Migrating from MSDE to SQL Server 7.0
Comparison Reference: Jet, MSDE, and SQL Server Technologies
Editor's Note: Since this article was first published Microsoft has released SQL Server 2000 Desktop Engine (MSDE 2000), a major version upgrade to the Microsoft Data Engine (MSDE). The following notes assist in relating the original MSDE information to MSDE 2000:
- All references to “MSDE” indicating the original SQL Server 7.0-based “Microsoft Data Engine” are also technically accurate for SQL Server 2000 Desktop Engine (MSDE 2000), the SQL Server 2000-based MSDE successor. Likewise, the below references to Access 2000 and SQL Server 7.0 remain valid for Access 2002, and SQL Server 2000 except as otherwise noted.
- All references to Windows 98 also apply to Windows Me.
- All references to Windows 2000 also apply to Windows XP.
- For MSDE 2000 product overview and availability information see MSDE 2000 on the SQL Server product site.
- For feature comparison of SQL Server 2000 editions see Features Supported by the Editions of SQL Server 2000.
- For information about upgrading from MSDE 1.0 to MSDE 2000, and for additional information about using the MSDE database from Access, see the MSDN article, When to Choose MSDE 2000 as the Database Engine for Your Application.
Developers creating applications are faced with a choice: with so many database options available, which technology should they choose? Many factors need to be considered before selecting a database. For example, how many users will the application need to support? Will all of the users access the system at the same time? What are the costs involved? Will the technology allow the database to grow if the application requirements change over time?
Until recently, Microsoft offered two means of database management: Microsoft Jet (the Microsoft® Access database engine that is shipped with both Access and Microsoft Visual Studio®) and Microsoft SQL Server™. While each technology has its strengths, it was previously difficult for developers to select a single database system that would meet both the long term and short term needs of an application from desktop to data center. Jet-based solutions are a good choice for desktop or shared applications with their ease of use and low cost. However, developers find that as their systems grow, many code changes are required before their Jet applications can be migrated to the performance and scalability of SQL Server. With the release of the Microsoft Data Engine (MSDE), a SQL Server-compatible database engine, there is an exciting new option for creating desktop and shared database solutions. MSDE enables Visual Studio developers to build freely distributable desktop and shared database solutions that easily migrate to SQL Server 7.0 when the solution must scale.
This document is intended for developers who are comfortable developing applications with the Jet database engine as well as for those who are new to the Microsoft SQL Server database development environment. This paper provides all the information needed to evaluate MSDE as a database engine for desktop and shared database solutions, including installing the MSDE service and migrating existing solutions that use Jet to MSDE.
This document discusses the benefits of using the Microsoft Data Engine (MSDE) in creating desktop and shared database solutions. We walk through the migration of an existing sample Access database—using the Jet engine—to an Access and MSDE client-server database application and install a complete version of a client-server Access application. We discuss various options for accessing data within MSDE databases, including Microsoft's ActiveX Data Objects (ADO) and Data Access Objects (DAO) technologies. Finally, a reference section comparing embedded database technologies is included at the end of this document.
Before discussing the relative pros and cons of each database engine, it is important to understand their features and functionality. The following section briefly describes the three database technologies this paper examines.
A "database engine" is the core process that a database management system, such as Access or SQL Server, uses to store and maintain data. A database engine is often just a database management system without its graphical management tools. Engines typically serve two main purposes. The first is to store information, and the second is to process requests for stored information. Requests to retrieve or alter stored data are often called database queries.
The most common use for a database engine is in conjunction with standalone custom database applications created with tools such as Microsoft Visual Studio. In these scenarios, generic graphical database management tools are not needed since the custom application generally provides its own facilities for management of the underlying database. In addition, since database engines are easily distributable, custom application developers can include the engine as a part of the "setup" application.
Microsoft Data Engine (MSDE)
The Microsoft Data Engine (MSDE) is a fully SQL Server-compatible database engine that can be used for desktop and shared database solutions built with either Visual Studio 6.0 or Access 2000. In addition, solutions built with MSDE for Visual Studio 6.0 or Office 2000 Developer can be distributed royalty-free. MSDE is based on SQL Server technology and is fully compatible with SQL Server 7.0. When used in Access 2000, MSDE is an attractive alternative to Jet—the default Access 2000 database engine. In addition, MSDE is available to Visual Studio 6.0 developers who wish to build custom applications with embedded databases that are SQL compliant. SQL compliance enables desktop and shared database solutions using MSDE to be scaled up to SQL Server without code modifications.
MSDE operates as a service. Services are applications that run as background processes. The behavior of services differs from that of other applications. For example, while most applications are executed only when a user launches the application from the Start menu, services such as MSDE are generally started and stopped by the operating system environment. Desktop and shared database solutions using MSDE require that the MSDE service be running. Services are typically used by other applications, not end-users. A service runs in the background and waits for processing requests. In the case of MSDE, these requests are for database operations. Services need not have a user interface, since services are used by other applications, not users. An Access 2000 project or a Visual Basic application can interact directly with a service. These applications serve as the user interface to MSDE.
In summary, the MSDE relational database engine exhibits the following characteristics and features:
- Is fully compatible with Microsoft SQL Server 7.0 and SQL Server technologies.
- Is freely distributable for solutions built with MSDE for Visual Studio 6.0 or Microsoft Office 2000 Developer.
- Runs on Windows® 95, Windows 98, Windows NT, and Windows 2000 (both Intel and Alpha) platforms.
- Has client-server architecture (database operations occur on the database server, not the client machine – unless, of course, the SQL Server database engine is installed on the client machine for embedding with client-side applications).
- Runs as a service.
- Does not include a user interface for database development. Instead, other applications such as Access 2000 or custom Visual Basic® applications provide the user interface. Licensees of other SQL Server 7.0 products, or Visual Studio Enterprise Edition, can use the included user interfaces for database development to develop their MSDE applications, however these tools cannot be redistributed with an MSDE application.
Jet 4.0 is the default database engine used in Microsoft Access 2000, and it is also included with Visual Studio 6.0 tools. Jet is a file-based data management system: databases are contained in single database file, and read-write operations to the database are controlled by the client workstation. Jet can handle multiple users and is scaled for desktop and shared database solutions. Microsoft Access provides an easy-to-use interface to the Jet database engine. Developers can also use Visual Basic and other development tools to create custom front-end applications that use Jet as an embedded database.
SQL Server is a client-server database management system. Unlike file-based databases, client-server database engines manage read-write operations to the database. Because of this, client-server databases such as SQL Server can handle many more concurrent users and vastly greater amounts of data. SQL Server is designed to meet the most demanding database application requirements for the enterprise, including operational and decision support systems implemented today and in the future. SQL Server is offered in different editions based upon data, user, and hardware requirements.
SQL compliance is the level to which databases adhere to the ANSI Structured Query Language (ANSI-SQL) industry standards for database communication. MSDE and SQL Server both conform to Transact-SQL (T-SQL) language guidelines. Jet does not. Instead, Jet implements its own query language that is capable of utilizing several Visual Basic for Applications functions. Transact-SQL is a well-defined language for communicating with and programming a database server. Transact-SQL is a superset of ANSI-SQL, extending functionality to enable a server to be programmed with custom business logic. This custom business logic can be invoked from the client and executed on the server. Conformance with Transact-SQL allows MSDE objects to be fully compatible with SQL Server, an advantage over Jet.
Whether you are creating a new application that will access a database or migrating an existing database application to a new database engine, it is important to consider each technology. Which one should you choose? You should consider MSDE as an alternative to Jet 4.0 for your desktop and shared database solutions. The following section discusses a variety of database application requirements seen in most development efforts and provides recommendations as to which database engine may best suit your project's requirements.
Database Requirements at a Glance
Table 1 shows a list of enterprise requirements and tells how each of the data engines handles these requirements. If your application has any of the needs listed in the left column (or might have any of these needs in the future), you will want to consider implementing MSDE as an alternative to Jet.
Table 1. Database Requirements
|Requirement||SQL Server and MSDE (use MSDE if these are future requirements)||Microsoft Access (Jet)|
|Scalability|| || |
|Business Critical|| || |
|Rapid Application Prototyping|| |
|Additional User Interfaces|| || |
When to Choose MSDE
MSDE is an attractive option for upgrading existing Jet applications to improve scalability or for creating new desktop and shared database solutions that can be easily migrated to full SQL Server, as the solutions must need to scale.
SQL Server compatibility
MSDE data engines are fully compatible with SQL Server. Thus, tables, stored procedures, triggers, and any other database objects created in MSDE will operate without modification in a SQL Server database. In contrast many Jet objects, such as queries, cannot be migrated to SQL Server without modification. If you are considering migrating to SQL Server in the future, or it is likely that your database application will exceed the limitations of Jet, you should choose MSDE. Doing so will provide you with the simplest path to upsizing the database to SQL Server without changing a single line of code.
MSDE differs from Jet in that it is not file based and is not subject to the limits of a file-based database. File-based databases limit performance of applications because database processing occurs on the client machine. With server-based database systems like SQL Server and the MSDE technology, requests for information are processed on the server and only the resultant data is sent back to the client application. This scenario greatly reduces network traffic; it also allows users to access data and experience acceptable application performance without a high-end client workstation.
When multiple users are using a file-based database, each must open and read information from the same file. This can create a bottleneck, as each application user waits for their turn to read from, or write to, the database file. Server-based database systems are specifically tuned to shield client applications from this kind of problem, resulting in improved application performance.
When applications are distributed across a network, client-side processing begins to take a toll on the performance of file-based databases. When a shared file-based database is located on a network file server, each client application must "pull" the entire contents of the query set's source data to the client workstation, even if the request yields only a single row of summarized data. Server-based data engines perform all processing on the server and return only query results to the client. Applications that access the database can then perform well over low-bandwidth links.
MSDE is the best database engine choice if your database application is to be distributed over a network, accessed by multiple users simultaneously, or accessed over a slow link.
MSDE databases running on Windows NT and Windows 2000 platforms are capable of utilizing Windows 2000-integrated security to protect database objects. Jet databases are not. Both MSDE and Jet allow you to secure individual database objects such as tables or stored procedures.
If you want to leverage the security features of Windows 2000, MSDE is the right choice.
MSDE is available to any licensed customer of Office 2000 Professional, 2000 Premium edition, or any Visual Studio 6.0 Enterprise or Professional edition tool, including:
- Visual Studio 6.0, Professional and Enterprise editions.
- Visual Basic 6.0, Professional and Enterprise editions.
- Visual C++® 6.0, Professional and Enterprise editions.
- Visual InterDev™ 6.0 Professional edition.
- Visual J++® 6.0, Professional edition.
- Visual FoxPro® 6.0 Professional edition.
Developers that build desktop and shared database solutions using MSDE may distribute their applications royalty-free, provided that the developer of an MSDE solution is licensed for the following products:
- Microsoft Office 2000 Developer.
- Any Microsoft Visual Studio 6.0 Professional or Enterprise tool (as listed above).
Jet databases are also freely distributable as the data engine for solutions built with Microsoft Office and Visual Studio development tools. However, desktop and shared database solutions built with Jet do not offer the clearest path to SQL Server. If cost is a limitation with your development effort, MSDE is the best choice.
For desktop or shared database solutions that have growth potential, using MSDE as the data engine technology provides a low-cost solution for the near term, while maintaining a clear migration path for future growth. If and when organization needs change, scaling up to a full-size SQL Server solution can be done without changes to a single line of code. Additionally, by using the same SQL-compliant methods, developers can transition seamlessly between small MSDE systems and larger SQL Server systems using the same data access language.
When to Choose Jet
Jet 4.0 can be a good choice for many database systems. This section outlines several situations where a Jet database would be the optimal solution.
With Jet, backup and distribution is easy because the database is contained in a single file. Other files are required to access Jet databases from applications such as Visual Basic or Microsoft Access, but these file dependencies are well defined and do not require a significant amount of space or processing power to operate. MSDE is more difficult to distribute and install than Jet. If you want to simplify database distribution and setup, Jet is a good choice.
Jet 4.0 databases can be distributed as part of solutions built with:
- Microsoft Office 2000.
- Microsoft Access 2000.
- Any Visual Studio 6.0 language product, all editions.
If cost is a factor in your project, Jet is a good choice.
Small Workload and User Base
Both MSDE and the Jet database are tuned for desktop and shared use. There are applications that may realistically never grow beyond a 2-gigabyte (GB) size or need to support more than twenty users. If you have an existing Jet application that probably won't grow much in size or be required to support a large user base, Jet will continue to be a good option.
Jet is fairly compact, which makes it a good choice for systems with limited resources. Of the three database-engine options outlined here, Jet uses the least amount of storage space and consumes the least amount of system memory. If you are using machines with limited disk space or system resources, Jet is the best choice.
Jet 4.0 maintains the greatest compatibility for systems developed with previous versions of Jet. You may have multiple versions of an existing Jet application (a difficult situation in and of itself!) and want to maintain a Jet/Access solution without adding SQL Server or MSDE into the mix. If you have a database application that is dependent on a function available in a previous version of Jet, or a function that would require a great deal of effort to convert to a SQL-compliant database, Jet may be a better option than MSDE or the full SQL Server 7.0.
For desktop systems that will not grow in size or user base, Jet databases provide a low-cost, low-overhead solution that maintains the greatest backward compatibility with existing Jet solutions.
When to Choose SQL Server
Why not develop all of your applications in MSDE or Jet? There are several situations where MSDE and Jet become impractical. Several of these cases are outlined in what follows.
MSDE has a data limit of 2 GB. Jet also has a data limit of 2 GB in each of its data files, but because Jet database files can be linked together, size is really only limited by disk space. However, managing multiple data files can become a difficult administrative task. If a database is currently larger than 2 GB, or has the potential to grow beyond this limit in the near future, SQL Server 7.0 is the best option.
MSDE performance at five concurrent workloads or less matches SQL Server, and above five concurrent workloads is tuned to match Jet.
Jet can support up to 255 users from a technical and licensing standpoint, but is recommended for less than 20 total users. As the user base increases, performance degrades significantly with file-based databases.
SQL Server 7.0 is capable of supporting hundreds or thousands of concurrent users at the highest levels of enterprise-class performance. If an application requires more than five concurrent users, SQL Server is the best option.
If database requirements call for more than 2 GB and performance is an important factor, SQL Server is the best option. SQL Server provides the best performance and highest scalability in database technology.
MSDE Licensing Explained
MSDE is available for use by all licensed customers of Office 2000 Professional, Office 2000 Premium, and Office 2000 Developer editions and Visual Studio 6.0 Professional and Enterprise editions, including:
- Visual Studio 6.0, Professional and Enterprise edition.
- Visual Basic 6.0, Professional and Enterprise edition.
- Visual C++ 6.0, Professional and Enterprise edition.
- Visual InterDev 6.0 Professional edition.
- Visual J++ 6.0, Professional edition.
- Visual FoxPro 6.0 Professional edition.
Licensing MSDE solutions for distribution
Solutions using MSDE for Visual Studio 6.0 or Office 2000 Developer are freely distributable subject to the terms of the end-user license agreement accompanying these products. Developers may distribute these solutions to their customers and end-users royalty-free to Microsoft.
Licensing when interoperating with SQL Server databases
MSDE solutions that interact with a central SQL Server 7.0 server require a per-seat client access license (CAL). It is also possible to purchase a processor license for SQL Server 7.0. For more information on SQL Server 7.0 pricing and licensing, see the SQL Server site at http://www.microsoft.com/sql/howtobuy/production.asp..
MSDE runs as a service on the machine where it is installed. It only needs to be installed once as the MSDE service can host multiple databases from different applications.
Note If any editions of SQL Server are installed on a machine, then MSDE is not required, since all editions of SQL Server are capable of hosting any databases that rely upon MSDE. Using SQL Server rather than MSDE to host an application's databases will not limit functionality. SQL Server supports all of the functionality of MSDE. MSDE does not, however, support all of the features of SQL Server. Certain features, such as rich text search for example, are not supported by MSDE. For more details on SQL Server features that are supported by MSDE, see the SQL Server 2000 Desktop Engine (MSDE 2000).
Requirements for a Proper Installation
The first step in installing MSDE is to review its operating requirements. A typical MSDE installation requires 55 megabytes (MB) of local hard drive space, and 64 MB of system memory is recommended.
MSDE is for use with Windows 98, Windows 95, Windows NT 4.0 with Service Pack 4 or later installed, and Windows 2000. MSDE runs on both the Intel and Alpha versions of Windows. Each platform has slightly different requirements, and certain features are available under Windows 2000 and Windows NT 4.0. The requirements are outlined below.
Certain MSDE features are not available under Windows 95 or Windows 98. These include integrated security options, advanced performance features, and support for certain network platforms. Check the SQL Server 2000 Desktop Engine (MSDE 2000) for further details on limitations when installing on these platforms.
Windows NT 4.0/Windows 2000
MSDE runs on both Workstation and Server editions of Windows NT 4.0. Service Pack 4 is required to run MSDE on any Windows NT 4.0 platform. MSDE runs on all editions of Windows 2000.
Obtaining the MSDE for Visual Studio 6.0 Setup Program
MSDE is available to registered users of any Visual Studio 6.0 Professional or Enterprise edition tool (such as Visual Basic 6.0 Enterprise). MSDE for Visual Studio 6.0 is available in the MSDE folder of the MSDE for Visual Studio 6.0 CD.MSDE for Visual Studio 6.0 is intended for inclusion with custom applications. Visual Studio developers are encouraged to
- Develop SQL Server-compatible applications using the SQL Server Developer edition that is included as part of the MSDE for Visual Studio 6.0 package.
- Deploy the application with the MSDE redistributable engine that is available with MSDE for Visual Studio 6.0.
SQL Server Developer edition is available to Visual Studio 6.0 developers who receive MSDE for Visual Studio 6.0, and is a full copy of the SQL Server 7.0 product licensed for the sole use of developing and testing MSDE solutions (see the end user license agreement for more details). SQL Server 7.0 Developer edition includes SQL Server management tools that are useful during database application development.
The redistributable MSDE engine included with MSDE for Visual Studio 6.0 does not have its own UI for developing MSDE databases. Visual Studio developers building MSDE solutions may use the Visual Studio 6.0 data tools, Access 2000, or the SQL Server Developer Edition database management tools, such as SQL Server Enterprise Manager, as the development environments.
Note Windows 95 and Windows 98 developers who have also licensed Visual Studio 6.0 may use SQL Server Desktop, included with the SQL Server Developer edition, to build MSDE solutions on the Windows 95 and Windows 98 development.
Installing MSDE from Office 2000
MSDE is distributed in all versions of Office that include Access, such as Office 2000 Professional, Office 2000 Developer, and Office 2000 Premium. Although MSDE is not part of Microsoft Office 2000 or Microsoft Access 2000 standalone setup, you can install MSDE from the Office 2000 CD-ROM by running Setupsql.exe located in the \Sql\x86\Setup folder.
The Microsoft Office 2000 version of MSDE also contains additional help files that are located in the \Windows\Help folder, the SQL Server Enterprise Manager Help (entmgr.chm), and the Replication Wizard Help (replwiz.chm), which you can use with the SQL Server database utilities available from the Access Tools menu.
Once you install MSDE on your computer, on Microsoft Windows 95 or later, you may need to start the SQL Server Service Manager (double-click the MSSQLServer icon on the task bar and click Start/Continue). MSDE starts automatically on Windows NT 4.0 or later; you may want to automatically start MSDE on Windows 95 or later by selecting the Auto-Start service when OS starts check box on the SQL Server Service Manager window.
Access 2000 supports MSDE through new Access Project files. Projects in Access 2000 are different from linked databases. Project files allow for control of SQL Server and MSDE features that are not supported by linked tables in Jet databases.
For SQL Server users, Office 2000 solutions using MSDE databases can be identified in SQL Server's Enterprise Manager as the product "SQL Server Office" in the SQL Server Properties window.
Installing MSDE for Visual Studio 6.0
Visual Studio 6.0 developers are encouraged to develop SQL Server-compatible applications using SQL Server Developer edition, and then use MSDE as part of the custom application distribution. MSDE for Visual Studio is installed through a command-line InstallShield installation program. This installation program is designed to allow deployment of MSDE solutions to customers and end-users. For example, consider a new, custom desktop or shared database solution created with Visual Basic 6.0 that uses MSDE. In order for the application to function, MSDE must be installed. In creating the installation program for the Visual Basic application, the developer includes an argument to execute the command line MSDE setup. Then, during installation of the custom application, MSDE is installed silently, without requiring any additional steps.
For detailed information on MSDE for Visual Studio installations, see How to Install the Microsoft Data Engine (MSDE) from the Command Line in the MSDE for Visual Studio 6.0 Technical FAQ at http://msdn.microsoft.com/vstudio/downloads/addins/MSDE/techfaq.aspx.
MSDE solutions are usually created in one of two ways: either a new database is created, or an existing Jet database is converted into an MSDE solution. The first part of this section will walk through the basics of migrating an existing Jet database to MSDE using Access 2000 and the Access Upsizing Wizard. It will also explain how to install a completed MSDE sample database. The second part of this section will walk through the creation of a new MSDE database solution using Access 2000.
Note While we use Access 2000 in our example, any tool capable of creating a SQL Server database, such as the SQL Server 7.0 Enterprise Manager or Microsoft Visual InterDev, can be used to create an MSDE solution. It is recommended that Visual Studio 6.0 developers use the included SQL Server Developer edition to create SQL Server-compatible databases, and then deploy the custom solution with the MSDE redistributable engine.
Migrating existing Jet databases to MSDE
The following section walks through the steps involved in moving an Access Jet database application to an MSDE solution. We use the Northwind Traders sample database that is distributed with Access.
Using the upsizing wizard
Access 2000 includes an upsizing wizard designed to convert Access Jet databases into SQL Server databases. Because MSDE is compatible with SQL Server 7.0, we can use the same migration wizard to convert a Jet database into an MSDE solution. If, at a later time, we need to convert the database to SQL Server 7.0, the MSDE solution can be converted directly.
A note about backup strategies
It is possible to migrate to MSDE without updating an existing Access database file. Even so, it is always a good idea to back up an existing Access Jet database before using the migration wizard. The simplest way to accomplish this is to make a copy of the Access database file (.mdb). Back up this file to the appropriate backup media.
Creating a Client/Server Database under MSDE Using the Access Upsizing Wizard
This section assumes that the developer has successfully installed MSDE on their desktop. If you have not installed MSDE, see the "Installing MSDE" section of this paper.
To create the client/server database:
- Verify that you have the security permissions to create a database on your MSDE dataserver.
CREATE DATABASE permission is required during the migration process.
- Open the existing Access database.
For this example we will be using the Northwind Traders sample Microsoft Access database (NWIND.mdb).
- From the Access menu, choose Tools, then choose Database Utilities, then choose Upsizing Wizard.
- Select Create a New database.
The Upsizing Wizard offers a choice of adding the contents to an existing SQL Server database or creating a new SQL Server Database. Although the upsizing wizard mentions only SQL Server databases, the wizard will also operate against MSDE. For our purposes, we will migrate Northwind.
- At the prompt requesting information about the destination location, enter the server name (after you have checked to make sure that the service is running).
The name of the destination server is the name of the machine on which MSDE has been installed.
Note The MSSQLServer service must be started on the target MSDE for the Upsizing Wizard to function. MSSQLServer services can be started and stopped with the SQL Server Service Manager distributed with MSDE.
- Choose a name for the new database.
The Wizard suggests a name based on the original Access database name. Use NorthwindSQL as the database name for this example.
If you are on a Windows NT or Windows 2000 platform and running the wizard from the same account that was used to install MSDE, you have permission to create a database by default when using Windows NT integrated security. If not, you need to enter the user ID and password of an account that has CREATE DATABASE permission.
- Now you can use the Upsizing Wizard to select which tables you would like migrated to the new MSDE solution. To migrate a table, select the table name from the list on the left using the arrow buttons or the enter key. For this example, select all of the tables for migration.
The Upsizing Wizard allows conversion of table attributes. It allows conversion of all indexes, default values, validation rules, and table relationships. Relationships between tables can be migrated in one of two ways:
The first method for enforcing table relationships is through the use of Declarative Referential Integrity (DRI). DRI enforces referential integrity through primary and foreign key constraints.
Triggers are the second method for enforcing referential integrity in table relationships. Triggers are operations that can be associated with the INSERT, UPDATE, and DELETE operations of a table. By adding logic to each of these events, triggers can be used to enforce referential integrity. Triggers can also replace the functionality of cascading updates and deletes, something that DRI does not support.
Note If you choose triggers, the Upsizing Wizard will still create DRI relationships, but the foreign key constraints will be disabled. No foreign key checking will occur.
- You are given the option to add Timestamp Fields. The Timestamp data type is automatically updated every time a row containing a timestamp column is updated or inserted. When working with concurrent users or linked databases, it is important to have the ability to recognize when a row of data that has been retrieved is still the most current data. The timestamp allows you to see if a row of data has been updated since it was retrieved from the database. Timestamps become very useful tools in databases as they scale. Allow Access to determine when timestamps should be added to the destination database.
Migrating Access applications
The upsizing wizard provides two options to migrate your Access application with your database. The first option is to link the new SQL Server tables to your existing application database. This will create links to the new MSDE database tables, and keep the existing Access tables. The existing Access tables are renamed with the suffix "_local."
In our migration of the Northwind database, we do not need to retain the local Access tables, so we will not choose this option.
Note If you choose to link to your existing application, it is important to back up your existing database file before attempting migration as this option will attempt to make updates to your existing database file.
The second option, which is the one we will use to convert Northwind, is to create a new Access client-server application. To do this, you need to specify a new name for the application different from the original name.
Next you need to specify data link properties. Here you can use a user name and password, or if you are working with Windows NT or Windows 2000, you can use Windows 2000 integrated security. In either case, you must specify a user that has CREATE DATABASE privileges on MSDE. Microsoft recommends using the Windows 2000 integrated security, if possible.
Items that are not migrated
The Upsizing Wizard does not convert certain items. Other items are converted, but require verification after the wizard is complete. When using the Upsizing Wizard, it is important to review all changes made to tables, views, forms, and reports. It is equally important to verify the logic used in any stored procedures that are created, and to review any declared table relationships.
Specifically, the Upsizing Wizard uses the following rules when converting databases:
- Duplicate column names are given an alias.
- Date delimiters are converted.
- Boolean constants are converted to integers.
- String concatenation is converted from an ampersand (&) to a plus sign (+).
- Wildcard characters are converted to their appropriate Transact-SQL equivalents.
- WITH TIES is added to all TOP queries that contain an ORDER BY clause.
The following Access SQL syntax elements are not supported by MSDE and are not converted. The Upsizing Wizard removes these items from SQL statements:
- DROP INDEX.
- Table in UNION.
- ORDER BY in Unions.
For more information on converting Access items into SQL Server compatible objects, see the Knowledge Base article INF: How to Convert an Access Database to SQL Server.
For additional information about converting Access databases to MSDE, see When to Choose MSDE 2000 as the Database Engine for Your Application.
Installing the Client-Server Northwind Access Project
Now that we have looked at the steps required to move an Access Jet database to an Access 2000 MSDE project, it is useful to examine a completed Project. Microsoft Access 2000 provides a complete sample Microsoft Access Project called Northwind Traders (NorthwindCS.adp) that is located in the \Program Files\Microsoft Office\Office\Samples folder. The Project is a good example of what a migrated Access database might look like.
Access can automatically create the completed Northwind MSDE database the first time you open the Northwind Access Project. The Startup form looks for MSDE running on your computer and if it finds it, asks if you want to create the NorthwindCS SQL Server database. If the Startup form doesn't find MSDE running on your computer, it prompts you to enter a server name, user name, and password so you can connect to an MSDE or SQL Server database on the network. In either case, if you answer "Yes," the Northwind sample Access project runs the SQL installation script (NorthwindCS.SQL) located in the \Program Files\Microsoft Office\Office\Samples folder. This SQL script is a sequence of SQL statements that creates the database, data structure, properties, views, and database diagrams, and loads the sample data. Once the script has finished running, the Northwind sample Access Project connects to the newly created database.
Creating New Databases and Database Objects in MSDE Using Access 2000
You may be creating a new database application and decide to use MSDE as an alternative to Jet. You can create an MSDE database solution using any tool that is capable of creating a SQL Server database. This includes SQL Server's administrative tools, WSQL, and Access 2000. With its wizards and extensive help files, Access 2000 is also a good tool for creating your first MSDE database.
To create an MSDE solution in Access 2000, you create a new Access Project file (.adp) rather than an Access database (.mdb). Microsoft Access Project files connect to MSDE or SQL Server databases through OLE DB component architecture. They do not contain any data. All data is stored in the database server.
This section explains the steps used to create a new database project in Access 2000.
To create MSDE databases using Access 2000:
- Start Microsoft Access.
- From the File menu choose New.
- On the General tab, select Project (New Database).
- Select a name and location to save your database project file.
Select the name of the machine that will serve as your database server. This should be the machine on which MSDE is installed and running. Your MSDE server can be your local machine, or it can be a machine on the network.
Note The Database Wizard asks for the name of a SQL Server, but an MSDE Server can be used as well.
- Enter a user name and password.
Note If you are using Windows NT or Windows 2000, and you would like to use integrated NT security, simply leave the username and password blank. The Database Wizard will attempt to connect to the database using your NT user account.
- Select a name for the new database.
This will become the name that identifies the database on the MSDE server. You may choose a name other than the one the wizard suggests.
Follow these steps to create a new MSDE table, stored procedure, and trigger in Access 2000.
To create MSDE database objects using Access 2000:
- From the Access menu, choose View, then choose Database Objects, then choose Tables.
To create new tables in Access:
- Select Create Table in Design view.
To create stored procedures in Access:
- From the Access menu, choose View, then choose Database Objects, then choose Stored Procedures.
- Select Create Stored Procedure in Designer.
- When you have completed the procedure, click Save and confirm the procedure name.
To create a new trigger on an MSDE server table using Microsoft Access:
- Right click the table and select Triggers.
- Select New.
- Enter the trigger statement.
- When you have completed the trigger statement, click Save and enter a name for the new trigger.
Note It is only possible to manage triggers through Access for SQL Server compatible database tables. The option does not appear for local Access tables.
Defining referential integrity in Access 2000
The simplest way to enforce table relationships in an MSDE database is through the use of Declarative Referential Integrity (DRI). You can define DRI relations for your MSDE tables using an Access database diagram.
To define DRI relations:
- From the Access menu, choose View, then choose Database Objects, then Diagrams.
- Select Create Database Diagram in Designer.
- Right-click in the designer and select Show Table.
- Choose the tables for which you would like to define relationships by selecting them in the tree and dragging them into the designer. When you have selected all of the tables necessary, close the Show Tables box.
- To create a relationship between database columns, drag the row selector located to the left of the column name from one column to another column on the related table.
- Verify the columns and settings in the Create Relationship box.
- Enter a name for the relationship and click OK.
In addition to Access, SQL Server tools can be used to create MSDE databases. These tools are included in the evaluation edition of SQL Server available to Visual Studio developers. For more information about creating MSDE database objects through SQL Server tools see the SQL Server Books Online.
There are a number of options for accessing data in an MSDE solution. This section looks at the three of these options.
Access client-server applications are created using Access Projects. These Projects use OLE DB to connect to MSDE databases. Microsoft Access front-ends can be converted into database projects using the Access 2000 Upsizing Wizard.
ActiveX Data Objects (ADO) is an ActiveX programming interface that wraps the OLE DB API. ADO provides data connectivity to Visual Basic applications as well as any application capable of controlling ActiveX in-process servers. ADO can be used to connect a custom Visual Basic application to an MSDE database, access data, and perform administrative functions in the database.
For more information on how to access MSDE from a Visual Basic application using ADO, see Code Sample: Connecting to an MSDE databasehttp://msdn.microsoft.com/vstudio/downloads/addins/msde/condwnld.asp, a Visual Basic 6.0 example application containing sample code that demonstrates how to connect and execute statements against an MSDE database.
For more information on ADO, see www.microsoft.com/data/ado/prodinfo.htm.
Microsoft Data Access Objects (DAO) is an ActiveX programming interface that wraps the Jet database engine. DAO provides database connectivity to any program that is capable of controlling in-process ActiveX DLLs, such as Microsoft Visual Basic. Many of the data access options in DAO have been improved in ADO. Unless you are accessing a Jet database, you should use ADO.
For information on converting DAO applications to ADO, see the following articles:
- Migrating from DAO to ADO
- Migrating DAO/ODBCDirect to ADO To Use MSDE
Applications may grow beyond the MSDE 2-GB data limit. An application's user base may grow beyond the five concurrent workload boundary in MSDE where performance limitations begin. If this occurs, migrating the MSDE solution to SQL Server may be necessary. SQL Server is capable of handling hundreds of concurrent users and growing databases to sizes beyond one terabyte (TB). Migrating to SQL Server can fulfill scalability needs without necessitating costly and time-consuming changes to existing applications.
SQL Server Compatibility
All editions of SQL Server 7.0 can be upgraded to from an existing MSDE solution. The SQL Server installation program recognizes an MSDE installation and its accompanying data files and transaction logs. This allows MSDE to be upgraded directly to any edition of SQL Server by installing SQL Server.
Another way to upgrade an MSDE solution is to use SQL Server 7.0 Enterprise Manager from a SQL Server machine to connect to the MSDE. Once connected, data can be transferred from the MSDE installation to SQL Server. This is a good method when upgrading to SQL Server using a different machine.
SQL Server Technologies at a Glance
Table 2 shows some of the features, limits, requirements, and upgrade paths of each of the SQL Server technologies.
Table 2. Operating Systems
|SQL Server |
|SQL Server |
|Windows NT Server||Yes||Yes||Yes||Yes||Yes||Yes|
|Windows 2000 Server||Yes||Yes||Yes||Yes||Yes||Yes|
|Windows 2000 Professional||No||No||No||Yes||Yes||Yes|
|Windows NT Workstation||No||No||No||Yes||Yes||Yes|
|Windows 95, Windows 98||No||No||No||Yes2||Yes2||Yes2|
|SQL Server |
|SQL Server |
|DB Storage Limit||None||None||10 GB||None||2 GB||2 GB|
|Suggested Concurrent User Limit||None||None||30||5||5||5|
|Processors||Up to 32||Up to 4||Up to 4||2||2||2|
|Merge & |
|SQL Server |
|SQL Server |
|SQL Server |
|SQL Server |
|SQL Server; |
1. SQL Server Desktop is only available as an install option with either SQL Server or SQL Server, Enterprise.
2. Features not available on Windows 95 and Windows 98 include:
Windows NT Authentication
Fiber Mode Scheduling
Named Pipes Server Netlib
Appletalk Server or Client Netlib support
Banyan Vines Server Netlib support
Multiprotocol Server Side encryption support
3. SQL Server Desktop, MSDE only, default features
Autoclose unused DBs
4. CAL or processor license not required unless communicating with a SQLServer
5. (via Jet) requires either per seat or per server; (via MSDE) requires per seat CAL or processor license
This reference section provides information concerning technical considerations involved in selecting a database management system (DBMS). For comparison, information about Jet, MSDE, and SQL Server is included for each of the items discussed.
Capacity refers to the amount of data that a database engine is capable of managing. There are limits to the amount of data every database can contain.
A Microsoft Access Jet database file can contain up to 2 GB. However, because a Jet database can include linked tables in other files, its total size is limited only by available storage capacity.
There is a hard 2-GB limit on data in an MSDE. However, multiple MSDE databases (each with a 2-GB limit) be supported by a single MSDE server.
SQL Server can support over 1 TB of data.
Concurrency refers to the number of users that can simultaneously access the database. Each of the database technologies discussed in this paper is capable of handling two or more concurrent users. The following section describes how many users each technology can support at the same time.
In order to prevent multiple users from editing the same data during transactions, database engines typically implement some form of data locking. This section will also discuss the data locking capabilities of each database system.
Jet can support up to 255 concurrent users, but performance of the file-based architecture can prevent its use for many concurrent users. In general, it is best to use Jet for 10 or fewer concurrent users. Jet 4.0 now supports row-level locking.
MSDE enjoys the full performance of SQL Server with five concurrent workloads or less. MSDE can support more than five concurrent workloads both from a technical and licensing standpoint, but Microsoft strongly suggests conducting application testing as appropriate to ensure adequate performance for greater than five concurrent workloads can be achieved. MSDE also supports row-level locking.
SQL Server can support hundreds of concurrent users. Like Jet and MSDE, SQL Server also supports row-level locking.
Dynamic Backup and Restore
Backups are a vital part of any database system. Backups insure that data can be recovered in the event of an emergency.
Backing up a Jet database requires simply backing up the database file.
In MSDE, you can backup both data files and transaction logs. Data files contain the data in the database. Transaction logs contain the changes that have occurred to the database over a fixed period of time. A typical backup procedure for a large database would be to back up the transaction log every night and back up the data once a week. In the event of a failure, the most recent database backup is restored. Then, by applying the changes contained and all of the subsequent transaction log files, the database is brought back to the state in which the last nightly transaction log was backed up.
SQL Server handles backups in the same manner as described for MSDE.
Remote administration refers to a user's ability to make changes to a database without sitting at the database server. It's often convenient to administer database servers from remote locations whether the remote location is across the office or across the country. The following section describes remote administration options for each database technology.
Jet databases cannot be remotely administered in the conventional sense. Jet database files, however, can be shared across a network. The network file system, and not the database file, controls this centralized administration.
There are some limitations in remote administration of MSDE. If an MSDE is on a Windows 95 or Windows 98 platform, then the service cannot be started remotely. Once started locally, Windows 95 or Windows 98 MSDE can be administered remotely.
MSDE servers cannot be involved in a multi-server remote administration scenario where the remote administration transaction in question involves more than one MSDE or SQL Server. Thus, it is not possible to perform a transaction involving two remote servers at the same time, if one or more of the servers is an MSDE.
SQL Server supports full remote administration and multi-server administration.
Many systems require data to be replicated from one database to another. The following section describes the replication options with each database technology.
Entire Access Jet databases can be replicated with a simple file copy. Jet databases can also be replicated with any ODBC data source. To replicate data from one Access database to another, Jet databases tables can be linked together.
For more detailed information on Jet 4.0 replication, see the Access 2000 Help files.
A SQL Server client access license (CAL) or processor license is required on the SQL Server database which MSDE replicate with.
SQL Server Desktop requires a CAL to replicate with other SQL Server databases.
Security is an important aspect in many database systems.
Jet databases can be secured with a password. Password security only applies to opening a database. Once a database is open, all of its objects are available to the user.
Additionally, Jet databases support user-level security. Under user-level security, users are required to identify themselves with a user id and then type a password when they start Microsoft Access. Permissions are granted to groups and users to regulate how they are allowed to work with each table, query, form, report, and macro in a database. The users defined in Jet databases are not the same as the integrated Windows 2000 security users.
- Windows 95, Windows 98
MSDE supports SQL Server user-level security. Users are created and managed within MSDE. Windows 95 and Windows 98 servers do not recognize integrated Windows 2000 users.
- Windows NT/Windows 2000
Windows NT/2000 servers recognize both SQL Server users and integrated Windows 2000 users.
- Windows 95, Windows 98
MSDE supports SQL Server user-level security. Users are created and managed within MSDE. Windows 95 and Windows 98 servers do not recognize integrated Windows 2000 users.
- Windows NT/Windows 2000
Windows NT/2000 servers recognize both SQL Server users and integrated Windows 2000 users.
Stored Procedures and QueryDef Objects
Databases are useful for storing data and for performing logical operations on data as well. The following section describes some of the objects used to perform logical operations with each database technology.
Jet databases do not support stored procedures; instead, they contain QueryDef objects. Permanent QueryDef objects serve to increase performance by saving a version of a query that has been compiled, or rather, evaluated for use by the Jet database engine. By storing a compiled version of a query, the database engine can execute without having to re-evaluate the statement. The same functionality is accomplished in both the MSDE and SQL Server through the use of stored procedures.
Stored procedures exist as permanently compiled objects in MSDE. Precompiling reduces the overhead required for execution. Stored procedures can accept and return data. Stored procedures can also be used to group complex SQL statements. The contents of stored procedures may be hidden from applications.
Because a stored procedure is a group of Transact-SQL statements, they can contain much more complicated business logic.
Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. When SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied. All of the work is done on the server. This increase in server-side processing can increase performance.
SQL Server stored procedure functionality exactly matches that of MSDE. SQL Server and MSDE stored procedures are fully compatible.
There are defined standards for SQL statements, language, and methods. These standards allow interaction between different database systems. The following section describes how each technology complies with SQL standards.
Jet 4.0 databases are capable of processing ANSI SQL statements, but Jet is not fully compatible with Transact SQL standards. The differences in Transact SQL compliance prevents some Jet objects from directly converting into SQL Server objects.
MSDE is fully compliant with ANSI SQL and Transact SQL guidelines. MSDE is fully compatible with SQL Server 7.0.
SQL Server is fully compliant with ANSI SQL and Transact-SQL guidelines.
Tables Data Types and Default Values
Data types are the primary tools for describing data in a database. The following section discusses the difference in how each technology defines its data.
Jet supports property values for columns that are not handled directly in SQL Server and MSDE. These properties include format, captions, and zero length fields.
Some of these features of Jet can be enforced in SQL Server and MSDE through the use of triggers.
- Identifiers can be up to 64 characters.
- Identifiers can contain key words.
- Identifiers can start with any character.
Instead of zero-length strings, MSDE supports null values. Not equal to zero, null represents a lack of value. Null values usually indicate data that is unknown, not applicable, or to be added at a later time. The nullability of a column determines whether the rows in the table can contain a null value for that column. If null values are not allowed, the user entering data in the table must enter a value in the column or the table row cannot be accepted into the database.
MSDE is fully compatible with Access identifiers.
Like MSDE above, SQL Server supports the concept of null values. SQL Server 7.0 is also fully compatible with Access identifiers.
Table Relationships and Referential Integrity
Defining relationships between data is an important part of maintaining the integrity of the information in a database.
Engine level cascading updates and deletes provide a method for keeping the relationships between data in different tables aligned. MSDE and SQL Server do not support cascading updates and deletes (this feature was added to MSDE 2000).
Declarative Referential Integrity (DRI) and Triggers can be used to enforce table relationships.
Declarative Referential Integrity (DRI) and Triggers can be used to enforce table relationships.
MSDE provides an excellent option for developers who need to build desktop or shared database solutions today but who anticipate future increases in system requirements. MSDE provides an inexpensive method for building client server database applications with the highest level of scalability providing an easy migration path to full SQL Server on the same code base. Cost, performance, and the highest level of compatibility with SQL Server makes MSDE an excellent choice for mobile solutions.
This paper identifies the factors involved in selecting a database engine and converting existing Jet-based applications to MSDE. But developers face other concerns as well, such as how to distribute MSDE with custom applications, and how to create MSDE tables through Visual Basic. These and other technical questions are addressed in the MSDE for Visual Studio 6.0 Technical Frequently Asked Questions (FAQ).
This paper does not explain how to build data-centric applications or how to administer an MSDE database. For more detailed information on building custom client-server database applications, see:
- MSDE for Visual Studio 6.0 Technical Frequently Asked Questions(FAQ)
- The MSDN Online Library for detailed information on Access, SQL Server, and all of Microsoft's development tools
- The Microsoft Universal Data Access Web Site for information on ADO, OLE_DB, DAO, ODBC and other Microsoft data access technologies
- Microsoft SQL Server Web Site
- Microsoft Access newsgroups
- SQL Server newsgroups
James Fitzgerald is a consultant with Clarity Consulting, Inc. Clarity is a Chicago-based consulting firm that specializes in the design and development of client/server information systems. James has been designing and building custom enterprise client-server database solutions for Fortune 500 clients for the past 5 years. He can be reached at firstname.lastname@example.org or on the Web at Clarity Consulting.