sql及vba英文文献及翻译
Introduction Why SQL? We have demonstrated in this book how much of SQL script writing can be done through the Access query grid. In fact, the query grid is so easy to use that Microsoft has incorporated it into the SQL Server and is indicating that it is going to be the major way most future SQL will be done. Despite this trend, there is much still going for raw, text-based SQL. For starters, SQL is far easier to handle and manipulate than query grids. It also provides a degree of functionality that is not available to the query grid developer. In this chapter we will show how SQL is critical for Visual Basic development. The next chapter will continue this theme with a demonstration of how the coding of web Active Server pages can be enhanced using SQL.
Definitions
Recordset — A collection of records in Visual Basic programming.
VBA — Visual Basic for Applications. The flavor of Visual Basic incorporated in Access and in much of the Microsoft Office suite.
This chapter will assume that you are familiar with Access programming and that you know your way around modules and basic Visual Basic code. It also assumes that you have a good understanding of items and properties of those items. In particular, we will be concentrating on the properties of forms and combo boxes and how you can set some of these properties dynamically using code. Before some of you begin to panic, we promise to keep things as simple as possible to make our points. On the other hand, if you have made it this far, you have a desire to learn SQL, and what better reason for this than to improve your Visual Basic programming ability?
Fixed Queries vs. “On-the-Fly” Queries
The first reason for developing queries dynamically rather than building them in the query grid and storing them is a simple matter of logistics and aesthetics. Access is a very powerful program. It permits the user to develop queries to do just about anything. Unfortunately, as powerful as the query development tools are, the management and organization of the queries leaves much 原文请找腾讯752018766优,文-论~文;网http://www.youerw.com . To see how far we should have come in Access, we need to go back to the early days of DOS. In those early days, all files on storage media were kept in a single list on the media. In the case of floppy disks, each floppy would have a single directory and all files would be in the directory. While simple and straightforward, the lone directory could have hundreds of files, which in turn could be associated with multiple applications. It was the responsibility of the operator to know which files were associated with each application and to keep things straight. Generally the operator did not keep up with this responsibility, which resulted in chaos.
This problem was alleviated with the introduction of cascading directory trees. With directories, files could be grouped and put together with related files separate from nonrelated files. For example, a data directory could be set up to contain data. A template directory could be set up to hold all the templates associated with a program. Finally, a program directory could be set up to contain the actual program files. Directories could be placed in other directories, establishing a hierarchal system to manage all files on the media.
Unfortunately, Access has never gotten past the initial stage of putting forms in one container, modules in a second container, tables in a third container, and so on. There is no provision to group queries based on function or tables based on contents. The net effect is that if you have a hundred queries, they will all be in a single list. There could be a dozen queries that are performing similar tasks, but just like in the early days of computers, there is no real way to organize the queries other than by careful user-managed naming conventions. Until Access provides a better way of organizing queries, one of the tricks that the programmer can implement is to reduce the number of needed queries, thereby simplifying the organization of the remaining queries.
This is where SQL enters the picture. One of the easiest ways to avoid having queries appear in the list of queries is to build the queries dynamically in code rather than by having each query stored in the query list. By entering query operations as inline code rather than as separate, unique queries, you have fewer queries, which are far easier to manage.
This is just the first of many reasons for building queries dynamically in code and creating them on the fly rather than to have them permanently saved in the query list. But this is by far the most important reason. We will introduce a few more reasons as this chapter progresses.
Using our earlier example of the Customers table, let us first add a few records to the table to give us a larger number of records with which to work. This will provide us with additional filtering capabilities and show off a few additional features of filter parameters.1661
[1] [2] [3] [4] [5] [6] [7] 下一页