MS Access query using SQL tables with an Index

Bob Vitter 181 Reputation points
2021-06-07T18:04:14.06+00:00

MS Access 2016, SQL Server Express 2017
2 SQL tables: one has primary key ClientID (10k records), other has Foreign Key ClientID (30+k records) which has a non-clustered index
Access query links the tables and is used to create a summary table
I relinked the tables and recompiled the application after creating the Index
Creating the index in SQL does not change the time required to process the query
Do I have to do something in Access to get the query to use the index?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
825 questions
0 comments
Accepted answer
  1. Albert Kallal 4,811 Reputation points
    2021-06-08T01:26:24.877+00:00

    As pointed out in the other post?

    Consider creating a view on sql server, and link to that.

    Access often will "mess up" a join between two linked tables.

    So, you probably built this query - dropped in the two tables - joined them.

    However, access often "thinks" the two tables don't really have anything to do with each other, and with linked tables - it can often try to join local - not on the server. And this does run slow.

    So, create the query main table + child table in sql server. Now from access link to that view.

    It will join and perform really fast. And you can even filter or use where clauses against this linked table - performance will be top notch. And even now a client side query built in access will ONLY pull rows of data based on your criteria.

    For a single linked table + query builder? (access client side). Performance is great - indexing is used server SQL side.

    For "some" queries with two tables (both linked tables to sql server), sometimes access can and will mess this up. I have no hard and fast rule as to "when" this bad choice occurs.

    However, if you build that query as view sql server side - do the join in that view?

    Now link from Access to that view.

    Then you get 100% high speed indexing for the join - and as noted - it will run very fast. And as noted, even if you now build query in Access against that view - add criteria etc? SQL server indexing will be used - and ONLY the records you ask for will be pulled by access (so indexing and even filtering against that linked view works very well).

    Regards,
    Albert D. Kallal (Access MVP 2003-2017)
    Edmonton, Alberta Canada

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. DBG 2,301 Reputation points
    2021-06-07T21:53:49.43+00:00

    Hi. Are you saying the query is slow? Can you post the SQL statement? Maybe you can consider using a View instead.

    0 comments