You are previewing Microsoft® Access Projects with Microsoft SQL Server™.
O'Reilly logo
Microsoft® Access Projects with Microsoft SQL Server™

Book Description

The definitive guide to building enterprise solutions with Access Projects and SQL Server

Professional developers who use Microsoft® Access, the popular desktop database program, often confront limits to working with large amounts of data. This hands-on guide demonstrates in detail how to build powerful database solutions of unlimited size by retaining the familiar development environments of Access 2000 and Access 2002 while exploiting the enterprise-level power and performance of SQL Server™ 7.0 and SQL Server 2000. Find out how to use MSDE—the SQL Server version included with Microsoft Office Developer and Microsoft Visual Studio®—to store data on a SQL Server. Then learn how to create and use Access Projects, complete with queries, forms, reports, macros, and modules, to access SQL Server and MSDE databases.

Table of Contents

  1. Copyright
  2. Microsoft Access Projects and Microsoft SQL Server
    1. Introduction to Microsoft SQL Server and Access
      1. Who Should Read This Book?
      2. Chapter Overview
      3. The Sample Database
      4. System Requirements
      5. Support
    2. Access Projects
      1. Interfaces
      2. Access Projects
      3. SQL Server and MSDE
      4. Standalone, Network, and Client Server
    3. Installation
      1. Installing SQL Server 2000
      2. Installing MSDE 2000
      3. Installing SQL Server 7
      4. Installing MSDE 1
      5. Running SQL Server/MSDE
    4. Databases
      1. SQL Server/MSDE Databases
      2. Creating a New Access Project
    5. Tables in Access Projects
      1. Creating Tables
      2. Indexes for Tables
      3. Copying Tables
      4. Working with Tables
      5. System Tables
    6. Database Diagrams
      1. Creating a Database Diagram
      2. Editing Tables
      3. Defining Relationships
      4. The Properties Dialog Box
      5. Diagram View
    7. Views
      1. Access Queries vs. Views
      2. Creating Views
      3. Query Designer Areas
      4. Adding Tables or Views
      5. Selecting Columns
      6. Aliases
      7. Saving a View
      8. Executing a View
      9. Datasheet View
      10. Criteria
      11. Joins
      12. Groups
      13. Calculated Columns
      14. Properties of a View
      15. Union Queries
      16. Subqueries
      17. Creating Views with SQL
    8. Stored Procedures
      1. Creating Stored Procedures
      2. Parameters and Variables
      3. Transact-SQL Program Commands
      4. Triggers
      5. Searching for Errors in Stored Procedures
      6. User-Defined Functions
    9. Transact-SQL
      1. Select Queries with SELECT
      2. Calculated Columns
      3. SQL Functions
      4. Sorting
      5. Criteria with WHERE
      6. Aggregate Functions
      7. Groups
      8. DISTINCT
      9. SELECT TOP
      10. Links with JOIN
      11. UNION Queries
      12. Subqueries
      13. Action Queries
      14. Error Handling
      15. Temporary Tables
      16. Programming a Cursor
  3. Forms and Reports
    1. Forms
      1. Record Source
      2. Editing Data in Forms
      3. The InputParameters Property
      4. Referencing Forms and Reports
      5. Synchronized Subforms
      6. Form Filters
      7. General
      8. Opening Forms with VBA
    2. Reports
      1. Creating Reports
      2. Subreports
      3. Opening Reports with DoCmd.OpenReport
  4. Programming with ADO
    1. Connection Objects
      1. Connection Strings
      2. Connection Objects in Access Projects
      3. The Open Method
      4. Other Properties and Methods
      5. The Errors Collection
    2. Recordset Objects
      1. Understanding Recordsets
      2. Opening a Recordset
      3. Working with Recordsets
      4. Searching Records
      5. Sorting Recordsets
      6. Editing Data
      7. The GetRows Method
      8. The GetString Method
      9. The Clone Method
      10. The Requery and Resync Methods
      11. State Properties
      12. Batch Processing
      13. Recordsets Without a Database Connection
      14. Working with Multiple Recordsets
      15. Saving Recordsets
    3. Command Objects
      1. Using Command Objects
      2. Queries with Parameters
    4. ADO Events
      1. Events
      2. Asynchronous Queries
  5. Upsizing
    1. Upsizing Wizard
      1. The Upsizing Wizard
      2. Converting Tables
      3. Porting Relationships
      4. Upsizing Queries
      5. Converting Forms and Reports
      6. Converting VBA Programs
    2. From DAO to ADO
      1. Basics
      2. Opening a Database
      3. Accessing the Data by Recordset
      4. Executing Queries
      5. DAO in Access Projects
  6. SQL Server/MSDE Administration
    1. Database Administration
      1. Creating Databases
      2. Deleting Databases
      3. Additional Administration Functions
    2. SQL Server Tools
      1. Enterprise Manager
      2. Query Analyzer
      3. The OSQL Program
      4. Profiler
    3. Security
      1. Security for .Adp Files
      2. Securing VBA Modules
      3. Security with SQL Server/MSDE
      4. User Administration with SQL Server Enterprise Manager
      5. SQL Commands for Granting Permissions
      6. System Stored Procedures for Users and Roles
    4. Data Backup Functions
      1. Basics
      2. Data Backup Functions
      3. Restoring Data
    5. Data Transformation Services
      1. The DTS Wizard
      2. DTS in Enterprise Manager
    6. The SQL-DMO Library
      1. Object Model
      2. A Script for All Database Objects
      3. Setting Up the SQL-DMO Library
    7. External Data Sources
      1. Setting Up a Linked Server
      2. Data Access to Linked Servers
      3. Ad-Hoc Accesses
      4. Linked Tables in Access 2002
    8. System Stored Procedures
      1. Grouped by Function
      2. Alphabetical List
  7. Transactions and Locks
    1. Transactions and Locking
      1. Transactions
      2. Locking Records
  8. Internet
    1. Web Publishing with SQL Server
      1. The SQL Server Web Assistant Wizard
    2. XML with Access 2002 and SQL Server 2000
      1. XML with Access 2002
      2. XML with SQL Server 2000
  9. Appendices
    1. Naming Conventions
      1. Hungarian Notation
      2. Tags
      3. Tags for Collections
      4. Tags for Constants
      5. Menu Elements
      6. Creating Data Types
      7. Creating Procedures
      8. Prefixes
      9. Suffixes
      10. Tags for Access
      11. Tags for ADO
      12. Tags for SQL Server Objects
    2. Internet Addresses
      1. Commonly Used Resources
      2. Newsgroups
    3. SQL Server/MSDE Specifications