A query that takes parameters from a cell in the spreadsheet

OK, the simplest way I was able to find:

Record a macro of running a query on your database. "Tools";"Macro";"Record New Macro"

Not really important what query you run, it's just to get the wizard to do the heavy lifting of writing the code to connect to a db, run a query, and put the results into a spreadsheet.

Now, edit the macro. The wizard's macro will probably be about 20 lines or so long. You will be looking for a line that says something like:

.CommandText = Array( _
"SELECT tPersons.FirstName, tPersons.PersonID," _
& "tPersons.LastName, tPersons.MiddleName" _
& Chr(13) & "" & Chr(10) _
& "FROM Contacts.dbo.tPersons tPersons" _
& Chr(13) & "" & Chr(10) _
& "WHERE (tPersons.LastName Like '[red]p%[/red]')" _
& Chr(13) & "" & Chr(10) _
& "ORDER BY tPersons.LastName")

I added the space-underscore-ampersand line extenders to make it easier to see on this forum. The robot that writes the macro in Excel hasn't heard of pretty-printing; it's one long line.

Here's how you modify it to look in a cell of another worksheet for its criterion:

.CommandText = Array( _
"SELECT tPersons.FirstName, tPersons.PersonID," _
& "tPersons.LastName, tPersons.MiddleName" _
& Chr(13) & "" & Chr(10) _
& "FROM Contacts.dbo.tPersons tPersons" _
& Chr(13) & "" & Chr(10) _
& "WHERE (tPersons.LastName Like '" _
& [red]Sheets("Sheet2").Range("a1") [/red]& "%')" _
& Chr(13) & "" & Chr(10) _
& "ORDER BY tPersons.LastName")


See the change right after the "WHERE" ?

My query happens to be in SQL Server dialect of SQL, where '%' is the wild-card character. Yours will need to be in the proper dialect for your db, so it may be different in that spot.

Ron