Lab #20

  1. We have looked at one SQL query, All Titles, in Access.  Which of the keywords from Fig 18.13 were used? SELECT, FROM, ORDER BY
    Select, From, and Order By

  2. Exercise 18.4 on p. 806 lists several “reports” that the author suggests that you could program as a query following the example of All Titles.   Write the required SQL statement for each here. SELECT AUTHORS.Author

FROM AUTHORS;
SELECT Publishers.*

FROM Publishers;
SELECT Authors.Author, Titles.Title, Titles.[Year Published], Titles.ISBN

FROM Authors INNER JOIN (Titles INNER JOIN [Title Author] ON Titles.ISBN = [Title Author].ISBN) ON Authors.Au_ID = [Title Author].Au_ID

WHERE (((Authors.Author)="Cox, Joyce"))

ORDER BY Titles.Title;
SELECT Authors.Author

FROM Authors;

 

SELECT Authors.Author, [Title Author].ISBN, Titles.Title, Titles.[Year Published]

FROM Titles INNER JOIN (Authors INNER JOIN [Title Author] ON Authors.Au_ID = [Title Author].Au_ID) ON Titles.ISBN = [Title Author].ISBN

ORDER BY Titles.Title;

 

SELECT Publishers.Name, Publishers.Address, Publishers.City, Publishers.State, Publishers.Zip, Publishers.Telephone, Publishers.Fax

FROM Publishers

ORDER BY Publishers.Name;

SELECT Publishers.Name

FROM Publishers;

 

SELECT Publishers.Name, Titles.Title, Titles.[Year Published], Titles.ISBN

FROM Publishers INNER JOIN Titles ON Publishers.PubID = Titles.PubID

ORDER BY Titles.Title;



  1. Test each of your SQL statements in Access (type the query into the SQL view screen and report on the result of the run)
    They all worked.  I built them with the Access design query function.

4.      Recall that the result of a query is a recordset in a work area of memory that can be further massaged by the control that owns it.  The recordset object has a current setting (a record pointer) and a variety of properties about the set as well as objects associated with the fields of the records.   Like the Seek command, the Recordset object can “positioned” at a particular record by using the Bookmark attribute.  The Recordset object can initialize a  Fields object that provides access to properties of the contents of the argument named for the current record (see p. 801).   The recordset thus is manipulated much like an array in memory except that it is nonhomogenous and it can be drastically changed by successive (chained) operations.    The technique that the author illustrates is the extraction of a recordset from a database and then the use of a control to perform manipulations on it.   Fig 18.15 is the first such example requiring more than setting the ADO and DataGrid properties to “channel” the data.   Fig 18.14 lists some Ado properties that emphasizes the ADO’s role in coordinating between the program and the database via the Recordset.   Notice that the ADO has little to do with either display or interpretation of the Recordset—other controls accomplish that (like the DataGrid).   The RecordSource property tells the ADO the criteria for the Recordset extraction.   The program in Fig 18.15 loads a value into this property and then calls the refresh method that causes the ADO to return to the database and extract the records meeting the criteria, reinitializing the Recordset object.   When this happens, other controls, such as the DataGrid, that are bound to the recordset are also “refreshed”.  The first program action is to load the form.  What happens here? THE PROCESS BEING DONE IS THAT REFRESH METHOD GIVES CONTROL BACK TO ADO CONTROLS.
Sets the txtUserQuery.Text and Adodc1.Caption are both set to Adodc1.RecordSource which is the text of the default query to be displayed.

 

  1. When is the second Subroutine called (note that it has only one statement in its body)?IT IS CALLED AFTER THE BUTTON IS CLICKED AND THE Adodc1.RecordSource = txtUserQuery.Text IS IMPLEMENTED  Do the Adodc1_MoveComplete arguments matter? YES, BECAUSE THERE USED IN THE CALL

The second Subroutine is called when the Command Button is clicked.  Yes, the arguments matter.  The program needs to know what type of data it is bringing into memory.

 

  1. Make this program run on your machine and supplicate the picture on p. 787.

      Done

 

  1. The FlexGrid Control example operates just as the DataGrid did.   Install it as instructed in Fig. 18.17 and execute the program and compare to the other control.

Seems to work virtually the same except for the column label to the left that remains fixed even when Grid is horizontally scrolled.

 

  1. The next example uses three controls that are data aware.   The first, the DataCombo, is initialized to read “Select an Author” and the user is invited to type or select from the list that drops down.   Where does this list come from?

SELECT AUTHOR, AU_ID FROM AUTHORS ORDER BY AUTHOR ASC

 DataCombo’s source is AdoAuthorNames and DataList’s source is adoBooks.

 

  1. When the user selects an author’s name. Nothing happens.  Why?

THE SYSTEM IS WAITING  FOR THE RETRIEVE BUTTON, TO BE PRESSED.

 There is no instructions for the selection event to produce any other actions.  The command button “Retrieve Books” is programmed to take action upon the click event.

  1. Why the single quotes in line 28 and lines 35? BOTH CONTAIN STRINGS

To ensure that the program, reads the information as string constant to finish its query.

 

 

  1. Where does the list in the listbox come from? THE RECORD SOURCE AND ALL INFO QUERY LIST.

DataSource: AdoBooks

 

  1. What happens when you click on any entry in the listbox? THE ISBN NUMBER APPEARS AND THEN THE TITLE, NAME, AND YEAR PUBLISHED COMES UP.

DataGrid1 displays Title, Name, and Year Published.

 

  1. set up and run Fig 18.20 by following the property specification in Fig 18.21. How many independent recordset objects are there in this application?

3  

There are 3 independent record sets.  One each for DataCombo1, DataList1, and DataGrid1.