Excel & SQL server

[bijan]

Hi All
I need a sample code to connect, run a query in sql server and display
results in excel with these informations:
-worksheetname is "SHEET1"
-sql server name is "MYSQL"
-database name is "TEST"
-table name is "TABLE1"
-userid is "USER1"
-password is"PASS"
-my script file located in my computer D:\test\QUERY1.TXT(can be done in
query analyzer)
Best regards

 

[Sam Wilson]

First, go to tools/references and tick Microsoft ActiveX Data Objects 2.7 (or
whatever the latest version is)

The use this:

Sub demo()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim s As String

Set con = New ADODB.Connection
con.Open "Driver={SQL Server};Server=MYSQL;Database=TEST;Uid=USER1;Pwd=PASS;"

s = "Your SQL statement here"

Set rs = con.Execute(s, , 1)

Range("a1").CopyFromRecordset rs

rs.Close

con.Close

End Sub

 

[bijan]

Thank you so much Sam,it's working perfectly
I want to know is there another way to keep my query statments in sql server
and just pass parameters from excel to it or not?
Thanks

 

[Sam Wilson]

Bijan,

I've never used it with a query statement from SQL server, but I would
imagine it is possible. In the line

"set rs = con.execute(s,,1)"

s was the sql query, but it can also be a QueryDef object - I've never used
them but that could be the way to do it.

Sam