Trying to get data from sql server 2005 from excel macro

Question

Hi

I am trying to get data from sql server 2005 to excel.. I have written code in excel vba

Below is my code

Dim strConnection, conn, rs, strSQL

strConnection = "Provider=sqloledb;Data Source=LEON7269-G09\SQLEXPRESS;Initial Catalog=test;User Id=sa;Password=sa@123;"

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnection

Set rs = Server.CreateObject("ADODB.recordset")
strSQL = "SELECT * FROM UserDetails"
rs.Open strSQL, conn, 3, 3

rs.MoveFirst
While Not rs.EOF
     Response.Write (rs("myField") & "<br/>")
rs.MoveNext
Wend

rs.Close
Set rs = Nothing

conn.Close
Set conn = Nothing

But i am getting error at line Set conn = Server.CreateObject("ADODB.Connection")

as runtime error 424

i have tried adding references in vba-->tools-->references but nothing is working ...Please guide me

Answer 1

If this is Excel VBA, you should get rid of all references to server, that is:

 CreateObject("ADODB.Connection")

Not

 Server.CreateObject("ADODB.Connection")

This won't work, either:

  Response.Write (rs("myField") & "<br/>")

Answer 2

You mentioned you have laid a reference then you should have this

Dim conn as Connection
Dim rst as Recordset
Set conn = New Connection