Chapter 4: Editing data
Applies to: Access 2013, Office 2013
The preceding two chapters explained how use ADO to connect to a data source, execute a command, get the results in a Recordset object, and navigate within the Recordset. This chapter focuses on the next fundamental ADO operation: editing data.
This chapter continues to use the sample Recordset introduced in Chapter 3 — with one important change. The following code is used to open the Recordset:
. . .
'BeginEditIntro
Dim strSQL As String
Dim objRs1 As ADODB.Recordset
strSQL = "SELECT * FROM Shippers"
Set objRs1 = New ADODB.Recordset
objRs1.Open strSQL, GetNewConnection, adOpenStatic, _
adLockBatchOptimistic, adCmdText
' Disconnect the Recordset from the Connection object.
Set objRs1.ActiveConnection = Nothing
'EndEditIntro
. . .
The important change to the code involves setting the Connection object's CursorLocation property equal to adUseClient in the GetNewConnection function (shown below), which indicates the use of a client cursor. For more information about the differences between client-side and server-side cursors, see Chapter 8: Understanding Cursors and Locks.
The CursorLocation property's adUseClient setting moves the location of the cursor from the data source (the SQL Server, in this case) to the location of the client code (the desktop workstation). This setting forces ADO to invoke the Client Cursor Engine for OLE DB on the client in order to create and manage the cursor.
You might also have noticed that the LockType parameter of the Open method changed to adLockBatchOptimistic. This opens the cursor in batch mode. (The provider caches multiple changes and writes them to the underlying data source only when you call the UpdateBatch method.) Changes made to the Recordset will not be updated in the database until the UpdateBatch method is called.
Finally, the code in this chapter uses a modified version of the GetNewConnection function, introduced in Chapter 2. This version of the function now returns a client-side cursor. The function looks like this:
'BeginNewConnection
Public Function GetNewConnection() As ADODB.Connection
Dim objConn1 As ADODB.Connection
Set objConn1 = New ADODB.Connection
strConnStr = "Provider=SQLOLEDB;Initial Catalog=Northwind;" & _
"Data Source=MySrvr;Integrated Security=SSPI;"
objConn1.ConnectionString = strConnStr
objConn1.CursorLocation = adUseClient
objConn1.Open
Set GetNewConnection = objConn1
End Function
'EndNewConnection
This chapter covers the following topics: