Ten Essential ADO Tips
by Jason T. Roff06/05/2001
I've been using ActiveX Data Objects (ADO) since version 1.5--that's a lot of versions ago. Many changes have taken place in this time. With each version I've learned more about ADO, stuff that you can't always read in books, or at least not all in one place.
I've put together a handful of these essential tips. Some are issues you should always keep in mind; some are techniques you might not have known about; and a couple are just classified as essential knowledge when developing with ADO.
- Share Connection objects.
When you pass a connection string to a Command, Recordset, or Record object, you are implicitly instructing ADO to create a Connection object each time:
Dim rec1 As ADODB.Record Dim rec2 As ADODB.Record Dim rec3 As ADODB.Record Set rec1 = New ADODB.Record rec1.Open "localstart.asp", _ "URL=http://localhost/" Set rec2 = New ADODB.Record rec2.Open "global.asa", _ "URL=http://localhost/" Set rec3 = New ADODB.Record rec3.Open "iisstart.asp", _ "URL=http://localhost/" ' ' do something here ' rec1.Close rec2.Close rec3.Close Set rec1 = Nothing Set rec2 = Nothing Set rec3 = NothingTo save resources, you should use one Connection object and pass it to each object that requires an active connection:
Dim con As ADODB.Connection Dim rec1 As ADODB.Record Dim rec2 As ADODB.Record Dim rec3 As ADODB.Record Set con = New ADODB.Connection con.Open "URL=http://localhost/" Set rec1 = New ADODB.Record rec1.Open "localstart.asp", con Set rec2 = New ADODB.Record rec2.Open "global.asa", con Set rec3 = New ADODB.Record rec3.Open "iisstart.asp", con ' ' do something here ' rec1.Close rec2.Close rec3.Close con.Close Set rec1 = Nothing Set rec2 = Nothing Set rec3 = Nothing Set con = Nothing - Read the ConnectionString property.
You can always read the ConnectionString property of any Connection object that is open, including one returned from the Recordset, Command, or Record object’s ActiveConnection property.
Dim com As ADODB.Command Dim rst As ADODB.Recordset Set com = New ADODB.Command com.ActiveConnection = _ "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=NWind.mdb;" com.CommandText = "SELECT * FROM Customers" Set rst = com.Execute MsgBox com.ActiveConnection.ConnectionString rst.Close Set rst = Nothing Set com = NothingWhen the above code is run, you will get the following output displayed in a message box:
Provider=Microsoft.Jet.OLEDB.4.0; Password=""; User ID=Admin; Data Source=NWind.mdb; Mode=Share Deny None; Extended Properties=""; Jet OLEDB:System database=""; Jet OLEDB:Registry Path=""; Jet OLEDB:Database Password=""; Jet OLEDB:Engine Type=4; Jet OLEDB:Database Locking Mode=0; Jet OLEDB:Global Partial Bulk Ops=2; Jet OLEDB:Global Bulk Transactions=1; Jet OLEDB:New Database Password=""; Jet OLEDB:Create System Database=False; Jet OLEDB:Encrypt Database=False; Jet OLEDB:Don't Copy Locale on Compact=False; Jet OLEDB:Compact Without Replica Repair=False; Jet OLEDB:SFP=FalseYou can now parse this string to find out specific information about the connection, such as if the database will be encrypted when it is compacted (Jet OLEDB:Encrypt Database property).
- Use dynamic properties.
The Properties collection of the Connection object can be used to set provider-specific options, such as the OLE DB driver for SQL Server’s Prompt dynamic property.
Dim con As ADODB.Connection Set con = New ADODB.Connection con.Provider = "SQLOLEDB" con.Properties("Prompt") = adPromptAlways con.Open ' ' the user will be prompted for the database ' con.Close Set con = NothingWhen this code is run, users will be prompted with a dialog that will allow them to choose the database to log on to.
- Choose your cursor location wisely.
Related Reading
When choosing your cursor location, you need to consider what services are important for your connection.
If services from a data provider are what you seek, you will need to use a server-side cursor. These are the services offered with the driver for a data source, and they are usually very flexible. In addition, by keeping a server-side cursor you don't always have to move all of the data to the client, as you would with a client-side server.
On the other hand, local cursor services, such as the Microsoft Data Shaping Service for OLE DB, offer services only available if you choose a client-side cursor. For these services to be manipulated, they require data to reside on the local machine, as with the Data Shaping Service.
You can use the Connection.CursorLocation property to set the location for your cursor, but choose wisely.
- Choose your cursor type wisely.
Equally as important as choosing the location of your cursor is choosing the type of cursor you want to use.
There are four types of cursors, each with its own set of advantages and disadvantages.
The Static cursor provides a snapshot of the data at a given moment. Modifications, including additions and deletions from other users, are not available in this type of cursor. The Static cursor is good for reporting, where you need a nonchanging view of data, but it is not necessarily the fastest. Because changes to data are not shown, a copy of the source at a given moment needs to be created and maintained by the service provider for each connection using a Static cursor.
A Forward Only cursor is identical to a Static cursor except you can only move forward through the data, without moving back. This will improve the performance, as compared to the Static cursor, but it still requires the data source to maintain a temporary copy of itself so that changes by other users do not affect your data.
The Dynamic cursor allows you to see changes and deletions by other users and you are allowed to move freely throughout the Recordset. Unlike the Static or the Forward Only cursors, the Dynamic cursor does not require that the data source maintains a still image of the data, and thus, a Dynamic cursor can be faster than the first two cursors.
The last cursor type is the Keyset cursor. The Keyset cursor is very similar to the Dynamic cursor, except you can't see records added by other users. Records deleted by other users also become inaccessible in a Keyset cursor. As with a Dynamic cursor, changes by other users are also visible. A Keyset cursor can be faster than a Dynamic cursor because it doesn't have to constantly look to see if new records are added or deleted (because added records are not seen and deleted records become inaccessible).
A cursor type for each season--take your pick.
- Create parameters manually.
Microsoft technical writers and anyone else who writes about ADO will beat you silly with this one, so I don’t want to be any different.
When performance is an issue (and when is it not?), manually define your parameters.
Dim con As ADODB.Connection Dim com As ADODB.Command Dim par As ADODB.Parameter Dim rst As ADODB.Recordset Set con = New ADODB.Connection con.Open "Provider=SQLOLEDB;" _ & "Server=localhost;" _ & "Initial Catalog=Northwind;" _ & "User ID=sa;" Set com = New ADODB.Command Set com.ActiveConnection = con Set par = com.CreateParameter _ ("CategoryName", _ adVarWChar, _ adParamInput, _ 15) com.Parameters.Append par Set par = com.CreateParameter _ ("OrdYear", _ adVarWChar, _ adParamInput, _ 4) com.Parameters.Append par com.CommandText = _ "EXECUTE SalesByCategory 'Produce', '1997'" Set rst = com.Execute ' ' do something here ' rst.Close con.Close Set com = Nothing Set rst = Nothing Set con = NothingBy manually defining your parameters, ADO does not have to query the data source to find out what the parameter list is for a stored procedure. This may not matter if you are executing a single, stored procedure while the user is away from her desk, but it will matter if you are executing dozens at a time, when the user is waiting for her information.
- Create buffers with Stream objects.
Stream objects can be used without a physical data source. You can use these objects to create buffers in memory on the local machine. Simply instantiate the Stream object and start writing to it.
Dim str As ADODB.Stream Set str = New ADODB.Stream str.Open str.WriteText "This is a message that I " str.WriteText "would like to keep in " str.WriteText "memory.", adWriteLine str.WriteText "This will be the second " str.WriteText "line.", adWriteLine MsgBox "The buffer has " & _ str.Size & " characters." str.Position = 0 MsgBox "The buffer contains: " & str.ReadText str.CloseYou can also use binary data with the Write and Read methods instead of the text methods WriteText and ReadText. After putting your data into the buffer, you can use the SaveToFile method to persist the contents.
- Check for warnings.
The Errors collection of the Connection object is used to not only report data-provider errors with the execution of an operation, but also to indicate non-halting warnings from the execution of an operation.
The Connection.Open, Recordset.CancelBatch, Recordset.Resync, and Recordset.UpdateBatch methods as well as the Recordset.Filter property can all generate warnings.
To detect a data-provider warning (or error), call the Connection.Errors.Clear method prior to using any of the above methods to begin an operation. After the operation is complete, use the Count property of the Errors collection to determine if there were any warnings.
- Nest transactions.
With the Jet OLE DB provider, you can nest transactions up to five levels. Using multiple-level transactions gives you unprecedented control over your data.
Dim con As ADODB.Connection Dim iLevel As Integer Set con = New ADODB.Connection con.CursorLocation = adUseClient con.Open _ "Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=NWind.mdb;" con.BeginTrans ' ' change 1 ' con.BeginTrans ' ' change 2 ' con.BeginTrans ' ' change 3 ' iLevel = con.BeginTrans ' ' change 4 ' MsgBox "Level " & iLevel con.CommitTrans con.RollbackTrans con.CommitTrans con.CommitTrans con.Close Set con = NothingIn the above example, changes 1 and 2 will be made while 3 and 4 will not. Change 4 looked like it was going to make it, but the third-level transaction was rolled back, causing all subsequent levels to be rolled back.
- Don’t underestimate data shaping.
My last tip for you is do not underestimate the power of the Microsoft Data Shaping Service for OLE DB.
Data shaping allows you to create aggregations with multiple SQL statements to create hierarchical recordsets, where individual fields can point to entire children recordsets.
For instance, if you took two tables from the Biblio database, Publishers and Titles, you could create the following SQL statement to join them in one recordset.
SELECT Publishers.Name, Titles.Title FROM Publishers INNER JOIN Titles ON Publishers.PubID=Titles.PubID ORDER BY Publishers.Name, Titles.Title;The first few records look like this:
Name (Pub) Title -------------- ----------------------------- A K PETERS A Physical Approach to Col... A K PETERS Colour Principles for C... A SYSTEM PUBNS C Plus Plus Reference Card A SYSTEM PUBNS C Reference Card AA BALKEMA Planning With Linear Progr... AARP Thesaurus of Aging Termin... ABACUS Access 2.0 Programming Bible ABACUS Advanced Access ProgrammingWith data shaping, we can use the following statement to greatly reduce the size of the returned data.
Three recordsets are created on the server and returned to the client as needed.
Name (Publisher) ------------------------------------ A K PETERS A SYSTEM PUBNS AA BALKEMA AARP ABACUS Title ------------------------------------ A Physical Approach to Color... Colour Principles for Computer... C Plus Plus Reference Card C Reference Card Planning With Linear Programming Thesaurus of Aging Terminology : ... Access 2.0 Programming Bible Advanced Access ProgrammingThe above tables are brought to the client and passed to the data shaping cursor service, where they are linked in a hierarchical fashion using Chapters as field types to access child recordsets.
Dim con As ADODB.Connection Dim rstPubs As ADODB.Recordset Dim rstTitles As ADODB.Recordset Dim sShape As String Set con = New ADODB.Connection Set rstPubs = New ADODB.Recordset con.Provider = "MSDataShape" con.Open _ "Data Provider=Microsoft.Jet.OLEDB.4.0;" _ & "Data Source=Biblio.mdb;" sShape = "SHAPE {SELECT Name, PubID " _ & " FROM Publishers} " _ & " APPEND ({SELECT Title, PubID " _ & " FROM Titles} " _ & " As PubTitles " _ & " RELATE PubID TO PubID) " rstPubs.Open sShape, con Do Until (rstPubs.EOF) Debug.Print rstPubs!Name Set rstTitles = rstPubs("PubTitles").Value Do Until (rstTitles.EOF) Debug.Print " " _ & rstTitles!Title rstTitles.MoveNext Loop rstPubs.MoveNext Loop rstPubs.Close con.Close Set rstPubs = Nothing Set con = NothingWhen the above code is run, we see the following output:
A K PETERS A Physical Approach to Color... Colour Principles for Computer... A SYSTEM PUBNS C Plus Plus Reference Card C Reference Card AA BALKEMA Planning With Linear Programming AARP Thesaurus of Aging Terminology : ... ABACUS Access 2.0 Programming Bible Advanced Access ProgrammingPowerful stuff.
Jason T. Roff is the author of three database development books, including ADO: ActiveX Data Objects. He specializes in Visual Basic, ASP, and SQL Server development and architecture and he has experience working with C++ and Assembly on everything ranging from a Commodore to a Unix box. Jason graduated from the University of Albany with a degree in Computer Science with Applied Mathematics. Currently, he manages local and off-site development teams to create Web- and Windows-based applications.
O'Reilly & Associates will soon release (June 2001) ADO: ActiveX Data Objects.
Sample Chapter 3, Accessing ADO with Various Languages, is available free online.
You can also look at the Table of Contents, the Index, and the Full Description of the book.
For more information, or to order the book, click here.
