A working VB.net front-end for Access Database - but VERY slow. How can I speed it up?

Question

I have produced working (single user) application for a Vet friend of mine.
He has successfully used this for three years but as his client history gets larger it has slowed down.

As you see it has multiple relational tables

I used the data controls to produce a data-bound form

This is an example of the code used - Can anyone advise how I might speed this up - preferably without rewriting the entire program?

Public Class frmAnimalsFind
Dim FoundArray(30000) As Integer
Dim here As Integer
Dim Searching As Boolean = False
Public historyShown As Boolean = False

Private Sub frmAnimalFind_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
    Me.MdiParent = frm_MDIMain  
    Me.Top = 1  
    Me.Left = 1  
    Me.Width = 775  
    Me.Height = 745  

    Me.TblClientTableAdapter.Fill(Me._Viking_Bay_Vets___Client_DataDataSet.tblClient)  
    Me.TblAnimalTableAdapter.Fill(Me._Viking_Bay_Vets___Client_DataDataSet.tblAnimal)  

    TblAnimalBindingSource.Position = 1  
End Sub  

Private Sub btnFirst_Click(sender As Object, e As EventArgs) Handles btnFirst.Click  
    TblAnimalBindingSource.MoveFirst()  
End Sub  

Private Sub btnPrevious_Click(sender As Object, e As EventArgs) Handles btnPrevious.Click  
    TblAnimalBindingSource.MovePrevious()  
End Sub  

Private Sub btnNext_Click(sender As Object, e As EventArgs) Handles btnNext.Click  
    TblAnimalBindingSource.MoveNext()  
End Sub  

Private Sub btnLast_Click(sender As Object, e As EventArgs) Handles btnLast.Click  
    TblAnimalBindingSource.MoveLast()  
End Sub  


Private Sub txtFind_TextChanged(sender As Object, e As EventArgs) Handles txtFind.TextChanged  
    Dim Searchname As String  
    Dim FoundCount As Integer = -1  
    If txtFind.Text <> "" Then  
        Searchname = UCase(Trim(txtFind.Text))  
        LstResults.Items.Clear()  
        TblAnimalBindingSource.MoveLast()  
        TblAnimalBindingSource.MoveNext()  
        Do  
            If InStr(UCase(AnimalNameTextBox.Text), Searchname) = 1 _  
                Or InStr(ID_chipTextBox.Text, Searchname) = 1 Then  
                FoundCount = FoundCount + 1  
                FoundArray(FoundCount) = TblAnimalBindingSource.Position  
                TblClientBindingSource.MoveLast() ' add client details to list  
                Do  
                    If ClientIDTextBoxAn.Text = ClientIDTextBoxCl.Text Then  
                        Exit Do  
                    End If  
                    If TblClientBindingSource.Position = 0 Then Exit Do  
                    TblClientBindingSource.MovePrevious()  
                Loop  
                LstResults.Items.Add(AnimalNameTextBox.Text & vbTab & SurnameTextBox.Text & ", " & InitialsTextBox.Text)  
            End If  
            If TblAnimalBindingSource.Position = 1 Then Exit Do '   1 not 0  to hide Admin Animal  
            TblAnimalBindingSource.MovePrevious()  
        Loop  

        If FoundCount > -1 Then  
            TblAnimalBindingSource.Position = FoundArray(0)  
        Else  
            MsgBox("Name/ID-Chip not found")  
        End If  

    Else  
        LstResults.Items.Clear()  
    End If  
End Sub  

Private Sub LstResults_SelectedIndexChanged(sender As Object, e As EventArgs) Handles LstResults.SelectedIndexChanged  
    TblAnimalBindingSource.Position = FoundArray(LstResults.SelectedIndex)  
End Sub  

End Class

Answer 1

As you mentioned, it's slowed down over time. This is one of the downsides of using MS-Access over SQL-Server Express edition which does not bloat and has countless other benefits. Also, TableAdapter method of working with data consumes more memory than using a DataAdapter or Entity Framework (both support MS-Access and SQL-Server).

With that said, you might consider setting up in a calendar to compact the database every couple of months.

EDIT Do this

EDIT 2

Since the compact didn't work the next option is to take which ever form loads the 5,000 records and if possible create a combo box which can be used to filter data. You can go into the table where it says Fill/GetData and create a SELECT WHERE statement then in code use this to apply the filter. Other than that there is what's called pagination which unless you have done this before is not easy so consider creating one or more SELECT WHERE statements and note they are easy to call.

Answer 2

Karenpayneoregon

I've tried compacting but it made little difference

I know I'll eventually rewrite the whole program using SQL-Server, but I'd like to give a "quick fix" just to keep it going in the meantime.

Program was fast enough originally and the data size is not that great (3MB - 5000 records)

Anyone got the "magic pill"?

Answer 3

AlbertKallal

This is single user on own laptop.
When first written - 3 years ago - it ran like lightening.
Now there are 30 sec, even minute delays, per query - EVEN if the form is open and similar query just completed

I am searching manually through records for matches using Movenext, moveprevious etc

ie find Appointment, then find the Pet, then the owner - Database is fully relational and fully normalised i.e No duplicate fields.

500 pets owned by 450 clients with 5000 consultations over last 3 years
Can't remove completed consultations as history of treatment is needed

Is there any way to force data to load into memory (cache) and run there?