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:
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