Run a query from excel that selects specific records from access

[crystalball]

Hello,

I have a query I created in an Access DB. It functions as intended when I run it in access, whether I'm using hard coded data in my WHERE clause or prompt the user, but now I have to figure out how to pass the variables I want from an excel spreadsheet.

If I run the query that's already set up in access from excel and the data is hardcoded it works. But that won't let me change the variables in excel like I want. If I try to send the SQL straight from excel to the table it doesn't run even if I change nothing between access and excel. It seems to be hung up on the WHERE statement (if I remove it the query will run, just choosing from all rows in the table).

I've tried to resolve this on my own, attempting to use a linked sheet in access to get the data in there and run the saved access query, but it doesn't seem to let me select the query in excel once I start using that data. Attempting to force it anyway failed.

I'm trying to make that query pull data from two cells ("Sheet1!A2 and Sheet1!A4) and return 5 random results from Column3.

The Query (I'm not actually using Sheet1!A2 and Sheet1!A4 in this, my last attempt tried to pull the data from a linked sheet. But it's there to keep the logic clear):

SELECT TOP 5 Column3
FROM DB
WHERE ((DB.Column1)= (Sheet1!A2))
And ((DB.Column2)= (Sheet1!A4))
ORDER BY RND(Unique ID);

Can anyone help me sort this out? I'd appreciate it.

PS - The how of this is open to change. If there's a way to get 5 random column3s where column1=x and column2=y, even if it requires weird jury rigging or pulling the whole table into excel first then doing some work on it, I'm open to that. But I need something where the user will never need to open access, the UI must be entirely excel.

 

[Gary McMaster]

Try building the query in Excel using the "query wizard". Here is a link that may help:

http://www.tushar-mehta.com/excel/newsgroups/rdbms_in_excel/index.html

Please see "Building the queries" and "Query 4 -- A parameterized query ..."

Hope it helps.

Gary

 

[crystalball]

I'd been trying to play with that, actually, having read through the help file. But when I connect to the access file the parameters option in the connection properties->Definition tab is grayed out.

 

[Gary McMaster]

Are you able to connect to the Access table via the "query wizard" and select the fields you want to bring in?

Gary

 

[crystalball]

Edit: Nevermind, I found the wizard (sneaky dropdowns...) However the parameters button is still grayed out.

 

[Gary McMaster]

I see "Get External Data" under the Data menu tab (2007, FYI)

That's it. I don't have 2007 in front of me so I can't walk you through it. However, somewhere in the dialog boxes that follow in that wizard, you will be allowed to select the table and fields you want to extract to Excel.

Also, somewhere in those dialog boxes there will be a radio button that says something like "Edit with MS Query". From there you can actually get into MS Query and set up the criteria to use in selecting specific records. You can also add & remove fields and other tables from within MS Query.

The link I posted earlier spells it out quite clearly although it is pre 2007. The process is the same in 2007 but the look and location of the buttons etc. are completely different from pre 2007 versions.

When you are in MS Query look up "Parameter Query" in its help file.

Gary

 

[crystalball]

Nevermind again, I got it mostly working. The only problem is the randomization by Unique ID (just a number starting at 1) no longer functions. Excel pulls the same 5 every time.

 

[crystalball]

The link I posted earlier spells it out quite clearly although it is pre 2007. The process is the same in 2007 but the look and location of the buttons etc. are completely different from pre 2007 versions.

Yeah, sorry about the confusion on that. The new look still throws me. 1 problem away from completion. Whether I solve it or not, I relaly appreciate your help so far!

 

[Gary McMaster]

Here are a couple more links (2007) that may help:

http://www.dailydoseofexcel.com/archives/2004/12/13/parameters-in-excel-external-data-queries/

http://office.microsoft.com/en-us/e...ry-to-retrieve-external-data-HA010099664.aspx

Gary