Getting data from SQL Server into Excel
 

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.

Getting data from SQL Server into Excel
 

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" wrote in message
...
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.

Getting data from SQL Server into Excel
 

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)

"Bob Phillips" wrote in message
...
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" wrote in message
...
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.

Getting data from SQL Server into Excel
 

Bob, To my amazement, it worked! Thanks very much for your help.

Getting data from SQL Server into Excel
 

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)

"Sophea" wrote in message
...
Bob, To my amazement, it worked! Thanks very much for your help.