Access 2002 Development Unleashed

by Stephen Forte, James Ralston, Tom Howe

Overview

Access 2002 Development Unleashed provides the experienced Access user information needed for high-end enterprise applications. This includes both Jet engine multi-user applications and SQL Server enterprise applications. This book will strengthen their existing Access knowledge and move them to a professional level of development. Written by respected Access professionals, this book will also give readers real world examples for their business solutions and advice from years of working experience.

Topics include: Database Design, Data Access, VBA, Access Client/Server, User Interfaces, Interoperability, Multi-user Issues, Web Publishing with Access.

Product Details

ISBN-13:	9780672321207
Publisher:	Pearson Education
Publication date:	09/01/2001
Series:	Unleashed Series
Pages:	835
Product dimensions:	7.34(w) x 9.13(h) x 1.91(d)

About the Author

Stephen Forte runs the New York City consulting firm, The Aurora Development Group, which specializes in Microsoft Database and Web technology. He has authored and co-authored several books, including Microsoft Jet Database Programmer's Guide (Microsoft Press), Access 97 Unleashed, Second Edition, and Microsoft Access 2000 Development Unleashed (Sams Publishing). He has written several magazine articles and is a contributing editor of Access/VB Advisor. Stephen also serves as president of the NYC Access and VB Developers group and travels around the world speaking at Microsoft Developer Conferences.

Tom Howe graduated from Northwestern School of Law in 1982 and has been a practicing attorney ever since. Having tired of the practice of law, he decided to join the most dynamic profession in the world-software development. So now he spend most of his time developing software and speaking at developer conferences. Tom specializes in application development using Microsoft Access, Visual Basic, Outlook, Exchange, and SQL Server. Tom is a regular speaker at TechEd, Advisor, Informannt and other developer conferences around the world.

Kurt Wall has been using and programming with Access and Visual Basic since 1994. In no particular order, he enjoys coffee, cooking, coding, staying up late, and sleeping even later, the latter of which makes any day job a challenge. When he gets tired of computers, he reads the occasional novel, historical fiction, political theory, American history, and cookbooks, and dreams of going to culinary school. Computers and cooking have nothing to do with his cum laude degree in American history from the University of Utah. A working stiff for nineyears, Kurt is now a freelance writer, editor, and consultant. While in the Land of Cubicles, Kurt has been Caldera Systems' technical publications manager, an enterprise application technician (pager slave) for Qwest, an Informix and Visual Basic programmer with Virtual Solutions, an EDI administrator (pager slave) for First Health Solutions, a lowly help desk grunt for First Health Solutions, and a network administrator for the Utah Division of State History.

Kurt is the author of Linux Programming Unleashed (first and second editions), and Linux Programming by Example. He is currently coauthoring Red Hat Linux Network and System Administration and Red Hat Linux Weekend Crash Course. Kurt has also contributed chapters to The Informix Handbook, The Linux Bible, forthcoming titles on Linux clustering, Linux Performance Tuning and Capacity Planning, UNIX Unleashed, and too many Caldera OpenLinux user guides, manuals, and white papers to count.

Kurt is also the technical editor for all or part of Practical Linux, Red Hat Administrator's Handbook, Linux Security Toolkit, Sams Teach Yourself KDE 1.1 in 24 Hours, Sams Teach Yourself Linux in 24 Hours, 2nd Ed., Linux: The Complete Reference, 4th Ed., Linux Database Bible, KDE 2.0 Development, Caldera OpenLinux Secrets, Linux Administrator's Bible, Caldera OpenLinux Bible, an upcoming book on Linux performance tuning and capacity planning, and other titles.

Kurt is the vice president of the Salt Lake Linux Users Group, former president of the Informix on Linux SIG of the International Informix Users Group, and former maintainer of the Informix on Linux FAQ.

Having survived Marine Corps boot camp, Kurt has an extreme dislike for talking about himself in the third person.

Paul Kimmel is the author of many books on Access programming and Visual Basic programming. Paul is a freelance contributing author to the CodeGuru Newsletter and is available for development work.

Russ Mullen has been using and programming with Access and Visual Basic for more years than he cares to remember. He has technically edited a number of books both on Access and Visual Basic. He enjoys getting up very early in the morning and coding with coffee in hand long before the sun rises; then, retiring early to prepare for the next day's work. In recent years he has been doing Web site design including databases.

