Results 1 to 6 of 6
  1. #1
    Vladisan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2

    Question Saved query gets renamed when using INTO keyword

    Hello all,

    Read the rules for posting on the forum but please bear with me in case i do any mistakes as I am a new member. I will try to describe as best as I can the situation I am facing for which I would like to ask for support / help.

    Long story short I have a database which contains 5 main tables which I update daily from other sources and which I only use as input data. Based on those tables I have created various queries. In turn i use these queries or "read" them from some programs which exist outside of the database.

    Recently I have created a bigger query that is doing a lot of counting based on different criteria and groups the data. I then made a function inside excel to read from that query and populate parts of an excel spreadsheet. Upon testing the code, it runs in 160 -180 seconds. Because of this, I thought that since I only update the database once per day, I could write the results of the query into a new table, and then read from the table instead of the query in the hopes that performance would improve. Indeed it did from 160 - 180 seconds to 0,24 seconds.

    To continue, I decided that since I have an automated way of updating the database, through VBA, I can:
    - add the [code]INTO table[code] keyword after the [code]SELECT[code] statement in order create a table with the queries results every time i update the database.
    - create a function with a database object type to use the [code] dbObj.Execute QueryName, dbFailOnError [code] so it can run the above mentioned query every time i update the database.

    The problem is that after the first run of the query with the [code]INTO[code] keyword, the name of the query changes. So in this case the query i run is saved with the name StockCount. Inside it after the select statement, i want to create a table called stock_count by using [code]INTO stock_count[code]. It works and it creates the table, but the query gets renamed into StockCountstock_count (but only when i hover the mouse on top of it). Because of this I cannot run the above mentioned [code] dbObj.Execute QueryName, dbFailOnError [code] on it, as i receive an error that it cannot find the name of the query that im trying to pass as a parameter. See attached picture:

    Click image for larger version. 

Name:	example.jpg 
Views:	12 
Size:	9.6 KB 
ID:	38259


    I tried searching for an answer in multiple places but i cannot find anything relevant which could shed some light on this event.



    Could anyone please help me ? I would highly appreciate it.

    Kind regards,
    Vlad

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't have two query objects with same name. Give the make table query different name.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Vladisan is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2019
    Posts
    2
    Hello June7,

    Thank you for your reply, however i do know that it is not allowed to have objects with the same name. As you can notice in the original post i mentioned the name of the created table and the name of the query and the fact that somehow they combine (attached screenshot). But just to be sure i am clear enough I will do it again with what I hope is a clearer example. I have a saved query named StockCount, which lets say contains the following general SQL statement:
    [code]
    SELECT * INTO [stock_count]
    FROM [first_table]
    WHERE condition
    [code]

    After running this query somehow it gets renamed becoming a combination of its initial name (StockCount) and the table that i want to store the results in stock_count.

    Am i doing something wrong here ? Should i try a different approach ?

    Kind regards,
    Vlad

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Read post narrative again and have better understanding.

    I don't use make table queries. I use an established 'temp' table - table is permanent but data is temporary. So, I was not familiar with this issue. Just tested and do observe the same behavior.

    If I did have a procedure to routinely run make table query, I would probably execute SQL statement in VBA instead of saving a query object.

    There are your alternatives.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    I think this is to do with table Stock_Count already existing.
    Try deleting it before running the query , and just for good measure change the name of the table to tmpStock_Check and change your query name to qryTmpStockCreate.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't matter. Query still saved with the 'hidden' extended name. I created query and saved it without running so table definitely did not already exist.

    However, this time I tried VBA calling the query by the visible name and it works. Then I ran it again and Access deleted existing table and re-created it. All works.

    Use DoCmd.OpenQuery to run the saved object, not Execute.

    My preference would still be a 'temp' table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Renamed Field requesting data
    By joshuag in forum Forms
    Replies: 3
    Last Post: 04-23-2018, 02:36 PM
  2. Query Seperate Column by Keyword
    By Dormie in forum Queries
    Replies: 1
    Last Post: 08-13-2017, 11:31 AM
  3. Replies: 4
    Last Post: 06-05-2014, 02:24 PM
  4. Renamed a field in a table
    By Toollady in forum Access
    Replies: 1
    Last Post: 05-24-2012, 01:09 PM
  5. query multiple tables for keyword
    By BF15 in forum Queries
    Replies: 1
    Last Post: 01-29-2012, 05:18 PM

Tags for this Thread

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