Access Projects and SQL Server Versions

To understand how Access 2002 interoperates with SQL Server, you need to learn about Access projects. Access projects dramatically enhance the ability of developers to use Access to create, manage, and deploy SQL Server solutions. This section describes what an Access project is and presents the basic architecture of an Access project. It also examines the versions of SQL Server with which Access projects interoperate.

Understanding Access Projects

An Access project is a new Access file type that offers an alternative to the traditional Access database file. The new file type has an .adp extension, which differentiates it from the traditional .mdb file. Both file types feature a Database window for opening and designing database objects, such as tables, and application objects, such as forms and reports. The .mdb file is optimized for processing Jet databases in a file/server environment. The .adp file is optimized for working with SQL Server in a client/server environment.

NOTE

Prior to Access 2000, Access developers typically worked with SQL Server databases by using linked tables from Access database files. The introduction of Access projects with Access 2000 did not eliminate this linked table capability, but its functionality is dwarfed by the vastly superior capabilities of Access projects.

What I find most exciting about Access projects is their ability to easily create SQL Server objects. Access 2002 provides visual designers for creating databases, tables, views, stored procedures, and user-defined functions, which appeared initially with SQL Server 2000. In addition, through database diagrams, you can create primary keys, indexes, constraints, and relationships between tables. A database diagram is similar to the Access database file Relationship window, but you can do more with a database diagram and you can have more than one diagram per database. Access 2002 makes basing forms and reports on SQL Server objects straightforward. By using server-side filters, you can improve the performance of your forms because less data will move over a network.

Because this book is about programming, you probably will be happy to learn that it's much easier now to program SQL Server from Access because of text-based design environments for creating and editing stored procedures and user-defined functions. This chapter will demonstrate the use of these environments.

The SQL Distributed Management Objects (SQLDMO) Object Library, which Access developers can readily program from Access project modules, helps you administer SQL Server. This hierarchical object model is great for enumerating, exploring, and creating server-side database objects, such as tables, views, stored procedures, and user-defined functions. A redesign to the UI for Access 2002 requires that you programmatically administer typical SQL Server security tasks, such as adding users and assigning permissions. You can easily program these tasks using SQL-DMO in an Access module or Transact-SQL (T-SQL) in a stored procedure. Chapter 15 discusses SQL Server security programming with SQL-DMO.

NOTE

Transact-SQL is an extension of the SQL standards authorized by the American National Standards Institute (ANSI) and the International Organization for Standards (ISO). This extension conforms with all SQL Server versions, enabling you to optimize and simplify your code for any SQL Server version you use. The T-SQL extension for SQL Server is analogous to Jet SQL for Jet databases.

Access Project Architecture

The Access project represents the client side of a client/server application. The SQL Server database to which the Access project connects represents the server side of the client/server application. An OLE DB connection links the client and server sides of this application. Notice that there isn't a database file as in a traditional Access application. Instead, the connection to the database, the OLE DB connection, serves as a conduit through which an Access project provides data access.

Figure 14-1 depicts the Database window for an Access project connected to the NorthwindCS SQL Server database that ships with Office XP. This database is a client/server version of the classic Northwind sample database. The left panel in the figure displays the Data Link Properties dialog box for the connection. You can open this dialog box by choosing File-Connection for an Access project connected to the NorthwindCS database. The panel on the right shows the Database window for the Access project. From the title bar of the Database window, you can see that the Access project file name is adp3.adp and that the project connects to the NorthwindCS database.

Figure 14.1 - The Data Link Properties dialog box and the Database window for an Access project.

The Data Link Properties dialog box represents the OLE DB connection element of the Access project architecture. It shows that the NorthwindCS database for the Access project resides on a SQL server named CABARMADA. You can have other versions of the NorthwindCS database on different servers, but the database names on any one server must be unique. The CABARMADA SQL server in my office network happens to be running MSDE 2000 on a computer running Microsoft Windows NT 4. The Access project authenticates itself to the SQL server through Windows NT integrated security. Authentication is the process by which a SQL server verifies that a user has permission to access the server. This is MSDE 2000's default mode of authentication on Windows NT and Windows 2000 computers. On Windows 98 and Windows Millennium Edition, MSDE 2000 installs exclusively with SQL Server authentication. In Chapter 15, you will explore the different types of authentication and see how to control them programmatically.

