ActiveX Data Objects (ADO) is Microsoft’s latest database object model.

1. ADO ActiveX Data Objects (ADO) is Microsoft’s latest database object model. The goal of ADO is to allow VB developers to use a standard set of objects to refer to any OLE DB source.

2. ADO Objects Connection Command Recordset Errors Parameters Fields Error Parameter Field

3. Establish Reference to ADO Before using ADO, you must establish a reference to the ADO library. Project, References, Microsoft ActiveX Data Objects 2.5 Library

4. Basic Steps to executing a query using ADO • Create a Connection Object. • Specify the connection string to connect to the database of interest. • Open the connection • Create a Recordset object. The Recordset object contains the results of the query after execution. • Specify the SQL text. When you open a recordset, you can either use a table, a stored procedure, or string containing a SQL statement. • Open the recordset. • Opening the recordset executes the query and returns the records to the recordset object. The records are accessible through the recordset object now.

5. Connection Object • A Connection object represents a unique session with a data source. In the case of a client/server database system, it may be equivalent to an actual network connection to the server. • Important Properties and Methods • ConnectionString • ConnectionTimeout • Mode • CursorLocation • Provider • Open and Close

6. Connection Object 'Create ADODB Connection Dim connAVB As ADODB.Connection Set connAVB = New ADODB.Connection connAVB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51;" & "Data Source=" & App.Path & "\AVB.mdb;Mode=readwrite“ connAVB.Open

7. Connection Object 'Create ADODB Connection Dim connAVB As ADODB.Connection Set connAVB = New ADODB.Connection connAVB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.3.51;" & "Data Source=" & App.Path & "\AVB.mdb;Mode=readwrite“ connAVB.Open ‘code connAVB.Close Set connAVB = Nothing

8. Recordset Object • Represents the entire set of records from a base table or the results of an • executed command.   • You use Recordset objects to manipulate data from a provider. • Important Properties and Methods • CursorLocation • CursorType • LockType • Mode • Open, Close • MoveFirst, MoveLast, MoveNext, and MovePrevious • BOF, EOF • Update, AddNew, Delete • GetString, GetRows

9. CursorLocation • Cursor refers to system resources needed to manage a set of data. • Specifies the location of the cursor service. • Constant Value Description • adUseClient -Uses client-side cursors supplied by a local cursor. Use for desktop applications. • adUseServer - Default. Uses data-provider or driver-supplied cursors. These cursors are sometimes very flexible and allow for additional sensitivity to changes others make to the data source. However, some features of the Microsoft Cursor Service for OLE DB (such as disassociated Recordset objects) cannot be simulated with server-side cursors and these features will be unavailable with this setting.

10. Cursor Types • Dynamic cursor — allows you to view additions, changes, and deletions by other users; allows all types of movement through the Recordset • Keyset cursor — behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data changes by other users will still be visible. • Static cursor — provides a static copy of a set of records for you to use to find data or generate reports; always allows bookmarks and therefore allows all types of movement through the Recordset. Additions, changes, or deletions by other users will not be visible. This is the only type of cursor allowed when you open a client-side Recordset object. • Forward-only cursor — allows you to only scroll forward through the Recordset. Additions, changes, or deletions by other users will not be visible. This improves performance in situations where you need to make only a single pass through a Recordset.

11. Lock Types LockType is important when you have multiple users accessing the same data.

12. Opening a RecordSet Public Sub MoveX() ' connection and recordset variables Dim rstAuthors As ADODB.Recordset Dim Cnxn As ADODB.Connection Dim strCnxn As String Dim strSQLAuthors As String ' Open connection Set Cnxn = New ADODB.Connection strCnxn = "Provider=sqloledb;Data Source=a-iresmi2000;Initial Catalog=pubs;User Id=sa;Password=;" Cnxn.Open strCnxn ' Open recordset from Authors table Set rstAuthors = New ADODB.Recordset rstAuthors.CursorLocation = adUseClient

13. Opening a RecordSet strSQLAuthors = "SELECT au_id, au_fname, au_lname, city, state FROM Authors ORDER BY au_lname" rstAuthors.Open strSQLAuthors, strCnxn, adOpenStatic, adLockOptimistic, adCmdText rstAuthors.MoveFirst numRecords = rstAuthors.RecordCount city = rstAuthors!city rstAuthors.Close Cnxn.Close Set rstAuthors = Nothing Set Cnxn = Nothing End Sub

14. Opening a RecordSet Source = Table rsObject.Open tablename, Connection Object, CursorType, LockType, adCmdTable Source = Stored Procedure                      rsObject.Open stored procedure name, Connection Object, CursorType, LockType, adCmdStoredProc Source = string                        rsObject.Open SQLstring, Connection Object, CursorType, LockType, adCmdText