Microsoft Access Projects with Microsoft SQL Server

691 Pages·2002·37.83 MB·English

This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Copyright © 2002 by Microsoft Corporation
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
PUBLISHED BY
Microsoft Press
A Division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright ? 2002 by Microsoft Corporation
All rights reserved. No part of the contents of this book may be reproduced or
transmitted in any form or by any means without the written permission of the
publisher.
Library of Congress Cataloging-in-Publication Data pending.
Printed and bound in the United States of America.
1 2 3 4 5 6 7 8 9 QWE 7 6 5 4 3 2
Distributed in Canada by Penguin Books Canada Limited.
A CIP catalogue record for this book is available from the British Library.
Microsoft Press books are available through booksellers and distributors worldwide.
For further information about international editions, contact your local Microsoft
Corporation office or contact Microsoft Press International directly at fax (425)
936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to
[email protected].
Microsoft, Microsoft Press, MS-DOS, Windows, and Windows NT are either registered
trademarks or trademarks of Microsoft Corporation in the United States and/or other
countries. Other product and company names mentioned herein may be the
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
trademarks of their respective owners.
The example companies, organizations, products, domain names, e-mail addresses,
logos, people, places, and events depicted herein are fictitious. No association with
any real company, organization, product, domain name, e-mail address, logo, person,
place, or event is intended or should be inferred.
Acquisitions Editor: David Clark
Project Editor: Lynn Finnel
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Part I
Microsoft Access Projects and Microsoft SQL
Server
This section describes the basics of setting up Access projects. Access projects differ
from traditional Access mdb databases in that data and queries are managed and run
with SQL Server and MSDE.
This section will explain in detail how SQL Server and MSDE manage data in tables
and how they differ from Access mdb database tables. The SQL Server database
diagrams correspond to the relationship diagrams in Access mdb databases for
creating and documenting key relationships.
Queries are created with SQL Server or MSDE in the Transact-SQL programming
language. Select queries can be defined as views. Entire program processes and
action queries are implemented as stored procedures. You will learn how to create,
edit, and run views and stored procedures from an Access project and will be given an
overview of programming with Transact-SQL.
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Chapter 1
Introduction to Microsoft SQL Server and Access
Microsoft Access is the most commonly used product for desktop databases among
beginners and professionals alike. However, Access has limitations when it comes to
the administration of large amounts of data that many users access through a
network.
Microsoft positions Microsoft SQL Server as a database server that can make large
amounts of data available to many users at the same time. In addition, a SQL Server
version is included with Microsoft Office Developer suites and Microsoft Visual Studio
as a Microsoft Data Engine (MSDE) with limited functionality.
One goal of this book is to describe how Access and SQL Server or MSDE work
together. Since the release of Microsoft Access 2000, it is possible to create Access
projects. Access projects provide users and programmers with the usual Access
development environment that includes queries, forms, reports, macros, and modules,
with the data being saved on a SQL Server. Because this book focuses on creating
Access projects (adp) and describing its new options and functions, the book does not
cover the options of accessing SQL Server tables using Open Database Connectivity
(ODBC) and so on using regular Access databases in .mdb format.
Access is a comfortable, high-performance development environment for the
implementation of user-friendly applications. The second goal of this book is to
demonstrate how you can retain Access as a development environment while using a
high-performance server database at the same time. Access is often ridiculed as a
“toy,” with its usefulness limited to the implementation of a few small applications on
desktop systems. However, by now, many Access programmers have proven the
program’s capability for handling medium-sized and large applications.
This book is based on working with software projects that utilized Access projects and
SQL Server to replace existing Access solutions and original project developments
that took advantage of SQL Server’s performance features. Several version changes
occurred while this book was being written: Microsoft SQL Server 2000 is the
successor of Microsoft SQL 7, and Microsoft Access 2002 as part of Microsoft Office
XP is the successor of Access 2000. SQL Server 2000 and Access 2002 feature a
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
large number of improvements, particularly for tasks that require programming with
Access projects. We have incorporated information about both new versions in the
book and we discuss the problems related to version incompatibilities. For example,
some Access 2000 project functions do not work while accessing SQL Server 2000.
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Who Should Read This Book?
This book was written for readers who are familiar with Access, who have experience
creating forms and reports, and who know how to program with Microsoft Visual
Basic. This book does not cover the basic options available for creating forms and
reports. Instead, it presents the differences between Access adp projects and
traditional Access mdb applications.
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Chapter Overview
The book’s individual chapters are organized into parts for a clearer overview. The
following summary introduces the topics covered in each chapter.
Part I: Microsoft Access Projects and Microsoft SQL
Server
Part I teaches you how Access projects are structured and how to use an Access
project to access SQL Server or MSDE databases. We explain the components of
SQL Server or MSDE databases, such as tables, views, and stored procedures.
Chapter 2, “Access Projects,” describes the basic properties of Access projects. We
point out how conventional Access databases differ and introduce the various SQL
Server and MSDE versions.
Chapter 3, “Installation,” covers the SQL Server or MSDE installation process, both for
version 7 and for 2000. We also discuss the different configuration options for
transferring data between the Access client and SQL Server over the network.
Chapter 4, “Databases,” provides an overview of SQL Server or MSDE databases and
illustrates their structure. In addition, we introduce preinstalled sample databases,
followed by a description of how to create new Access projects that access new or
existing SQL Server or MSDE databases.
Chapter 5, “Tables in Access Projects,” teaches you how to create tables in a SQL
Server or MSDE database. We introduce the SQL Server data types and column
properties for tables available in SQL Server or MSDE. The chapter also covers the
creation of indexes, the definition of constraints, and much more. Next, you learn
about how to work with tables in the Access datasheet view. At the end of the chapter,
we briefly describe the system tables used by SQL Server and MSDE that can be
found in every database.
Chapter 6, “Database Diagrams,” is devoted to the definition of relationships among
database tables. A database diagram lets you create well-organized graphical models
that illustrate the tables in a database and the relationships among them.
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Chapter 7, “Views,” focuses on views. A view is based on a SQL SELECT command
and corresponds to a select query in Access mdb databases. The chapter describes
the query designer options for interactively creating views.
Chapter 8, “Stored Procedures,” is an introduction to programming stored procedures.
Stored procedures are routines written in the Transact-SQL programming language
that you can use to query, add, edit, or delete data. We demonstrate how you can use
variables and parameters in stored procedures and introduce the Transact-SQL
Syntax. This chapter also covers how to create fundamental triggers, which are
specialized stored procedures assigned to tables that are executed during the process
of inserting, editing, or deleting the table’s data. We address the process of debugging
stored procedures in this chapter as well. We then move on to describe the
user-defined functions that are new features in SQL Server 2000.
Chapter 9, “Transact-SQL,” focuses on the Transact-SQL programming language.
This chapter provides you with an overview on how to program with Transact-SQL
and includes information about subqueries, temporary tables, and error handling.
Part II: Forms and Reports
Part II is devoted to forms and reports in Access projects. Note that the chapter does
not cover all options available for creating and programming forms and reports.
Instead, the focus is on those Access project form and report issues that are new or
different from Access mdb database functions.
Chapter 10, “Forms,” describes special circumstances related to working with forms in
Access projects.
Chapter 11, “Reports,” presents the changes and new functions available for creating
reports in Access projects.
Part III: Programming with ADO
Part III is an introduction to programming the ActiveX Data Objects (ADO) data access
interface. With conventional Access mdb databases, you could choose between the
Data Access Objects (DAO) data access interface and ADO. In Access projects,
however, you can only use ADO.
This document was created by an unregistered ChmMagic, please go to http://www.bisenter.com to register it. Thanks .
Chapter 12, “Connection Objects,” teaches you how to work with Connection objects
that specify connections between the program and the database server. We describe
how to create new connections, particularly in Access projects, and how to use the
Access project’s connections.
Chapter 13, “Recordset Objects,” details how you can edit, add, or delete data and
introduces the most important Recordset object methods and properties. ADO uses
Recordset objects to return a query’s result sets.
Chapter 14, “Command Objects,” is devoted to Command objects. The primary
purpose of these objects is the execution of queries that edit, add, or delete data.
Chapter 15, “ADO Events,” outlines the process of programming Connection and
Recordset object events. Many of the objects’ operations initiate events that you can
capture and process with your own routines.
Part IV: Upsizing
Part IV consists of two chapters that provide helpful information on the conversion of
existing Access mdb applications to Access adp projects.
Chapter 16, “Upsizing Wizard,” presents the Upsizing Wizard that supports you during
the conversion of Access mdb applications to Access projects. The Upsizing Wizard
helps you transfer data from an mdb database to a SQL Server or MSDE database.
On your request, the wizard attempts to transfer forms, reports, macros, data access
sheets, and modules to an Access project.
Chapter 17, “From DAO to ADO,” contains tips on how to use ADO to convert existing
Access Microsoft Visual Basic for Applications (VBA) programs that use the DAO data
access interface to Access projects.
Part V: SQL Server/MSDE Administration
Part V covers the different administration options for SQL Server and MSDE systems.
We discuss using an Access project alone (particularly with respect to using SQL
Server 2000 Desktop Engine and MSDE), as well as the use of the complete SQL
Server version’s administration tools.