Category Archives: 13044

VBA Has No Class




Recently, I was working on one of my apps, one that is database centric. Whilst making some changes, I came across this piece of code that inserts a new record into the database


    With RS

        .AddNew
        
        .Fields(FIELD_AUDIT_DATE) = sh.Cells(Rownum, COL_FU_AUDIT_DATE).Value
        .Fields(FIELD_CONSULTANT_ID) = GenerateID(FIELD_CONSULTANT_ID, Designer)
        .Fields(FIELD_SALES_TYPE_ID) = GenerateID(FIELD_SALES_TYPE_ID, sh.Cells(Rownum, COL_FU_SALES_TYPE).Value)
        .Fields(FIELD_CUSTOMER) = sh.Cells(Rownum, COL_FU_CUSTOMER).Value
        .Fields(FIELD_QUOTE_NUMBER) = QuoteNumber
        .Fields(FIELD_PRODUCT_ID) = GenerateID(FIELD_PRODUCT_ID, sh.Cells(Rownum, COL_FU_PRODUCT).Value)
        .Fields(FIELD_QUOTE_DATE) = sh.Cells(Rownum, COL_FU_QUOTE_DATE).Value
        .Fields(FIELD_QUOTE_AMOUNT) = sh.Cells(Rownum, COL_FU_QUOTE_AMOUNT).Value
        .Fields(FIELD_ESTIMATED_PROFIT) = sh.Cells(Rownum, COL_FU_PROFIT).Value
        .Fields(FIELD_REVISED_QUOTE) = sh.Cells(Rownum, COL_FU_REVISED_QUOTE).Value
        .Fields(FIELD_ACTUAL_PROFIT) = sh.Cells(Rownum, COL_FU_ACTUAL_PROFIT).Value
        .Fields(FIELD_STATUS_ID) = GenerateID(FIELD_STATUS_ID, sh.Cells(Rownum, COL_FU_QUOTE_STATUS).Value)
        .Fields(FIELD_DECLINED_ID) = GenerateID(FIELD_DECLINED_ID, sh.Cells(Rownum, COL_FU_DECLINED).Value)
        .Fields(FIELD_QUOTE_NOTES) = sh.Cells(Rownum, COL_FU_NOTES).Value
        
        .Fields(FIELD_UPDATED_BY) = ThisApp.LogonUser
        .Fields(FIELD_UPDATED_ON) = Format(Now, FORMAT_TIMESTAMPS_DB)
    
        .Update
    End With

Whilst looking at this code, for some reason my mind wandered to thinking about disconnected recordsets. Whilst most of my application involve database access, it is no longer on enterprise databases, so I don’t have the connection issues of high-end systems. As such, my use of disconnected recordsets is infrequent, my apps are safe in creating a user connection at logon, maintaining the connection throughout their session, and dropping at the end.


As an aside, I always use ADO in my applications. I found it easy to use and it performs fine for me. I have frequently been told that DAO performs better, usually by old Access’ers, but I have no issues with ADO, and will continue with it.


As often happens, my mind started wandering over this topic, forgetting what I was doing and thinking more about disconnected recordsets. I roamed on to thinking about collection classes. Collection classes are a very useful way of creating an in-memory dataset that can be manipulated by creating class methods, but they do require rather a lot of setup. In my musings, it occurred to me that I could use disconnected recordsets to achieve the same functionality, and use the builtin recordset functionality rather than creating my own methods.


Usually, a recordset would connect to a data source at some point, even a disconnected recordset, if only to get the data and/or write it back. It occurrs to me that this is not an absolute necessity, a recordset can be created and used without ever connecting to a data source, for instance where the data is maintained on a spreadsheet.


In this discussion, I will be working with a simple dataset as shown in Figure 1. 



Figure 1


In these examples, I am using late-binding, so I first declare some constants to emulate the ADO constants.


Enum ADOConstants
    adOpenStatic = 3
    adUseClient = 3
    adVarChar = 200
End Enum

Next, the data is extracted from the worksheet, and stored in an array.


Dim RSUsers As Object
Dim vecUsers As Variant
Dim Lastrow As Long
Dim i As Long, j As Long

    With ActiveSheet

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        vecUsers = .Range("A1").Resize(Lastrow, 5)
    End With

