MSDE Primer for Access 2000

Microsoft Database Engine (MSDE) doesn’t install with the basic setup for Office 2000 and Access 2000. However, you can install MSDE from Office 2000, CD 1. Run setupsql.exe from the \sql\x86\setup folder. Note that you can’t run SQL Server and MSDE on the same computer. MSDE operates as a database server for a local workgroup in a peer-to-peer network or on a Windows NT computer. On Windows NT computers, MSDE supports either Windows NT integrated security or SQL Server security. Applications for MSDE on a Windows 9x computer exclusively support SQL Server security.

If you install MSDE on a Windows 9x computer and attempt to link to it from a Windows NT computer, your users might experience a network failure. One cause of this failure is that the NT computer attempts to link to the server from Named Pipes, whereas the MSDE installation procedure for a Windows 9x computer installs multiprotocol and TCP/IP settings. To fix this problem, use the Client Network Utility on the NT computer to revise network client settings to match those on the Windows 9x computer running MSDE.

A valid reason to install MSDE even when you use Access projects exclusively with SQL Servers is to fix a known problem in the operation of the Tools, Security, Database Security command. This command enables login and user ID creation and permission and role assignments from a GUI that mimics Enterprise Manager’s Action, New Login command. Unfortunately, MSDE won’t operate from Access 2000 unless the workstation that MSDE is running on has MSDE installed on it. Therefore, to set security, even for SQL Server databases from Access projects, you need to install MSDE.

Although MSDE has a database technology similar to SQL Server 7.0, it has several distinct limitations. First, Microsoft optimized MSDE for a handful of users. Microsoft asserts that performance starts to degrade after five users. You’ll want to check your MSDE applications for performance in real situations to assess whether any performance degradation is within acceptable limits for your needs. Second, the maximum size of an MSDE database is 2GB, instead of the essentially unlimited size of a SQL Server database. Third, SQL Server has many features that Access doesn’t have. For example, a MSDE database can’t serve as a replication publisher for transactional replication. And, MSDE can’t act as an OLAP or English Query server.