SonghaySystem(::)

• Songhay Home
• Privacy Policy
• Send Mail to Us

Developing a Customized COM Object for ADO

The motivation behind building a COM object for ADO (which is already a COM object) is to generalize and package all of the routines and objects involving ADO that are useful in my solutions. In Chapter One of the ADO Programmer’s Guide, Microsoft writes, “ADO is designed to provide developers with a powerful, logical object model for programmatically accessing, editing and updating a wide variety of data sources through OLE DB system interfaces.” Where we see “wide variety” suggesting that ADO is extremely general. My solutions require a finite number of generic routines that are a subset of the total number of features ADO provides. The routines are:

• Connecting to Microsoft Jet 3.x/4.x engines, ODBC-compatible sources (DSN-less), Oracle DBMS, SQL Server DBMS, Sybase DBMS and data sources specified by Microsoft Data Link files (UDL files). When connecting to SQL Server 7.x, I want to take advantage of the new security features of SQL 7 on Windows NT.
• Executing ad hoc SQL (or “dynamic” SQL).
• Executing stored procedures.
• Returning data sets to a “business object” accessing the ADODB library and a client (or user interface).
• Trapping/Reporting data-related errors.

The remainder of this document will list and describe the properties and methods of this object, explore a few real world cases where this object is implemented, and look toward future enhancements.

ADO Services

The file name is rasxADO.dll, containing the Class ADOServices.

Remarks

The arguments forClientRst(),CnnCmd(), andCnnRst() are identical. The following table summarizes:

Two of the arguments for Connect() has its own Enumerated Constants. The following table summarizes:

adJetOLEDB = 0&
adJet4OLEDB = 1&
adODBCOLEDB = 2&
adOracleOLEDB = 3&
adSQL7OLEDB = 4&
adSybaseOLEDB = 5&
adUDL = 6&

adConnectAsynchronous = 16&
adConnectSynchronous = -1&

^*By omitting the UserName and Password when calling Connect() for SQL Server 7.x (or MSDE 1.x) on Windows NT/2000, ADOServices will use the NT/2000 login credentials to attempt to connect to the database server.

The arguments forStoredProcCmd(), andStoredProcRst() are identical. The following table summarizes:

, 
, 
, 
, 

Example Implementations

The function basGetKeyString() is used to retrieve string literals from a remote database. These miscellaneous strings are stored in a single table with columns name, caption and string. This function attempts to eliminate the need to “hard-code” strings into a code Module that is “globally” accessible. This function calls the stored procedure GetKeyString:

