Query with a parameter

[jozi86]

Hi,
I have a problem with a query with parameters in MS Excel.
I have a database in MS Access where I set up some queries. I want to be able to transfert a result of a query to MS Excel to a pivottable. I did it by getting external data (ms query). I wanted my query to get parameters from a spreadsheet to limit the amount of data i get to Excel. I also managed to do that by adding "?" in query definition. I set up cells from which Excel should get criteria. It works fine until I close my workbook. When opening again and trying to refresh data connection Excel crashes down with a message "Excel stopped working..." It looks for me that query parameters are cleared out when closing the workbook. Is there a way to prevent that? At the moment I just have to define parameters each time I open the workbook.
I looked on many websites but didn't find a solution. Hope you can help.

 

[jozi86]

Does anyone have any ideas? Or it's just a MS Excel bug?

 

[jozi86]

Not any?

 

[jozi86]

Not any?

 

[par60056]

Sorry I do my queries to Oracle and Sybase from Excel not Access. So I not be much help.

If you post a snip of your code I may be able to point you at something.

When I make queries from values supplied in Excel, I build the entire SQL statement with the values substituted in and then pass that string to the Query object.

 

[jozi86]

But, do you do that as a string and then refresh connection of the whole string in vba? It's just that when i parametrize my query by substituting values with "?" in MS Excel, after closing the file it forgets there were any parameters values stored and after a try of refreshing it crashes down...

 

[par60056]

MSQuery has the concept of the parameter when you define the query. I never got that to work when querying Oracle or Sybase.

I just build the complete SQL string in the VBA and then execute the query.

I think I know what you are trying to do and I have never gotten that to work. What you want to do is alter some values on a sheet and then tell the connection to refresh. I have never done my queries that way. Generally, I am pulling data and then formatting reports from it and I just make a new query each time.

 

[par60056]

Is your query set to auto refresh? Is it possible that there is some issue where the sheet the parameters are on is not yet loaded when it tries to refresh?

 

[jozi86]

Well, not really. I refresh it manually and then Excel crashes.

I might try build my query in vba where parameters will be substituted with cells values, but when should I refresh connection with data? When I refresh pivottable or it should be done earlier? For example when opening workbook?

 

[par60056]

Many of the projects I work on, the saved book simply has a sheet for parameters and a bunch of macros. When I open the book, I change the parameters and run a macro. The macro adds a sheet and queries the data based on the parameters, then adds another sheet and builds the pivot table off of the data from the query. If I am sending the book with the data and pivot table out, I often copy query data and paste the values on another sheet so that the book that goes out does not contain the query and information to access my database.

You could have the pivot built and just replace the query sheet and then refresh the pivot. I just find that the files are too big when I do that and it is easier just to have the macro build the pivot from the new data.