Combo Query on linked MYSQL Vs VBA combo row source (5 Viewers)

[nectorch]

In MS Access linked to MYSQL database I want to gain high performance on the combo box as stated below, please check if the VBA I'm trying to implement will be better than the query also stated below:

SQL below for a combo box

SELECT DISTINCTROW tblProducts.ProductID, tblProducts.ProductName, tblProducts.vatCatCd, tblProducts.dftPrc, tblProducts.itemCd, 0 AS RRP, 0.16 AS VatRate, tblProducts.Sales, tblWarehouse.WHID, tblProducts.taxAmtTl
FROM tblProducts INNER JOIN tblWarehouse ON tblProducts.WHID = tblWarehouse.WHID
WHERE (((tblProducts.Sales) Is Not Null) AND ((tblWarehouse.WHID)=[Forms]![FrmLogin]![CboCompanies]))
ORDER BY tblProducts.ProductName;


VBA Same structure below


Private Sub ProductID_GotFocus()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim varWHID As Variant

    ' 1. Pull the filter value from the external hidden form
    ' Use a variable to handle potential Nulls gracefully
    On Error Resume Next
    varWHID = Forms!FrmLogin!CboCompanies
    On Error GoTo 0

    ' 2. Validation: If the login form is closed or WHID is missing, exit
    If IsNull(varWHID) Or varWHID = "" Then Exit Sub

    ' 3. High-performance SQL Construction
    ' Using table aliases (p, w) and explicit field selection for speed
    strSQL = "SELECT DISTINCTROW p.ProductID, p.ProductName, p.vatCatCd, p.dftPrc, " & _
             "p.itemCd, 0 AS RRP, 0.16 AS VatRate, p.Sales, p.taxAmtTl " & _
             "FROM tblProducts AS p INNER JOIN tblWarehouse AS w ON p.WHID = w.WHID " & _
             "WHERE p.Sales Is Not Null AND w.WHID = " & varWHID & " " & _
             "ORDER BY p.ProductName ASC;"

    Set db = CurrentDb
    
    ' 4. Open as Snapshot for 2026 performance standards (read-only, fast memory fetch)
    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' 5. Direct Object Binding
    ' This bypasses the RowSource string-parsing engine entirely
    Set Me.ProductID.Recordset = rs
    
    ' 6. Performance Optimization: Force full population immediately
    ' This prevents the "blank list" or scrolling lag in large recordsets
    If Not rs.EOF Then
        Dim dummyCount As Long
        dummyCount = Me.ProductID.ListCount
    End If

    ' Cleanup
    Set rs = Nothing
    Set db = Nothing
End Sub

 

[CJ_London]

Why are you joining on the warehouse table?

You only use w.whid as a criteria? You could just use p.whid

You might find group by provides better performance than distinct or distinctrow. Although I would question the need for any of these

Assigning a recordset over a recordsource is unlikely to be any faster unless the recordset already exists

You might as well use

Set Me.ProductID.Recordset = db.openrecordset(…,

 

[nectorch]

Ok let us see what others will say also

 

[dalski]

CJ'll know much more than me. I don't think you should be building the query in Access. You want it handled at the BE, then just pull through that query/ view (or equivalent in you BE). There is an issue with Access using a Combo Box that it pulls the record itself from the query to display in the combo box or something like that & isn't as good as it should be. @Albert D. Kallal & @GPGeorge & other great contributors wrote a great explanation of this somewhere but despite searching I have not managed to find, this is partly relevant but I do not think it is the thread I mean.