Using Access Query w/Variable

[cstimart]

I have an access 2007 database that has a handful of queries that are date range driven. Currently, I have to open the db and put the dates in a form...then the queries provide me the data for that data range.

Is it possible to use excel to connect to the access db and set those dates via a cell or variable in excel...therefore filling different tabs with the query data?

 

[SydneyGeek]

One option is to have a version of the query without the data parameters, use MS Query to connect to that query, and create parameters that use cells on a worksheet.
Tutorial here... http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

Denis

 

[cstimart]

One option is to have a version of the query without the data parameters, use MS Query to connect to that query, and create parameters that use cells on a worksheet.
Tutorial here... http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

Denis

Thanks mate...that looks like it will be the trick.

I assume the query parameters in excel/MS Query are identical to how I have them set currently in Access...except for the reference to the "variable" dates?

 

[cstimart]

I gave it a try and have to iron out some kinks....but it seems that any query I create, all the fields will be shown in the resulting table, whereas my queries in Access use fields for the query but are not shown. Am I missing something?

 

[SydneyGeek]

When you edit the MSQuery view, you can delete fields that you don't want to display. Have you done that?

Or, in case I misinterpreted...
You have some criteria fields that are not displayed in Access but you see them in MSQuery. Is that right?

Denis

 

[cstimart]

When you edit the MSQuery view, you can delete fields that you don't want to display. Have you done that?

Or, in case I misinterpreted...
You have some criteria fields that are not displayed in Access but you see them in MSQuery. Is that right?

Denis

Actually, I just hide the columns in the resulting table.

However, in the few queries I have tried, I am unable to edit a query and have to delete and re-create.

 

[SydneyGeek]

In the MSQuery grid you *should* be able to add and remove fields like you do in Access.
But if you have a query that does the job in Access already, why not just use that as the data source? You don't have to go back to the tables. And if you insert parameters in the Access query they should show up in the MSQuery grid and allow you to assign the parameter values to cells.

Denis

 

[cstimart]

In the MSQuery grid you *should* be able to add and remove fields like you do in Access.
But if you have a query that does the job in Access already, why not just use that as the data source? You don't have to go back to the tables. And if you insert parameters in the Access query they should show up in the MSQuery grid and allow you to assign the parameter values to cells.

Denis

What happens is that I create my query and after I look at my results, I am unable to edit (or really see) the query grid....I have to delete and start over.

I am constantly given the "This Query cannot be edited by the Query Wizard." error/pop-up.

 

[SydneyGeek]

Sometimes you can see it anyway. The alternative, if you're feeling brave, is SQL.

By using ADO to connect to Access you can retrieve the data efficiently. That includes using cell contents as parameters in the queries, and it's my preferred way to go because you get more control.

See how you go with this tutorial.

Denis

 

[cstimart]

Sometimes you can see it anyway. The alternative, if you're feeling brave, is SQL.

By using ADO to connect to Access you can retrieve the data efficiently. That includes using cell contents as parameters in the queries, and it's my preferred way to go because you get more control.

See how you go with this tutorial.

Denis

Hmmm...that is intriguing. Is it easily converted to create/utilize an Access 2007 database? I tried changing the Constant to '.accdb' but that crashes the code later on.