OLE DB Query Command Text

[RaviWildcat]

Hello everyone!

I've just started playing with importing data into excel from an access database using Data / From Access / and then selecting a table.

I can even edit the query by right on the data returned, clicking on Table, Edit Query and playing with the command text.

So - my OLE DB Query Command text can look like:

select blah from blah

My question is - is there a way to embed a variable in the command text? I'm hoping to create another worksheet with values and then name one of the cells as a variable, and then use that variable in the query of the OLE DB Query command text

So, if I have another worksheet and in cell $a$1 I insert the date 11/30/2010 and name that 'pull_date'

I'd like to use that variable in OLD DB Query command text as

select blah from blah where blah = %pull_date%

I can use any number of formulas once I have the data pulled in from my access db, but I'm wondering if there's a fun way to play with the query in the query definition?

 

[xenou]

Parameterized Queries are supported by MSQuery:
http://www.dailydoseofexcel.com/archives/2004/12/13/
http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html (queries 4 and 5)
http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters
http://office.microsoft.com/en-us/excel-help/customize-a-parameter-query-HP010216113.aspx

You will notice that when you create a parameter Excel will put a question mark in the SQL command text - so that usually works too, if you like to edit the SQL directly. I should give it a try with the parameters button. Though in Excel 2007/2010 offhand I'm not sure how all the menus have been re-arranged.