Re: [MSAccessProfessionals] Search records slow, Access 2010 front end, SQL Server 2019 back end

Hi Wei,

Can you also include the significant parts of the previous thread so we don't need to search for the context and background information?

If you are responding in the web interface, there is a small icon that looks like a speech bubble that can be used.

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent: Thursday, June 18, 2020 3:28 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Search records slow, Access 2010 front end, SQL Server 2019 back end

 

Hi Wei,

I would expect if the user has an idea of what they are looking for, you should be able to provide a filtered list rather than all 40k. You can use a little DAO code to change the SQL property of a saved pass-through query.  https://www.tek-tips.com/faqs.cfm?fid=7433

How to Change SQL property of saved query (DAO) - Microsoft: Access Queries and JET SQL FAQ - Tek-Tips How to Change SQL property of saved query (DAO) by dhookom. faq701-7433. Posted: 27 Jan 11. There are times when the easiest method of creating complex queries with multiple filters is to change the SQL property of a saved query. This works well when you need to send form/control parameters to a pass-through query. ... www.tek-tips.com

Duane

---

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Wei Qian via groups.io <wei_qian2001=yahoo.com@groups.io>
Sent: Thursday, June 18, 2020 3:13 PM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Search records slow, Access 2010 front end, SQL Server 2019 back end

 

Thanks very much, Duane!  You meant I should add a list screen to display the records and let user choose one for editing.  But if it still brings back 40k records, how can users easily locate the one record they want to edit?

BTW.  I accidentally made 2 topics here, is there anyway to merge them?