Lookup from Excel to Access

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pat.morley@gmail.com - 28 May 2008 23:57 GMT

Is there a way to use MS Query to lookup a range of values from a
spreadsheet? Currently we use an excel front end to let users query
an access db - ms query allows them to enter a value into the
parameter window. I'd like to be able to pass a list of values -
rather than doing this one at a time.

The whole process for the end users has been simplified to pushing a
button - fyi.

Thanks much.

Reply to this Message

ward376 - 29 May 2008 02:44 GMT

Yes, you can tell MS Query that a cell value is a parameter. You can
also tell it to always use that range as the parameter for a query abd/
or to refresh the query if the value changes.

Cliff Edwards

Reply to this Message

ward376 - 29 May 2008 03:05 GMT

Here's a link that may help you:

http://www.codeforexcelandoutlook.com/DQYFiles.html

Rather than saving a query file as featured in the link, I usually set
up a very hidden sheet with the query range stored on it and refresh
the query when the procedure is run. Just one short line of code to
trip it then: srcSh.QueryTables(i).Refresh BackgroundQuery:=False.

I'm not saying one technique is better than the other, just offering
options.

Cliff Edwards

Reply to this Message