FIGURE 1: In Access 2000, a database container for an Access Project.
This is the first installment in a four-part series on Access 2000 and Microsoft SQL Server. Access 2000 introduces a mix of innovations and updated features for working with Microsoft SQL Server (hereafter often referred to as SQL Server) databases. This article starts with a brief overview of the new SQL Server interoperability functionality, then discusses two of the new features in some detail.
The first innovation we'll discuss is the new .adp file type (adp stands for Access Data Project). Microsoft refers to applications developed with .adp files as Access Projects. This new file type hooks directly to a SQL Server database through a single OLE DB connection. It offers performance and other advantages over prior methods of linking to SQL Server databases. If you're working with SQL Server databases, there are significant incentives for working with this new file type instead of the traditional .mdb file.
The second innovation is the Microsoft Data Engine (MSDE). This new database engine offers an alternative to Jet. MSDE permits local data storage in SQL Server database format. MSDE databases can serve as the back end - just as SQL Server databases - for solutions in .adp file types. Unlike SQL Server, however, MSDE is tuned to best service the needs of small workgroups of five or fewer users.
By developing with .adp files and MSDE, Access database developers can smooth the transition of their solutions from those serving local, departmental needs, to those addressing the needs of entire enterprises with thousands of users. The new data engine and file type empowers Access developers to work on mission-critical solutions with graphical development tools similar to those used with .mdb files. SQL Server databases enjoy a higher status than .mdb files in many organizations. With Access 2000, developers can build Access solutions for the enterprise the same way earlier versions of Access supported departmental needs.
From Desktop to Server: An Overview
This overview of Access 2000/Microsoft SQL Server will focus on three aspects of their interoperability:
Five remain with the .adp file:
The Access Project coordinates with its server via an OLE DB connection. Two pieces - the database file (typically an .mdf file) and the .adp file - compose the client/server solution.
A significant part of the power of Access Projects is that they provide SQL Server interoperability through a relatively familiar user interface. FIGURE 1 shows the .adp file for the Pubs database. This database ships with Microsoft SQL Server, much as Northwind ships with Access. Notice that the database container for an .adp file mirrors the one for an .mdb file. The Outlook-style bar presents its collections. Double-clicking on any collection opens the members of that collection in the database window. The Tables objects appear in FIGURE 1 with a Small Icons representation. Access users can choose from this representation, and any of three other layouts, just as with .mdb files.
FIGURE 1: In Access 2000, a database container for an Access Project.
Notice there is no collection of Queries objects in an Access Project. Views and stored procedures perform some of the functions that queries perform in .mdb files. While there is a graphical user interface for designing views, they have more limited functionality than queries. For example, you cannot sort the records representing a view because they do not support the ORDER BY clause. In addition, views do not support bulk actions, such as deleting, appending, and updating records. Stored procedures enable both of these capabilities, but they don't have a graphical user interface. You must learn how to write SQL code to create stored procedures, and mix it with your ADO code to run it programmatically.
One especially important feature of Access Projects is the File | Connection command. This opens a Data Link Properties dialog box (see FIGURE 2). Use this dialog box to configure the .adp file connection to a SQL Server database. It shows a connection to the Pubs database on the SQL Server named cab2200. The connection uses the generic "sa" user ID with a blank password. Although you might want to use this user ID and password when you're developing a solution, you'll likely use a different user ID and password for any solution you deploy.
FIGURE 2: A Data Link Properties dialog box for an Access Project.
The Microsoft Data Engine (MSDE). MSDE is a strategic data access technology for Access 2000 developers. It enables them to build solutions on single-processor personal computers running Windows 9x operating systems that scale to multi-processor clusters running the most advanced Windows operating systems. These advanced operating systems include Windows NT Server Enterprise Edition, and higher-end Windows 2000 operating systems. MSDE solutions are completely compatible with those developed for Microsoft SQL Server 7.0. You can prototype a solution with MSDE and .adp files on your local computer. Later, transfer the database tables, views, and stored procedures from MSDE to SQL Server for testing, refinement, and rollout. Distribute the original .adp files to testers and users with their data link changed to point at the SQL Server database instead of your prototype MSDE-based system.
Developing applications in this style gives Access developers a chance to gain familiarity with building solutions in a style suitable for serving enterprise - as opposed to departmental - needs. MSDE, along with .adp files, offers a means to efficiently serve many more users than ever before. Because MSDE is not Jet, and .adp files interact with database engines differently than .mdb files, you must ascend a learning curve. This article and the remaining three in the series aim to flatten the curve for you. You'll gain exposure to core concepts for working with MSDE databases, such as installation, basic management, and disaster recovery.
MSDE doesn't install routinely with the rest of Office or Access; you must run SetupSQL.exe from your Office 2000 CDs. This will install a new MSDE item off the Start button menu. In addition, you'll gain an icon on your status bar that opens the SQL Server Service Manager dialog box. Use either the dialog box or the menu item to set the behavior of MSDE. For example, you can start, stop, pause, and set MSDE to start automatically. The menu item adds the ability to fine-tune the network connectivity your server extends to other workstations on a LAN. You can also use the menu item to invoke the Data Transformation Services Wizard. This powerful wizard helps with transferring data in other formats in to, and out, of MSDE.
The redistributable MSDE enables developers to build stand-alone applications for MSDE on a royalty-free basis. Using a reference to the SQL Distributed Management Objects (DMO) library, you can programmatically start and attach databases to MSDE (or SQL Server). This is particularly appropriate when you're using the redistributable MSDE with no user interface. FIGURE 3 shows a program listing that copies an .mdf file from a remote SQL Server (cab2200) to a local MSDE on a Windows 9x computer.
ConnectData()
On Error GoTo
Trapper
Dim strMsg As String
Dim FSO As
Scripting.FileSystemObject
Dim oSvr As SQLDMO.SQLServer
Set
FSO = CreateObject("Scripting.FileSystemObject")
Set
oSvr = CreateObject("SQLDMO.SQLServer")
' Log onto database.
oSvr.Connect
"(local)", "sa", ""
' Copy File to data folder; remote server must be closed
' for copyfile method to succeed.
FSO.copyfile
"\\cab2200\c\mssql7\data\pubs.mdf", _
"c:\mssql7\data\pubs.mdf", True
' Attach to database.
strmessage =
oSvr.AttachDBWithSingleFile("LocalPubs2", _
"c:\mssql7\data\pubs.mdf")
' Display the success or failure message.
MsgBox strmessage
MyExit:
oSvr.Disconnect
Set
oSvr = Nothing
Exit Sub
Trapper:
Debug.Print Err.Number, Err.Description
Resume
MyExit
End Sub
FIGURE 3: This sample code demonstrates how to load a database locally from a remote SQL Server.
The remote server must be closed, and the local workstation must have a reference to the Microsoft SQL-DMO Object Library (see FIGURE 4). The name of the computer on the remote server is Pubs, but on MSDE, the listing gives the database a new name: LocalPubs2.
FIGURE 4: The References dialog box selecting the Microsoft SQL-DMO Object
Library.
SQL-DMO is a powerful object library for programmatically manipulating SQL Server and MSDE. For example, you can programmatically log on to a server with DMO while specifying a particular user ID and password. The .adp file for this article includes a couple of sample procedures that demonstrate this task (they're available for download; see the end of this article for details).
The sample in FIGURE 3 fails if the Pubs database on the remote database server has Pubs attached. The SQL-DMO DetachDB method for the SQL Server object can make a database invisible to a SQL Server installation. The method needs to run on the server with the database that needs to be made invisible. You can learn more about SQL-DMO objects, properties, methods, and events from the Object Browser. Reference the SQL-DMO library and select SQL-DMO from the Project/Library drop-down edit box.
Using ADO to work with SQL Server data sources. ADO programming skills for Jet databases transfer directly to SQL Server and MSDE databases with a few simple extensions. These pertain primarily to Connection objects. Principally, you need a Provider in the connection string that's appropriate for SQL Server databases. In addition, your connection must include a user ID, and if the ID has a password, the connection string must include that as well. Finally, you must specify a server name and a database name.
Because SQL Server processing sometimes dictates the need to work with remote data sources, DSNs (data source names) can encapsulate elements of the connection string. While this can simplify the syntax for the connection string, it requires the manual creation of a DSN on the workstation running the connection string. Specifying the individual connection string parameters results in a connection that is independent of the DSN on an individual workstation.
FIGURE 5 presents two pairs of approaches to specifying a connection string for ADO with SQL Server databases. The first pair illustrates approaches that rely on DSNs. The first sample creates a connection to an MSDE database on the local computer, which just happens to be a Windows 9x computer in the test environment. Opening the connection merely involves invoking the Open method, specifying a DSN name, and designating a user ID. The DSN has the same name as the database to which it points. (The listing in FIGURE 3 attaches the database to the local MSDE.) The second sample uses a DSN name that points at a remote SQL Server database on a remote computer running Windows NT.
Sub SQLConnects()
Dim cnn1 As New ADODB.Connection
Dim strCnn As String
' This sample illustrates four ways of connecting to SQL
' data sources -- two with DSNs and two without.
' Connect to local MSDE based on DSN named LocalPubs2.
cnn1.Open
"LocalPubs2", "sa"
cnn1.Close
Set cnn1 = Nothing
' Connect to remote SQL Server based on DSN named Pubs.
cnn1.Open
"Pubs", "sa"
cnn1.Close
Set cnn1 = Nothing
' Connect to local MSDE based on DSN-less connection.
strCnn =
"Provider=sqloledb;" & _
"Data
Source=(local);" & _
"Initial Catalog=LocalPubs2;" & _
"User Id=sa;Password=;"
cnn1.Open strCnn
cnn1.Close
Set cnn1 = Nothing
' Connect to remote SQL Server based on
' DSN-less connection.
strCnn =
"Provider=sqloledb;" & _
"Data
Source=cab2200;" & _
"Initial Catalog=Pubs;" & _
"User Id=sa;Password=;"
cnn1.Open strCnn
cnn1.Close
Set cnn1 = Nothing
End Sub
FIGURE 5: Four ways to connect with SQL Server databases.
The third sample in FIGURE 5 specifies individual connection string parameters without relying on a DSN. As you can see, this complicates the connection string, but it relieves the application of depending on a DSN on the individual workstation. The SQL Server OLE DB provider is "sqloledb," which is installed with Office 2000.
The Data Source parameter must be a path to the server. When you're running against a local server, you can simply enter (local). This feature is particularly handy when working with MSDE databases, because there can be so many servers with different names. For example, you can have a separate name for MSDE on each workstation. In fact, when you use local) as the server name, it doesn't matter whether the local data source is MSDE or SQL Server.
The Catalog parameter points at the name of the database to which you want to connect. The designation of a user ID is mandatory, but a password is optional. For example, when you're developing an application with "sa," you need not specify it. When you deploy a solution, you're likely to use different roles for assigning permissions to users. Deployed solutions will typically require a password for the database connection. The third sample doesn't include one, but the fourth does. Because both samples use "sa" as the user ID, it's optional in both cases.
The final connection sample connects to a remote database. Notice the similarity in syntax using ADO to connect to a local, versus a remote, data source. The only significant differences between the third and fourth samples are the Data Source and Catalog parameters. When using a remote data source, you must always designate a specific server name instead of simply (local). The Catalog parameter must reference a particular database name on the remote server.
The code in FIGURE 6 shows how to use a SQL Server database connection, no matter how you construct it. A common reason for creating a connection is to open a recordset and modify its values. The sample in FIGURE 6 shows how to do this for an MSDE data source on the local computer (in this case the NorthwindCS database). This is the .mdf file version for the NorthwindCS database. It's a SQL Server database model for the traditional .mdb version that has long been an Access sample database. The sample opens a recordset based on the Employees table in NorthwindCS. Then, it prints the name and extension for the first employee, updates and prints the extension, and restores and prints the extension. The recordset code is the same code you would use with a .mdb file, so your existing ADO skills for Jet databases transfer directly to working with SQL Server databases.
Sub SQLRecords()
Dim cnn1 As New ADODB.Connection
Dim strCnn As String
Dim tempExt As String
Dim rst1 As New ADODB.Recordset
' Connect to local MSDE based on DSN-less connection.
strCnn =
"Provider=sqloledb;" & _
"Data
Source=(local);" & _
"Initial
Catalog=NorthwindCS;" & _
"User Id=sa;Password=;"
cnn1.Open strCnn
' Create a recordset based on the connection
' and print telephone extension for first record.
rst1.Open "SELECT *
FROM Employees", cnn1, _
adOpenKeyset,
adLockOptimistic
Debug.Print
rst1.Fields("FirstName") & " " & _
rst1.Fields("LastName") & " has the extension "
& _
rst1.Fields("Extension") & " before editing."
' Save old extension and enter new extension (9999)
' and print telephone extension for first record.
tempExt =
rst1.Fields("Extension")
rst1.Fields("Extension") = "9999"
rst1.Update
Debug.Print
rst1.Fields("FirstName") & " " & _
rst1.Fields("LastName") & " has the extension "
& _
rst1.Fields("Extension") & " after editing."
' Restore old extension and print telephone extension
' for first record.
rst1.Fields("Extension") = tempExt
rst1.Update
Debug.Print
rst1.Fields("FirstName") & " " & _
rst1.Fields("LastName") & " has the extension "
& _
rst1.Fields("Extension") & " after restoring."
End Sub
FIGURE 6: Updating and restoring a recordset based on an MSDE database. Note the similarity to working with Jet databases.
MSDE in Detail
Next, we'll consider two aspects of MSDE. First, I compare MSDE to Jet. Access developers have a choice of database engines. This section describes important issues in deciding which one to use. The second section explores issues pertaining to the installation and configuration of MSDE.
Comparing MSDE and Jet. Jet-based Access solutions are familiar, easier to manage, more compatible with solutions built from prior Access versions, and consume fewer resources. MSDE-based Access solutions require you to gain some familiarity with new data types, different rules for developing queries, and database administration techniques. In return, MSDE offers superior data recovery, built-in transaction logging, integrated Windows NT security, and the potential for huge database capacity. Developers can use the Access user interface to build forms and reports for solutions with either database engine. Because of the inherent client/server design of MSDE solutions, there are some unique rules for MSDE about how to use the user interface. You can also program solutions with VBA and ADO for either database engine.
Both MSDE and Jet support declarative referential integrity to let you graphically declare relations between tables. However, Jet uniquely supports cascading updates and deletes at the engine level. MSDE enables these features as well, but developers must implement them with triggers written in SQL. Open a trigger template for a table by right-clicking the table in the Database window and selecting Triggers. Click New to open the template. The Books Online component that ships with SQL Server 7.0 includes syntax rules, many samples, and general background on developing triggers. (It's also a good general resource for all the SQL Server topics this article addresses.)
Invoke the Tools | Database Utilities | Restore command in an Access Project when your needs can be satisfied by a full restore from a backup file. This is similar to restoring backup copies of Jet database files. MSDE solutions uniquely support recovery to a specific transaction from a log file. You'll need to program this type of restore with Transact-SQL (the Microsoft SQL Server dialect of SQL). Access 2000 online Help documents this language, including sample scripts for recovering data in a table to a specific point in time corresponding to a transaction from its log file.
If you find yourself managing a collection of tasks that require Transact-SQL, you may prefer to obtain a copy of the full SQL Server version 7.0 (instead of the free MSDE package with the same core database engine). The new SQL Server Enterprise Manager provides a large collection of wizards and a graphical user interface feature that eliminates the need for Transact-SQL code for tasks such as backup and restoration.
Jet database files are typically smaller than comparable MSDE-based solutions. The Northwind solution in a .mdb file ships at about 2MB. This includes all the tables, other database objects, and code. MSDE-based version of the Northwind sample solution takes a database file (.mdf) and an Access Project file (.adp). These files total about 3.7MB. MSDE solutions automatically maintain log files for transaction processing.
Jet databases must pass their databases across a network in multi-user solutions. MSDE-based solutions perform all database processing at the server, and pass their return sets across a network. This can reduce network traffic and speed performance. Both Jet and MSDE permit databases as large as 2GB, but MSDE solutions can migrate easily to SQL Server 7.0 for support of databases in excess of 1,000,000 terabyte. SQL Server 7.0 scales up its performance as you run on computers with more processors, but Jet doesn't. MSDE, unlike its big sibling, SQL Server 7.0, is tuned to work with small workgroups. Performance begins to degrade significantly after more than about five concurrent users.
Installing and configuring MSDE. MSDE will be an optional database engine for Access developers and users. Recall that you can install MSDE by running SetupSQL.exe from your Office 2000 CDs. No special operating system preparation is necessary for installation on a Windows 9x operating system. Users of Windows NT 4 systems must perform two steps to prepare their systems for MSDE installation. First, they must install Service Pack 3. Second, they must run hotfix.exe from the Office 2000 installation CDs. NT 4 users running Service Pack 4 have no special setup requirements.
After installing MSDE, you must start the engine before you can use it. For Windows 9x computers, you can perform this with the following three steps:
Depending on your degree of use for MSDE, you may optionally select the check box for Auto-start service when OS starts. If you don't select this option, you must repeat the preceding steps whenever you use MSDE.
Windows NT users can launch MSDE just as any other NT service. Open Control Panel, and double-click the Services icon. Then, highlight MSDE Service, and click the Start button to launch the service. Click Startup to designate a Startup Type (Automatic, Manual, or Disabled).
After installation and startup, an icon appears representing SQL Server Service Manager on your Windows task bar. You can use this icon to start, pause, or stop MSDE. The icon also permits you to open Service Manager to perform the same functions for the Microsoft Distributed Transaction Coordinator, and Microsoft SQL Server Agent.
Before opening a file for a client/server database, it's normal to require a user ID and password. MSDE permits you to manage login security with individual and group accounts for specific databases, or with integrated, pass-through security from Windows NT. The MSDE defaults are "sa" for user ID, and blank for the password. You can use the Tools | Security | Database security command to establish security accounts for individual databases. Windows 9x operating system users can only use MSDE security accounts for individual databases. Access developers will recognize MSDE database roles as comparable to setting up groups. With roles, an individual can belong to multiple groups. Individuals inherit the most permissive security of any role or group to which they belong.
When you run MSDE from a Windows 9x operating system, it does not support Named Pipes, but rather TCP/IP and Multi-Protocol. However, most clients attempt to connect to an MSDE server computer via Named Pipes by default. Invoke the MSDE Client Network Utility command on the MSDE menu for a client computer to establish an alias for the MSDE server that connects via either TCP/IP or Multi-Protocol.
Access Projects in Detail
We'll examine Access Projects from three perspectives. First, we'll describe procedures and issues related to linking them to SQL Server and MSDE databases. Second, we'll discuss how to recover MSDE databases. Third, we'll illustrate how you can learn about Access Projects more quickly with the NorthwindCS and Pubs databases.
Linking Access Projects to databases. When you open Access 2000, you can choose to open an existing file. If the file is an .adp file with a connection to a database, it opens with a database window. The first time you try to display a table, view, stored procedure, or database diagram, a dialog box opens, prompting you to log on to the database. You must specify a valid user ID and password to gain entry. If you click Cancel in the Logon dialog box, you will disconnect the active Access Project from its database. This gives you the opportunity to choose File | Connect so you can link the project to any SQL Server or MSDE database to which you can connect. Use the Data Link Properties dialog box (again, see FIGURE 2) to accomplish this.
When opening Access, you can choose Access database wizards, pages, and projects. Choose Project (New Database), OK, and Create to launch the process of making a new Access Project that has its own database. This opens the Microsoft SQL Server Database Wizard dialog box (see FIGURE 7). You can specify any SQL Server or MSDE service to which you can connect. The wizard offers a default name for the database, but you can override this. Because the new database creation task requires a network connection, you cannot create a new Access Project on a stand-alone computer. You can, however, open and modify an existing Access Project with a link to your local SQL Server service without a network connection.
FIGURE 7: The Microsoft SQL Server Database Wizard can automatically create a
new database for an Access Project.
When opening Access 2000, you can also choose Project (Existing Database). This creates a blank Access Project that you can link to any previously existing SQL Server or MSDE database through the Data Link Properties dialog box.
As you plan your Access Project applications, keep in mind that they link to only three types of databases:
Recovering your SQL Server databases. In spite of the fact that Microsoft SQL Server is an industrial-strength database, it's possible to damage its system to the extent that you lose the ability to work with your data. For example, a media failure can corrupt the master database file. With sufficiently severe damage, you may not be able to start SQL Server. In this situation, you'll have to uninstall SQL Server or MSDE. You can use the Uninstall command from the Start | MSDE menu, or the Control Panel to remove the faulty installation. This removes all system databases, such as the master, model, msdb, and tempdb databases, but leaves any user databases, such as NorthwindCS. You may need to register user databases with your new system after reinstalling SQL Server.
Keeping a current copy of the master database is highly desirable because it can simplify connecting a fresh installation of SQL Server with your previously existing databases. Back up your master database after creating or deleting new user databases or login accounts. Adding a new user to a database doesn't modify the master because security information, such as user accounts, goes into the database file.
If you don't have a current master database, you'll need to attach your old user databases to the new master. There are at least two approaches to this task. In either case, you'll need to recognize that MSDE and SQL Server 7.0 store their databases in the \Data folder of the \MSSQL7 directory. SQL Server 6.5 stores its databases in the \Data folder of the \MSSQL directory.
The first way to register your old user databases with the new master database relies on SQL-DMO programming, such as that illustrated in FIGURE 3. In particular, notice the syntax for the AttachDBWithSingleFile method. If a database spans more than one file, apply the AttachDB method instead.
The second approach to registering your old databases with a new SQL Server or MSDE service relies on the user interface. Rename your old user databases. Next, create new Access Projects with the Microsoft SQL Server Database Wizard that link to databases with the names of your original databases. Finally, copy the renamed old user database files over the newly created ones. This process updates your master so it knows about the user database files from a prior installation.
Learning from the NorthwindCS and Pubs databases. Access 2000 ships with file server and client/server versions of the Northwind database sample application. The client/server version sets up as an Access Project. The Pubs database doesn't ship with Access 2000, but it has long been the standard SQL Server sample. If you use Access Projects with a SQL Server database manager, this sample will almost surely be available to you. You can open it in an Access Project. Many ADO and other database samples in the Access 2000 online Help files specifically reference this database, so you can enhance your grasp of new database features in Access 2000 by becoming familiar with Pubs.
The first time you run NorthwindCS.adp, a script detects if you have MSDE on your computer. If MSDE is installed, the script asks if you want to load the NorthwindCS database. A positive reply causes the script to load the NorthwindCS database and connect the NorthwindCS Project to it. Otherwise, you can attach the NorthwindCS Project to the Northwind database on a SQL Server database manager.
The NorthwindCS Project inserts a special Show Me item on its menu bar. Choose the item to open a dialog box (see FIGURE 8) that helps you drill down into the Access Project demonstration. See this opening dialog box and the subsequent ones from which you can link to learn about special Access Project features and SQL Server data types. While there are some general correspondences between Jet and SQL Server data types, there are also significant differences, e.g. the SQL Server timestamp data type. SQL Server can also represent currency and data/time values differently than Jet. In addition, SQL Server has explicit Unicode and non-Unicode data types. Unicode data subscribes to an international standard for representing character sets appropriate to a wide range of languages.
FIGURE 8: The NorthwindCS Project and its Show Me dialog box are powerful tools
for moving along the Access Project learning curve.
The Show Me dialog box is a great place to start learning about the differences between .adp and .mdb files. If the Show Me dialog boxes fail to address your needs completely, move on to the Access online Help. Open the Office Assistant and ask for help on Work with a Microsoft Access Project. Additional online help for Access Projects is available by opening an Access Project Table in Design view and pressing [F1]. Close the Microsoft Access Help window before pressing [F1] to enter another part of the help system with a lead heading of "Working with Access Project Components." This area of the online help system also includes detailed sections on Transact-SQL and SQL Server Error messages. Finally, consider using the SQL Server Books Online if that resource is available to you (recall that you need a computer with the full version of SQL Server 7.0).
To work with Pubs from an Access Project, you need an Access Project that points to Pubs. If a disconnected .adp file isn't already available, you can create one by creating a new Access Project, then dropping its database (use the Tools | Database Utilities | Drop SQL Database command on the database window for this). Then, open the Data Link Properties dialog box and point the disconnected Access Project at the Pubs database.
After you create your Access Project linking to the Pubs database, you can mine the database for interesting samples of SQL Server technology that will allow you to refine your own tools. FIGURE 9 shows the four stored procedures in Pubs with the byroyalty procedure open. This sample returns the author IDs for any authors with a royalty percentage agreement that matches the input to the parameter query. The Mybyroyalty stored procedure is an extension of byroyalty that returns the author's first name, last name, and phone number matching a royalty percentage.
FIGURE 9: A Pubs stored procedure, byroyalty,
that served as a model for the custom one Mybyroyalty
stored procedure.
Conclusion
This article introduces Access 2000 features for working with Microsoft SQL Server databases. These new capabilities are important to Access developers because they open the way to enterprise and mission-critical assignments with many of the familiar graphical interface tools they already know. Of course, there are many new programmatic features as well. These offer Access developers the opportunity to extend their ADO and VBA programming skills.
MSDE is another important new feature. It gives Access developers the opportunity to work on SQL Server databases from a desktop computer. Furthermore, MSDE ships free with Office 2000 (Standard Edition and above). In addition, there is a redistributable MSDE (available exclusively with the Microsoft Office Developer Edition) that enables Access developers to ship royalty-free custom applications, just as they've always been able to do with Access Run Time.
Jet and MSDE each has its place for Access developers. MSDE is the ideal tool if you envision using Microsoft SQL Server for future versions of your application. When MSDE runs on Windows NT systems, it provides integrated operating system and database security. This can reduce database security administration burden. MSDE offers point-in-time recovery, i.e. transaction processing. Jet is more appropriate for non-mission critical applications where ease of development is an overriding consideration. If memory or disk constraints are issues, Jet solutions have smaller footprints. Because only a marginally updated Jet engine ships with Access 2000, it offers the highest compatibility with Access 97 and earlier versions.
Rick Dobson, Ph.D., is an author/trainer/developer. His computer practice has been in full-time operation since 1991, and he's provided Access training on three continents. Microsoft Press contracted with him to write its Microsoft Access 2000 Developer's Handbook. You can reach his consulting practice at http://www.cabinc.net.