Table of Contents

	Introduction	1
Part I	Database Design Unleashed	5
1	What's New for Developers in Access 2002	7
	User Interface Changes	8
	Changes to the VBE	9
	Compatibility with Access 2000 Databases	10
	Database Conversion Error Logging	11
	Offline Data Access Pages	11
	Improved Integration with SQL Server 2000	12
	Other Access 2002 Features	13
	Summary	14
2	Planning the Development Process	15
	Gathering Requirements	17
	Architecture	24
	Development Planning	24
	Construction	28
	Summary	43
3	Database Design and Normalization	45
	The Relational Design Theory	48
	Summary	58
4	Advanced Queries	59
	Using the Query Object	60
	Creating Queries in Access	62
	Creating Advanced Queries	74
	Mastering Totals Queries	75
	Mastering Crosstab Queries	83
	Mastering Parameter Queries	87
	Mastering Pass-Through Queries	91
	Mastering Data Definition Queries	93
	Summary	97
5	The Microsoft Jet Database Engine	99
	The History of Microsoft Jet	100
	Using Jet 4.0 Features in Your Applications	103
	Summary	116
Part II	Data Access	117
6	Introduction to ActiveX Data Objects	119
	History of Data Access	120
	Microsoft's Universal Data Access Initiative	121
	ActiveX Data Objects (ADO)	122
	The ADO Object Model	125
	Moving from DAO to ADO	142
	The ADO Object Model Compared to DAO	145
	Summary	146
7	Advanced ADO	147
	Using the OLE DB Provider for Jet in Access 2002	148
	Accessing Non-Relational Data with ADO	157
	Advanced Data Manipulation with ADO	162
	Data Definition with ADOX	167
	Summary	176
Part III	User Interfaces Unleashed	177
8	Advanced Form Design	179
	Form Properties	180
	Access Form Controls	189
	Creating PivotTable and PivotChart Views	197
	Summary	202
9	Enhancing Forms with ActiveX Controls	203
	How to Use ActiveX Controls	204
	Using ActiveX Controls	206
	21 ActiveX Controls	210
	Distributing ActiveX Controls	238
	Summary	239
10	Reporting Unleashed	241
	Approaching Reports	242
	Understanding the Architecture of Access Reports	242
	Building a Single Table Report Using the Report Wizard	244
	Customizing Reports	247
	Working with Subreports	256
	Creating Simple Mailing Labels	259
	Publishing a Report	262
	Modifying a Report at Runtime	266
	Building Reports Programmatically	274
	Tips and Tricks	279
	Summary	284
Part IV	VBA Unleashed	285
11	Creating Objects with Class Modules	287
	Exploring the Benefits of Using Objects	289
	Reviewing Objects, Properties, and Methods	291
	Creating Classes	292
	Creating Properties	292
	Creating Methods	299
	Creating Events	300
	Using Objects	302
	Creating Multiple Instances of an Object	304
	Examining More Object Examples	304
	Implementing an Error Handler Object	311
	Using Objects with VBA Collections	312
	Summary	316
12	Debugging Access Applications	317
	Eliminating Logic Errors	318
	Working with the Visual Basic Development Environment (IDE)	318
	Working with the Debug Object	322
	Using the Immediate Window	324
	Using the Debugger	326
	Using Conditional Compilation	331
	Application Testing	335
	Practice Debugging Techniques	335
	Summary	336
13	Professional Error Handling	337
	Eliminating Syntax Errors	338
	Eliminating Logic Errors	341
	Eliminating Runtime Errors	341
	Errors in Various Applications	364
	Error Handling With Nested Procedures	365
	Advanced Error Topics	365
	Summary	368
14	Application Optimization	369
	Strengthening the Foundation: Hardware and Windows Optimization	371
	Installing the Application for Optimal Performance	373
	Optimizing the Configuration of the Jet Database Engine	374
	Tools to Measure Performance	381
	Looking Behind the Scenes	383
	Optimizing the Database from the Start	384
	Boosting Query Performance	387
	Getting Forms to Run Faster	395
	Writing Fast Code	400
	Coding Tips and Hints	402
	Summary	410
Part V	Access Client Server	411
15	Introducing Access Data Projects and the Visual Tools	413
	Introducing Access Data Projects	414
	Using ADPs	415
	Working with ADPs and Existing SQL Server Databases	419
	Creating a Project Based on a New Database	428
	Summary	434
16	Developing Access Front-Ends to Microsoft SQL Server	435
	Client/Server Architecture: OLE DB Versus ODBC	436
	Setting Up Your SQL Server Front-End Connection	436
	Stored Procedures and SQL Pass-Through Queries	440
	Reporting Against SQL Server in Access 2002	443
	Using Forms in Your Application	448
	Advanced Features of the SQL Server OLE DB Provider	450
	Executing Commands with Parameters	452
	Using a Connection Class	459
	Summary	460
17	Access 2002 Front-Ends to Oracle	461
	Accessing Oracle Data with Access	462
	Functions in Oracle Versus Access	470
	Understanding Views and Stored Procedures	482
	Creating an Unbound Interface to Oracle	491
	Summary	502
Part VI	Interoperability	503
18	Using ActiveX Automation	505
	What Is ActiveX Automation?	506
	Why Use Automation?	506
	Distinguishing Automation Server Versus Automation Client	506
	Determining Automation Resource Requirements	507
	Understanding the Big Picture	507
	Creating and Setting a Reference to Another Application	507
	Assigning an Object Variable to an Application	512
	Creating an Instance of the Application	513
	Using the Automation Object's Properties and Methods	517
	Releasing the Automation Object	517
	Putting It All Together	518
	Closing the Automation Server Application	519
	Using the UserControl Property to Determine How an Application Was Opened	520
	Using WithEvents to Expose Events of the Automation Server	520
	Using the Automation Tips and Techniques	523
	Summary	527
19	Integrating with Microsoft Office	529
	Why Integrate with Microsoft Office?	531
	Use the Right Tool	533
	Using the Macro Recorder to Write Code	533
	Using Auto Macros	535
	Microsoft Forms	536
	Object Browser	537
	Class Arguments for Office Applications	537
	Automation Example	538
	Automating Word	540
	Automating Excel	555
	Automating PowerPoint	559
	Automating Outlook	562
	Automating Graph	567
	Summary	569
20	Using Visual Basic with Access	571
	Creating ActiveX Code Components	572
	Creating ActiveX Controls	593
	Summary	614
Part VII	Multiuser Issues	615
21	Multiple Users and Database Locking	617
	Understanding Multiuser Issues	618
	Reviewing Jet's Multiuser Design	619
	Understanding Jet's Multiuser Locking	619
	Selecting the Proper Architecture	623
	Working With Locks	623
	Optimizing Multiuser Applications	631
	Oracle/SQL Server Locking	632
	Summary	633
22	Security	635
	Elements of Security	636
	Workgroup Creation	638
	Users and Groups	641
	Implementing Security Using Startup Options	649
	Security Concerns with Replication	650
	Security for Split Databases	651
	Security for Client/Server	653
	Securing a Database Step by Step	663
	Common Security Errors	664
	Summary	665
Part VIII	Web Publishing with Access 2002	667
23	Configuring a Web Site for Web Publishing	669
	Development Versus Production Environment	670
	Choosing Your Platform	671
	What Is the Option Pack?	673
	Setting Up Your Web Server	675
	Managing and Configuring Your Web Server	682
	Securing Your Web Applications	692
	What Is the Difference Between a Site and a Virtual Directory?	693
	Summary	699
24	Web Enabling Access 2002 with Office Web Components	701
	What Are the Office XP Web Components?	702
	Using the Office Spreadsheet Control	703
	Using the Office Chart Control	707
	Using the Office PivotTable Control	711
	Summary	713
25	Using Data Access Pages	715
	Creating Your First Data Access Page	717
	Implementing Interactive Drill-Downs	722
	Incorporating the Office Web Components with DAPs	724
	Scripting Data Access Pages	726
	Summary	729
26	Web Publishing with Access 2002 and Active Server Pages	731
	Using Active Server Pages	732
	Understanding Active Server Pages	733
	Getting Started with Active Server Pages	734
	Active Server Pages 101	739
	Real-Life Example: Establishing a Self-Maintaining Membership-Based Web Page	754
	Access 2002 Web Publishing with XML	763
	Creating Charts with the Chart Control	770
	Summary	772
	Index	773