Performance Issues with FAYT Combobox Using Linked SQL Server Table (20.000+ Records) (1 Viewer)

[Gaztry80]

Hi everyone,

I am trying to implement MajP's FAYT solution for a combobox, as discussed in the following post and MajP's provided solution "(MajP FAYT V12, class module: FindAsYouTypeCombo) (topic: "Search as you type with in Combobox / instant filter combobox itself" < cannot post the link)

I am using a linked table from an Azure SQL Server as the data source for the combobox. This linked table contains the following two coloms:

Table Structure:

-IngredientId: Primary key, initially set as an identity column. I removed the identity setting to avoid needing dbSeeChanges, which significantly slowed down performance.

-Ingredient: Originally set as varchar(1500), reduced to varchar(255) due to combobox character limits, even though some ingredients exceed this limit.-

-Number of Records: Approximately 20.000.

Unfortunately, I am encountering performance issues. The database frequently crashes, and the combobox response is slow. I suspect the issue may caused by the DAO connection but i am not sure.

Has anyone successfully implemented MajP's solution with large linked SQL Server tables (20.000+ records) in Access? Any tips on optimizing the DAO connection or general advice to improve performance would be greatly appreciated.

Thanks in advance for any insights!

 

[arnelgp]

i am guessing that even with ordinary combobox, it will still Load slow considering the rowsource of your combo is not a Local table.

 

[MajP]

I am trying to implement MajP's FAYT solution for a combobox, as discussed in the following post and MajP's provided solution "(MajP FAYT V12, class module: FindAsYouTypeCombo)

I do not think this solution is a good idea for a very large external list.
The code is not designed to be efficient. In fact there are several things that make it very inefficient. It was really designed to be very flexible and easy to use for most Access internal lists of reasonable size. I think with most local tables of less than 50k it works well.

I think you would have to modify the class to work better with a large external list.
At a minimum you would require the user to add a few letters before filtering. Maybe they have to at least have 3 characters typed before filtering.
Also for simplicity and flexibility the code filters the recordset and does not create a new sql. This is easier to implement but not efficient.
Somewhere someone modified this to work with ADO, but not sure if that would improve anything.

 

[isladogs]

20000 records is very unwieldy for a combo box even without FAYT. Suggest you use cascading combo boxes instead

 

[Gaztry80]

Thank you for sharing some ideas. I am doing some trial and error to see if I can get something to work.

I forgot to mention in my original post that this combobox is within a continuous subform. While experimenting, I keep getting an error that the recordsource cannot be changed while the record is in edit mode when the requery is launched (after changing the sql recordsource). I also foresee that after applying the requery, other records will appear blank. I know that placing a text box above the combobox can resolve this, but it makes typing more difficult.

Am I heading in the right direction, or am I going down a rabbit hole?
Please see my simple code so far:

Private Sub cmbIngredient_Change()
    Dim sText As String
    sText = Me.cmbIngredient.Text
   
    If Len(sText) >= 3 Then     
        Me.cmbIngredient.RowSource = "SELECT IngredientID, Ingredient FROM dbo_tblIngredient WHERE Ingredient LIKE '*" & sText & "*'"
    Else     
        Me.cmbIngredient.RowSource = "SELECT IngredientID, Ingredient FROM dbo_tblIngredient"
    End If       
    Me.cmbIngredient.Requery   
    Me.cmbIngredient.Dropdown
End Sub

 

[sonic8]

...
Me.cmbIngredient.RowSource = "SELECT IngredientID, Ingredient FROM dbo_tblIngredient"
End If
Me.cmbIngredient.Requery

Changing the RowSource will automatically cause the ComboBox to requery.
So, you will increase performance a lot by just leaving out the explicit Requery.

 

[Gaztry80]

Changing the RowSource will automatically cause the ComboBox to requery.
So, you will increase performance a lot by just leaving out the explicit Requery.

Thanks! I managed to get it working with this code:

Option Explicit

Private mHandleArrows As Boolean
Private mAutoCompleteEnabled As Boolean

Private Sub Form_Load()
    mHandleArrows = True
    mAutoCompleteEnabled = True
End Sub

Private Sub cmbIngredient_KeyDown(KeyCode As Integer, Shift As Integer)
    If mHandleArrows = True Then
        Select Case KeyCode
            Case vbKeyDown, vbKeyUp, vbKeyReturn, vbKeyPageDown, vbKeyPageUp
                Me.cmbIngredient.Dropdown
                mAutoCompleteEnabled = False
            Case Else
                mAutoCompleteEnabled = True
        End Select
    End If
End Sub

Private Sub cmbIngredient_KeyPress(KeyAscii As Integer)
    On Error Resume Next
    Me.cmbIngredient.Dropdown
End Sub

Private Sub cmbIngredient_Change()
    On Error GoTo ErrorHandler

    If mAutoCompleteEnabled Then
        Static bIsProcessing As Boolean
        If bIsProcessing Then Exit Sub
        bIsProcessing = True

        Dim sText As String
        sText = Me.cmbIngredient.Text

        If Len(sText) >= 3 Then
            Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient WHERE IngredientNew LIKE '*" & sText & "*'"
        Else
            Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient"
        End If

        DoEvents
        Me.cmbIngredient.Dropdown

        bIsProcessing = False
    End If
    Exit Sub

ErrorHandler:
    bIsProcessing = False
End Sub

Private Sub cmbIngredient_AfterUpdate()
    Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient"
End Sub

I am using the after update event to reset the recordsource to prevent blanks. And have used MajP code for the dropdown. Any improvement suggestions or other comments? Performance is now fast !

 

[Minty]

In addition to Sonic8's suggestion, don't reset the combo box row source to default every time if it doesn't need it to be reset.
You can check if it's already in the default state using something like.

If Len(sText) < 3  AND Me.cmbIngredient.RowSource <> "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient" Then
    'It needs resetting 
          Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient"
ELSE 
     Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient WHERE IngredientNew LIKE '*" & sText & "*'" 
End If

It's early here, so the logic might be a bit off, but you should get the drift.

 

[CJ_London]

Any improvement suggestions or other comments?

another improvement would be to not bring through all the the records so instead of this

Me.cmbIngredient.RowSource = "SELECT IngredientID, IngredientNew FROM dbo_tblIngredient"

try this air code, change names to suit - I would expect your recordsource to be a query so you will need to replace ; with "" if you have it

Me.cmbIngredient.RowSource = "SELECT DISTINCT IngredientID, IngredientNew FROM dbo_tblIngredient INNER JOIN (" me.recordsource & ") AS S ON dbo_tblIngredient.IngredientID = S.IngredientID

It just brings through the items that have already been selected rather than all of them. Whether it is worthwhile doing depends on your form recordsource and filters