Data Connections: Parameterized Query of Access DB... Is it possible?

**twointum** · 06-19-2012, 11:54 AM

Hello forum, I've been Googling and searching for this answer. I have a data connection to an access database and I wrote a simple SQL query in the SQL field.

Please Login or Register  to view this content.

Is it possible to parameterize that query? Ideally, I'd like it to reference a cell on an Excel sheet where I can use a drop down to change the game title.

Thanks in advance!

**tkowal** · 06-19-2012, 12:42 PM

It is not that straight forward..... excel doe not naturally accept parameterized queries. Using VBA coding is the most straight forward way of passing parameters but you will have to manage the connections to the database from code. However, it appears that you want a non-code way of doing it. This is not so straight foward.

Basically, when connection to your database using the Data Tab choose "From Other Sources." Then choose From Microsoft Query. You will have to create a Parameterize Query from there to your data source.... Then in excel go to Data choose connections then choose properties then click on the Definition Tab. If you have set up a proper Parameter Query the Parameters button on the bottom will be highlighted choose that and then you can reference the parameters to Excel Cells .....

Here is a reference link and solutions with same issue as you:
http://www.pcreview.co.uk/forums/exc...-t3519096.html

**twointum** · 06-26-2012, 12:28 PM

Thanks tk, I have dived into the VBA and it doesn't seem too daunting.

I created a dropdown and populated it using the Workbook_Open() function.

Then I used the ComboBox1_Change() function to fire off the code to change the SQL call. So here is where I am: I want to know if I can streamline my code any further. I generally like to code in the most elegant and minimal way possible. What are the minimum requirements? I used the "Record Macro" function to give me a baseline, but I feel there is a lot of extraneous code. Let me know what you think I can cut out:

Please Login or Register  to view this content.

It just seems like lots of that stuff is unnecessary. I would assume I just need the new SQL query, the DB connection and a refresh command.

Can I do this without having an initial DB connection? Thanks!

**tkowal** · 06-26-2012, 01:36 PM

There are lots of ways of connecting to databases through vba each has their own requirements... say for example you want to connect through ADODB.... Make sure the VBA IDE has the Microsoft ActiveX Data Objects 2.x Library checked under references under the Tools menu....

Below is a slim'd down version of the connection and queries assuming you need two queries open at the same time.... The connection is made when you need it and closed when your finished withit

Please Login or Register  to view this content.

above code was tested! Note: You will be unable to use the built in Data Refresh commands in Excel. You will have to manage the refreshing of data your self through code...

**twointum** · 06-27-2012, 01:25 PM

I think you went in a direction that I'm not familiar with. I am a PHP master, but VBA noob. When you went the recordset way, you lost me.

So basically, if anyone else runs along this, I pared this down to the bare essentials for me.

So if you have an existing data connection that populates a pivot table and want to make it dynamic based on a drop down, you can do this:

Please Login or Register  to view this content.

So basically, you need to only use one attribute/property on the with command. 10 lines of code. I think this works for me. Thanks for the brainstorming tkowal!