Slow when typing in textbox

behedwin

Active Member
Joined
Dec 10, 2014
Messages
399
Hi
I have recently started to have big issues with my database being slow.
It is pretty much only slow in one way, when typing in textbox.
I do not why this started to happen...
my database is on a network drive so others can use it. It is splitted.
the frontend has a size of 6mb and the backend have a size of 1.2mb.

I have run the optimizing database thing.

The textbox have this code set to when textbox is changed

Code:
  Dim strSource As String

  strSource = "SELECT KeyOwnerSelect_Query.Key_ID, KeyOwnerSelect_Query.Nyckel_Kort_Nummer, KeyOwnerSelect_Query.Nyckeltyp, KeyOwnerSelect_Query.LasSystem, KeyOwnerSelect_Query.Profile_ID_SK, KeyOwnerSelect_Query.Firstname, KeyOwnerSelect_Query.Lastname, KeyOwnerSelect_Query.KeyArchived " & _
    "FROM KeyOwnerSelect_Query " & _
    "Where (KeyOwnerSelect_Query.Key_ID Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Nyckel_Kort_Nummer Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Nyckeltyp Like '*" & Me.txtSearch.Text & "*' " _
    & "Or LasSystem Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Profile_ID_SK Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Firstname Like '*" & Me.txtSearch.Text & "*' " _
    & "Or Lastname Like '*" & Me.txtSearch.Text & "*') AND KeyArchived is null "

      Me.ListPicker.RowSource = strSource
      Me.ListPicker.Requery

It works like a search, so it filters down the data that is displayed in a listbox called listpicker.

I have used this setup for over a year
I have had pretty much the same amount of data in the database from day one.
But now suddenly this textbox is very slow. Typing in the textbox and it lags very much.

I have tried to disable to code and then the textbox works.
But i cant understand why this code suddenly slows down so much?
Anyone able to help?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
I have now fixed this temporarly, but i really would like to understand better and maybe fix the issue.
I have now created a button called SEARCH
instead when user click on this button above code is activated and that triggers the search.
This becomes less laggy and it only takes a few seconds for the result to display in the listbox.

But i really enjoyed having that onChange search, it looked so smooth and nice. Anyway to get it to work?
 
Upvote 0
In this i took a copy of my frontend file and backend file.
Placed them on my local c:drive.
Did a new connection between the files and suddenly the speed was back.
Everything seemed to work fine again when not on my networkdrive.
Could this be an issue with network speed?
 
Upvote 0
Yeah - network speed/traffic, connection speed and number of active connections to the database will slow this down as you are running the query after every keystroke.

this would be much slower again over Wifi (as with all things access) or a VPN.

I tend not to use the OnChange event for things like this, although it looks good when working the lag can become unbearable.
 
Upvote 0
Thank you.
I think i need to start asking questions about our network then since this have not been a problem before.
Also noticed that going from one form to another loading new data also is very very slow now. Before everything opened really fast... more or less instant.
Now it takes 3-6 seconds....

Hopefully this can be fixed by networkspeed
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top