Query a SQL database using VBA

Question

I want to create a VBA program where it connects to the database you give it (hard coded). From here you are to program a cell to take the input for the WHERE clause in the query and then in a separate sheet it outputs the queried data. I do not want to filter this data, I need to query it. Can anyone help?

What kind of database? Excel has tools under Data>>Get External Data which can do this, so try recording a macro and see if you can modify it to do what you need. — Tim Williams
– Tim Williams, Commented Jul 3, 2013 at 19:36
Step #1 guard against SQL injection -see here Step #2 tell us what type of database you are trying to connect to — barrowc
– barrowc, Commented Jul 4, 2013 at 2:01

Doug Glancy · Accepted Answer · 2013-07-03 19:52:36Z

1

You can do this using Microsoft Query. The basic steps are:

1 - Connect to the database using Data>From Other Sources>Microsoft Query

2 - Modify the resulting query by right-cliking the resulting table and choosing Table>External Data Properties and clicking the little icon in the upper-corner. Then choose the Definition tab:

enter image description here

3 - Right-click the table and choose Parameters. In that dialog you can set cells to hold the parameters for the Where clauses.

For more info on this see this MSDN page and this Daily Dose post.

edited Jul 3, 2013 at 19:52

answered Jul 3, 2013 at 19:46

Doug Glancy

27.5k6 gold badges72 silver badges118 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mitchell Walker Over a year ago

So Doug, I did what you said there, and got that working. I ran a macro at the same time because I wanted to get VBA code so I can change it when I needed to. When I tried running the macro later I keep getting an error saying there is a run time error with the subscript out of range. When I click debug to show what is wrong it points me to this: With ActiveWorkbook.Connections("Query from ProductId").ODBCConnection.

Doug Glancy Over a year ago

It means that your connection name isn't "Query from ProductId." I'd guess it might now be "Query from ProductId2". This kind of programming isn't trivial, so you'll have to figure out a bunch of things like this. Based on this little bit of information, I'd suggest you program it to use the same connection every time, which would mean not adding a new one. That sounds obvious when I say it, so sorry if it's not much help.

Collectives™ on Stack Overflow

Query a SQL database using VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related