Access Projects and SQL Server Versions
To understand how Access 2003 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 2003 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 2003 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. |
You probably will be happy to learn that it's now much easier to program SQL Server from Access thanks to text-based design environments for creating and editing stored procedures and user-defined functions. This chapter demonstrates use of these environments with Transact SQL (T-SQL), which is a SQL dialect optimized for SQL Server.
| Note | Transact-SQL (T-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 11-1 depicts the Database window for an Access project connected to the NorthwindCS SQL Server database that ships with Office 2003. 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 adp1.adp and that the project connects to the NorthwindCS database.
Figure 11.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 CabSony1. You can have other versions of the NorthwindCS database on different servers, but the database names on any one server must be unique. The CabSony1 SQL server in my office network is running SQL Server Developer Edition on a computer running Microsoft Windows XP. 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 the MSDE 2000 default mode of authentication on Windows NT, Windows 2000, and Windows XP computers.
The Database window in the right panel of Figure 11-1 shows the table names in the database connection for the Access project. The Database window for the Access project closely aligns with the Database window in traditional Access database files. Notice the entries for Tables and Queries in the Objects bar. The Queries entries group together three kinds of SQL Server objects: views, stored procedures, and user-defined functions. Views and stored procedures in SQL Server correspond generally to the ADOX objects with those names (see Chapter 4). User-defined functions enable you to use T-SQL to code custom functions that perform like built-in functions in many ways. These functions are described more fully later in the chapter.
The first three object classes in the Objects bar shown in Figure 11-1 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 adp1.adp in this example. These objects reside within the NorthwindCS database on the CabSony1 server. You can use the Database window to open collections of the Forms , Reports , Pages , Macros , and Modules classes. The objects in these classes reside within the Access project file. They are client-side objects. If I had another Access project file (for example, adp2.adp) connected to the NorthwindCS database on the CabSony1 server, it could have a different collection of forms than the one in adp1.adp.
Table 11-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. Developers migrating to Access projects from Access database files will need to learn new SQL Server data types to create their own SQL Server tables (just as developers had to learn the Jet data types to create tables for Jet databases). 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.
| 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 2003 removes views and stored procedures from the Objects bar (where they used to be in Access 2000), it retains these objects. They are available from the Queries item in the Objects bar. You can also see 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 |
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.
Supported SQL Server Databases
Microsoft designed Access 2003 to work especially well with SQL Server 2000 and MSDE 2000 on Windows 2000 or a later version of Windows. However, Access 2000 supports a wide range of SQL Server versions on a variety of operating systems. Table 11-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.
| 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 2003 that includes Access. 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 data warehousing 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 Access 2003 Developer Edition, ADE) can support this function.
Installing and Using MSDE 2000
MSDE 2000 ships with any version of Office 2003 that includes Access 2003, but it does not install with the standard Office 2003 setup.exe program. To install MSDE 2000 so that it can operate side by side with the Jet database engine, run setup.exe from the MSDE2000 folder in the Office 2003 installation CD. If you have a network installation of Office 2003, 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 XP, Windows 2000, and Windows NT 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. You can use this same dialog box to pause and stop the SQL Server service.
| 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 2003 installation CD. 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. |
EAN: 2147483647
Pages: 144