Excel VBA best practice advice for SQL and dynamic subtotals

[makeitwork09]

I am about to embark upon a task using Excel VBA, but my experience is somewhat limited. I want to go at this task using best practices and I figured it was best to first get advice from more knowledgeable people than I.
 
Currently, I am storing data in and Access database (Access 2002 - I also have Access 2007, but not all users have been migrated to that yet), but I eventually may be using SQL Server.
 
The users want a common set of data, but any or all of the following will be desirable:
(1) ability to have different groupings (i.e. investor level data, loan level data only, or loan level data, but grouped by property type)
(2) ability to have different parameters (i.e. a particular city, a particular property type, all property types, all regions, a particular region, etc.)
(3) given the above, that means the subtotals would need to change on the fly.
(4) ability to add fields that were not included in the core set of fields provided. I was thinking a drop-down list would be required to select the remaining fields from the table and allow them to select one or more fields and them pass these values to create a SQL string. Thing is I am not sure of the best way to accomplish this? Do I use controls or form objects in Excel?
 
For the above, I was thinking maybe dynamic SQL strings will be required to build out the group by statements. Would that be the best thing to do. I was even thinking if they just wanted a data dump, a group by statement would not be necessary, thus again, some sort of string and if-then or case statements would be required?
 
Additionally, for the selections mentioned in 1 and 2 specifically, I was thinking I need a named range, but since data can be added, I was thinking this needs to be dynamic. Would it be best to create dynamic named ranges using offset in VBA code or using Insert -> Name -> Define?
 
I usually start my VBA code by using Record Macro and then modify them accordingly. Is that advisable when dealing with SQL? If not, how should I approach this? Examples, or links, are appreciated.
 
Thank You in advance for your advice and guidance.
 

 

[SkipVought]

Hi,

You can do all that you stated above in Excel. I'm not sure that is the best tool, given that I do not know what other tools your organization might have available.

Assuming that you've chosen Excel, I'd start simple and build functionality.

Use Data > Import External Data > New database query... to build your basic query, based on one set of criteria.

Return the resultset to Excel.

Turn on your macro recorder and record Data > Import Externa Data > EDIT QUERY and just return the data to Excel. Stop the recorder.

This macro will be the basis for you process.

Alternatively, you could execute this macro to observe your connection string and command text (SQL)

sub QueryPameters()
  dim qt as querytable
  for each qt in activesheet.querytables
    with qt
      debug.print .name, .resultrange.address
      debug.print .connection 
      debug.print .commandtext
    end with
  next
end sub

You can modify the recorded code, which looks kind of messy. Post back to get some help with that. The SQL can easily be modified for different sceniarios and criteria.

Here's an example of some code where I access an MS Access table. This code uses ADODB objects rather than MS Query, but the SQL modification is similar.

Function OperCOMP_Date(sTRAV As String, sOPER As String, sPST_LPST As String, sLast_This As String)
'SkipVought/2008 May 23
'--------------------------------------------------
' Access: MS Access.DueThisWeek
'--------------------------------------------------
':gets assumed PCT for OPER as PST of next OPER OR PCT of TRAV if LAST OPER, for given traveler/OPER
'--------------------------------------------------
    Dim sConn As String, sSQL As String, sServer As String
    Dim rst As ADODB.Recordset, cnn As ADODB.Connection
    Dim sPath As String, sDB As String
    
    Select Case sPST_LPST
        Case "PST", "LPST"
        Case Else
            OperCOMP_Date = DateSerial(1900, 1, 0)
            Exit Function
    End Select
    Select Case UCase(sLast_This)
        Case "LAST", "THIS"
        Case Else
            OperCOMP_Date = DateSerial(1900, 1, 0)
            Exit Function
    End Select
    
    Set cnn = New ADODB.Connection
    
    sPath = "\\dfwsrv222\public\SkipV"
    
    sDB = "DueThisWeek"
    
    cnn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
               "Dbq=" & sPath & "\" & sDB & ".mdb;"
           
    Set rst = New ADODB.Recordset
    
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  TRAVELER, OPER, " & sPST_LPST
    sSQL = sSQL & vbCrLf
    Select Case UCase(sLast_This)
        Case "LAST"
            sSQL = sSQL & "FROM tblLast_Week"
        Case "THIS"
            sSQL = sSQL & "FROM FPRPTSAR_MC_BUILD_SCHEDULE_FP"
    End Select
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "Where TRAVELER='" & sTRAV & "'"
    sSQL = sSQL & "  AND OPER>'" & sOPER & "'"
        
    Debug.Print sSQL
        
    rst.Open sSQL, cnn, adOpenStatic, adLockReadOnly, adCmdText
                          
    On Error Resume Next
                          
    rst.MoveFirst

    If Err.Number = 0 Then
        If Not rst.EOF Then
            OperCOMP_Date = rst(2)
        Else
        'TRAV complete - use TRAV PCT
        
        End If
    Else
    'no OPEN TRAV
        OperCOMP_Date = DateSerial(1900, 1, 0)
    End If
    
    rst.Close
    cnn.Close
    
    Set rst = Nothing
    Set cnn = Nothing
End Function

Here's some typicla code MS Query...

Sub Fixtures()
    Dim sSQL As String

    sSQL = sSQL & "SELECT DISTINCT"
    sSQL = sSQL & " SUBSTR(TPI.TOOL_NO_TEXT,LENGTH(TPI.TOOL_NO_TEXT)-6,7) AS FIXTURE"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FPRPTSAR.MC_BUILD_SCHEDULE MBS"
    sSQL = sSQL & ", FPRPTSAR.TOOL_PART_INFO TPI"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE TRIM(MBS.PART_NUM) = TPI.PART_ID"
    sSQL = sSQL & "  AND MBS.MACH_GRP = TPI.MACH_GRP"
    sSQL = sSQL & "  AND TRIM(MBS.OPER) = TPI.OPER"
    sSQL = sSQL & "  AND TPI.TCD<>'MMC'"
    sSQL = sSQL & "  AND MBS.MACH_GRP='" & [GRP_CODE] & "'"
    sSQL = sSQL & "  AND MBS.PST<=SYSDATE+7"

    With wsFixtures.QueryTables("qryFixtures")
        .CommandText = sSQL
        .Refresh False
        Application.DisplayAlerts = False
        .ResultRange.CreateNames True, False, False, False
        Application.DisplayAlerts = True
    End With
End Sub

In this case, I also create range names using the field headings, after the query refreshes.

I'd consider using a PivotTable report rather than SUBTOTALS in the querytable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Thanks Skip - your approach was the way I was going to go, but wasn't confident it was the best way to start.

As to Excel being the best tool, the other one I usually use is Crystal Reports, but too many fields are being requested. It would require a font size of 5 to do it there.

Eventually, this will be taken over by our IT group to use in a better tool, but it will be some time before they can even start. Therefore, what I am
doing is a band-aid until they can start.

Thanks for your response Skip.

Renee

 

[SkipVought]

Excel is often a good breadboard tool for this sort of project, IMHO.

Post back with your questions whenever needed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[makeitwork09]

Thanks Skip.

I've never done VBA with Pivot Tables, but I think your suggestion is a good one. What I'm going to do first is play around with creating pivot tables using my data to see if I can get it to do what I want. If I can do that, then I'll move forward with recording the process and modifying the resulting code.

 

[SkipVought]

I would not CODE the PT. All I would do is REFRESH.

However, if the Souce Data column names change, its different story.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}