Getting data from SQL Server into Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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)
 
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)
 
Back
Top