Design advice needed (Array or List?)

[SIMMS7400]

Hi Folks -



I have an MS Access Database with quite a few Functions. Each function serves a different purposes but there are functions where I am re-using most of the same values in arrays. Therefore, rather than maintain each Function, I want to maintain 1 section where I identify the array value(s) and then indicate the Function it should be made available in, if that makes sense?

For this example, I have (3) functions that all use 90% of the same array values. My hope is to central maintain that list by identifying the full set of values and then indicating what function each value belongs to. And then when the Function is executed, it will read the array value AS WELL potentially another value that checks if this value should be used in "this" array. If so, continue, if not, skip.

I have attached an excel workbook with the Master List of value and where each value should be used as an example.

Below is a Function I currently use so I'd need to update based on the suggestions here.

Public Function Function1()
 
'::-- Error Handler --::'
On Error GoTo Proc_Err
   
    '::-- Initialize --::'
    Call Initialize_Global_Content
    
    Dim db As DAO.Database
    Dim ws As DAO.Workspace
    
    Dim sRS As DAO.Recordset, tRS As DAO.Recordset
    Dim fld As DAO.Field2
    
    Dim i As Integer, z As Integer
    
    Dim sTable As String, tTable As String, strMask As String, strNameSub As String
    Dim tgtStr As String, tmpStr As String, tFlds As String, sFlds As String, vCriteria As String
    Dim tFld() As String, sFld() As String, vStr() As String
    
    Dim strFunctName As String, strStartTime As Date, strEndTime As Date, strTimeDiff As String
    Dim strStep As String, strSubject As String, strBody As String, strTo As String, strProcError As String
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.Databases(0)
    
    'Start a transaction to ensure all updates are run or rolled back
    ws.BeginTrans: strTFlag = 1
    
    strFunctName = "Function1": strStartTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strMask = "PFC*"
    sTable = "MDM_Project_Portfolio_Reference"
    tTable = "rdAssets"
    strNameSub = "AssetID"
    
    tFlds = strNameSub & ",[AssetAlias],[Termination_Reason],[Brand Name],[Generic Name],[IpOwner],[AlliancePartner]," & _
            "[GovernanceStatus],[Route of administration],[Mechanism],[ResearchCode],[TA],[Alternate_Name],[Development_Name]," & _
            "[Modality_Detail],[Target Long Name],[Target Short Name],[Business_Owner],[PartnershipID]," & _
            "[PartnershipAlias],[Family],[DDU_Portfolio],[NME_LCM],[Status_Date],[Origin]"
    sFlds = "Name,[Alias],[Termination_Reason],[Brand Name],[Generic Name],[IP Owner],[Alliance Partner]," & _
            "[Portfolio Status],[Route_of_Administration],[Mechanism],[PF_Research_Code],[PF_TherapeuticArea],[Alternate_Name],[Development_Name]," & _
            "[Modality_Detail],[Target Long Name],[Target_Short_Name],[Business_Owner],[Partnership_Name_Link]," & _
            "[Partnership_Alias_Link],[Family],[DDU_Portfolio],[NME_LCM],[Status_Date],[Origin]"
    
    tFld = Split(tFlds, ",")
    sFld = Split(sFlds, ",")
       
    strFldsQry = sTable & Replace(sFlds, ",", ", " & "[" & sTable & "].")
    strFldsQry = Replace(strFldsQry, sTable & "Name", "[" & sTable & "]" & "." & "[" & "Name" & "]")
    
    strStep = "Step 1: Add New Data Elements"
    strSQL = "" & _
            "INSERT INTO [" & tTable & "] (" & _
                tFlds & ",RequestStatus" & _
            " )" & _
            " SELECT" & _
                strFldsQry & _
                " ,'Published' As RequestStatus" & _
            " FROM [" & sTable & "]" & _
            "LEFT JOIN [" & tTable & "] ON [" & tTable & "].[" & strNameSub & "] = [" & sTable & "].[Name]" & _
            " WHERE [" & sTable & "].[Name] LIKE '*" & strMask & "*'" & _
                " AND [" & tTable & "].[" & strNameSub & "] IS NULL;"
    db.Execute strSQL, dbFailOnError
       
    'Clear Variables
    strStep = "": tgtStr = ""

    'Open a table-type Recordset
    Set sRS = db.OpenRecordset("Select * from [" & sTable & "] where [Name] like """ & strMask & """", dbOpenDynaset)
    Set tRS = db.OpenRecordset("SELECT * FROM [" & tTable & "] WHERE [RequestStatus] = ""Published""", dbOpenDynaset)
    
    Do Until sRS.EOF
    
        tgtStr = ""
        
            vCriteria = strNameSub & " = '" & sRS.Fields("Name").value & "'"
            tRS.MoveFirst
            tRS.FindFirst vCriteria
            
            If Not tRS.NoMatch Then
            
                'Do Standard field mapping property updates
                For i = 0 To UBound(tFld)
                            
                    'Set fld to check .IsComplex property
                    Set fld = tRS(tFld(i))
                    
                    strStep = "Step 2: Update Data Element Attributes" & vbNewLine & vbNewLine & _
                              "Data Element - " & Nz(tRS.Fields(strNameSub).value, "") & vbNewLine & _
                              "Source Field - " & Nz(sFld(i), "") & vbNewLine & _
                              "Source Value - " & Nz(sRS.Fields(sFld(i)).value, "") & vbNewLine & _
                              "Target Field - " & Nz(tFld(i), "") & vbNewLine & _
                              "Target Value - " & Nz(tRS.Fields(tFld(i)).value, "")
                              
                    'Ignore MVF Attributes
                    If Not fld.IsComplex Then
                        If Nz(tRS.Fields(tFld(i)).value, "foo") <> Nz(sRS.Fields(sFld(i)).value, "foo") Then
                            tRS.Edit
                            tRS.Fields(tFld(i)).value = sRS.Fields(sFld(i)).value
                            tRS.Update
                        End If
                    Else
                        'Process Multi Value Field Attributes
                        tRS.Edit
                        Set tRSMV = tRS(tFld(i)).value
                        
                        tgtStr = ""
                        Erase vStr
                         
                        'Concatenate the multiple values in a single string
                        Do Until tRSMV.EOF
                            tRSMV.MoveFirst
                                Do Until tRSMV.EOF
                                    tgtStr = tgtStr + tRSMV!value.value + ","
                                    tRSMV.MoveNext
                                Loop
                        Loop
                        If Not tgtStr = "" Then
                            tgtStr = Mid(tgtStr, 1, Len(tgtStr) - 1)
                            tRSMV.MoveFirst
                        End If
        
                        'Compare the concatenated strings
                        If Nz(sRS.Fields(sFld(i)).value, "") <> tgtStr Then
                            Do Until tRSMV.EOF
                                tRSMV.MoveFirst
                                    Do Until tRSMV.EOF
                                        tRSMV.Delete
                                        tRSMV.MoveNext
                                    Loop
                            Loop
                            
                            If Nz(sRS.Fields(sFld(i)).value, "") <> "" Then
                                vStr = Split(sRS.Fields(sFld(i)).value, ",")
        
                                For z = 0 To UBound(vStr)
                                   tRSMV.AddNew
                                   tRSMV.Fields(0).value = vStr(z)
                                   tRSMV.Update
                                Next
                            End If
                        End If
                        tRS.Update
                     End If
                Next
                
                If tRS.Fields("RequestStatus").value <> "Published" Then
                    tRS.Edit
                    tRS.Fields("RequestStatus").value = "Published"
                    tRS.Update
                End If
                
            End If
        
        sRS.MoveNext
    Loop
    
    '::-- Ensure Temp Table is available --::'
    Call Initialize_Temp_Tables
    
    strStep = "Update PartnershipID & PartnershipAlias in [rdAssets] Table"
    strSQL = "" & _
        "UPDATE [rdAssets]" & _
            "LEFT JOIN [LTMDM_Project_Portfolio_Reference]" & _
                "ON [rdAssets].[AssetId] = [LTMDM_Project_Portfolio_Reference].[Name]" & _
            " SET " & _
                "[rdAssets].[PartnershipID] = [LTMDM_Project_Portfolio_Reference].[Partnership_Name_Link], " & _
                "[rdAssets].[PartnershipAlias] = [LTMDM_Project_Portfolio_Reference].[Partnership_Alias_Link], " & _
                "[rdAssets].[Workflow_PartnershipID] = [LTMDM_Project_Portfolio_Reference].[Partnership_Name_Link], " & _
                "[rdAssets].[Workflow_PartnershipAlias] = [LTMDM_Project_Portfolio_Reference].[Partnership_Alias_Link], " & _
                "[rdAssets].[Partnered] = Switch([LTMDM_Project_Portfolio_Reference].[Partnership_Name_Link] Is Null, 'No',[LTMDM_Project_Portfolio_Reference].[Partnership_Name_Link] Is Not Null, 'Yes')" & _
        "WHERE " & _
                "(" & _
                    " Nz([rdAssets].[PartnershipID]+[rdAssets].[PartnershipAlias], """") <>" & _
                    " Nz([LTMDM_Project_Portfolio_Reference].[Partnership_Name_Link]+[LTMDM_Project_Portfolio_Reference].[Partnership_Alias_Link],"""") OR " & _
                    " Nz([rdAssets].[PartnershipID]+[rdAssets].[PartnershipAlias], """") <>" & _
                    " Nz([rdAssets].[Workflow_PartnershipID]+[rdAssets].[Workflow_PartnershipAlias], """")" & _
                ") AND [RequestStatus] = 'Published';"
    db.Execute strSQL, dbFailOnError
    
    'commit all changes
    ws.CommitTrans: strTFlag = 0
    
