Combo Query on linked MYSQL Vs VBA combo row source

[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 my argument with new threads opposed to joining onto existing threads; the supefluity makes it very difficult to find previous topics), this is partly relevant but I do not think it is the thread I mean.

 

[tvanstiphout]

I have a few questions:
1. Roughly how many products do you have, and how often do they change?
2. Why are you joining with tblWarehouse? Do you only want products that are in the warehouse? If so, there may be better ways.

 

[Albert D. Kallal]

Ok, long story short?

You need to turn this into a SQL view.

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;

Writing VBA code WILL NOT improve performance here, not one bit.

However, due to the fact that the above query has a client side "criteria", then of course you can't just toss up the above SQL to a view, and then link to that view.

but, we can pre-create MOST of the SQL, and then use client side against that view.

The 100% issue here? That query has a join, and thus you do not, and should not run that query client side - the join going to be slow.

However, creating a view? And linking to that view? And then using client side code to fill the combo box on form load event?

You be good to go.

So, I suggest that you take above sql, convert to a view - probably like this without the forms! critera

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)

With above saved as a SQL view - and now a existing link to the view from Access?

Then you do need to filter above, but STILL enjoy server side join of the data that you have.

Hence, your forms on-load event to load up the combo box will thus look like this:

     dim strSQL       as string

     strSQL = "SELECT * from MyView WHERE tblWarehouse.WHID = '" & [forms]!FrmLogin]![CboCompanies] & "'"
     strSQL = strSQL & " ORDER BY tblProducts.ProductName"

     me.cboMyComboBox.RowSource = strSQL

So, the "real" goal here is to have that sql join occur server side.
Using VBA code to load up a reocrdset will NOT speed up this process.

Last but not least?

You don't mention how many rows are in that data source to drive the combo box. I would suggest maybe 200, or about 500 rows is about the max you want here. However, like most of us here? I have often used a combo box with many more rows and thus "guilty" of breaking what I am preaching here.

Try above, report back.

The magic trick here is of course creating that SQL as a server side view, linking to that view, and then using the above wee bit of code to set the criteria, and set the order. However, since this is a sql server side view, then the join, and in fact the "filter" will correctly occur server side.

Last FYI:
When you create the view, then you probably do NOT need the table name qualifier.
So, likely, your code will look like this:

  strSQL = "SELECT * from MyView WHERE WHID = '" & [forms]!FrmLogin]![CboCompanies] & "'"
     strSQL = strSQL & " ORDER BY ProductName"

Where you REALLY but REALLY going to notice the speed up?

Well, assuming the combo box is bound to a underlying "id" or column in the table that drives the form?
Then you REALLY notice the speed up when you move to the next record.......

I can explain why this matters - but short explain?
This means you can't use a stored procedure, and YOU CAN NOT use a pass-through query. And the reason is that Access client side can't filter a PT query, nor one that is a stored procedure (looking like a PT query) as "server side", but can only do client side after having pulled all rows from the data source (and a combo box needs such filter ability)......

R
Albert

 

[nector]

I have a few questions:
1. Roughly how many products do you have, and how often do they change?
2. Why are you joining with tblWarehouse? Do you only want products that are in the warehouse? If so, there may be better ways.

Exactly that is the point, some warehouse will different products unrelated others now Imagin you do not filter according to the warehouse concerned then it will useless

 

[tvanstiphout]

Exactly that is the point, some warehouse will different products unrelated others now Imagin you do not filter according to the warehouse concerned then it will useless

Thank you for answering question 2. We'll wait for the answer on question 1.

 

[CJ_London]

Exactly that is the point, some warehouse will different products unrelated others now Imagin you do not filter according to the warehouse concerned then it will useless

Need to clarify why there is a whid in the product table - the implication is any given product is unique to a specific warehouse - and you only need to filter on the whid in the product table. Otherwise you would have a many to many join table