Pass parameters to SQL query in Excel

[lcpx]

Hi all,

I am new to this forum and a novice at VBA programming, only have couple of years experience of MS SQL.

I have 2 problems in VBA for Excel.

1. How to establish an ODBC connection in VBA for MS SQL Server, can anybody give me an example based on Northwind DB. Many thanks.

2. I also want to ask about Pass parameters into a SQL query in Excel.

In my work sheet, at the the first row, I would like my users can type a DateTime in the field A1 or whatever, and also select a value from a drop list next to it. for example, user can type date like: 2005-10-28 and select a product_code. Then press a button next to the field. At the end, the worksheet will display the result from SQL server DB.

Let's say: Select t.* from northwind.orders t where t.OrderDate = '2005-10-28' and t.shipcity like 'xxxxx'

I don't know how to pass the value typed by a user to a SQL query in VBA. Can anybody help me out, or is there any documents or demo I can have a look.

Thanks very much for your help.

[Bob Phillips]

1. How to establish an ODBC connection in VBA for MS SQL Server, can anybody give me an example based on Northwind DB. Many thanks.

Carl Prothman provides examples at http://www.carlprothman.net/Default....erForSQLServer

2. I also want to ask about Pass parameters into a SQL query in Excel.

In my work sheet, at the the first row, I would like my users can type a DateTime in the field A1 or whatever, and also select a value from a drop list next to it. for example, user can type date like: 2005-10-28 and select a product_code. Then press a button next to the field. At the end, the worksheet will display the result from SQL server DB.

Let's say: Select t.* from northwind.orders t where t.OrderDate = '2005-10-28' and t.shipcity like 'xxxxx'

I always setup my SQL query in a string and pass that string to the query call. For your case, that would be

[VBA]
sSQL = "SELECT t.* FROM northwind.orders t " & _
"WHERE t.OrderDate = '" & Format(Range("A1").Value, "yyyy-mm-dd") & "' " & _
"AND t.shipcity LIKE '" & Range("B1").Value & "'"
[/VBA]

[lcpx]

Thanks very much for your prompt response, it's very helpful.

I am also facing another 2 problems,

1. How to make a drop list box, like ShipCitys, instead of typing a city's name in the cell, users can select one or more than one citis from the drop list, then pass the value to the SQL query. (We may have more than 100 cities to ship product, so is there any way I can add the items to the drop down list from database, like: Select distinct ShipCitis from Orders)


2. how should I add a submit button, when I click the button, data will be loaded to the spreadsheet from DB.

Appreciate any thought you have on it!

[XL-Dennis]

Bob - I don't know how You see things but personally I feel that people who cross post missuse the free resources offered on public boards:

http://www.ozgrid.com/forum/showthre...133#post210133

Kind regards,
Dennis

[lcpx]

Hi Dennis,

I am very sorry about this, I hope you can have a look at this:

http://www.ozgrid.com/forum/showthre...140#post210140

I promise you it won't happen again, and thanks for your time, consideration and understanding.

Best regards,
Peter