WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

Ten Essential ADO Tips

by Jason T. Roff
06/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.

  1. 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 = Nothing
    

    To 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
    
  2. 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 = Nothing
    

    When 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=False
    

    You 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).

  3. 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 = Nothing
    

    When this code is run, users will be prompted with a dialog that will allow them to choose the database to log on to.

  4. Choose your cursor location wisely.

    Related Reading

    ADO: ActiveX Data Objects
    By Jason T. Roff

    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.

  5. 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.

  6. 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 = Nothing
    

    By 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.

  7. 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.Close
    

    You 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.

  8. 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.

  9. 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 = Nothing
    

    In 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.

  10. 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 Programming
    

    With 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 Programming
    

    The 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 = Nothing
    

    When 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 Programming
    

    Powerful 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.