Overview of book's content

This book makes a conscious attempt to provide content that will make a difference for Access developers. It is chuck full of the kind of code samples that made the ProgrammingMSAccess.com one of the most popular places on the Internet for Access users and developers. You'll find plenty of material on programming tables, queries, forms reports,, and security, but there's lots more too.

If you are an Access developer who wants to become proficient with ADO or VBA, there may be no better source for accomplishing this task. In addition, the material on Access-SQL Server interoperability is substantially updated and expanded from the prior version of the book. Serious Access developers need to grow their SQL Server skills now that Jet is in maintenance mode. This book includes three chapters that teach you SQL Server from the perspective of an Access developer. These chapters go beyond table linking to address creating your own tables and queries in SQL Server, how to interface SQL Server objects with Access forms and reports, and how to secure and connect securely to your SQL Server databases. The book closes with a look at XML processing techniques that enable you to export individual and related Access tables as well as the data behind forms as XML documents. Also, learn how to import XML documents into Access tables -- plus more!

Data Access Models: Part I

The subject of data access models is vast. To make this discussion more digestible, I've broken it into two chapters. This chapter and Chapter 2, "Data Access Models: Part II," focus primarily on data access for Jet with the ADO object model. You’ll learn about the 2.6 and 2.7 versions of ADO that go beyond the 2.1 version introduced with Access 2000.  The two chapters also include some coverage of ADO with other database engines—particularly Microsoft SQL Server. Numerous programming samples show you how to accomplish typical database chores. This chapter begins with an overview of ActiveX Data Objects and then moves on to discuss the Connection, Recordset, and Field objects.

This is the second of two chapters covering data access models. As I mentioned at the beginning of Chapter 1 ("Data Access Models: Part I"), the topic of data access models is extensive, which is why I've broken it into two chapters. Chapter 2 presented you with an overview of ActiveX Data Objects (ADO) and examined the Connection, Recordset, and Field objects. In this chapter, you'll learn about the Command and Parameter objects and the Errors collection. I'll also show you several techniques for managing access to the files on a Web server, demonstrate the features of the new Record and Stream ADO objects, and explore further the new functionality of the Recordset object.

The ADOX Library and Tables

The main objective of this chapter is to equip you with the skills needed to start designing tables with the ADOX object model for Jet databases. This chapter assumes you have an intermediate to advanced knowledge of table design. However, the chapter also assumes you have a novice or intermediate level of experience with the ADOX library. This will be the case for all experienced Microsoft Access developers switching from Access 97 and earlier versions to Access 2003. Even if you have had moderate exposure to ADOX programmatic table design, this chapter will reinforce your existing ADOX skills and prepare you for more advanced programmatic table design. The chapter concludes with a series of samples that illustrate how to populate tables with data after creating them with the ADOX model. The samples focus primarily on gathering data from sources outside the current Access project.

Jet SQL, the ADOX Library, and Queries

This chapter contains four major sections. The first section, "Jet SQL Syntax for Select Queries," examines the most common Jet SQL formulations for returning records from tables. You'll get Jet SQL code samples that show how to use ORDER BY, DISTINCT, GROUP BY and HAVING.  This section also demonstrates how to create inner joins, outer joins, and self joins  as well as subqueries and unions.  Learning Jet SQL liberates you from depending on the Microsoft Access Query Designer to develop queries. Mastering Jet SQL will also help you expand the roles that recordsets play in your applications.

The second section, "Managing Row-Returning Queries with ADOX," shows you how to create new queries programmatically by adding members to the Views and Procedures collections. This section also demonstrates how to add or modify queries in a database even when you temporarily disconnect your workstation from the database.

"Creating and Running Action Queries with ADOX" drills down on the design and use of parameter queries. This section pays special attention to using parameters for inserting, deleting, and updating records from a record source.

This final section of this chapter, "Introduction to Jet SQL's DDL," discusses the use of Jet SQL for table definition tasks. By learning how to perform data definition with Jet SQL, you can take advantage of the native Jet language for typical tasks, such as creating check constraints for columns..

Forms, Controls, and Data