The Database window in the right panel of Figure 14-1 shows the table names in the database connection for the Access project. Access 2002 has undergone a redesign of the Database window from the Access 2000 version. The Database window for the new version more closely aligns with the Database window in traditional Access database files. In particular, there are no Objects bar entries for views and stored procedures. These object classes, which used to appear with their own entries in the Access 2000 Objects bar, are now grouped in the Queries object class. This class appears in the Database window for traditional object classes. The Access 2002 version also provides access to user-defined functions through the Queries class in the Objects bar. User-defined functions enable you to use T-SQL to code custom functions that perform like built-in functions in many ways. I'll describe these functions more fully later in the chapter in "User-Defined Functions."

The first three object classes on the Objects bar are Tables, Queries, and Database Diagrams. These are server-side elements of the client/server architecture for an Access project. In other words, the objects in these classes do not reside within the Access project file, which is adp3.adp in this example. These objects reside within the NorthwindCS database on the CABARMADA server. You can use the Database window to open collections of the Forms, Reports, Pages, Macros, and Modules classes. The objects in these classesreside within the Access project file. They are client-side objects. If I had another Access project file (for example, adp4.adp) connected to the NorthwindCS database on the CABARMADA server, it could have a different collection of forms than the one in adp3.adp.

Table 14-1 provides a summary of the object classes available from the Database window of an Access project, along with their location on the server or client side of the client/server application. If you have experience designing SQL Server tables with Access 2000, you'll be familiar with the SQL Server data types. In any event, SQL Server 2000 introduces several new data types that you'll want to learn about. Developers migrating to Access 2002 from Access 97 or an earlier version will need to learn the new data types from scratch. The Database Diagrams object class is not available with traditional Access database files. If you find programming SQL Server databases more of a challenge than you prefer (or if you just want a break from programming), learn to use these diagrams. They offer a graphical approach to designing tables and the relationships between them. The Tables, Queries, and Database Diagrams collections contain all the server-side objects in an Access project.

The Forms, Reports, Pages, Macros, and Modules collection members for Access projects comprise the set of client-side objects in an Access project. These client-side objects work almost identically in Access projects to the way Access database files do. This is one of the main reasons Access projects are such an attractive model for developing SQL Server solutions. Whether you're an old hand at Access or a SQL Server DBA with limited Access experience, you can easily and quickly start generating custom forms and reports.

Table 14-1 Object Bar Classes in the Database Window of an Access Project

Class Name	Comment	Location
Tables	Tables for SQL Server databases work similarly to the way they do in traditional Access database files. However, you will have to learn some new designations for data types in order to specify columns. In addition, you will have to learn a new way to specify a column with the AutoNumbers data type.	Server
Queries	Although Access 2002 removes views and stored procedures from the Objects bar, it retains these objects. They are available from the Queries item in the Objects bar. You can also view user-defined functions from the Queries object class.	Server
Database Diagrams	SQL Server databases permit more than one database diagram per database. This is particularly handy when you want to break a large database application into parts. You can also use database diagrams to create other objects, such as tables.	Server
Forms	Works generally the same way as it does in a traditional Access database file.	Client
Reports	Works generally the same way as it does in a traditional Access database file.	Client
Pages	Works generally the same way as it does in a traditional Access database file.	Client
Macros	Works generally the same way as it does in a traditional Access database file.	Client
Modules	Works generally the same way as it does in a traditional Access database file.	Client

Supported SQL Server Databases

Microsoft upgraded Access 2002 to work especially well with SQL Server 2000 and MSDE 2000 on Windows 2000. However, Access 2000 supports a wide range of SQL Server versions on a variety on operating systems. Table 14-2 summarizes the three groups of SQL Server installations with which Access projects are compatible. Notice that if you use SQL Server 2000 or MSDE 2000, you cannot use Windows 95. However, Access projects do support working with SQL Server 7 and MSDE on Windows 95. In addition, you need to install a variety of service packs for earlier operating systems or SQL Server versions to obtain support by Access projects.

Table 14-2 SQL Server and Windows Versions Supported by Access Projects

SQL Server Version	Operating System Version
SQL Server 2000 or MSDE 2000	Works on Windows 2000, Windows NT (Service Pack 6 or later), or Windows 98
SQL Server 7 or MSDE	Works on Windows 2000, Windows NT (Service Pack 4 or later), and Windows 95 (or later)
SQL Server 6.5 (Service Pack 5 or later)	Works on Windows 2000, Windows NT (Service Pack 4 or later), and Windows 95 (or later)

Using MSDE 2000 is a great way to start learning SQL Server development techniques. MSDE 2000 is free with any version of Office XP. Its T-SQL and SQL-DMO programming syntax is compatible with SQL Server 2000. Its file format is compatible with SQL Server 2000, meaning you can reattach the MSDE 2000 database files to a SQL Server 2000 database for greater processing power and access to better graphical client management tools. These graphical tools can dramatically simplify the administration and use of a SQL Server database.

