Access doesn't come just with Jet anymore -- now you get your very own copy of SQL Server. SQL Server developer James Beidleman takes a look at what you get with Access 2000.
Lately there have been many changes for us all. Visual Studio 6, Office 2000, and SQL Server 7 are all knocking down our doors with exciting new versions. All of these changes make our lives more interesting. But I'm here to talk about using Access with the Microsoft Data Engine (MSDE) and look at how the MSDE affects client-server development with Access. In addition, throughout this article, I'll be looking at how this new data engine compares to or differs from the full SQL Server product.
MSDE is just one of the names used to refer to this technology. As of the Beta 1 release of Office 2000, the MSDE is referred to as the Integrated Store. For a while, the approved abbreviation was MDE. If you're looking from the SQL Server side of things, the MSDE is a scaled-down version of SQL Server for single users and is called the Desktop version of SQL Server. From a historical point of view, it seems to be the embedded version of SQL Server 7 that the Access developers have incorporated into 2000 and are now referring to as the MSDE. By the time you read this, it will probably be called something else that has a little more pizzazz. Whatever it's called, the MSDE that ships with Microsoft Access runs on Windows 95, Windows 98, or Windows NT 4.0 and is intended for use on an individual's desktop computer.
The Office 9 Beta CD contains the MSDE, as will the released product. The best way to find out about the MSDE is to install it. Following Microsoft's instructions, I installed the MSDE from the Office 9 CD by running \sql\x86\setup\setupsql.exe. What appeared at this point, in the beta, was the SQL Server Beta splash screen (this screen will, I assume, be replaced in the final release with one that displays whatever the final name of the product is). From there, it's just a matter of following the dialog boxes to install this scaled-down version of SQL Server (see the sidebar, "Installing the MSDE," for more information on the installation process).
After I had installed the MSDE, I restarted Windows 98. Normally I use Windows NT, but since I had already installed SQL Server 7 under NT, I thought it would be best to take advantage of my dual boot capability and install the MSDE under Windows 98. I did install the MSDE to a different directory than SQL Server. Installing into the same directory as SQL Server might have caused some problems with the master database that manages all of the SQL Server application databases. However, when I get braver (and get some full backups of every database), I intend to try installing both versions into the same directory.
Before you can use the MSDE from Access, you need to create an Access Project. To do this, after starting Access, select "Access database wizards, pages, and projects," click on the second option button, and then move to the General tab. On that tab you select "Blank Project (New Database)" and -- bingo! -- you've started to create a new SQL Server database. Your other choice is "Blank Project (Existing Database)" to connect to an existing SQL Server database on any SQL Server. The Existing Database choice lets you connect to any existing database (SQL Server or otherwise) using OLE DB or ODBC. Unlike Jet, where "project" and "database" were synonymous, you can have many separate projects in a single SQL Server database.
The next step is to provide the project with a filename and select where the file will reside. This file is where the Access-specific objects (forms, reports, code modules, and so forth) will reside. Unlike a Jet MDB file, the Access component of a SQL Server database holds everything but the data -- SQL Server holds only the data. This "Access Project" file has the extension .ADP.
As I did this, I got my first taste of Office's incremental install capability. On my first go-around, Rocky (the default Office Assistant) barked and informed me "something" was missing. After I hit "Yes," Rocky went on to install whatever was missing. I assume it was the wizard that was missing, because that was what then popped up, beginning with a dialog box titled Microsoft SQL Server Database Wizard (see Figure 1). This dialog box required that I provide a server name, which, in this case, was the name of the computer that I was running on. Since I had just installed MSDE and there's no easy way to create login ids, I logged in with the pre-installed user "sa" (system administrator) with no password.
Everything worked as expected, which made me a bit nervous. I'd like to be able to create logins; I don't like using the administrator account when I don't need to. With the full-blown version of SQL Server, I would have used Enterprise Manager to create login ids before opening my database.
At this point in the process, you need to name the SQL Server database (unless, of course, you're adding your project to an existing database). In addition to being the name of the database, this name will be used to name the files that make up your database on your hard disk.
The weird thing, which I hope is fixed with the release, is that there was no way to tell SQL Server where to put those database files. SQL Server normally would default to placing the database files in the SQL Server directory (\MSSQL\Data). In this case (and in all my other attempts), the database and transaction log files both ended up in the root of C drive.
But stop for a minute and look at what I've just accomplished. With ODBC, Access has always been able to act as a client to a database server. The problem was that Jet, while a great ISAM database engine, really just got in the way of doing client-server development. After all, the database server you access in a client-server application was optimizing and processing the queries, so what was Jet adding? With the MSDE, you now have the first step to using Access purely as a front-end client development tool. There were always many options for Access's data store, but now there are two native options: Jet or the MSDE (other options include the full version of SQL Server or any other ODBC/OLE DB data store).
To access your MSDE-based application, you open the ADP file that you created with the wizard. The ADP project contains only the Access forms, reports, and code modules. The project's tables, stored procedures, views, and database diagrams, while they appear in the database window, are actually stored in the SQL Server database.
Creating tables is very similar to creating tables in the SQL Server's Enterprise Manager -- in fact, the table properties dialog box is identical. The table properties dialog box is where indexes, constraints, and relationships can be created or modified. The data types to choose from when defining fields are all from SQL Server and are slightly different from the Jet data types. I should note that Jet supports some new data types, which match the ones in SQL Server and other ODBC data sources. This will be a big help in upgrading from native Jet to SQL Server.
Access also now has the "DaVinci" tools built in. The DaVinci tools provide a consistent user interface for manipulating SQL Server objects across multiple development tools. The DaVinci tools are used in the Visual Database Tools from Visual Studio, SQL Server's own Enterprise Manager, and are built into Visual Basic 6. Using the DaVinci tools, you can create tables and then base forms on them using either design view or the form wizard. I used the tools to create a few simple tables called Company, Contacts, and Address. Here are those tables' schemas, shown as a series of Transact SQL commands:
CREATE TABLE Company (
CompanyID int NOT NULL IDENTITY (1,1),
ShortName char (4) NOT NULL,
CompanyName varchar (50) NULL)
CREATE TABLE Contacts (
ContactID int NOT NULL IDENTITY (1,1),
FirstName varchar (20) NULL,
LastName varchar (25) NULL,
Title varchar (50) NULL,
Company varchar (50) NULL,
Phone varchar (15) NULL,
Fax varchar (15) NULL,
MailDrop varchar (40) NULL)
CREATE TABLE Address (
AddressID int NOT NULL IDENTITY (1,1),
CompanyID int NULL,
ContactID int NULL,
Addr1 varchar (80) NOT NULL,
Addr2 varchar (80) NULL,
City varchar (30) NULL,
Region varchar (30) NULL,
Country varchar (30) NULL,
Postal varchar (30) NULL)
The first column of each table is the primary key and -- as we would say in the SQL Server world -- an identity, which is just like an Access auto-number. Unlike Access auto-number fields, which were typed as Long Integer, identities are of the int (integer) data type. As you'd expect, the tables include alternate keys (like ShortName) and foreign keys to join tables in relationships. As a hard-core SQL Server developer, I used the preceding Transact-SQL commands to create my tables. Access developers will probably be more comfortable using the design tool to point and click their way through the creation of tables.
Once you've created several tables for your database application, a diagram will become handy. A diagram is used for a lot more than just seeing a picture of the tables. Like the Access Relationships window, Database Diagrams are used to build the relationships between the tables. The first step in the process is to add a diagram by selecting New Diagram and then adding the appropriate tables to the diagram. Relationships are created by selecting the foreign key column from one table and dragging it to the parent table's primary key. When you save the diagram, the referential constraints specified in the diagram are built. Unlike Access's single Relationship window, you aren't limited to a single diagram.
For my sample database, I created a new diagram and then right-clicked on the diagram to add my three tables. I moved the tables into a pyramid with the Company table in the top position. From the Contacts table, I grabbed the CompanyID column and dropped it on the Company table's CompanyID column. A dialog box popped up to confirm the relationship (if you're following along, be sure the Company table is the parent). If you "misdropped" the column, the dialog box lists the columns in a combo box so you can change them. If you've picked the wrong tables, then you'll need to repeat the drag and drop, always making sure to start with the child table in the relationship. I repeated the process between the Address and Company tables, and completed the trio by joining the Address table's ContactID to the Contact table's ContactID.
Relationships in SQL Server are different than in Access. The first thing you'll notice is the lack of an option to make the relationship cascade on deletes or updates. That's because SQL Server referential constraints only enforce the Restrict Business rule. The Restrict Business constraint will ensure that a parent exists for any new children added or modified. It also prevents a parent from being deleted if the parent has children, which forces you to use a bottom-up process when deleting records. To have cascade effects, you have to write a trigger.
Rather than Roy Rogers' horse, a trigger is a block of code that's invoked when an event occurs. A SQL Server event is an INSERT, UPDATE, or DELETE on a table. Triggers are written to handle one or more of these events for a specific table. For instance, after a DELETE in the Company table occurs, the DELETE trigger on the Company table would be invoked and its code executed. A typical trigger would contain, in T-SQL code, the commands to delete the children for the deleted record. The end result is a "cascaded" delete, but you have to code the operation yourself. You'll use triggers not only for cascades but also for validating data on inserts, as constraints are checked before triggers are fired. Needless to say, creating these triggers will cause you to write a lot of code.
If creating these triggers seems daunting, one alternative (as I discussed in my article, "Using Access to Prototype for SQL Server," in the December 1997 issue of Smart Access) is to create your relationships in a Jet database and upsize to SQL Server. Doing this reduces some of the work, since the Upsizer will write triggers for any existing cascaded constraints. While it might seem odd to you Access developers, I actually prefer to use stored procedures to handle the entire delete process, including both the children and parents. I like using procedures: It puts all of the code in one place. Without procedures, the initial Delete command is in the client code, while the cascade effects are buried in the relationship definition. In my approach, the client calls the stored procedure with any required parameters (typically, the key of the record to be deleted), and the procedure handles the delete of the parents and children. With all that said, Microsoft has promised to give us cascade effects in SQL Server constraints soon -- but it's not in version 7.0. Maybe 7.1.
I'm sure you've created a few queries in your Access development activities. In place of these queries, SQL Server lets you use views. A view, like a QueryDef, is a stored query with the significant difference that the query's definition resides on the server. Also, like a QueryDef, a view doesn't contain any data because the data remains in the underlying tables that the view references. I've used views extensively to support report writers trying to access SQL Server databases. Views also allow for row-level security by adding a WHERE clause to determine which rows to return for the users doing the retrievals.
Views are created in the Design window using an interface similar to creating a query. You add tables to the upper pane of Design view and, in the lower grid, work with the columns, criteria, and sorting values.
Begin by selecting the Views item from the database window and selecting Create a New View. Tables can be dragged into Design View from the Database window. In my sample database, I dragged the Company and Contacts table into the upper pane of the view. Because I had tied the tables together in the Database Diagram, the tables appeared with a line connecting them like the line in the Diagram. In the tables' windows, I checked the CompanyName, FirstName, LastName, and Phone fields, which caused them to poplutate the grid below the tables with those columns. To view the corresponding SQL, you select View|Show Panes|SQL, a process equivalent to selecting the SQL View in the Access's Query Design window. The result is pretty familiar, though:
SELECT Company.CompanyName, Contacts.FirstName,
FROM Company INNER JOIN Contacts
ON Company.CompanyID = Contacts.CompanyID
Selecting Run from the toolbar for a SQL Server View triggers an extra step: You're prompted to save your View before it's executed. The view needs to exist in SQL Server prior to it being used to extract data. Once the view is built, you can use the report wizard to create a report based on the view. In the Database window, the view will appear under the Queries section. Because the query is stored in SQL Server, the view is also accessible from outside of Access.
Return of Trigger
I touched briefly on stored procedures when discussing triggers, and I'm going to return to them now. Stored procedures can do everything a query can do and a lot more. While queries basically perform a single operation, stored procedures can perform many operations by including procedural constructs such as IF, WHILE, functions, and even GOTOs.
In addition to increased power, stored procedures provide many other benefits. Saving a stored procedure causes it to be compiled into an internal form, so, when called from the client, they execute faster. Subsequent executions run even faster. Clients connected to SQL Server can invoke the procedure with just the procedure's name and any required parameters. Since a stored procedure can include multiple queries, the result is fewer trips across the network with less data having to be transferred. That's always good, since client-server application performance tends to run at the speed of the network.
Another benefit is that the client-side developer who uses the procedure doesn't need to know how the procedure does its job. The developer can focus on the client-side processing and ignore the server side. A side effect of this is that stored procedures make it easy to distribute work between the different types of programmers. Access programmers don't need to know Transact-SQL, while Transact-SQL coders don't have to know how to develop with Access.
For the individual programmer, these benefits might not seem all that attractive. However, providing access to Transact-SQL does provide a growth path for both the lone developer and the departmental applications. When developing a database application with the MSDE, that path allows the small database to grow and eventually move to a full-fledged enterprise-wide application -- along with the developer. The process of upsizing is also made easier when you're moving from desktop SQL Server to the enterprise version.
The MSDE will allow Access developers to get started in developing client-server application systems. For the Access developers who have been using Access as a front end to some other database server, things are getting simpler. But, quite frankly, these changes aren't that big a deal. Most Access developers are already using VBA to create what are, effectively, stored procedures, for instance. Developers have also been using passthrough queries to take advantage of special capabilities of back-end databases. The real change is inheriting the ability to create the server components from within Access. Basically, you can now defer your move to SQL Server for a considerable period of time and, when the time comes, transition more easily.
I've mentioned a few things already about the differences between the MSDE and SQL Server. For me, the most notable difference was the absence of SQL Server support tools: Enterprise Manager, Query Analyzer, and all the other graphical tools I'm used to. With Enterprise Manager missing, at least in the beta, I had no way to create users from within Access. On the Tools menu, there's a Security option, and it does lead to Database Security. However, selecting it only brings up an error indicating that the database administration tools failed to start. Hopefully, this means that the option is looking for Enterprise Manager, and that it will be added in the final version of Access 2000.
The MSDE is basically the Desktop version of SQL Server minus most of the tools and some additional features. Many of those features are missing because of the host environment: Windows 95 and 98 don't support asynchronous I/O, integrated security, or symmetric multi-processing, for instance (running under NT can provide these capabilities). On the other hand, MSDE isn't designed to be your complete client-server solution. MSDE is designed to help get you to a client-server solution. Your final solution will include the standard or enterprise version of SQL Server.
Some tools are present. The MSDE does install the Service Manager and is placed in the Startup group. You'll see the icon representing SQL Server with a circle red "X" through it, meaning the SQL Server process isn't running. The Service Manager allows you to start and stop the SQL Server process, SQL Server Agent, and MS DTC (Distributed Transaction Coordinator). These processes (called services on NT) are installed as part of the MSDE. These tools perform the following tasks:
|•||The SQL Server engine does the job of accessing and managing the data store.|
|•||SQL Server Agent provides support for the engine by performing replication, scheduling, and executing jobs, among other tasks.|
|•||The DTC handles distributed transactions between multiple SQL Server or other OLE DB data sources (that is, DTC provides two phase commits for coordinating updates among multiple data stores).|
The SQL Server data directory contains the master, model, msdb, and tempdb files just as would be found with a full implementation of SQL Server. Again, some items are missing: You can't have any of the services start on Windows 9x, as you can with NT.
Using the MSDE
So what can the Data Engine can do for you? In my company, we've used Jet's MDB files as a way to prototype our development activities prior to moving the application onto SQL Server. Once we were ready to implement on SQL Server, we either had to upsize the MDB or export the tables (and data), and then create the indexes in a separate step. While this wasn't too difficult, it was time consuming. Now you can prototype or even do the whole development on the MSDE, then roll out the production database on the server version of SQL Server (or on the ultimate Microsoft platform: SQL Server Enterprise Edition running on NT Enterprise).
The process for upgrading from the MSDE is as simple as 1-2-3:
1. Copy the MSDE database and transaction log files to a separate directory.
2. Boot up NT and SQL Server 7.
3. Add the MSDE databases created from inside MS Access using the system stored procedure sp_attach_db.
The sp_attach_db stored procedure must be passed the name for the new database as the first parameter, followed by optional parameters specifying the database files (the procedure can handle up to 16 files). My MSDE database had two files. The syntax for adding the database was:
sp_attach_db 'myDB', 'C:\temp\ss7\mydb.mdf',
The database from the MSDE and the SQL Server database that you're attaching the files to must be using the same code page and sort order. Again, see the sidebar "Installing the MSDE" for more background on these settings. If they don't match, then you'll have to use SQL Server's Data Transfer Services to load the database tables and data into a new database on the SQL Server.
Part of a client-server solution might involve replicating data from various sources. The MSDE supports Full Merge Replication, but in Transactional Replication, the MSDE and Desktop SQL can only act as a subscriber (receiving, not initiating, replication). This makes sense, since the primary users of these engines in a production environment are probably mobile users or other single-user scenarios. With previous versions of Microsoft Jet, SQL Server data could only be replicated to a Jet database. Now, with version 4.0 of the Microsoft Jet Database Engine and version 7.0 of Microsoft SQL Server, support for bi-directional replication between Microsoft Jet and Microsoft SQL Server has been added. Of course, only data can be replicated between the two systems; Access objects such as forms or reports can't be replicated, since they can only reside in the Access ADP project file. As with any other replication scenario, there are a lot of issues to worry about, including conflicts and their resolution. However, the ability to replicate data between the various versions of SQL and Jet also opens the door to many new solutions.
The last bit of good news is that the gap between Jet's version of SQL and Transact-SQL (T-SQL) is closing. For example, T-SQL now accepts the square brackets ([My Table]) around object names. The ANSI standard is to use double quotes, but this option must be explicitly turned on. My rule of thumb is to keep spaces out of my table and other object names. It just makes life easier. Both Jet's SQL and T-SQL are entry-level ANSI 92 level compliant, which means, among other things, that they support ANSI joins ("Join...On"), and they've been doing that for a while now. ANSI 92 compliance is new for Jet, but T-SQL has been complying since version 6.5. Why do we care? It will help your client-server activities when you're able to write a single version of SQL.
As I said, for client-server developers, things haven't changed all that much. It will be easier to work in Access and create the necessary server-side database objects. You'll still benefit from using Access on the front end to build forms and reports quickly and easily. You'll still benefit from having a full-fledged database server on the back-side performing the data processing. But now you'll be able to begin your development on a single machine, and that makes it easier to continue developing the application from the beaches of Saint Lucia -- which is, after all, the goal of any good developer.
Sidebar: Installing the MSDE
Before installing the MSDE, you should answer a few questions:
|•||Do you really need the MSDE installed on your machine? You shouldn't just start installing the MSDE on every machine that currently has Access on it. The MSDE should only be installed for single-user applications, mobile users, and developers needing the data engine.|
|•||Find out what the standard is for your SQL Server's character set and sort order. The code page will most likely be the ISO standard. The sort order is more likely to vary. The default is dictionary order, case-insensitive (that is, [Aa]..[Zz]). As I discussed in the article, while there are workarounds, it's important to match the server if there's a chance you might want to put your MSDE database under SQL Server's control. The workarounds include replication, Data Transformation Services, or even the bulk copy program to dump and load the raw text.|
|•||Is your database case-insensitive? Case sensitivity applies to the object names (tables, user ids, procedures, and so forth) and your data. In a case-sensitive database, there are many different versions of "Hello" -- Hello, hello, HELLO, and every other combination of upper and lower case. In a case-insensitive database, those three hellos would be treated as equals. I highly recommend that you settle on a company standard and stick to it.|
|•||If you're running on Windows NT 4.0, has Service Pack 4 been applied to SQL Server 7.0?|
The steps for installing the MSDE are pretty straightforward. The MSDE should be installed on the same machine on which you've already installed Access by running \Sql\x86\Setup\setupsql.exe from the CD. You'll be prompted to enter the serial number from the disk, so keep it handy. You can hit Next to accept all defaults, or choose to customize your installation from the options presented. If you choose the custom installation, you'll be prompted to make the following decisions:
|•||You can choose where to put the SQL Server files and database files. The default is C:\MSSQL7.|
|•||You can change the character set and sort order. The default is ISO character set and dictionary order case-insensitive sort order.|
|•||You can select the network libraries. With Windows 95/98, the default setting is for TCP/IP. Multi-protocol is available, as well as some of the other, more common protocols. Under Windows NT, Named Pipes is available.|
I noticed a weird problem on Windows 98 that hopefully will be resolved with the release (this also occurred on the Desktop version of SQL Server). When first booting up, the Dial-up Connection dialog box was presented. The dialog box popped up again later when I used the SQL Server tools. I was able to stop the dialog box from trying to call the Internet by setting the Internet properties under the Control Panel to also work on the local network. Of course, the result was that IE doesn't dial when I need it now. But hey, who needs the Internet these days anyway.