How to cache or store static reference table data in MS Access database.

[anavagomez]

I was able to fix that part and also to load the sql server data-table values into an array, here is the code in case you want to see if I am making a mistake:

Private Sub LoadArray()
    
    On Error GoTo errhandler
    
    Dim ADOCon As ADODB.Connection
    Dim ADORS As ADODB.Recordset
    Dim avarRecords As Variant
    Dim intRecord As Integer
    
    Set ADOCon = New ADODB.Connection
    ADOCon.ConnectionString = GetConnectionString("Conn")
    ADOCon.Open
    
    Set ADORS = New ADODB.Recordset
    ADORS.ActiveConnection = ADOCon
    ADORS.Open _
                "SELECT DISTINCT" & _
                "  [Category]" & _
                ", [ProductDescription]" & _
                ", [BasePrice]" & _
                ", [AdditionalPrintPrice]" & _
                ", [MinimumPurchaseAmount]" & _
                ", [isChoral]" & _
                ", [isScoringBasedMinAmount]" & _
                ", [isTierBased]" & _
                "FROM [dbo].[Price] " & _
                "ORDER BY Category", , adOpenStatic, adLockReadOnly
                
    avarRecords = ADORS.GetRows(50)
    
    Debug.Print UBound(avarRecords, 2) + 1 & " records retrieved."
    
    For intRecord = 0 To UBound(avarRecords, 2)                
        
		PriceCategory.AddItem (avarRecords(0, intRecord) & ";" & _
                               avarRecords(1, intRecord) & ";" & _
                               avarRecords(2, intRecord) & ";" & _
                               avarRecords(3, intRecord) & ";" & _
                               avarRecords(4, intRecord) & ";" & _
                               avarRecords(5, intRecord) & ";" & _
                               avarRecords(6, intRecord) & ";" & _
                               avarRecords(7, intRecord))
            
    Next intRecord   


eofit:
    
    On Error Resume Next
    ADOCon.Close
    ADORS.Close
    Set ADOCon = Nothing
    Set ADORS = Nothing


    Exit Sub


errhandler:
    
    z = ErrorFunction(Err, Err.Description, Erl, "LoadArray", , True)
    
    Err = 0
    
    Select Case z
        Case 0: Resume Next
        Case 1: GoTo eofit
    End Select
    
End Sub

One issue I am still experiencing is that there is one record that has a "ProductDescription" value of:


Easy Play (Piano, Guitar) or Banjo

When I look at this value in the combo-box, the value is splitted in 2 columns:

Col1 Col2
Easy Play (Piano Guitar) or Banjo

I think this is due to the comma in between the string containing the description. Do you know how to escape this character so the string gets written all together?


Thank you.

[anavagomez]

I was able to fix the "comma" issue. Within the For loop:

For intRecord = 0 To UBound(avarRecords, 2)
        
        If InStr(avarRecords(1, intRecord), ",") > 0 Then
            avarRecords(1, intRecord) = """" & avarRecords(1, intRecord) & """"
        End If       
            
        PriceCategory.AddItem (avarRecords(0, intRecord) & ";" & _
                               avarRecords(1, intRecord) & ";" & _
                               avarRecords(2, intRecord) & ";" & _
                               avarRecords(3, intRecord) & ";" & _
                               avarRecords(4, intRecord) & ";" & _
                               avarRecords(5, intRecord) & ";" & _
                               avarRecords(6, intRecord) & ";" & _
                               avarRecords(7, intRecord))
            
    Next intRecord