Like its predecessor, MSDE, MSDE 2000 has a built-in performance degrader after five users connect, but there is no hard limit on the number of users. Nevertheless, if you have many more than five users, you can boost performance by switching to the standard or enterprise edition of SQL Server 2000. By switching to either version of SQL Server 2000, you also gain the client management tools, Enterprise Manager and Query Analyzer. These easy-to-use yet powerful tools enable you to administer one or more SQL servers (Enterprise Manager) as well as an integrated development environment for debugging and running T-SQL (Query Analyzer). The full version of SQL Server 2000 delivers other SQL Server components that are unavailable with MSDE 2000. These include the Full-Text Search and Indexing tools as well as Online Analytical Services for datawarehousing applications. In addition, you cannot use MSDE 2000 as a transactional replication server. However, the standard edition of SQL Server 2000 (as well as the developer edition that ships with Microsoft Office XP Developer edition, MOD) can support this function.

Installing and Using MSDE 2000

MSDE 2000 ships with any version of Office XP that includes Access 2002, but it does not install with the standard Office XP setup.exe program. The Access Help file for MSDE advises you to remove MSDE if you have it installed before installing MSDE 2000. Then you run setup.exe in the \MSDE2000 folder on your Office XP installation CD. If you have a network installation of Office XP, you can run the MSDE 2000 setup.exe application from the network installation. Complete the installation by restarting the computer. This launches MSDE 2000 as a service on Windows NT and Windows 2000 computers.

If MSDE does not start automatically, run the Service Manager from the Startup folder of the Windows Start button. On Windows 98 computers, you might need to select the Auto-Start check box if you want MSDE 2000 to launch automatically when the computer boots. This check box appears at the bottom of the SQL Server Service Manager dialog box, shown in Figure 14-2. You can use this same dialog box to pause and stop the SQL Server service.

Figure 14.2 - The SQL Server Service Manager dialog box.

After following the instructions for a standard MSDE 2000 installation, your old MSDE database files will still be available. However, they won't be attached to your new server. This is because you removed the old version of MSDE without attaching the old files to the new server. SQL Server databases typically have two files. (Very large and sophisticated database applications can have even more files.) The first is a data file with an .mdf extension. The second is a log file with an .ldf extension. Unless you changed the default location for your MSDE data files, you'll find them at c:\Mssql7\Data.

You can use the sp_attachd_db system stored procedure to reattach an old database file from MSDE to your new MSDE 2000 installation. This stored procedure takes three arguments. The @dbname parameter represents the name of the database and takes a string value. Microsoft recommends using Unicode format for string values—especially when dealing with the character set of more than one country. Enclose the string value in single quotes (') and precede it with an N to denote Unicode format. The @filename1 and @filename2 parameters are for the .mdf and .ldf files. You specify these files with both the path and filename.

The following code sample shows the syntax for invoking the sp_attach_db stored procedure. The code attaches a pair files for the adp1sql database to the SQL server for the current project. The SQL string for invoking the sp_attach_db stored procedure begins with the EXEC keyword. A Connection object invokes the SQL string with its Execute method. The Connection objection points at the database for the current project and indirectly to the SQL server for the project's Connection property. If you want to attach the files to another server, you'll have to use a Connection object for that server. In addition, your user ID must have permission to attach a database to the server.

Sub AttachAnOldDB()

Dim cnn1 As ADODB.Connection

Dim str1 As String

'Set up the SQL string to attach the database files

'from the default location for database files maintained

'by MSDE

str1 = "EXEC sp_attach_db @dbname = N'adp1sql', " & _

    "@filename1 = N'c:\Mssql7\Data\adp1sql.mdf', " & _

    "@filename2 = N'c:\Mssql7\Data\adp1sql.ldf'"

'Point a Connection object at the current project,

'and execute the SQL string

Set cnn1 = CurrentProject.Connection

cnn1.Execute (str1)

'Clean up objects

cnn1.Close

Set cnn1 = Nothing

End Sub

In Chapter 15, I will present a utility for automatically attaching all database files from a folder to a server. I will also examine SQL Server security and show you the permissions needed to attach a database to a server. When you initially install MSDE 2000, you have that permission by default.

NOTE

You can customize the installation of MSDE 2000 by assigning values for named switches when you invoke setup.exe. Search Access Help for "Install and Configure SQL Server 2000 Desktop Engine" for details on a subset of the switches and their settings. Additional settings not documented in Access Help appear in the readme.txt file within the \MSDE2000 folder on your Office XP installation disc. Look under headings 3.1.4 and 3.1.23 for setup parameters that enable you to customize MSDE 2000 authentication at startup and database file recovery from a prior MSDE version.