后勤工程学院本科毕业设计（论文）
入库单详细信息表 列名 入库单号 编号 供应商编号 物资编号 数量 单价 用户信息表 列名 用户名 密码 真实姓名 角色 出库单表 列名 出库单号 操作员 建単时间 审核时间 出库时间 状态 备注
出库单详情表 列名 出库单号 编号 库户编号 物资编号 数量 单价
数据类型 nchar（20） int nchar（10） nchar（10） nchar（10） money 是否主键 是 是 否 否 否 否 是否为空 否 否 否 否 是 是 数据类型 nchar（20） nchar（10） smalldatetime smalldatetime smalldatetime smallint nchar（50） 是否主键 是 否 否 否 否 否 否 是否为空 否 否 是 是 是 是 是 数据类型 nchar（10） nchar（20） nchar（16） nchar（10） 是否主键 是 否 否 否 是否为空 否 是 是 是 数据类型 nchar（20） int nchar（6） nchar（50） nchar（10） money 是否主键 是 是 否 否 否 否 是否为空 否 否 否 否 是 是 36
后勤工程学院本科毕业设计（论文）
物资详情表 列名 物资编号 品名 重量 单位 物资单价 屋子类型 包装规格 库存总量 货位表 列名 库位编号 列数 行数 层数 状态 货位信息表 列名 库位编号 物资编号 供应商信息表 列名 供应商编号 供应商名称 联系人 地址 邮编 电话 备注
数据类型 nchar（6） nchar（20） nchar（16） nchar（40） nchar（6） nchar（13） nchar（50） 是否主键 是 否 否 否 否 否 否 是否为空 否 是 是 是 是 是 是 数据类型 nchar（4） nchar（10） 是否主键 是 是 是否为空 否 否 数据类型 nchar（4） nchar（2） nchar（2） nchar（2） bit 是否主键 否 否 否 否 否 是否为空 否 否 否 否 否 数据类型 nchar（10） nchar（16） nchar（10） nchar（10） nchar（10） nchar（10） nchar（10） nchar（10） 是否主键 是 否 否 否 否 否 否 否 是否为空 否 否 否 否 否 否 否 是 37
后勤工程学院本科毕业设计（论文）
附录2 英文文献
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 . 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
38
后勤工程学院本科毕业设计（论文）
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 Now that we have added these records, assume that you want to display only those people who are in Florida. One method of addressing this requirement is to build and save a special query where the state equals Florida. A second method of doing this is to build the recordsource for the form using the builder (the ellipsis that appears after the drop-down). Using the builder you can select the recordsource as the table Customers and add your filter for the state .What happens when you save this query is interesting. Access evaluates what you have entered in the query grid and automatically saves it in the recordsource as an SQL query.This brings up several other possible uses for Access SQL. The first is that you can type the SQL directly into the recordsource instead of going to the query builder. Sometimes this is a far faster way of entering the recordsource. Second, if you have a form that already has the recordsource that you want to use for your current form, copy the SQL code from the first form and paste it into the second form. This is often much quicker than building a query from scratch..Sidebar: The concept of viewing the query as an SQL statement also gives the designer a great degree of flexibility that does not exist in the query grid. If you wish to take a recordsource from one form and copy it to another
39
后勤工程学院本科毕业设计（论文）
form but have the additional complication of having the recordsource being a different but similar table, you can copy the SQL string into a text editor such as Microsoft Word. You can then use search and replace to change the initial table to the new table using the global search and replace feature. This is extremely useful if you have a very complex query and don’t wish to recreate everything. A quick search and replace followed by cutting and pasting the result back into Access completes the operation. Unfortunately, Access does not have a convenient way of doing this. The Access text editing capabilities are more primitive than even those of Notepad. Filtered Recordsets for Forms. Now that we have shown that the recordsource of a form is just an SQL statement, we can make the leap to dynamically set up the query as needed. Dynamic queries are extremely useful when working with a filtered recordset in a form or report. Just like with every other task in Access, there are many ways to filter a recordset. First, you can enter the filter when opening a form. We will demonstrate this with the frmCustomers form in the sample database (see Figure 14-4). Selecting a record on the Customers for that customer. If there is only one transaction, frmTransactions will only have one record to display. If there is more than one transaction, the user is able to move forward and backward through the records.
So much for the overview — now we will get into the fun stuff! The first button, Open with filter, is the equivalent of setting the filter parameter of the recordset to the entered value. Take a look at the code behind this button:
In effect, this code opens the frmTransactions form and sets the Filter property of the form. In the example above, when the user selects the customer with the ID of 1, the filter string ―customerID =1‖ is placed into the Filter property .
The one problem with this approach is that you generally want to empower the user to perform additional filters with the data. If the user enters a new filter via the Filter button or through filter by form, the new filter will overwrite the one you had carefully built and will change the list of records displayed with no obvious way to get back to the initial filter set. For example, selecting ―filter by selection‖ when the date sold of 2/8/2003 is selected produces the filter shown in Figure 14-6. You get the records that you want but you have to tread on shaky ground.
If the user removes the filter to try to go back to the full set of pertinent records, the results are not what is expected. The resulting display will have all the records, not just the ones that meet your original customer filter, as shown in .
Let’s take a step back for a moment and look at this problem in a bit more detail. You really don’t want the user to be able to view the full set of records, no matter what filters he chooses to set up. You want the user to only have access to the records you want him to see. This is best accomplished by setting the recordsource of the form to a recordset that only has the values that you want.
Looking at the properties of our form, notice that the Recordset property is either a table or a query expressed as an SQL statement. We have the ability to change this property and when doing so we can change the collection of records that the form uses. We do this by opening the form, then setting the Recordsource property to a filtered query. Since the form is not filtered by the use of the Filter property, clearing the filter will not affect our dataset. The user is limited to the records we give him
40