Searching a Form (using "Find") after an Access to SQL Migration

[Delboy]

I have completed a successful migration of an access database to SQL Express. Most things work fine in Access except when in a "Form" and using "Find" (CTRL+F) in a field & doing a search. It takes forever (over 5 mins) & in some cases crashes the database totally. Is all the users need to really do is search for old customers in the database & amend the records but they need to do that from the Access "Form" not enter SQL commands. For information purposes:



Windows Server 2022
Access 2019
Database Size = 1.6Gb (the reason to move to SQL backed)
23 Tables
Hundreds of reports, forms, queries
5 Users (Not very IT literate) so asking them to use a SQL command is out of the question
Some tables have 66000 records

I have read that creating a pass-through query would complete a search much quicker but that doesn't help my users amend records in an Access Form.

PLEASE HELP ANYONE I AM TEARING MY HAIR OUT! I'm not to Access literate myself so coding would be a bit out of my comfort zone.

[Minty]

The Find dialogue you invoke by pressing control and F is a text search tool, that will search all fields(by default) and won't make use of any indexing.

You need to make a control on the form that will do a proper search on the field for you. There are numerous ways of doing this.
Search on here or tell us the field names and data types of what you are needing to search and we'll try and assist.

[Welshgasman]

Perhaps give them a serach form. Make sure the fields you search on are indexed.
http://allenbrowne.com/ser-62.html

Just a thought, do you have a persistent connection to the back end?

[Delboy]

Thanks for your help Minty & Welshgasman, Ill definitely give that a try

Welshgasman..I'm using a Windows SSL VPN to connect to the backend. It's not a powerful cloud Server through Ionos (1 x core 2Ghz, 4Mb) but the processor & memory aren't maxing out when I do the search.

You guys are legends! Thanks again for your help

Cheers Del

[Gicu]

Usually when you have your Access front-end connected to a back-end in SQL Server, Oracle, MySQL or similar database (via an ODBC driver) you want to minimize the traffic between the form(s) and the back-end. That is best achieved by retrieving only one record at the time to display in the form. There are many ways to do this, like using a tempvar, global variable or hidden control on a "main" or "switchboard" form that stays open throughout your session. You would set your form's record source to only return the specified record:
"SELECT * FROM tblYourTable WHERE PK_ID = Forms!frmSwitchBoard!txtHiddenSelectedID".
On the form you could have a combo or listbox listing the CustomerID and CustomerName fields (with the CustomerID hidden); you would have to test if you can use a live query to give you these two fields from the SQL backend table. If that is too slow one option is to use a local front-end table that you populate\refresh with these two fields when you open the front-end (in the AutoExec macro or the Open event of the opening form) and also give the user a button to refresh on demand.
In the combo of listbox AfterUpdate event you would set the hidden control (or tempvar or global variable) to the selected entry in the combo and simply issue a Me.Requery for the form:

Forms!frmSwitchBoard!txtHiddenSelectedID=Me.cboSearchCustomer
Me.Requery

Here are some pertinent links:
https://www.access-programmers.co.uk...ackend.301830/
https://www.fmsinc.com/microsoftacce...ing/index.html
Cheers,

[NoellaG]

Hi, if you look at the SQL backend I think you will find you're doing a row by row search there and find high ASYNC_NETWORK_IO waits. Using indexes will help, but not much if you use the FIND in Access. In your case I would use search forms, and start all your Access forms with empty date (data source: select ... from ... where 1 = 0) and populate them through search forms using pass through queries. Then use the profiler to view which queries are send to SQL Server and look at the query plan. This will give you a pretty good idea which indexes to create.

[Gicu]

While opening the (main data) forms with no data is the fastest I prefer to open them at the last viewed record. It is a fairly frequent request from the users and very easily done. All you need is a local front-end "settings" table (I like to name mine "usysLocalSettings" as using the "usys" prefix will hide them as part of the system tables) in which to store the PK of the form's current record (using the form's Current event or the search control AfterUpdate). Then in the form's Load event you would use that stored value to retrieve the last view record. And obviously having a local settings table can be helpful with all sorts of other user customizable features such as various import\export paths, user name (for login authentication), etc.
Cheers,

[jojowhite]

you can create a "custom" search form (replacement for Ctrl-F).
then using code, it will search the match in the recordset of the form:

With Me.RecordsetClone
    .FindFirst "Customer = '" & Me.txtCustomerToSearch & "'"
    If Not .NoMatch Then
        Me.Bookmark = .Bookmark
    End If
End With

[NoellaG]

At least, before starting to reprogram your application, look what is happening on the database itself. There is no use starting to change the front end if you don't know what is happening on the back end.