Solved Filtering a combo box with over 300 k data in Ms Access linked to SQL Server (1 Viewer)

[nector]

First of all my apology experts you provided some solutions on another post but this one appear to be unique, I have a combo box with data coming from SQL server as BE to MS access as FE. with total 300ks

Now when it populate data for users to select it populates everything hence making terribly slower like 45 minutes, one of the blogger mentioned to say I need to filter that combo box with one record at a time , this means that the combo box MUST LOAD EMPTY AND ONLY populate data as the user finish typing the first 3 letters. To that effect I was thinking that the code below will be easy for me but I'm getting stuck. Any help will be highly appreciated once again my apologies.

VBA code to be used

Private Sub Field1_Combo_Change()
Dim strText As String

strText = Nz(Me.Field1_Combo.Text, “”)

If Len(strText) > 2 Then
Me.Field1_Combo.RowSource = “Select keywords from ” _
& “My_Words ” _
& “where keywords like ‘” & strText & “*’ ” _
& “order by keywords”
Me.Field1_Combo.Dropdown
End If
End Sub

The Actual combo box query that populate data is below and all fields are mandatory populated but captured by VBA on after productID updates:

SELECT ViewtblProductSelect.ProductID, ViewtblProductSelect.BarCode, ViewtblProductSelect.ProductName, ViewtblProductSelect.TaxClass, ViewtblProductSelect.Prices, ViewtblProductSelect.RRP, ViewtblProductSelect.VatRate, ViewtblProductSelect.Insurance, ViewtblProductSelect.TourismLevy, ViewtblProductSelect.InsuranceRate, ViewtblProductSelect.TaxInclusive, ViewtblProductSelect.ItemCodes, ViewtblProductSelect.Tourism, ViewtblProductSelect.ExportPrice, ViewtblProductSelect.NoTaxes, ViewtblProductSelect.Sales, ViewtblProductSelect.TurnoverTax, ViewtblProductSelect.Bettings, ViewtblProductSelect.Excise, ViewtblProductSelect.ExciseRate
FROM ViewtblProductSelect
ORDER BY ViewtblProductSelect.ProductID DESC;

The VBA above is supposed to incorporate this query on the combo box rowsource = XXXXX, this is where I'm getting stuck.

The query has complex calculation done in the server and all fields must populate the subform , that is why it is quite trick , example one line at a time, so in this case just Fanta as a name all the above fields must show as one line just like that

 

[June7]

Review http://allenbrowne.com/ser-32.html

 

[Uncle Gizmo]

You might find this thread by @MajP of interest. "Find (Filter) as You Type Controls (Combobox, Listbox, Form)" https://www.access-programmers.co.u...u-type-controls-combobox-listbox-form.327863/

 

[KitaYama]

Now when it populate data for users to select it populates everything hence making terribly slower like 45 minutes

Though I think you have to filter down the rowsource of your combo, but as a test, I just added a combo box with an unfiltered rowsource to a table with 900K records. And it took only several seconds to show the contents.
I think you have a serious problem with your network or SQL server's configuration. 45 minutes to fill 300K records seems to be so strange.

Edit :
I tested again. The first several thousands of items are shown immediately. Even though the combo's Limit To List property is set to Yes, I can type the largest number and tab out of the combo without any error.

Edit 2:
I didn't notice you're using on change event. I filled the combo on load event of the form.

 

[CJ_London]

Not clear to me what 300ks means but if it is the number of records be aware a combo can only list 65k records, I'm guessing your 2 char limit before fetching data is intended to overcome that.

However each time you add or remove a character, the combo has to go back to the source to requery. So you may be better doing something like
this to filter the fetched records rather than fetching a shorter list on the 3rd and subsequent characters

static sText as string
dim rs as DAO.Recordset
Select case len(strtext)

    case is <1
        set field1_combo.recordset = nothing 'or similar, objective is to not return any records
    case 2
        if sText<>strText then 'you don't need to fetch the data again if it is the same
            sqlstr= “Select keywords from ” _
                   & “My_Words ” _
                   & “where keywords like ‘” & strText & “*’ ” _
                   & “order by keywords”
            set rs=currentdb.openrecordset(sqlstr)
            set field1_combo.recordset=rs
            sText=strText
        end if 
    case else
        rs.recordset.filter= "keywords like ‘” & strText & “*’ ”
        set field1_combo.recordset=rs

end select

edit: modified the code to save requerying if you already have the data and storing the fetched data in a separate recordset object if the user back spaces

 

[ebs17]

When it comes to desired performance, the Control_Change event used is a disaster, because with each additional character a new giant query is sent against the SQL Server.
The user should know what they're looking for (there's other ways to help them), so apply a one-time filter on Control_AfterUpdate.

combo box MUST LOAD EMPTY

SELECT ViewtblProductSelect.ProductID, ViewtblProductSelect.BarCode, ViewtblProductSelect.ProductName, ViewtblProductSelect.TaxClass, ViewtblProductSelect.Prices, ViewtblProductSelect.RRP, ViewtblProductSelect.VatRate, ViewtblProductSelect.Insurance, ViewtblProductSelect.TourismLevy, ViewtblProductSelect.InsuranceRate, ViewtblProductSelect.TaxInclusive, ViewtblProductSelect.ItemCodes, ViewtblProductSelect.Tourism, ViewtblProductSelect.ExportPrice, ViewtblProductSelect.NoTaxes, ViewtblProductSelect.Sales, ViewtblProductSelect.TurnoverTax, ViewtblProductSelect.Bettings, ViewtblProductSelect.Excise, ViewtblProductSelect.ExciseRate
FROM ViewtblProductSelect
WHERE False
ORDER BY ViewtblProductSelect.ProductID DESC;

Another problem: You formulate the query in Jet-SQL and fire it against the SQL Server, which speaks TSQL. If you want performance, send your query directly to the SQL Server using TSQL. With an ADODB connection to the server, you can address it directly and fetch the query result via ADODB.Recordset and assign it to the ComboBox.
The ADODB.Recordset can also be further filtered locally.

 

[CJ_London]

my suggestion can easily be adapted to use ADODB - I would also modify the recordset type to snapshot and once you have the download on 2 chars, disconnect it - it's not as if you need to modify the data

 

[amorosik]

Though I think you have to filter down the rowsource of your combo, but as a test, I just added a combo box with an unfiltered rowsource to a table with 900K records. And it took only several seconds to show the contents.

You mean you uploaded 900,000 records to a standard Access combobox?

 

[nector]

Thank you so much all this will surely help a lot especially the Allen codes I will surely be okay, many thanks once again to all

 

[nector]

Well said I appreciate your valid and valuables assistance, I will try to implement all your suggestion one by one. Early this year one blogger suggested to have the product , Pricing and customers tables in the front end not SQL server to help sales people create invoices faster , I can confirm this works very well and all the sudden the slow product and customers combos become faster after adding this small code

Private Sub Form_Load(  )
  Dim lngComboStatus As Long
  ComboStatus = ProductID.ListCount
End Sub

But I'm not ending here I have to try all the suggestions above and then choose the one with high benefits. Though the described one appear to be fine since the data does not real change every day, but any change means all the computers must be up updated. however, this will affect only the sales part.

Procurement will benefit also from the same change automatically , the general ledger processing , projects, budgeting and salaries will not be affected.

Once again many thanks , it time for me to work.