The yahoo groups have gone static. You may want to join the MS Access Professionals group at https://groups.io/g/MSAccessProfessionals
Duane
 | SPAM-FREE advice and knowledge pool for beginners through advanced users of Microsoft Access and databases in general. Please Note: New Members will have their posts moderated for a period of time so we can be sure you are a real member and not a spammer. Sorry for the inconvenience. Please Note: We do not allow job postings or recruiting at this site. Doing so will only get you banned ... groups.io |
From: AccessDevelopers@yahoogroups.com <AccessDevelopers@yahoogroups.com> on behalf of Wei Qian wei_qian2001@yahoo.com [AccessDevelopers] <AccessDevelopers@yahoogroups.com>
Sent: Thursday, June 18, 2020 10:17 AM
To: accessdevelopers@yahoogroups.com <accessdevelopers@yahoogroups.com>
Subject: [AccessDevelopers] Slow in search for a record, Access 2010 Front End, SQL Server back end
Sent: Thursday, June 18, 2020 10:17 AM
To: accessdevelopers@yahoogroups.com <accessdevelopers@yahoogroups.com>
Subject: [AccessDevelopers] Slow in search for a record, Access 2010 Front End, SQL Server back end
Hi,
I'm new to the group. I'm having an Access challenge I hope some experienced Access developers can give me some help.
We have an Access application that has been running for many years. It's a split database, both front and back end are Access files, with linked tables. In recently years, with more data and more users (50+), the system has more frequent "locked up" or "crashes" and users are frustrated. I was given a task of converting the database to SQL Server, which I did it with SSMA, I then changed linked tables from Access to SQL Server. It seems to be working fine, but I hit a performance issue in searching records. The data source of one of the front end forms is a query that joins 3 tables sorted by a date. One table very small, the other two have 40-50k records. When I run the query directly, it seems to be returning records quickly. But when searching a record (looks like using an build-in 'find and replace' function to me), it seems the Access front end requests record processing one by one (from SQL Server Profiler), so if I search a newly entered record (bottom of the sorted list), it could take 2 minutes to come back. I tried to change the query to pass-through query and make it run on SQL server, it's a bit faster but it becomes read-only so the user can't edit record. I tried to add a button to by pass the 'find and replace' function, call a stored proc in SQL Server to return only one record, but it seems I can't assign values to fields because they are bound to the query.
Could someone give me some ideas in how to fix this issues? I'm an experienced SQL Server db admin/developer but I don't have experience in front end app development, and we don't have developers in the team. Any input is greatly appreciated. Thanks very much!
~ Wei
__._,_.___
Posted by: Duane Hookom <duanehookom@hotmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Please zip all files prior to uploading to Files section.
.
__,_._,___
Tidak ada komentar:
Posting Komentar