I want to do something sort of complicated:
I have an access database which is updated every month with a new table from the previous month. Each monthly table follows a naming convention: Policies 06302014, Policies 07312014, etc... The structures of these tables are identical.
I have a query in this database which will Select the fields I want from a table based on a few parametrized criteria. When the current table changes over, we will edit the source table name in query design and leave the rest of the query statements intact.
We then typically export the results to excel.
What I want to do is to enter both the current table name and the query parameters into cells in excel, and to create a macro which will take these values as inputs, fit them into a valid access Select statement (not sure if it's correct to call this a SQL statement since I believe there are syntax differences from sql...) and run this query, returning the data to Cell A1 on tab XX of the workbook.
This whole thing is complicated by the fact that one of the parameters is in fact the table name.
I can't change the design of the database. It's going to remain a new table for each month.
I tried using the DoCMD.RunSQL method. I had problems getting Access to stop trying to compile my string, and also I'm not sure if this method requires a valid SQL statement or a valid Access quasi-SQL statement. Further, from google I found that this method will not work with a SELECT statement but requires some sort of action statement. So I'm not sure if this will be part of any working solution.
I am writing here to see if anyone can point me in a direction or provide some code which will help solve my problem.
Thx
I have an access database which is updated every month with a new table from the previous month. Each monthly table follows a naming convention: Policies 06302014, Policies 07312014, etc... The structures of these tables are identical.
I have a query in this database which will Select the fields I want from a table based on a few parametrized criteria. When the current table changes over, we will edit the source table name in query design and leave the rest of the query statements intact.
We then typically export the results to excel.
What I want to do is to enter both the current table name and the query parameters into cells in excel, and to create a macro which will take these values as inputs, fit them into a valid access Select statement (not sure if it's correct to call this a SQL statement since I believe there are syntax differences from sql...) and run this query, returning the data to Cell A1 on tab XX of the workbook.
This whole thing is complicated by the fact that one of the parameters is in fact the table name.
I can't change the design of the database. It's going to remain a new table for each month.
I tried using the DoCMD.RunSQL method. I had problems getting Access to stop trying to compile my string, and also I'm not sure if this method requires a valid SQL statement or a valid Access quasi-SQL statement. Further, from google I found that this method will not work with a SELECT statement but requires some sort of action statement. So I'm not sure if this will be part of any working solution.
I am writing here to see if anyone can point me in a direction or provide some code which will help solve my problem.
Thx
Access Query on variable table from Excel
0 commentaires:
Enregistrer un commentaire