Proc_Exit:

    '::-- Update Table with Procedure Information --::'
    strEndTime = Format(Now, "mm/dd/yyyy hh:mm:ss")
    strTimeDiff = strEndTime - strStartTime
    Call ADD_RUN_TIMES( _
                        strFunctName, _
                        strStartTime, _
                        strEndTime, _
                        Hour(strTimeDiff) & " hours " & Minute(strTimeDiff) & " minutes " & Second(strTimeDiff) & " seconds", _
                        Switch(strProcError = "", "Success", Not (strProcError = ""), "Failed"), _
                        strProcError _
                       )
    
    On Error Resume Next
        If Not sRS Is Nothing Then sRS.Close
        If Not tRS Is Nothing Then tRS.Close
        
        Set sRS = Nothing
        Set tRS = Nothing
        Set tRSMV = Nothing
        Set ws = Nothing
        Set db = Nothing
    
    Exit Function

Proc_Err:

    '::-- Rollback Transaction --::'
    If strTFlag = 1 Then ws.Rollback
    
    '::-- Capture VB Error --::'
    strProcError = Err.Number & " - " & Err.Description
    
    strSubject = "WARNING : Function '" & strFunctName & "' Failed " & strEnvType
    strBody = Switch(strStep = "", "", Not (strStep = ""), strStep & vbNewLine & vbNewLine) & _
              "VB Error : " & strProcError & vbNewLine & vbNewLine & _
              "VB Profile : " & CurrentUser() & vbNewLine & _
              "Database : " & strCPN
    strTo = strMDMSupportEmail
    Call MDM_Routines.Email_Utility(strSubject, strBody, strTo, "", "")
    
    Resume Proc_Exit
    
End Function

Any suggestions are greatly appreciated, thank you all!

[Micron]

I want to maintain 1 section where I identify the array value(s) and then indicate the Function it should be made available in

At this point, 45 views and no answer so I hope you don't mind a suggestion that may or may not work. Rather than associating a particular array to its particular function, why not just pass the array (whatever it contains) to a function? You could make this "other value" a 2nd, perhaps optional, parameter.