Getting data from SQL Server into Excel

S

Sophea

I am trying to pull data out of an SQL database from within my Excel workbook (VBA). The Help section advises that through References command (Tool menu), I establish a reference, which I do by checking the ActiveX Data Objects 2.7 library. However, this does not give me access to the functions that I require, namely SQLOpen, SQLExecQuery, SQLRetrieve, etc. Can anyone help me get access to these functions? Thank you.
 
B

Bob Phillips

Sophea,

It won't, it will give you access to ADO, which ia MS's proprietary
(universal) data access layer. This works in conjunction with vendor data
providers to give a uniform access layer.


Wht you do is setup a connection string, connect to the data source, and
then issue an SQL command. As an example

Dim oConn As ADODB.Connection

Set oConn = New Connection
oConn.Open "Provider=sqloledb;" & _
"Data Source=(local);" & _
"Initial Catalog=myDatabaseName;" & _
"User ID=myUsername;" & _
"Password=myPassword"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sophea said:
I am trying to pull data out of an SQL database from within my Excel
Click to expand...
workbook (VBA). The Help section advises that through References command
(Tool menu), I establish a reference, which I do by checking the ActiveX
Data Objects 2.7 library. However, this does not give me access to the
functions that I require, namely SQLOpen, SQLExecQuery, SQLRetrieve, etc.
Can anyone help me get access to these functions? Thank you.
 
B

Bob Phillips

Should have added this to get the data

Dim oRS As ADODB.RecordSet

Set oRS = New RecordSet
SQLString = "Select * From myTable"
Set oRS = oConn.Execute(SQLString)
aryRecordSet = oRS.GetRows() ' puts rows into an array or
Range("A2").CopyFromRecordset oRS 'put to worksheet range

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Why?<vbg>

Is Sophea Richard?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top