Adding New Records
Both DAO and ADO allow you to add new records to an updatable Recordset by first calling the AddNew method, then specifying the values for the fields, and finally committing the changes with the Update method. The following code shows how to add a new record using DAO and ADO.
DAO
Sub DAOAddRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers", dbOpenDynaset)
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = "Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 555-9876"
rst!Fax = "(425) 555-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
' Position recordset on new record
rst.Bookmark = rst.LastModified
Debug.Print rst!CustomerId
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOAddRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Customers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew
' Specify the values for the fields
rst!CustomerId = "HENRY"
rst!CompanyName = "Henry's Chop House"
rst!ContactName = "Mark Henry"
rst!ContactTitle = "Sales Representative"
rst!Address = "40178 NE 8th Street"
rst!City = "Bellevue"
rst!Region = "WA"
rst!PostalCode = "98107"
rst!Country = "USA"
rst!Phone = "(425) 555-9876"
rst!Fax = "(425) 555-8908"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out
' CustomerId for the new record
Debug.Print rst!CustomerId
' Close the recordset
rst.Close
End Sub
DAO and ADO behave differently when a new record is added. With DAO, the record that was current before you used AddNew remains current. With ADO, the newly inserted record becomes the current record. Because of this, it is not necessary to explicitly reposition on the new record to get information such as the value of an auto-increment column for the new record. For this reason, in the ADO example above, there is no equivalent code to the rst.Bookmark = rst.LastModified code found in the DAO example.
ADO also provides a shortcut syntax for adding new records. The AddNew method has two optional parameters, FieldList and Values, that take an array of field names and field values respectively. The following example demonstrates how to use the shortcut syntax.
Sub ADOAddRecord2()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the recordset
rst.Open "SELECT * FROM Shippers", _
cnn, adOpenKeyset, adLockOptimistic
' Add a new record
rst.AddNew Array("CompanyName", "Phone"), _
Array("World Express", "(425) 555-7863")
' Save the changes you made to the
' current record in the Recordset
rst.Update
' For this example, just print out the
' ShipperId for the new row.
Debug.Print rst!ShipperId
' Close the recordset
rst.Close
End Sub
Updating Existing Records
The following code demonstrates how to open a scrollable, updateable Recordset and modify the data in a record.
DAO
Sub DAOUpdateRecord()
Dim db As DAO.Database
Dim rst As DAO.Recordset
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
dbOpenDynaset)
' Put the Recordset in Edit Mode
rst.Edit
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOUpdateRecord()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=.\nn.mdb;"
' Open the recordset
rst.Open _
"SELECT * FROM Customers WHERE CustomerId = 'LAZYK'", _
cnn, adOpenKeyset, adLockOptimistic
' Update the Contact name of the
' first record
rst.Fields("ContactName").Value = "New Name"
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
Alternatively, in both the DAO and ADO code examples, the explicit syntax
rst.Fields("ContactName").Value = "New Name" can be shortened to
rst!ContactName = "New Name"
The ADO and DAO code for updating data in a Recordset is very similar. The major difference between the two examples above is that DAO requires that you put the Recordset into an editable state with the Edit method. ADO does not require you to explicitly indicate that you want to be in edit mode. With both DAO and ADO, you can verify the edit status of the current record by using the EditMode property.
One difference between DAO and ADO is the behavior when updating a record and then moving to another record without calling the Update method. With DAO, any changes made to the current record are lost when moving to another record without first calling Update. ADO automatically commits the changes to the current record when moving to a new record. You can explicitly discard changes to the current record with both DAO and ADO by using the CancelUpdate method.
Large Text and Binary Data Fields
Two kinds of fields in a Microsoft Jet database are used to store lengthy values. Pictures, OLE objects, or whole files can be stored in an OLE object field as binary data. An OLE object field is also called a Binary Large OBject (BLOB) field. A Memo field stores text data only. Memo fields are sometimes called BLOB fields, too, but that is somewhat misleading. A Memo field is merely a variable-length text field. Both fields are handled similarly.
Large variable-length fields like these must be handled differently than ordinary fixed-length fields, such as integer and date fields. Typically, a variant, a string, or a dynamic byte array is used to read or update the value of the field. However, sometimes the length of the data stored in the field will be too large to store at once in a variant. Breaking the data into smaller, manageable chunks reduces the memory that must be allocated to work with these values. The GetChunk and AppendChunk methods of the Field objects in DAO and ADO break up the data in these fields into smaller chunks.
The following listings demonstrate how to use GetChunk to read large values from a memo field.
DAO
Sub DAOReadMemo()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sNotes As String
Dim sChunk As String
Dim cchChunkReceived As Long
Dim cchChunkRequested As Long
Dim cchChunkOffset As Long
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Open the Recordset
Set rst = db.OpenRecordset _
("SELECT Notes FROM Employees", dbOpenDynaset)
' Initialize offset
cchChunkOffset = 0
' cchChunkRequested artifically set low at 16
' to demonstrate looping
cchChunkRequested = 16
' Loop through as many chunks as it takes
' to read the entire BLOB into memory
Do
' Temporarily store the next chunk
sChunk = rst!Fields("Notes").GetChunk _
(cchChunkOffset, cchChunkRequested)
' Check how much we got
cchChunkReceived = Len(sChunk)
' Adjust offset for next iteration
cchChunkOffset = cchChunkOffset + cchChunkReceived
' If we got anything,
' concatenate it to the main BLOB
If cchChunkReceived > 0 Then
sNotes = sNotes & sChunk
End If
Loop While cchChunkReceived = cchChunkRequested
' For this example, print the value of
' the Notes field for just the first record
Debug.Print sNotes
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOReadMemo()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sNotes As String
Dim sChunk As String
Dim cchChunkReceived As Long
Dim cchChunkRequested As Long
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the recordset
rst.Open "SELECT Notes FROM Employees ", _
cnn, adOpenKeyset, adLockOptimistic
' cchChunkRequested artifically set low at 16
' to demonstrate looping
cchChunkRequested = 16
' Loop through as many chunks as it takes
' to read the entire BLOB into memory
Do
' Temporarily store the next chunk
sChunk = rst.Fields("Notes").GetChunk(cchChunkRequested)
' Check how much we got
cchChunkReceived = Len(sChunk)
' If we got anything,
' concatenate it to the main BLOB
If cchChunkReceived > 0 Then
sNotes = sNotes & sChunk
End If
Loop While cchChunkReceived = cchChunkRequested
' For this example, print the value of
' the Notes field for just the first record
Debug.Print sNotes
' Close the recordset
rst.Close
End Sub
The code to use GetChunk and AppendChunk in ADO is similar to the code in DAO. In the DAO example, the offset to read the next chunk of data from the field must be explicitly calculated and given as a parameter to the GetChunk method. In ADO, the Recordset stores the offset for consecutive GetChunk calls on the same field automatically. Any clones of the Recordset share this offset. This offset is automatically incremented as you call call GetChunk consecutively on a single field. Reading from a different field in the Recordset or moving the Recordset to a different record will reset the offset to the beginning of the field.
The following listings demonstrate how to update binary data in an OLE object field without using the GetChunk or AppendChunk methods.
DAO
Sub DAOUpdateBLOB()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rgPhoto() As Byte
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Open the Recordset
Set rst = db.OpenRecordset( _
"SELECT Photo FROM Employees", dbOpenDynaset)
' Get the first photo
rgPhoto = rst.Fields("Photo").Value
' Move to the next record
rst.MoveNext
' Put the Recordset in Edit Mode
rst.Edit
' Copy the photo into the next record
rst.Fields("Photo").Value = rgPhoto
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOUpdateBLOB()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim rgPhoto() As Byte
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the recordset
rst.Open "SELECT Photo FROM Employees ", _
cnn, adOpenKeyset, adLockOptimistic
' Get the first photo
rgPhoto = rst.Fields("Photo").Value
' Move to the next record
rst.MoveNext
' Copy the photo into the next record
rst.Fields("Photo").Value = rgPhoto
' Save the changes you made to the
' current record in the Recordset
rst.Update
' Close the recordset
rst.Close
End Sub
The code for updating binary data without using GetChunk or AppendChunk is almost identical to the code for updating ordinary fields such as text, numeric, or date fields. The only difference is that it uses a dynamic byte array to store the values. The previous ADO code shows the same differences from DAO as was shown earlier in the example for Updating a Record.
Executing Queries
You can execute a query with ADO as easily as you can with DAO. ADO uses a slightly different technique for working with queries, but provides more functionality than DAO provides. ADO also has a number of abbreviated syntaxes that allow you to do the same thing as you would with DAO but with a lot less code.
Executing a Non-Parameterized Stored Query
A non-parameterized stored query is an SQL statement that has been saved in the database and does not require that additional variable information be specified in order to execute. The following listings demonstrate how to execute such a query.
DAO
Sub DAOExecuteQuery()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Open the Recordset
Set rst = db.OpenRecordset("Products Above Average Price", _
dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteQuery()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the recordset
rst.Open "[Products Above Average Price]", _
cnn, adOpenForwardOnly, adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
The code for executing a non-parameterized, row-returning query is almost identical. With ADO, if the query name contains spaces you must use square brackets ([ ]) around the name.
Executing a Parameterized Stored Query
A parameterized stored query is an SQL statement that has been saved in the database and requires that additional variable information be specified in order to execute. The code below shows how to execute such a query.
DAO
Sub DAOExecuteParamQuery()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim fld As DAO.Field
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Get the QueryDef from the
' QueryDefs collection
Set qdf = db.QueryDefs("Sales by Year")
' Specify the parameter values
qdf.Parameters _
("Forms!Sales by Year Dialog!BeginningDate") = #8/1/1997#
qdf.Parameters _
("Forms!Sales by Year Dialog!EndingDate") = #8/31/1997#
' Open the Recordset
Set rst = qdf.OpenRecordset(dbOpenForwardOnly, dbReadOnly)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
ADO
Sub ADOExecuteParamQuery()
Dim cnn As New ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Open the catalog
cat.ActiveConnection = cnn
' Get the Command object from the
' Procedure
Set cmd = cat.Procedures("Sales by Year").Command
' Specify the parameter values
cmd.Parameters _
("Forms![Sales by Year Dialog]!BeginningDate") = #8/1/1997#
cmd.Parameters _
("Forms![Sales by Year Dialog]!EndingDate") = #8/31/1997#
' Open the recordset
rst.Open cmd, , adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Alternatively, the ADO example could be written more concisely by specifying the parameter values using the Parameters parameter with the Command object's Execute method. The following lines of code:
' Specify the parameter values
cmd.Parameters _
("Forms![Sales by Year Dialog]!BeginningDate") = #8/1/1997#
cmd.Parameters _
("Forms![Sales by Year Dialog]!EndingDate") = #8/31/1997#
' Open the recordset
rst.Open cmd, , adOpenForwardOnly, _
adLockReadOnly, adCmdStoredProc
could be replaced by the single line:
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
In one more variation of the ADO code to execute a parameterized query, the example could be rewritten to not use any ADOX code.
Sub ADOExecuteParamQuery2()
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Create the command
Set cmd.ActiveConnection = cnn
cmd.CommandText = "[Sales by Year]"
' Execute the Command, passing in the
' values for the parameters
Set rst = cmd.Execute(, Array(#8/1/1997#, #8/31/1997#), _
adCmdStoredProc)
' Display the records in the
' debug window
Do Until rst.EOF
For Each fld In rst.Fields
Debug.Print fld.Value & ";";
Next
Debug.Print
rst.MoveNext
Loop
' Close the recordset
rst.Close
End Sub
Executing Bulk Operations
The ADO Command object's Execute method can be used for row-returning queries, as shown in the previous section, as well as for non row-returning queries—also known as bulk operations. The following code examples demonstrate how to execute a bulk operation in both DAO and ADO.
DAO
Sub DAOExecuteBulkOpQuery()
Dim db As DAO.Database
' Open the database
Set db = DBEngine.OpenDatabase(".\nn.mdb")
' Execute the query
db.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'"
Debug.Print "Records Affected = " & db.RecordsAffected
' Close the database
db.Close
End Sub
ADO
Sub ADOExecuteBulkOpQuery()
Dim cnn As New ADODB.Connection
Dim iAffected As Integer
' Open the connection
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=.\nn.mdb;"
' Execute the query
cnn.Execute "UPDATE Customers SET Country = 'United States' " & _
"WHERE Country = 'USA'", iAffected, adExecuteNoRecords
Debug.Print "Records Affected = " & iAffected
' Close the connection
cnn.Close
End Sub
Unlike DAO, which has two methods for executing SQL statements, OpenRecordset and Execute, ADO has a single method, Execute, that executes row-returning as well as bulk operations. In the ADO example, the constant adExecuteNoRecords indicates that the SQL statement is non row-returning. If this constant is omitted, the ADO code will still execute successfully, but you will pay a performance penalty. When adExecuteNoRecords is not specified, ADO will create a Recordset object as the return value for the Execute method. Creating this object is unnecessary overhead if the statement does not return records and should be avoided by specifying adExecuteNoRecords when you know that the statement is non row-returning.
Posts
No comments:
Post a Comment