This chapter discusses how to use Microsoft Access forms with data and offers a general introduction to working with forms and their controls.  The chapter's early samples illustrate techniques for developing and presenting application splash screens and switchboard forms. In the process, you'll get an introduction to the form's timer event and ways of using hyperlinks to invoke VBA procedures.

Next, the focus shifts to using forms with data. A progression of examples and samples demonstrate techniques building simple bound forms and linking them to programmatically controlled record sources. Particular attention is paid to how to use ADO recordsets as record sources for forms. After an introduction to using forms with record sources, the chapter switches its focus to main/subform design and data management issues. The presentation of this topic focuses on the parent-child relationship between the data behind the form. You'll learn about the relationships between main/subforms and subdatasheets for tables. The code samples demonstrate how to reference and manipulate the controls in subforms and their sub-subforms. Two additional collections of samples round out the treatment of data via forms. One of these sample collections demonstrates conditionally formatting the display of data values on forms. The other collection dwells on techniques for looking up and displaying data via Access forms.

The chapter concludes with a section that treat programmatic issues related to form development. You'll learn techniques for enumerating forms and their controls in the current project as well as other projects.  In addition, you'll learn how to show and hide forms.

Reports, Controls, and Data

The chapter begins with an introduction to manual techniques for building reports, but it also includes a demonstration of their value for creating reports programmatically. Using one of the many Access report wizards will often be a great first step to developing a custom report. Instead of building a default layout, you can spend your time customizing a wizard-generated report. This can include customizing the record source as well as making the report's content or formatting responsive to run-time specifications. If you're going to programmatically create new reports or edit existing ones, you'll also require a solid understanding of the Access report architecture and its Design view environment. The summary of manually created reports covers these topics as well.

The second collection of topics in this chapter deals mostly with programmatic issues. It starts out by covering approaches for creating and deploying snapshots of Access reports. Next, the chapter drills down into a variety of ways for making existing reports dynamic through programming.

Designing and Using PivotTables and PivotCharts

This chapter explores the capabilities of PivotTables and PivotCharts in Microsoft Access 2003.  PivotCharts appear for the first time in Access in the 2002 version. Moreover, Access 2002 substantially improves upon the PivotTable capabilities of prior versions of Access.

Both PivotTables and PivotCharts enable you to display data in an interactive manner. End users are likely to find PivotTables and PivotCharts appealing because of their easy-to-use, interactive interface. And programmers can readily compute the data depicted in PivotTables and PivotCharts based on their clients' record sources. The usefulness of programmatically constructed PivotTables and PivotCharts might render obsolete some traditional data analysis tools, such as crosstab queries and charts based on older technology. As the popularity of PivotTables and PivotCharts grows among end users, the demand for programmers who can readily create and edit these display devices will increase as well.

Microsoft Office Objects

This chapter starts with an overview of Office objects. After the overview, we'll examine the DocumentProperty object and related Access objects from Access database and Access project files. Within this discussion, I'll drill down on advanced techniques for replacing the Database window with a custom startup form in both .mdb as well as .adp files. Next we'll explore two specific objects: FileSearch, which you use to programmatically manage file searches; and CommandBars, which you use to create custom menus and toolbars.  The CommandBars commentary includes commentary and a code sample to secure your custom applications.

Integrating Access with Other Office Applications

This chapter explains how to programmatically integrate Access 2003 with the other Office applications using built-in Access features. For instance, your applications can tap installable indexed sequential access method (ISAM) drivers through the Connection object to work with the data in a Microsoft Excel spreadsheet. In addition, using Automation, your applications can simultaneously exploit the object models from two or more Office applications. For example, an application can export names and addresses from an Access data store to an Outlook Contacts folder.  After introducing general interoperability techniques, the chapter presents three sections that illustrate how to apply the techniques to making Access work with Excel, Outlook and Word.

In response to feedback from the previous edition of this book, we expanded the coverage of how to make Excel work with Access.  You learn how to work with Excel worksheets from Access, how to dynamically create Access tables based on Excel worksheets, how to run Excel procedures from Access, and how to base an Access form on Excel data.

The coverage of Access/Outlook interoperability features viewing the contents of and manipulating Access folders as well as merging Access contact data with an Outlook message.

The focus on Access/Word interoperability features examples of how to program merge mail for mailing labels and form letters.

Database Security

This chapter re-visits some old security features and presents some new security features introduced with Access 2003.

One highlight of the chapter is its in-depth coverage of user-level security.  You'll learn about a three-step process that gives you the best database security that Access has to offer.  After learning how to manually setup and design user-level security, we return to the topic to show you how to program user-level security with ADOX and Jet SQL.  You'll learn how to connect to a secure database, how to add and delete users and groups, and how to set permissions for users and groups.

Access 2003 introduces two new security features.  Both of these features help you to manage unsafe code instead of database access per se.  The Jet Sandbox helps to insure that you do not run unsafe code from quieries using the Jet engine or from controls in Access forms and reports.  Macro Security is another new feature that offers you a means of controlling what programmatic features can be executed by Access 2003 applications.  The Macro security feature in Access 2003 is an adaptation of the same feature introduced in Office 2000 for other Office components.

Using Access to Build SQL Server Solutions: Part I

Access 2003 offers tight integration with SQL Server 2000, and Access 2000 has a feature set tailored for SQL Server 7. You can obtain upgrades and patches to make Access 2000 compatible with SQL Server 2000. This chapter focuses almost exclusively on using Access 2002 with either SQL Server 2000 or the Microsoft SQL Server 2000 Desktop Engine.

The SQL Server 2000 Desktop Engine ships with any version of Microsoft Office XP that includes Access 2003. This engine version—abbreviated as MSDE 2000 throughout the chapter—is an upgrade to the Microsoft Date Engine (MSDE) that shipped with Office 2000. MSDE 2000 is compatible with SQL Server 2000, while its predecessor—MSDE—is compatible with SQL Server 7.

This chapter drills down on creating SQL Server objects from Access projects.  You learn how to create tables, database diagrams, views, stored procedures, user-defined functions, and triggers.  This chapter introduces the basics of T-SQL (SQL for use with SQL Server) from the context of Access projects. 

Using Access to Build SQL Server Solutions: Part II

This chapter continues the saga of how to build client/server solutions with Access projects. However, this chapter switches the focus to the client-side tools. Several early sections in the chapter examine how to use forms with SQL Server record sources. These are followed by another section on building solutions with reports. Do not feel that you are limited to the form and report techniques covered in this chapter. In fact, most techniques for applying forms and reports in Access database files also apply in Access projects; this chapter describes selected differences. This capability to use forms and reports means that you can refer back to the coverage of these topics in earlier chapters for more ways to create SQL Server solutions with Access.

Using Access to Build SQL Server Solutions: Part III

This chapter concludes the study of Access/SQL Server interoperability by examining SQL Server security especially via SQL-DMO programming.  Learn about core SQL Server security concepts, such as authentication, logins, user accounts, roles, and permissions.  After understanding the core concepts see how to program them from Access projects with SQL-DMO.  Separate sections explore login and user accounts as well as roles and permissions.

Access Does the Web

After providing a Web development overview targeting the special needs of Access developers, this chapter gives a quick introduction to Microsoft FrontPage 2003. Because FrontPage 2003 ships with Office 2003, it's a natural tool for managing the Web sites that make your solutions available to users. The chapter next presents some step-by-step examples of creating forms and reports on Web pages using graphical design techniques. These forms and reports bind directly to the Access database files and Microsoft SQL Server databases described throughout this book. I'll show you how to make these forms and reports dynamic and interactive by reviewing the new (since Access 2000) integrated development environment (IDE) behind them. You'll also see several samples in the chapter that illustrate how to build event procedures. Furthermore, you'll learn some easy ways to tackle advanced topics, such as programmatically creating Web-based forms and reports. Access 2003 enables you to create new Web-based forms and reports on the Access forms and reports you previously created.

XML and Access

This chapter introduces you to XML technologies -- particularly those that work well with Access 2003, such as XML document syntax, XML schemas, and XSLT.  In addition, the chapter demonstrates how to program Microsoft Core XML Services (MSXML) from Access.  MSXML offers a COM-based model for processing XML documents, such as those that you can generate from Access.

The chapter's first section offers an overview of XML technologies to facilitate your understanding of Access 2003 built-in XML features.  The second and third sections cover manual and programmatic techniques for exporting, importing, and and showing the contents of Access database objects via XML.