Sleeper: SQL Query

[shickles]

I am new to SQL Query in excel. I am trying to connect to a sql server and dump certain information into certain cells. I have used the data import. I need a more automated solution. Can someone point me in the right direction?

-Thx

[Zack Barresse]

Hello shickles,

Here is a good example of that .. [/url][url="http://www.excelguru.ca/XLVBA/XLVBA03.htm"]found here. (http://www3.telus.net/kenpuls/ExcelPages/export_via_sql.htm)

[Ken Puls]

Hi Shickles,

FYI, The code that Zack linked you to on my website is actually meant to take the Excel side and push it into Access. I built that, though, based on the code below. This was built spefically to pull data from an Access 2000 database using SQL, so the query itself may not be helpful, but I hope that you can digest what pieces go where. :yes I have commented it for you, so hopefully that will help a bit.


Private Sub cmdQueryFore_Click()
Dim sql
Dim Conn As ADODB.Connection
Dim RS As ADODB.Recordset
Dim startdate As String, enddate As String
'Create a new ADO connection
Set Conn = New ADODB.Connection
'Initialize the connection to the database
Conn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='\\ServerName\Sharename\Training\4reserve.mdb'"
'define start and end dates for the report
startdate = "#" & Format(Worksheets(1).Range("B1"), "mm/dd/yyyy") & "#"
enddate = "#" & Format(Worksheets(1).Range("B2"), "mm/dd/yyyy") & "#"
'Clear the data range where output should be placed
Worksheets("Sheet1").Range("A5:D" & Worksheets("Sheet1").Range("C65536").End(xlUp).Row).ClearContents
'Assign SQL query to a string
sql = "SELECT DISTINCT Schedule.ReservationDate, (select count(a.reservationdate)" & _
" from schedule a where a.reservationdate = schedule.reservationdate and" & _
" isnull(a.ghin1))+(select count(a.reservationdate) from schedule a where" & _
" a.reservationdate = schedule.reservationdate and isnull(a.ghin2))+(select" & _
" count(a.reservationdate) from schedule a where a.reservationdate =" & _
" schedule.reservationdate and isnull(a.ghin3))+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin4)) AS Available, (select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin1)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin2)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin3)=false)+(select " & _
" count(a.reservationdate) from schedule a where a.reservationdate = " & _
" schedule.reservationdate and isnull(a.ghin4)=false) AS Booked, [available]+[booked] AS Total " & _
" from Schedule " & _
" WHERE (((Schedule.ReservationDate)>=" & startdate & " And (Schedule.ReservationDate)<=" & enddate & "));"
'Create a new recordset, and execute the SQL query to fill it
Set RS = Conn.Execute(sql)
'Paste the SQL query results to the worksheet
Worksheets("Sheet1").Cells(5, 1).CopyFromRecordset RS
End Sub
Now, it is important to note that this will work with Excel 2000+, BUT NOT EXCLE 97! If you need to get it going in 97, take a look at this article (http://support.microsoft.com/default.aspx?scid=kb;en-us;246335&Product=xlw).

I'm not sure of your VBA level, so if you need any help with it at all, post back.

Cheers,

[NateO]

Hello, you'll need to adjust the provider from Jet to Sql Server as well. E.g.,

http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#OLEDBProviderForSQLServer

[Ken Puls]

Thanks Nate!

Don't have an SQL server myself, so wouldn't have caught that until it became an issue! :yes