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

  • 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.

  • 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

  • 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

  • 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