Who's the book for, how to get the most from the book, and what's covered?

The primary audience for this book is Access database developers who want to start building solutions with SQL Server instead of or in addition to Jet.  It thoroughly drills down on all the features of Access projects.  You learn the basics of SQL Server table design (including triggers), views, and stored procedures.  In addition, the book targets SQL Server database administrators and developers who are looking for easy ways to deliver SQL Server data to clients.  This book demonstrates a wide variety of techniques for delivering data to clients on LANs and webs.  You will discover how to integrate Access forms and reports with SQL Server database objects.  A special focus on web techniques emerges from three chapters that present techniques for publishing datasheets on webs, creating solutions with Active Server Pages, and employing Data Access Pages to build web solutions graphically and programmatically.

This chapter frames the background for the rest of the book.  You will need Access 2000.  In addition, you will need a web server.  The Internet Information Server (versions  4 or 5) will provide the most advantages, but you will be able to run many of the samples with the Personal Web Server.  The book demonstrates how to manage a web site with FrontPage 2000.  Microsoft makes Access 2000 and FrontPage 2000 with the Premium and Office Developer editions of Office 2000.  Your operating system can be Windows 9x, Windows NT 4, or Windows 2000.

Access 2000 and SQL Server

This chapter equips you with a brief introduction to core database development issues covered in the rest of the book.  Learn how Access projects fit into the multi-tier database architectures that Microsoft supports.  This chapter also conveys a quick overview of core SQL Server database concepts.

Installing Engines and Starting Projects

This chapter commences with instructions to help you install any of the three SQL Server database technologies used throughout the rest of the book, including the Microsoft Data Engine, SQL Server 7, and SQL Server 2000. The chapter also contrasts the technologies.  After the overview of SQL Server technologies, the chapter drills down on getting started with Access projects.  You learn how to expose and manipulate SQL Server and Access objects from an Access project.  Special attention goes to interfacing Access projects with SQL Server data sources.  For example, you learn how to create new SQL Server databases for your Access projects and how to link new Access projects with existing SQL Server databases.

Designing SQL Server Tables with Access 2000

This chapter equips you to build SQL Server tables manually and programmatically.  Before building SQL Server tables, you must learn the SQL Server data types and understand how they contrast with Access 2000 data types.  The overview of manual table design techniques exposes you to visual table designer in Access projects.  You also learn how to build tables programmatically with either Transact SQL (T-SQL) or SQL Distributed Management Objects (SQL-DMO).  This chapter also offers considerable coverage of constraints, primary keys, and foreign keys.

Using Database Diagrams

Database diagrams offer the functionality of the Relationship window -- plus much more.  With database diagrams, developers can graphically add, remove, and edit table definitions as well as the relationships between them.  You can also specify constraints on individual columns within tables, add and drop primary keys, assign and remove indexes, and control referential integrity.  New functionality introduced with SQL Server 2000 even exposes graphical techniques for managing cascading updates and deletes.

Designing SQL Server Views with Access 2000

Learn what a SQL Server view is and how to create it with the graphical Query Designer as well as with T-SQL, ADO, SQL-DMO.  If you are looking for a refresher or an introduction to T-SQL syntax for select queries, this chapter delivers the goods.  Learn about advanced features, such as indexed views and ways for creating and editing views with SQL-DMO.  Indexed views are a SQL Server 2000 innovation.

Using Stored Procedures

This chapter drills down into five major topics: (1) system stored procedures, (2) user-defined stored procedures, (3) using VBA and ADO with stored procedures, (4) working with triggers, and (5) user-defined functions.  This chapter shows how stored procedures complement views as database objects.  Learn specifically about INSERT, UPDATE, and DELETE statements.  In addition, special attention goes to using parameters and control-of-flow statements.  The chapter acquaints you with how to design stored procedures through the Access project UI as well as programmatically with T-SQL, ADO/VBA, and SQL-DMO.

Using Access Forms with SQL Server

This chapter takes you from form basics to advanced techniques.  It has content appropriate for experienced Access developers migrating to SQL Server as well as SQL Server database administrators and developers who want to learn how to build solutions with Access.  Some specific topics include how to use the AutoForm wizard with SQL Server tables, views, and stored procedures, how to build main/sub forms for SQL Server database objects, how to work with parameters in forms, how to programmatically assign a data source to a form, and how to use hyperlinks on forms to extend the scope of Access applications for SQL Server..

Using Access Reports with SQL Server

This equips you to design Access reports that work with SQL Server data sources. It covers six main topics, including (1) the elements of an Access report, (2) using the AutoReport wizard, (3) creating dynamic reports based on stored procedures, (4) using conditional formats on reports through the Access project interface and through VBA programming, (5) adding charts to reports based on SQL Server database objects, and (6) specifying the content for graphs programmatically.

Publishing Datasheets

This chapter describes the basics of creating and managing a web site with FrontPage 2000 as a foundation for publishing datasheets to the site.  You learn how to publish datasheets manually using either the FrontPage Publish Database Wizard and the Access project user interface.  The description of datasheet publishing techniques contrasts static and dynamic methods.  You will also discover VBA programmatic means for publishing datasheets to a web site and ASP programmatic means for manipulating a published datasheet.

Creating Forms with Active Server Pages

This chapter starts short introductions to the ASP object model -- especially for dealing with forms.  Next, the chapter drills downs on managing forms with FrontPage at web sites.  A series of samples bring ASP and form design principles to life with examples for searching, browsing, adding, updating, and deleting with forms.  You will also discover techniques for dealing with combo boxes and displaying image dynamically on forms based on database values.

Data Access Pages

If you think designing Access forms and reports is great, you are going to love creating web solutions with data access pages.  This chapter tells you all about it with a solid introduction to the basics, coverage of advanced topics such as the Microsoft Script Editor and event procedures, and plenty of samples to show how to put data access pages to use in your custom applications.   You will also discover how to enhance the functionality of data access pages by adding Office Web Components.  These additional features enable dynamic spreadsheets, charts, and pivot tables on a web page.

Programming Access for SQL Server

This chapter examines two key topics.  First, it explores how to easily program SQL Server databases with the DoCmd object.  Second, it revisits core ADO concepts, such as connections and, recordsets, for working with SQL Server databases.

Database Security

Learn the building blocks of SQL Server security as well as how to control them with the Access project UI.  This chapter clearly explains how to create and manage logins and user accounts.  You will also learn how to secure databases with fixed server roles and fixed database roles.  In addition, the discussion drills down further to demonstrate techniques for creating user-defined roles with custom permissions for individual database objects.  Finally, you also gain exposure to working with Application roles.

Converting Access Database Files to SQL Server

This appendix has two major parts.  The first part explores the Access Upsizing Wizard for migrating database schemas and their associated database contents to SQL Server database servers.  The material in this part covers traditional topics as well as innovations introduced with Access 2000.  The second part explores using the SQL Server Data Transformation Wizard to transfer just the data from an Access database file to a SQL Server database.  This section also demonstrates how to re-use VBScript code generated by the wizard to automate the process of transferring data from Access database files to SQL Server databases.

Copying Databases Between Servers

This appendix briefly demonstrates manual and programmatic means of copying databases between servers using T-SQL and the Access project UI.

Setting Up a Connection

Could you benefit from a quick review of database connections and how to work with DSNs?  If so, this appendix may be just what you need.

Reporting on Log Files

Did the chapter on reports leave you thirsting for a more advanced sample that demonstrates and integrates Access report design principles?  This sample takes a classic web application and shows you how to build a report that gets out several critical bits of information from a web log.  It is too long and involved for the chapter on reports, but it makes a great appendix.

ADO 2.1 Object Model

This appendix is the typical thorough reference material that Wrox puts at the end of the book.  In this case, it highlights the ADO Object Model that ships with Access 2000.  You are likely to find this resource handy for using ADO with SQL Server data sources.