The recordset is created, and because we are not retrieving the data from a data source (using ADO), we need to initialise the recordset by creating the columns.


    Set RSUsers = CreateObject("ADODB.Recordset")
    With RSUsers

        .Fields.append "FirstName", adVarChar, 25
        .Fields.append "LastName", adVarChar, 25
        .Fields.append "Gender", adVarChar, 1
        .Fields.append "Role", adVarChar, 25
        .Fields.append "Location", adVarChar, 25

        .CursorLocation = adUseClient
        .CursorType = adOpenStatic

        .Open

Note that you have to use a client side cursor.


Now that we have a defined recordset, we can load it with data. Each row of the array is iterated and a new record is added to the recordset, the fields are populated, and the recordset is updated.


    For i = 2 To Lastrow

        .AddNew
        .Fields("FirstName") = vecUsers(i, 1)
        .Fields("LastName") = vecUsers(i, 2)
        .Fields("Gender") = vecUsers(i, 3)
        .Fields("Role") = vecUsers(i, 4)
        .Fields("Location") = vecUsers(i, 5)
    Next i

    .Update

The recorsdet is now fully populated, contains all of the data, and so it is ready to use. To demonstrate this, I created a simply display function that outputs the recordset contents.


Private Function DisplayDetails( _
    ByVal RS As Object, _
    ByVal Title As String)
Dim msg As String
Dim i As Long
    
    With RS

        .MoveFirst
        Do Until RS.EOF
    
            msg = msg & "Name: " & RS.Fields("FirstName").Value & " "
            msg = msg & RS.Fields("LastName").Value & vbNewLine
            msg = msg & vbTab & "Role: " & RS.Fields("Role").Value
            msg = msg & "(" & IIf(RS.Fields("Gender").Value = "M", "Male", "Female") & ")" & vbNewLine
            msg = msg & vbTab & "Location: "
            msg = msg & RS.Fields("Location").Value & vbNewLine
            .MoveNext
        Loop
    End With

    MsgBox msg, vbOKOnly + vbInformation, Title
End Function

The first demo shows all of the rows, all of the columns.


    Call DisplayDetails(RSUsers, "List All Users")

We can also use the recordset Find function to find the rows that match the specified criteria. The criteria is based upon a column name.


    RSUsers.Find = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Users")

Find also can be used to skip rows, specify a start point, or set the search direction.


The recordset can be sorted,


    RSUsers.Sort = "Location"
    Call DisplayDetails(RSUsers, "List All Users Sorted By Location")

… or filter it.


    RSUsers.Filter = "Gender = 'M'"
    Call DisplayDetails(RSUsers, "List All Male Users")

You might wonder what is the difference between Find and Filter. Filter allows for multiple criteria, unlike Find.


    RSUsers.Filter = "Gender = 'M' AND Location Like 'P*'"
    Call DisplayDetails(RSUsers, "List All Male Users in P*")

As you can see, the disconnected recordset can do everything a collection class can do, but without having to hand-roll any of the methods, recordset has them builtin.


I think there is a killer usage for disconnected recordsets in Excel VBA apps, I just haven’t thought of it yet.

SUMPRODUCT Isn’t The Only SP Kid In Town









If you are a frequent use of SQL Server, or any such enterprise database solution, you are bound to be aware of Stored Procedures (SPs) and the advantages that they bring. In my experience, using SPs is almost de-facto in the SQL Server world.


 


However, it does not seem so prevalent in the Access databases and applications that I have seen. Presumably, this is because Access applications are very closely bound to the Access database, and the rationale drops off somewhat. But I never use the Access UI myself, although I have created many applications where Access is my database with Excel serving as the client interface. In these applications, I communicate with the database using ActiveX Data Objects (ADO).


 


When I first started using Access databases, I could not see how to create SPs in Access, indeed as I recall, it was not initially possible. This meant that I had a ton of SQL code in my applications for communicating with the database, which is not where in belongs according to my design principles. There are queries in Access that can be called, but as I said, I don’t use the Access UI, and I also want to pass parameters to my SP.


 


Access does now support SPs. I am not sure from which version, but it is certainly true form Access 2003 onwards.


 