Private Function basGetKeyString(ByVal KeyName As String, _
    Optional ByVal ColumnName = "string") As String

    Dim objADO As rasxADO.ADOServices
    Dim objRst As ADODB.Recordset
    
    On Error GoTo basGetKeyString_Err
    
    basGetKeyString = vbNullString
    
    Set objADO = VBA.CreateObject("rasxADO.ADOServices")
    
    If objADO.Connect(dbSERVER, adSQL7OLEDB, _
        adConnectSynchronous, dbCATALOG, dbUSER) Then _
        Set objRst = objADO.StoredProcRst("GetKeyString", KeyName)
        If objRst.BOF And objRst.EOF Then
            'Recordset with 0 rows.
            Call Me.basAppInfoMsg("KeyName """ _
            & KeyName & """ returns no records.")
        Else
            basGetKeyString = objRst.Fields(ColumnName) & vbNullString
        End If
    Else
        GoTo basGetKeyString_Err
    End If

basGetKeyString_Exit:
    Call objADO.CloseRecordset(objRst)
    objADO.Disconnect
    Set objADO = Nothing
    Exit Function

basGetKeyString_Err:
    If (VBA.Err.Number <> 0) Or (objADO.Connection.Errors.Count > 0) Then
        Call Me.basDataErrMsg(objADO, "basGetKeyString()")
        If (VBA.Err.Number <> 0) Then
            Resume basGetKeyString_Exit
        Else
            GoTo basGetKeyString_Exit
        End If
    End If
End Function

The subroutine basFavoritesScan() is one of three functions used to generate an HTML page out the Internet Explorer Favorites stored on a local machine. This function illustrates the use of two-dimensional parameter passed to the stored procedure NewFavorite:

Private Sub basFavoritesScan(ByRef objADO As rasxADO.ADOServices, _
    Optional ByVal Directory = "\")

    Dim objFolders As Scripting.Folders
    Dim objFolder As Scripting.Folder
    Dim objFiles As Scripting.Files
    Dim objFile As Scripting.File
    
    Dim lngFolderCount As Long

    Dim varParameters(0 To 1, 0 To 1) As Variant
    'To be a two-dimensional array of form:
    '   , 
    '   , 
    '   , 
    '   , 
    'etc.
    
    'For readability:
    varParameters(0, 0) = "@path"
    varParameters(1, 0) = vbNullString
    
    varParameters(0, 1) = "@url"
    varParameters(1, 1) = vbNullString
    
    On Error GoTo basFavoritesScan_Err
    
    p_strInFile = basGetKeyString("path_root_favorites") & Directory
    Set objFolders = p_objFileSys.GetFolder(p_strInFile).SubFolders
    
    'Initialize Progress Bar.
    Call basProgress(0, 0, objFolders.Count): p_lngStatusCount = 0
    
    For Each objFolder In objFolders
    
        varParameters(1, 0) = Directory & objFolder.Name
        varParameters(1, 1) = "Directory"
        If Not(objADO.StoredProcCmd("NewFavorite", varParameters, True)) _
        Then GoTo basFavoritesScan_Err
        
        Set objFiles = objFolder.Files
        For Each objFile In objFiles
            varParameters(1, 0) = Directory _
                & objFolder.Name & "\" & objFile.Name
            varParameters(1, 1) = "File"
            If Not(objADO.StoredProcCmd("NewFavorite", _
                varParameters, True)) _
            Then GoTo basFavoritesScan_Err
        Next
        
        'Mark progress.
        p_lngStatusCount = p_lngStatusCount + 1
        Call basProgress(p_lngStatusCount)
    
    Next
    
    'Terminate Progress Bar.
    Call basProgress(0): p_lngStatusCount = 0

basFavoritesScan_Exit:
    Exit Sub

basFavoritesScan_Err:
    If (VBA.Err.Number <> 0) Or (p_objADO.Connection.Errors.Count > 0) Then
        Call Me.basDataErrMsg(p_objADO, "basFavorites()")
        If (VBA.Err.Number <> 0) Then
            Resume basFavoritesScan_Exit
        Else
            GoTo basFavoritesScan_Exit
        End If
    End If
End Sub

Future Enhancements and Free Samples

The functionality of this COM object is directly related to the needs of the Solutions that inspired its creation. It follows that these proposed future enhancements primarily anticipate future demands that may be. What “may be” could be:

Recognizing ADOX. The Microsoft Access 2000 Project File (.ADP) is certainly using ADOX to support the UI. It is possible that one can “outgrow” what Microsoft is already offering through its use of ADOX in Access 2000. It may be prudent to prepare for that day. Chapter 15 of the ADO Programmer’s Guide, “ADOX Fundamentals” is a good start.

Recognizing ADO MD. Support for multidimensional data providers via ADO appears to be limited to OLE DB providers to Microsoft systems. However, exploring the issue for a few minutes can’t hurt. Mike Gunderloy’s Advanced Data Shaping article for Smart Access get right into the thick of it.

Recognizing XML. Paul Enfield’s Using Server-Side XSL for Early Rendering: Generating Frequently Accessed Data-Driven Web Pages in Advance seems to take ADO out of picture when he shows us an XML world. However, ADO 2.6 can be used to open XML files into an ADO Recordset and can even remind me of a really clunky in-memory database. The most compelling treatment of this subject comes from Don Box, his Using ADO to Create XML-based Recordsets, which had me seriously considering using the ADO schema.¹

Recognizing ADO.NET. Having seen the simplified object model in ADO.NET it is possible that my class will not be needed in .NET projects. This remains to be seen.