These examples provide DAO programmers with a brief reference to ADO syntax.
You are the correct audience for this page if you are using ASP recordset programming. The next generation recordset interface is referred to as ActiveX 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.
Because they are organized into functional topics and kept very short, none of the examples can stand alone. They won't run unless you combine them. 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. And you can copy and paste the data used in the examples from the table below.
pkPeople ID FirstName LastName Hire Review Salary Sex IsSelected 1 Carla Dumont 9/4/87 10/2/99 $60,249.82 F Yes 2 Andrew Frank 2/9/97 2/9/99 $55,081.10 M Yes 3 Janet Lydell 6/25/94 6/25/99 $49,875.00 F No 4 Margo Oniell 1/16/94 7/16/99 $77,629.58 F Yes 5 Edward Jones 1/17/98 9/17/99 $40,163.31 M No 6 Harry Jones 9/22/78 10/1/99 $103,500.00 M Yes 7 Jane Doe 8/9/78 10/3/99 $103,750.00 F Yes 8 Hugh Poynor 9/12/89 9/30/99 $30,601.00 M No 9 Jane Deaux 9/8/87 10/3/99 $79,368.71 F Yes
If you have this document open, highlight all 9 data rows (above) and copy them to the clipboard. 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. Be sure and name the new table tblPeople. If you have the examples only in hard copy, you would have to re-type the data in an Access table.
This is the starting place for both DAO and ADO recordset methods. A local instance of the recordset object must be created with a Dim statement (except with Server coding) for both methods. All the examples below use the object variable "rs" as this instance. The Smith and Sussman textbook uses "rec" but you can use any variable name.
You will have questions about the constants used in the examples, and should consult Access Help (Index: recordset) to learn detailed information about constants (e.g., dbOpenDynaset and adOpenStatic) used in examples below.
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblPeople", dbOpenDynaset)
' Instead of the line above try this SQL
Set rs = CurrentDb.OpenRecordset("SELECT pkPeopleID, LastName FROM tblPeople", dbOpenDynaset)
Dim rs As New ADODB.Recordset
rs.Open "tblPeople", CurrentProject.Connection, adOpenStatic
' Instead of the line above try this SQL
rs.Open "SELECT pkPeopleID, LastName FROM tblPeople", CurrentProject.Connection, adOpenStatic
<body> <!-- #INCLUDE FILE="./adovbs.inc" -->' You MUST include adovbs.inc for this code to function properly. <% ' ASP code Dim ObjConn Dim rs Dim strConn Set ObjConn = Server.CreateObject("ADODB.Connection") strConn="Microsoft.Jet.OLEDB.4.0;Data Source=" & _ ' permissions are set ONLY for the db folder server.mappath("/MIS333K-YourInstructor/FirstName.LastName/db/")&"\sample.mdb;" ObjConn.Open strConn Set rs = Server.CreateObject("ADODB.Recordset") rs.ActiveConnection = ObjConn rs.Open "SELECT * FROM tblSample", ObjConn, adOpenKeyset, adLockPessimistic, adCmdText ' The parameter names in the line above are explained below. %> </body>
The ADO Open Method: recordset.Open Source, ActiveConnection, CursorType, LockType, Options | |
| Parameter | Description |
|---|---|
| Source | This is the source of the recordset. The user can input a SQL statement or table name as the record source as long as the input corresponds with the proper option value. |
| ActiveConnection | This parameter defines which connection object to use. |
| CursorType | This parameter determines the type of cursor that the server should use when opening the recordset. For more information on CursorTypes, please see the CursorType table below. |
| LockType | This parameter determines what type of locking the server should use when opening the recordset. For more information on LockTypes, please see the LockType table below. |
| Options | This long value is an optional argument that indicates how the server should evaluate the CommandText argument. Some of the common options and their descriptions can be found lower on the page. |
| CursorType | Description |
|---|---|
| adOpenDynamic | This CursorType uses a dynamic cursor. Additions, changes, and deletions by other users are visible, and all types of movement through the recordset are allowed if the server doesn't support them. Bookmarks not supported. |
| adOpenForwardOnly | This is the default CursorType. This CursorType uses a forward-only cursor. With this LockType you can only scroll forward through records. This improves performance when you need to make only one pass through a recordset. Bookmarks not supported. |
| adOpenKeyset | This CursorType uses a keyset cursor. 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. Data changes by other users are still visible. |
| adOpenStatic | This CursorType uses a static cursor. A static copy of a set of records that you can use to find data or generate reports. Additions, changes, or deletions by other users are not visible. |
| adOpenUnspecified | This CursorType does not specify the type of cursor. |
| LockType | Description |
|---|---|
| adLockBatchOptimistic | This LockType indicates optimistic batch updates. This is required for batch update mode. |
| adLockOptimistic | This LockType indicates optimistic locking, record by record. The server uses optimistic locking, locking records only when you call the Update method. |
| adLockPessimistic | This LockType indicates pessimistic locking, record by record. The server does what is necessary to ensure successful editing of the records, usually by locking records at the data source immediately after editing. |
| adLockReadOnly | This is the Default value. This LockType indicates read-only records. You cannot alter the data. |
| adLockUnspecified | This LockType does not specify a type of lock. |
| Option | Description |
|---|---|
| adCmdUnspecified | Does not specify the command type argument. |
| adCmdText | Evaluates CommandText as a textual definition of a command or stored procedure call. You would use this option if you entered the record source as a SQL statement. |
| adCmdTable | Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query. You would use this option if you entered a table name as the record source. |
| adCmdTableDirect | Evaluates CommandText as a table name whose columns are all returned.. You would use this option if you use an INDEX and open a table as the record source. |
| adCmdUnknown | This is the Default value. Indicates that the type of command in the CommandText property is not known. |
rs.Close Set rs = Nothing
rs.Close Set rs = Nothing Set objConn = Nothing
After opening a recordset, you will probably need to refer to its contents. The data fields can be addressed by index number, name, or variable as shown in the five columns below. There is an alternate syntax, shown next, for referencing fields that is faster.
Dim ID as String
Dim FN as String
Dim SA as String
rs(0) rs("pkPeopleID") rs.Fields("pkPeopleID") rs!pkPeopleID rs(ID)
rs(1) rs("FirstName") rs.Fields("FirstName") rs!FirstName rs(FN)
rs(3) rs("Salary") rs.Fields("Salary ") rs!Salary rs(SA)
This alternate syntax, for DAO and ADO (Workstations only), references fields faster than above.
Dim ID as String Dim LN as String ' open the DAO or ADO recordsets as shown above (Workstations only) Set ID = rs("pkPeopleID")' open the object container for these fields Set LN = rs("LastName") Do While Not rs.EOF Debug.Print ID; LN ' compare this syntax to the usual syntax in the example below rs.MoveNext Loop
If opening a recordset through a Server, the only way to refer to fields is through the following method:
Dim ID, FN, SA
ID = rs("pkPeopleID")
FN = rs("FirstName")
SA = rs("Salary")
Here the value of the fields is put into different variables; using the variables is not necessary, but usually helps with clutter.
Do While Not rs.EOF
Debug.Print rs![pkPeopleID]; rs![LastName]
rs.MoveNext
Loop
Do Until RsList.EOF
FirstName = RsList("Firstname")
Response.Write(FirstName & " <br />")
RsList.MoveNext
Loop
rs.Edit
rs![LastName] = "Smith-Jones"
rs.Update
' changes to the LastName are lost if record position changes in edit-update
' ADO is naturally in edit mode, so do not use "rs.Edit"
rs![LastName] = "Smith-Jones"
rs.Update
' ADO is naturally in edit mode, so do not use "rs.Edit"
rs("LastName") = "Smith-Jones"
rs.Update
' A variety of syntax is used below for demonstration rs.AddNew ' rs("FirstName") = txtFirst rs!Last = txtLast rs.Fields("Hire") = Today rs(5) = txtStartingSalary rs("Sex").Value = cboSex rs.Update
' A variety of syntax is used below for demonstration rs.AddNew ' rs("FirstName") = txtFirst rs("LastName") = txtLast rs("Hire") = Today rs("Salary") = txtStartingSalary rs("Sex") = cboSex rs.Update
rs.Delete
rs.MoveFirst
rs.FindFirst "pkPeopleID=2"
If rs.NoMatch = True Then
Debug.Print "Not found"
Else
Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If
rs.MoveFirst
rs.Find "pkPeopleID=2", , adSearchForward
If rs.BOF Or rs.EOF Then
Debug.Print "Not found"
Else
Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If
rs.MoveFirst
rs.Find "pkPeopleID=2", , adSearchForward
If rs.BOF Or rs.EOF Then
Response.Write("Not found")
Else
Response.Write("Found" & rs("pkPeopleID") & rs("FirstName") & rs("LastName"))
End If
rs.FindLast "pkPeopleID=2"
If rs.NoMatch = True Then
Debug.Print "Not found"
Else
Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If
rs.MoveLast
rs.Find "pkPeopleID=2", , adSearchBackward
If rs.BOF Or rs.EOF Then
Debug.Print "Not found"
Else
Debug.Print "Found"; rs!pkPeopleID; rs!FirstName; rs!LastName
End If
rs.MoveLast
rs.Find "pkPeopleID=2", , adSearchBackward
If rs.BOF Or rs.EOF Then
Response.Write("Not found")
Else
Respones.Write("Found" & rs("pkPeopleID") & rs("FirstName") & rs("LastName"))
End If
The find syntax used above is "non-indexed" and is performed with constants. The examples are repeated here and followed by find syntax with variables.
rs.FindLast "pkPeopleID=2" ' numeric constant - DAO rs.Find "pkPeopleID=2", , adSearchForward ' numeric constant - ADO rs.FindLast "LastName='Jones'" ' string constant - DAO rs.Find "LastName='Jones'", , adSearchForward ' string constant - ADOOf course you will rarely write programs that use only constants. Most of the time your programs will use variables instead. Here are examples of numeric, string and date variables. These examples are for non-indexed finds.
Dim intTemp As Integer Dim strTemp As String Dim dteTemp As Integer ' DAO syntax examples rs.FindFirst "pkPeopleID=" & intTemp rs.FindFirst "LastName=" & "'" & strTemp & "'" rs.FindFirst "Hire=" & "#" & dteTemp & "#" ' ADO syntax examples rs.Find "pkPeopleID=" & intTemp, , adSearchForward rs.Find "LastName=" & "'" & strTemp & "'", , adSearchForward rs.Find "Hire=" & "#" & dteTemp & "#", , adSearchForward
Access provides a means of converting table data into two-dimensional arrays by using recordset programming. You can have all records or a few records placed into an array. Information on arrays is available elsewhere. For example, you could get the 10 earliest hires (names only) from tblPeople into an array called StaffNames(1,9) in this way:
'2 columns and 10 rows into StaffNames(1,9)MySQL = 'SELECT FirstName, LastName FROM tblPeople ORDER BY DateValue(Hire)'' Open the recordset in DAO or ADO (see above)StaffNames = rs.GetRows(10) rs.Close Set rs = Nothing
You sometimes can choose between two methods of performing the same table manipulations: recordset programming or action queries (SQL). 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. The examples that follow next do not show opening or closing recordsets since that is unnecessary for action queries.
There is also a small collection of SQL examples where you can see a wider variety of syntax. And there is an example below of combining SQL and recordsets.
Dim MySQL as String MySQL = "UPDATE tblPeople SET IsSelected = No;" CurrentDB.Execute MySQL ' see a 1-line version of this below ' Some examples of 1-line SQL programs (also see ADO below) CurrentDB.Execute "UPDATE tblPeople SET IsSelected = No;" CurrentDB.Execute "UPDATE tblPeople SET Salary = Salary*1.03 WHERE Sex='F';" CurrentDB.Execute "UPDATE tblPeople SET LastName = 'Smith-Jones' WHERE FirstName = 'Mary' AND LastName = 'Jones';" ' note: this statement works only for DAO CurrentDB.Execute "INSERT INTO tblPeople (pkPeopleID,LastName,FirstName,Hire, Salary,Sex) VALUES (101,'Smith-Kline','Mary',#11/01/00#,50000,'F')" ' 1-line programs to copy a table, and then change its structure CurrentDB.Execute "SELECT tblPeople.* INTO tblPeopleCopy FROM tblPeople;" CurrentDB.Execute "ALTER TABLE tblPeopleCopy ADD COLUMN SpouseName TEXT;"
Dim MySQL as String MySQL = "UPDATE tblPeople SET IsSelected = Yes;" CurrentProject.Connection.Execute MySQL ' Some examples of SQL statements (also see DAO above) CurrentProject.Connection.Execute "UPDATE tblPeople SET IsSelected = Yes;" CurrentProject.Connection.Execute "UPDATE tblPeople SET Salary = Salary*1.02 WHERE Sex='M';" ' Alternative ADO syntax Dim cd As New ADODB.Command Dim cn As ADODB.Connection Set cn = CurrentProject.Connection cd.ActiveConnection = cn cd.CommandText = MySQL cd.Execute
The following are examples of action queries being used through ASP.
strSQL = "INSERT INTO tblPeople (FirstName, LastName) VALUES ('Nick','Jones');"
' ObjConn must be set up as seen in previous examples
SET RsList = ObjConn.Execute(strSQL)
'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). Other action SQLs can
'be used that either update or 'delete data
"DELETE FROM tblSample WHERE FirstName = 'Nick' AND LastName = 'Jones';"
"UPDATE tblSample SET FirstName = 'Nicholas' WHERE FirstName = 'Nick';"
The records used can be filtered through recordset manipulation also.
'Here a filter is placed on the recordset to only show records with FirstName Harry
'It is important that the following line be put before the recordset is opened
RsList.Filter = "Firstname = 'Harry'"
RsList.Open
Do Until RsList.EOF
FirstName = RsList("Firstname")
Response.Write(FirstName & " < br />")
RsList.MoveNext
Loop
RsList.Close
Something that is very useful with the filter property is that along with the usual operators
(<, >, <=, >=, <>, =) the operator LIKE can also be used; when comparing strings
the LIKE operator allows the use of wildcards. Only the asterisk (*) and percent sign (%) wild cards are allowed, and they must be the last or first character in the string.
RsList.Filter = "Firstname LIKE 'H*'"
RsList.Open
Do Until RsList.EOF
FirstName = RsList("Firstname")
Response.Write(FirstName & " < br />")
RsList.MoveNext
Loop
RsList.Close
Here the records shown will be ones with FirstName beginning in H.
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. Use a reduced SQL statement to perform the initial part of the job. Finish it with recordsets. Here is an example.
Find the hire dates and current salaries of the male staff who have the smallest and largest paychecks. 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). Here is a skeleton program for either DAO or ADO.
MySQL = "SELECT Hire, Salary, Sex FROM tblPeople WHERE Sex='M' ORDER BY Salary;" ' open the DAO or ADO recordset (use example code provided elsewhere) rs.MoveFirst Debug.Print "Lowest paid man earns "; rs!Salary; " and was hired "; rs!Hire rs.MoveLast Debug.Print "Highest paid man earns "; rs!Salary; " and was hired "; rs!Hire ' close the DAO or ADO recordset(use example code provided elsewhere)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. And the smaller the program the less chance for bugs.
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. Use a reduced SQL statement to perform the initial part of the job. Finish it with recordsets. Here is an example.
Find the hire dates and current salaries of the male staff who have the smallest and largest paychecks. 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). Here is a skeleton program for either DAO or ADO.
MySQL = "SELECT Hire, Salary, Sex FROM tblPeople WHERE Sex='M' ORDER BY Salary;" ' open the recordset (use example code provided elsewhere) rs.MoveFirst Response.Write("Lowest paid man earns " & rs("Salary") & " and was hired " & rs("Hire")) rs.MoveLast Response.Write("Highest paid man earns " & rs("Salary") & " and was hired " & rs("Hire")) ' close the recordset(use example code provided elsewhere)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. And the smaller the program the less chance for bugs.