Suppose we had a simple database list that we wanted to use in a SP, SQL that looks like this


 


SELECT   RU.LoginID,
         RU.UserName,
         RU.Supervisor,
         RU.UserReports,
         RU.Salesman,
         RU.Deleted
FROM   refUsers AS RU
ORDER BY RU.UserName ASC;”


 


Assuming this SP is called spListUsers, the Excel code to bring this list back to a Recordset would be


 


Dim mpConnection As Object
Dim mpCommand As Object
Dim mpRS As ObjectDim mpConnection As Object
Dim mpCommand As Object
Dim mpRS As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    Set mpCommand = CreateObject(“ADODB.Command”)
    Set mpCommand.ActiveConnection = mpConnection
    mpCommand.CommandText = “spListUsers”
    mpCommand.CommandType = adCmdStoredProc

    Set mpRS = CreateObject(“ADODB.Recordset”)
    mpRS.Open mpCommand


 


And the great thing is that it is possible to create these SPs from within Excel using ADO. All that is needed is this code


 


Dim mpConnection As Object
Dim mpCommand As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    On Error Resume Next
        mpConnection.Execute “DROP PROCEDURE spListUsers”
    On Error GoTo 0
    mpConnection.Execute “CREATE PROCEDURE spListUsers ” & _
            “AS “ & _
            “SELECT RU.LoginID, “ & _
                  RU.UserName, “ & _
                   RD.DesignerID, “ & _
                   RU.Supervisor, “ & __
                   RU.UserReports, “ & _
                   RU.Salesman, “ & _
                   RU.Deleted “ & _
            “FROM   refUsers AS RU “ & _
            “ORDER BY RU.UserName ASC;”


 


Introducing parameters adds a certain level of complexity, but nothing too troublesome. The parameter has to be declared in the code to create the SP, and it has to be passed when executing the SP.


 


Creating an SP, spGetUser in this example, with a text parameter called prmUserName, would look something like this


 


Dim mpConnection As Object
Dim mpCommand As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    On Error Resume Next
        mpConnection.Execute “DROP PROCEDURE spGetUser
    On Error GoTo 0



     mpConnection.Execute “CREATE PROCEDURE spGetUser “ & _
        “(prmUserName Varchar (50)) “ & _
        “AS “ & _
        “SELECT RU.LoginID, “ & _
              RU.UserName, “ & _
               RD.DesignerID, “ & _
               RU.Supervisor, “ & __
               RU.UserReports, “ & _
               RU.Salesman, “ & _
               RU.Deleted , “ & _
               RU.UpdatedBy, “ & _
               RU.UpdatedOn “ & _
        “FROM   refUsers AS RU“ & _
               LEFT JOIN refDesigners AS RD“ & _
               ON RU.LoginID = RD.LoginID“ & _
        “WHERE  RU.UserName = prmUserName;”


 


And calling it would look like this


 


Dim mpConnection As Object
Dim mpCommand As Object
Dim mpParameter As Object
Dim mpRS As Object

    Set mpConnection = CreateObject(“ADODB.Connection”)
    mpConnection.Open = “Provider=Microsoft.Jet.OLEDB.4.0;” & _
                        “Data Source=” & mcDatabase

    Set mpCommand = CreateObject(“ADODB.Command”)
    mpCommand.ActiveConnection = mpConnection

    mpCommand.CommandText = “spGetUser”
    mpCommand.CommandType = adCmdStoredProc

    Set mpParameter = mpCommand.CreateParameter(“prmUserName”, _
                                                adVarChar, _
                                                adParamInput, _
                                                Len(“Bob Phillips”)) ‘the parameter value
    mpParameter.Value = “Bob Phillips”
    mpCommand.Parameters.Append mpParameter

    Set mpRS = CreateObject(“ADODB.Recordset”)
    mpRS.Open mpCommand


 


If your SP requires multiple parameters, each parameter is defined separately, loaded and appended as in the single example above.


 


Clearly, your SPs are likely to be more complex than these trivial examples, but the principle holds out with more complex SPs.


 


So there you have it, you can remove all of the inline SQL from your applications, create a separate SP creator app that creates the SPs, have better structured code, and more maintainable. Does it get any better than that?