CODEWITHSUNDEEP
excelvba
vinod
vinod

Reputation: 11

Trying to get data from sql server 2005 from excel macro

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

Upvotes: 1

Views: 1801

Answers (3)

acgm invent
acgm invent

Reputation: 11

One of the reason for using late binding could be:

Late binding has the advantage that you don not have to compile your code

In case of use in a vba macro, there's no need to set a reference, which make a vba macro harder to deploy

It is said that late binding perform slower because the object-interface is later assigned to the object, hence the expression late binding.

Regards.

Upvotes: 1

Sjuul Janssen
Sjuul Janssen

Reputation: 1812

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

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

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91366

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/>")

Upvotes: 1

Related Questions