All the examples below use the object variable "rs" as this instance. The data fields can be addressed by index number, name, or variable as shown in the five columns below. Find "Last Name='Jones'", , ad Search Forward ' string constant - ADO Of course you will rarely write programs that use only constants.
The Smith and Sussman textbook uses "rec" but you can use any variable name. There is an alternate syntax, shown next, for referencing fields that is faster. Most of the time your programs will use variables instead.
Although the best-practice method is often unclear to students, in larger commercial systems action queries would be preferred to recordsets because of automatic optimization of all SQL processing in those systems.
Recordset programming provides a more visible step by step procedure-based method for performing these same tasks, and in fact a combination of the two methods can be highly efficient, too.
Because they are organized into functional topics and kept very short, none of the examples can stand alone. A minimum program would need to use a combination of the "Opening", "Displaying" and "Closing" topics.
You can cut and paste the short program topics into a working program.
Use a reduced SQL statement to perform the initial part of the job. This job entails sequencing the male records by salary (use SQL) and finding the record at the top and bottom of the salaries (use recordsets). General Objective: for any recordset programming problem you should use SQL to open the minimum number of records and fields in the best sequence to accomplish the job.
Your program will run quickest with the smallest amount of data, and the fewest number of steps.
If your programming objectives cannot be distilled into a single SQL statement, and if you are inclined to use step-by-step programming methods, consider blending SQL and recordset programming. Find the hire dates and current salaries of the male staff who have the smallest and largest paychecks. Your program will run quickest with the smallest amount of data, and the fewest number of steps.Execute "UPDATE tbl People SET Last Name = 'Smith-Jones' WHERE First Name = 'Mary' AND Last Name = 'Jones';" ' note: this statement works only for DAO Current DB.Execute "INSERT INTO tbl People (pk People ID, Last Name, First Name, Hire, Salary, Sex) VALUES (101,'Smith-Kline','Mary',#11/01/00#,50000,'F')" 'Here the SQL used is an action query that will 'add a new record (in this case with the name /Nick Jones/ but this 'information is obviously just for the example).We obtain reports from the CMDB in CSV format, and link them to Access as well. We then use a combination of Access VBA and predefined queryies to add new data, or update or soft delete existing data. Specifically, inserts/soft deletes seem to work, but Access exhausts resources and crashes when running the update query. Close Set o Qdf Updt = Nothing Set o Rs = Current Db. Open Recordset(db Open Dynaset, db Read Only) Set o Qdf Updt = Current Db. Find First "RYCID = '" & cur FSCID & "'" End If ' sleep .1 seconds to avoid overloading the upstream workflow Sleep SLEEPTIMEINMILLIS o Rs. Close Set o Rs = Nothing Set o Qdf Updt = Nothing Debug. And there is an example below of combining SQL and recordsets. Execute "UPDATE tbl People SET Is Selected = No;" Current DB.Execute "UPDATE tbl People SET Salary = Salary*1.03 WHERE Sex='F';" Current DB.The recordset interface is referred to as Active X Data Objects (ADO).It requires a different syntax than DAO, but your experience with DAO will transfer nicely, and it has a much more powerful and consistent toolkit for accessing today's variety of PC and mainframe database formats on the Web.In Access create a new blank table and highlight 8 datasheet columns.Paste the example data into the table and rename the fields to the names above. If you have the examples only in hard copy, you would have to re-type the data in an Access table. With this Lock Type you can only scroll forward through records. Like a dynamic cursor, except that you can't see records that other users add, although records that other users delete are inaccessible from your recordset. This Lock Type indicates pessimistic locking, record by record. Find Last "pk People ID=2" ' numeric constant - DAO rs.