Results 1 to 5 of 5
  1. #1
    nrigheriu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    2

    How to cache tables? Access gives error when querying on multiple outer joins

    Hello,
    I have an Access DB which I use to create an Excel cost report. There are different queries in Access which lead to the creation of this report. There are different subcosts, like for services, parts and others. The person who worked on this previously, summed up the costs per department by doing a Left join between the subcosts. So it'd be like FROM services LEFT JOIN parts ON ... This is clearly not ok, because for a total cost I want to include the combinations in parts as well, even if some combination doesn't exist in services. So what I did was a full outer join by using a left join then a union with a right join. All good until now.
    The problem is that I need to do a full outer join for 4 of these subcost queries and Access only allows me to do 2. When I try to add a third one, it says "Cannot open any more databases". I assume this is because there are many queries being done in the background. I tried to save the queries in steps of joining, but it seems they are not cached and I have the same problem. Is there any way to cache the results of these queries stepwise?


    Or any other solution/blindspot I am missing out on?
    Would appreciate some responses so much!

  2. #2
    nrigheriu is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    2
    I just found a solution for this by calling the person who worked here before. I have to do a query along the lines of "SELECT * INTO tempTbl FROM originalTbl" to cache the result. Then I can redo the queries to complete the full outer join on all 4 tables, but I have to make sure that the temporary tables are up to date. I can do this by always recreating these temporary tables when I create the report.
    Just leaving this question here in case anyone in the future will have the same problem.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    A word of warning.....
    Constantly creating and deleting TABLES is a very good method to corrupt your dB. The tables should be created once and the DATA should be deleted before each report generation.
    (Instead of a Make table query, use an Append query)

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Access can in theory have up to 255 open connections where each table, query, form, report or record set will 'use' one or more connections.
    When that limit is reached, you will get the error 'cannot open any more databases'.

    In practice, performance will deteriorate long before that limit is reached.
    The best way of preventing that is to close all objects such as recordsets after use.
    I also agree with ssanfu's comments regarding using repeated make table queries.

    If you want a way of monitoring available connections, see my utility http://www.mendipdatasystems.co.uk/a...ons/4594418530
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    You might try to use a SQL database as a back-end and create views on this database that can be used in the access queries. T-SQL is far more powerfull than Access SQL.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query error - ambiguous outer joins?
    By totslnewbie in forum Queries
    Replies: 1
    Last Post: 08-27-2015, 04:30 PM
  2. How to prevent ambiguous outer joins error message
    By craigugoretz in forum Queries
    Replies: 1
    Last Post: 02-19-2015, 08:10 AM
  3. Need help with outer-joins
    By MadCrow in forum Queries
    Replies: 4
    Last Post: 06-19-2014, 07:36 AM
  4. Replies: 15
    Last Post: 10-22-2012, 06:06 PM
  5. Multiple outer joins - error message
    By Lipi in forum Queries
    Replies: 1
    Last Post: 09-16-2010, 02:44 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums