- Shopping Bag ( 0 items )
Showing how to use VBA to design efficient, user-friendly end-user applications in Access, this text gives programmers and advanced Access users the knowledge they need to master the power within Access - the world's most widely used database management system.
• As part of the Power Pack family of books, this book gives programmers and advanced Access users the knowledge they need to master the power within Access-the world's most widely used database management system
• Shows how to use VBA to design efficient, user-friendly end-user applications in Access
• Starts with database concepts and event-driven programming, then covers VBA programming fundamentals, the Visual Basic Editor, VBA language elements, and code debugging
• Covers advanced Access programming techniques like connecting to databases with ADO and interacting with other applications
• Includes helpful discussions on compatibility issues and manipulating Visual Basic components
• Companion Web site includes all the code examples in the book Microsoft Access is a database management system that lets database novices and experienced programmers view and analyze data and build powerful, custom database solutions that integrate with the Web and enterprise data sources. VBA is a sophisticated set of programming tools, which developers can use to harness the power of packaged applications. Everything you need to know about Using modules, procedures, and functions Coding with Visual Basicr Editor Understanding Access events and interacting with other applications Programming menus and toolbars Developing user-oriented and multi-user applications Manipulating Visual Basicr components Here's how to harness the power of VBA Visual Basic for Applications (VBA) is power-packed. This book gives you command of that power to build sophisticated applications in the perfect development environment Access provides. First, you'll get VBA programming fundamentals and an understanding of
Part I: Access Application Development.
Chapter 1: Access Application Development.
Chapter 2: Essentials of Database Application Development.
Chapter 3: Access Application Development without VBA.
Chapter 4: Customizing Menus and Toolbars.
Part II: Understanding Visual Basic for Applications.
Chapter 5: Introducing Visual Basic for Applications.
Chapter 6: VBA Programming Fundamentals.
Chapter 7: The Visual Basic Editor.
Chapter 8: Using VBA Language Elements.
Chapter 9: Building a Sub Procedure.
Chapter 10: Running and Debugging VBA Code.
Chapter 11: Creating Function Procedures.
Part III: Programming User Interactions with VBA.
Chapter 12: Building User Interaction.
Chapter 13: Programming Menus and Toolbars.
Chapter 14: Changing the Workplace with VBA.
Part IV: Advanced Access Programming Techniques withVBA.
Chapter 15: Connecting to Databases with ADO.
Chapter 16: Access Events.
Chapter 17: Interacting with Other Applications.
Chapter 18: XML Support.
Chapter 19: Add-Ins.
Part V: Developing Applications with VBA.
Chapter 20: Developing User-Oriented Applications.
Chapter 21: Developing Multi-User Applications.
Part VI: Other Important Topics.
Chapter 22: Compatibility Issues.
Part VII: Appendixes.
Appendix A: Helpful Web Links.
Appendix B: VBA Statements and Functions.
Appendix C: VBA Constants.
Appendix D: VBA Error Codes.
Appendix E: ANSI Codes.
IN THIS CHAPTER
* The various versions of Access and how they differ
* Developing database applications with Access
* The future of Access
Microsoft Access was introduced as a desktop database management system (DBMS) in 1992, and in the years that followed, became a leader in its category. As a part of the popular Microsoft Office suite of applications, Access is used daily by millions of people. Since its inception, Access has grown progressively more capable and easier to use. The most recent versions, while retaining their position as the most popular desktop DBMS in the world, enable developers to build applications that access information from large networked client/server databases based on Microsoft's enterprise scale SQL Server client/server DBMS.
Although many people use Access to perform relatively simple data management tasks that don't require any kind of programming, the Visual Basic for Applications (VBA) language gives Access the power to host systems that are customized to meet the exact needs of a wide variety of organizations, regardless of their size or the kinds of tasks that they need to perform.
The purpose of this book, after laying a little groundwork, is to take you from a non-programming Access power user to a developer who is comfortable using the full power of VBA to buildsophisticated applications that execute complex tasks while making things easy for the user.
Access 1.0 appeared in 1992, shortly after the appearance of Microsoft Windows. Access 1.1 came along in 1993, and in 1994, Access 2.0 arrived on the scene. Each new version was an incremental improvement that built on the strengths of the prior versions. Access 95 followed in 1995, Access 97 in 1997, Access 2000 in 1999, and Access 2002 in 2001. With each new version, Access has grown easier to use and has expanded its data-handling capability.
Access 1.0, 1.1, and 2.0
When Access 1.0 first saw the light of day, it introduced features that were new to personal database products, or for that matter, any kind of database products:
* OLE technology in Table Designer enabled users to create databases that contained graphical images and audio and video files, in addition to text and numbers. OLE stands for object linking and embedding, which is rather descriptive of what OLE does. Users were even able to incorporate Word documents and Excel spreadsheets into an Access database.
* The Forms Package enabled users to create custom forms by dragging form elements onto a blank page. This was a major step forward in ease of use.
* The Report Wizard made report creation equally easy.
* The drag-and-drop Query Designer used query by example (QBE) technology to enable users to create queries without writing program code.
* The Graph Wizard turned chart production into an intuitive step-by-step process.
* In addition to its native files, Access 1.0 could also operate on dBASE, Paradox, and Btrieve files with native drivers, as well as others, such as Sybase SQL Server, DEC Rdb, and FoxPro) via an ODBC interface.
ODBC stands for Object DataBase Connectivity, and provides a common application programmer's interface (API) to a large number of different and mutually incompatible databases.
Access 1.1 added the following improvements:
* Enhanced ODBC and Btrieve support.
* Improved connectivity to FoxPro.
* Easy data export to Word for mail-merge applications.
* Database sizes up to 1GB (about eight times larger than 1.0 could handle).
* An Access runtime became available. Developers could create applications that could operate without Access installed on the system.
Access 2.0 added more capabilities:
* Wizards that automated tasks that had previously required coding on the part of the application developer.
* More sophisticated event handling.
* A debugger in the development tools, plus an automatic documenter and an Add-in Manager.
* Jet database engine improvements, allowing more complex databases to be built and making queries run over twice as fast.
Access 95 was the first version to officially be a part of Microsoft Office. Its controls were brought into conformance with the other members of the Office application suite.
* VBA was one of several development tools that Access shared with other Office programs, such as Word and Excel.
* The Import Wizard and the PivotTable Wizard were added.
* The Form and Report Wizards were improved.
Access 97 became more Web-aware:
* It stored hyperlinks as a native data type and allowed saving to HTML. Users could publish database objects to the Web, and extract data from an HTML document that contained a table, and then put the data into a database table.
* Class modules could contain the definition of a new object.
* Procedures in a module could hold the properties and methods of that object.
* Conditional compilation enabled developers to create both debug and production compilations of their applications by setting flags in the code.
* Developers could remove source code from an application, thus speeding execution and preventing people from viewing and copying it.
* New ActiveX controls were introduced, and compatibility with source code control systems was added to the Developer Edition of Office 97.
Many aspects of Access were enhanced for this version, improving performance and expanding capability. This Access version introduced these features:
* ActiveX Data Objects (ADO) replaced practically all the data access functions that were previously performed with Data Access Objects (DAO).
* Access Project (.adp) files work with SQL Server and Microsoft Data Engine files as smoothly as .mdb files work with the Jet engine.
* Data access pages (DAPs) were introduced, enabling you to put things that look like Access forms and reports on the Web.
The previous Access release added these features:
* The Data Access Page Designer was improved with these features:
* Multi-level undo and redo
* Better grouping with multi-table data entry
* Enhanced support for the SQL Server Desktop Engine was included, plus cascading updates and deletes.
* The Upsizing Wizard converted applications written to drive the Jet engine so they work with the SQL Server Desktop Engine.
* XML support was added.
New features of Access 2003 include:
* Improved ease of use
* Expanded ability to import, export, and work with XML data files
* Flagging of common errors
* Identification of object dependencies
Access as an Application Development Environment
Access provides a particularly congenial application development environment for several different classes of developers. It is congenial for the following reasons:
* Low barrier to entry. You can start developing useful applications with a minimum of study or training.
* Intuitive. As you use Access more and increase your proficiency, you can tackle more complex assignments, thus using more of Access's power.
* Part of the Office suite. Access can draw on the capabilities of the other members of Microsoft Office, such as Word and Excel.
* Help from wizards. The many wizards that automate a wide variety of tasks can dramatically reduce development time.
* Tight integration with Microsoft SQL Server. Because of this, Access is applicable to a broad range of development tasks, from the smallest personal database application to enterprise-wide applications for large multinational firms. Other DBMS products typically aim for one end of the size spectrum or the other.
The difference between databases and database applications
Sometimes people fail to make the distinction between databases and database applications. This is particularly true of people who "learn by doing" with Access rather than of those who take a formal course in database theory. Access lumps database development and database application development together, with no apparent separation of the two:
* A database is a self-describing collection of integrated records. It is self-describing because the information that describes the structure of the database is included in the database. The records are integrated because relationships between data items are explicitly maintained. These two characteristics differentiate a database from a mere collection of data items.
A database is a structured collection of data. By itself, it doesn't solve any problem or serve any purpose. The database application uses the data in the database to solve problems or to accomplish a purpose.
* A database application is a program that works with a database to maintain and deliver desired information. A database application developer may write the database application or it may be generated semi-automatically by a DBMS, such as Access.
In a relational database, the data is stored in tables, which give the data its structure. A database application may include forms and reports that enable users to interact with the data in the database, with the objective of accomplishing some purpose.
The Database window
When you enter Access and specify the name of the database that you want to work on, the Database window shown in Figure 1-1 is displayed.
The column shown on the left lists the kinds of objects that it deals with: tables, queries, forms, reports, pages, macros, and modules.
For example, if Tables is selected, the pane on the right displays the names of all the tables in the current database plus three options for creating tables, either in Design View, with a wizard, or just by entering data. Similarly, selecting Forms displays the names of any forms that have been created, and gives you the option of creating a new form either in Design View or by using a wizard. Access makes it supremely easy to create all the different database objects that you may need for your application.
Because this is an advanced book on programming with Access, we assume that you can already create a table using Design View. Figure 1-2 shows an example of Design View after entering the specification for the first field of a table. You enter the field name, data type, and description of the field, and then specify some of its properties, such as field length and whether the field is indexed. After you have done this for all the fields in a table, name and save the table. That is all that's involved in creating a database table. Of course, we assume that you have already carefully thought about what fields should be included in the table, what their data types should be, what properties the fields should have, and how this table relates to other tables in the database.
After you have created the tables, you have created the database skeleton or structure. All you need to do now to complete the database is to fill it with data. You can do this in several ways, including through a data-entry form, by typing directly into the table, or by filling the table automatically from an existing file.
The following sections are about creating parts of a database application (forms, reports, data access pages, and so forth), and are not about creating the database itself. Unlike tables, forms, reports, and data access pages are all involved in performing specific tasks or answering specific questions.
For most applications, users see and interact with forms. Access forms are flexible tools. Forms can be different sizes, and they can contain such objects as controls, text, and graphics. For example, you can
* Create switchboard forms that control navigation around an application from one screen to another.
* Use a form to
* Enter data into one or more database tables
* Modify existing table data
* Delete data from tables
* View the data in one or more tables
Figure 1-3 shows a blank form.
The report creation process is similar to the form creation process. The main difference is that reports are more likely to be multi-page documents, and as a result, are more likely to be divided into sections, such as report header and footer, page header and footer, detail section, and group sections. Forms can also have form and page headers and footers, but often do not. Figure 1-4 shows the Design View of a blank report. You can add controls and other objects from the toolbox to appropriate sections of the report to achieve the report that you want.
Creating data access pages
Data access pages, introduced in Access 2000, provide an easy way to display information on the Web that on a local machine would be contained in forms and reports. Data access pages are HTML files that are bound to data in a data source. With data access pages you can
* Browse through the records in a data source
* Add, modify, and delete data
* Display the data either sorted or grouped
You can display database data on the Web in other ways, but data access pages, which are specifically designed for the task, provide an easy way to put your data online.
Macros are simple constructs for executing a sequence of operations. Before VBA became available to Access developers, macros provided the only way to automate execution. Now, there is little reason to use macros for any serious development. The many advantages of VBA make it the preferred alternative.
Excerpted from Access 2003 Power Programming with VBA by Allen G. Taylor Virginia Andersen Excerpted by permission.
All rights reserved. No part of this excerpt may be reproduced or reprinted without permission in writing from the publisher.
Excerpts are provided by Dial-A-Book Inc. solely for the personal use of visitors to this web site.