January 17, 2025 at 11:40 am
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.
January 17, 2025 at 12:58 pm
If you can figure out what Access doing in the background ( querystore / profiler / ...)
you may be able to optimize the query. Like adding an index on the searched field
Otherwise
You'll probably need some coding
https://www.codevba.com/msaccess/docmd_find_record.htm
Or looking up the primary key with passthrough and search on the retrieved primary key for the Access bookmark
For performance reasons we did most of the logic in VBA modules/forms using pass-through. Like setting the recordset where the form can navigate on.
Program is obsolete by now and the knowledge how to do so has withered
January 17, 2025 at 3:35 pm
Thanks a lot for your help Jo! I'll give the primary key with passthrough and search on the retrieved primary key for the Access bookmark a go & failing that I'll try coding.
Thank you so much for spending the time to respond.
Cheers Del
January 29, 2025 at 5:32 pm
Can you provide some additional details? Are they searching a single table or trying to pull data from multiple tables? You may want to investigate creating a stored procedure on the SQL side to do the search and then passing it required parameters. Also make sure you have proper indexes on the tables.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply