Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] - نسخه متنی

Alison Balter

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید



Action Queries


With Action queries, you can easily modify data without writing any code. In fact, using Action queries is often a more efficient method than using code. Four types of Action queries are available: Update, Delete, Append, and Make Table. You use Update queries to modify data in a table, Delete queries to remove records from a table, Append queries to add records to an existing table, and Make Table queries to create an entirely new table. Each type of query and its appropriate uses are explained in the following sections.

Update Queries


You use Update queries to modify all records or any records meeting specific criteria. You can use an Update query to modify the data in one field or several fields (or even tables) at one time (for example, a query that increases the salary of everyone in California by 10%). As mentioned, using Update queries is usually more efficient than performing the same task with Visual Basic for Applications (VBA) code, so Update queries are considered a respectable way to modify table data.

To build an Update query, follow these steps:


  • Click Queries in the Objects list from the Database window.

  • Double-click the Create Query in Design view icon.

  • In the Show Table dialog box, select the tables or queries that will participate in the Update query and click Add. Click Close when you're ready to continue.

  • To let Access know you're building an Update query, open the Query Type drop-down list on the toolbar and select Update Query. You can also choose Query, Update Query from the menu.

  • Add fields to the query that will either be used for criteria or be updated as a result of the query. In Figure 11.1, StateProvince has been added to the query grid because it will be used as a criterion for the update. DefaultRate has been included because it's the field that's being updated.

    Figure 11.1. An Update query that increases the DefaultRate for all clients in California.

  • Add any further criteria, if you want. In Figure 11.1, the criterion for StateProvince has been set to CA.

  • Add the appropriate Update expression. In Figure 11.1, the DefaultRate is being increased by 10%.

  • Click Run on the toolbar. The message box shown in Figure 11.2 appears (see the section "Special Notes About Action Queries" later in this chapter for how to suppress this message by programming.) Click Yes to continue. Jet updates all records meeting the selected criteria.

    Figure 11.2. The confirmation message you see when running an Update query.


  • You should name Access Update queries with the prefix qupd. To adhere to standard naming conventions, you should give each type of Action query a prefix indicating what type of query it is. Understanding SQL" section.) You can display the SQL for a query by selecting SQL view from the View drop-down on the toolbar. The SQL behind an Access Update query looks like this:

    UPDATE tblClients SET tblClients._
    DefaultRate = [DefaultRate]*1.1
    WHERE (((tblClients.StateProvince)="CA"));

    CAUTION

    The actions taken by an Update query, as well as by all Action queries, can't be reversed. You must exercise extreme caution when running any Action query.

    CAUTION

    It's important to remember that if you have turned on the Cascade Update Related Fields Referential Integrity setting, and the Update query modifies a primary key field, Jet updates the foreign key of all corresponding records in related tables. If you have not turned on the Cascade Update Related Fields option and referential integrity is being enforced, the Update query doesn't allow the offending records to be modified.

    Delete Queries


    Rather than simply modify table data, Delete queries permanently remove from a table any records meeting specific criteria; they're often used to remove old records. You might want to delete all orders from the previous year, for example.

    To build a Delete query, follow these steps:


  • While in a query's Design view, use the Query Type drop-down list on the toolbar to select Delete Query. You can also choose Query, Delete Query from the menu.

  • Add the criteria you want to the query grid. The query shown in Figure 11.3 deletes all time cards more than 365 days old.

    Figure 11.3. A Delete query used to delete all time cards entered more than a year ago.

  • Click Run on the toolbar. The message box shown in Figure 11.4 appears. You can suppress this message using the programming techniques explained later in the section "Special Notes About Action Queries."

    Figure 11.4. The Delete query confirmation message box.

  • Click Yes to permanently remove the records from the table.


  • The SQL behind a Delete query looks like this:

    DELETE tblTimeCards.DateEntered
    FROM tblTimeCards
    WHERE (((tblTimeCards.DateEntered)<Date()-365));

    NOTE

    It's often useful to view the results of an Action query before you actually change the records included in the criteria. To view the records affected by the Action query, click the Query View button on the toolbar before you select Run. All records that will be affected by the Action query appear in Datasheet view. If necessary, you can temporarily add key fields to the query to get more information about these records.

    CAUTION

    Remember that if you have turned on the Cascade Delete Related Records Referential Integrity setting, Jet deletes all corresponding records in related tables. If you have not turned on the Cascade Delete Related Records option and you are enforcing referential integrity, the Delete query doesn't allow the offending records to be deleted. If you want to delete the records on the one side of the relationship, you must first delete all the related records on the many side.

    Append Queries


    With Append queries, you can add records to an existing table. This is often done during an archive process. First, you append the records to be archived to the history table by using an Append query. Next, you remove them from the master table by using a Delete query.

    To build an Append query, follow these steps:


  • While in Design view of a query, use the Query Type drop-down list on the toolbar to select Append Query or choose Query, Append Query from the menu. The dialog box shown in Figure 11.5 appears.

    Figure 11.5. Identifying the table to which data will be appended and the database containing that table.

  • Select the table to which you want the data appended.

  • Drag all the fields whose data you want included in the second table to the query grid. If the field names in the two tables match, Access automatically matches the field names in the source table to the corresponding field names in the destination table (see Figure 11.6). If the field names in the two tables don't match, you need to explicitly designate which fields in the source table match which fields in the destination table.

    Figure 11.6. An Append query that appends the TimeCardID, EmployeeID, and DateEntered of all employees entered in the year 1996 to another table.

  • Enter any criteria in the query grid. Notice in Figure 11.6 that all records with a DateEntered in 1996 are appended to the destination table.

  • To run the query, click Run on the toolbar. The message box shown in Figure 11.7 appears.

    Figure 11.7. The Append Query confirmation message box.

  • Click Yes to finish the process.


  • The SQL behind an Append query looks like this:

    INSERT INTO tblTimeCardsArchive ( TimeCardID, EmployeeID, DateEntered )
    SELECT tblTimeCards.TimeCardID, tblTimeCards.EmployeeID,
    tblTimeCards.DateEntered
    FROM tblTimeCards
    WHERE (((tblTimeCards.DateEntered) Between #1/1/1996# And #12/31/1996#));

    Append queries don't allow you to introduce any primary key violations. If you're appending any records that duplicate a primary key value, the message box shown in Figure 11.8 appears. If you go ahead with the append process, only those records without primary key violations are appended to the destination table.

    Figure 11.8. The warning message you see when an Append query and conversion, primary key, lock, or validation rule violation occurs.


    Make Table Queries


    An Append query adds records to an existing table, but a Make Table query creates a new table, which is often a temporary table used for intermediary processing. You will often create a temporary table to freeze data while a user runs a report. By building temporary tables and running the report from those tables, you make sure users can't modify the data underlying the report during the reporting process. Another common use of a Make Table query is to supply a subset of fields or records to a user.

    To build a Make Table query, follow these steps:


  • While in the query's Design view, use the Query Type drop-down list on the toolbar to select Make Table Query or choose Query, Make Table Query from the menu. The dialog box shown in Figure 11.9 appears.

    Figure 11.9. Enter a name for the new table and select which database to place it in.

  • Enter the name of the new table and click OK.

  • Move all the fields you want included in the new table to the query grid. The result of an expression is often included in the new table (see Figure 11.10).

    Figure 11.10. Add an expression to a Make Table query.

  • Add the criteria you want to the query grid.

  • Click Run on the toolbar to run the query. The message shown in Figure 11.11 appears.

    Figure 11.11. The Make Table query confirmation message box.

  • Click Yes to finish the process.


  • If you try to run the same Make Table query more than one time, the table with the same name as the table you're creating is permanently deleted (see the warning message in Figure 11.12).

    Figure 11.12. The Make Table query warning message displayed when a table already exists with the same name as the table to be created.


    The SQL for a Make Table query looks like this:

    SELECT tblTimeCards.TimeCardID, tblTimeCards.EmployeeID,
    tblTimeCards.DateEntered,
    INTO tblOldTimeCards
    FROM tblTimeCards
    WHERE (((tblTimeCards.DateEntered) Between #1/1/1995# And #12/31/1996#));

    Special Notes About Action Queries


    Additional warning messages, such as the one shown in Figure 11.13, appear when you're running Action queries from the Database window or using code. You can suppress this message, and all other query messages, programmatically by using the SetWarnings method of the DoCmd object. The code looks like this:

    DoCmd.SetWarnings False

    Figure 11.13. A warning message you might see when running an Action query from code.


    CAUTION

    I strongly suggest that you use Docmd.SetWarnings True to restore the warning messages immediately after running your Action query. Failure to do so could result in accidental deletion or alteration of data at some later time, without any warning.

    To suppress warnings by modifying the Access environment, choose Tools, Options and click the Edit/Find tab. Remove the check mark from the Action Queries Confirm check box.

    NOTE

    There's a major difference between suppressing warnings by using the DoCmd object and doing so by choosing Tools, Options. Suppressing warnings by using the DoCmd object centralizes control within the application. On the other hand, using Tools, Options to suppress warnings affects all applications run by a particular user.

    Using Action Queries Versus Processing Records with Code


    As mentioned, Action queries can be far more efficient than VBA code. Take a look at this example:

    Sub ModifyRate()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    With rst
    .CursorType = adOpenKeyset
    .LockType = adLockOptimistic
    .Open "tblEmployees", CurrentProject.Connection
    Do Until .EOF
    !BillingRate = !BillingRate + 1
    .Update
    .MoveNext
    Loop
    End With
    End Sub

    This subroutine uses ActiveX Data Objects (ADO) code to loop through tblEmployees. It increases the billing rate by 1. Compare the ModifyRate subroutine to the following code:

    Sub RunActionQuery()
    DoCmd.OpenQuery "qupdBillingRate"
    End Sub

    As you can see, the RunActionQuery subroutine is much easier to code. The qupdBillingRate query, shown in Figure 11.14, performs the same tasks as the ModifyRate subroutine. In most cases, the Action query runs more efficiently.

    Figure 11.14. The qupdBillingRate query increments the BillingRate by 1.


    Chapter 14, "What Are ActiveX Data Objects and Data Access Objects, and Why Are They Important?"


    / 544