Excel & MS Sql
 

Hi All
I need to retrieve data from MSSQL2k Server table and populate the
spreadsheet page with the table data.
I can do this with a DSN and run querry data but I need to build the
connection string into the spreadsheet itself so when a tab is clicked it
retrieves a qry for that page.
I understand sql & connection details a bit so can work through a sample to
use in my situation.
The reason I want to email pass data to an employee from a remote site but
do not want to make the connection details available to anyone.
Also I just want to email the spreadsheet to employee so they don not have
to install dsn or configure any connection details.


Is this possible, and is it a tall order as if it is im will to renumerate
for any help.

Regards
Don

Excel & MS Sql
 

Gi Don,

With ADO it is straight-forward thing. Here is some sample code


Sub GetData()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
dim sSQL as string
Dim myArray

Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseClient
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

Set oRS = New Recordset
sSQL = "select * from [MyTable]",
oRS.Open sSQL,oConn, adOpenStatic, adLockOptimistic
'load a worksheet
Range("A2").CopyFromRecordset oRS
'load an array
my = oRS.GetRows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Sub

You should set a reference to the ACtiveX Data Objects and ACtuiveX Data
Objects Recordset libraries in ToolsReferences.

Also change the values in tyhe connection string to suit

--

HTH

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

"Don Grover" wrote in message
...
Hi All
I need to retrieve data from MSSQL2k Server table and populate the
spreadsheet page with the table data.
I can do this with a DSN and run querry data but I need to build the
connection string into the spreadsheet itself so when a tab is clicked it
retrieves a qry for that page.
I understand sql & connection details a bit so can work through a sample
to
use in my situation.
The reason I want to email pass data to an employee from a remote site but
do not want to make the connection details available to anyone.
Also I just want to email the spreadsheet to employee so they don not have
to install dsn or configure any connection details.


Is this possible, and is it a tall order as if it is im will to renumerate
for any help.

Regards
Don

Excel & MS Sql
 

Thanks Bob
This does what i want,.
I just set the Tab Activate to call each indervidual qry.
Now all i have to do is dress it up abit

Regards
Don

"Bob Phillips" wrote in message
...
Gi Don,

With ADO it is straight-forward thing. Here is some sample code


Sub GetData()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
dim sSQL as string
Dim myArray

Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseClient
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

Set oRS = New Recordset
sSQL = "select * from [MyTable]",
oRS.Open sSQL,oConn, adOpenStatic, adLockOptimistic
'load a worksheet
Range("A2").CopyFromRecordset oRS
'load an array
my = oRS.GetRows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Sub

You should set a reference to the ACtiveX Data Objects and ACtuiveX Data
Objects Recordset libraries in ToolsReferences.

Also change the values in tyhe connection string to suit

--

HTH

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

"Don Grover" wrote in message
...
Hi All
I need to retrieve data from MSSQL2k Server table and populate the
spreadsheet page with the table data.
I can do this with a DSN and run querry data but I need to build the
connection string into the spreadsheet itself so when a tab is clicked
it
retrieves a qry for that page.
I understand sql & connection details a bit so can work through a sample
to
use in my situation.
The reason I want to email pass data to an employee from a remote site
but
do not want to make the connection details available to anyone.
Also I just want to email the spreadsheet to employee so they don not
have
to install dsn or configure any connection details.


Is this possible, and is it a tall order as if it is im will to
renumerate
for any help.

Regards
Don

Excel & MS Sql
 

I said it was easy<vbg

--

HTH

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

"Don Grover" wrote in message
...
Thanks Bob
This does what i want,.
I just set the Tab Activate to call each indervidual qry.
Now all i have to do is dress it up abit

Regards
Don

"Bob Phillips" wrote in message
...
Gi Don,

With ADO it is straight-forward thing. Here is some sample code


Sub GetData()
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
dim sSQL as string
Dim myArray

Set oConn = New ADODB.Connection
oConn.CursorLocation = adUseClient
oConn.Open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword"

Set oRS = New Recordset
sSQL = "select * from [MyTable]",
oRS.Open sSQL,oConn, adOpenStatic, adLockOptimistic
'load a worksheet
Range("A2").CopyFromRecordset oRS
'load an array
my = oRS.GetRows()
oRS.Close
Set oRS = Nothing
oConn.Close
Set oConn = Nothing

End Sub

You should set a reference to the ACtiveX Data Objects and ACtuiveX Data
Objects Recordset libraries in ToolsReferences.

Also change the values in tyhe connection string to suit

--

HTH

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

"Don Grover" wrote in message
...
Hi All
I need to retrieve data from MSSQL2k Server table and populate the
spreadsheet page with the table data.
I can do this with a DSN and run querry data but I need to build the
connection string into the spreadsheet itself so when a tab is clicked
it
retrieves a qry for that page.
I understand sql & connection details a bit so can work through a
sample
to
use in my situation.
The reason I want to email pass data to an employee from a remote site
but
do not want to make the connection details available to anyone.
Also I just want to email the spreadsheet to employee so they don not
have
to install dsn or configure any connection details.


Is this possible, and is it a tall order as if it is im will to
renumerate
for any help.

Regards
Don

Excel & MS Sql
 

"Bob Phillips" wrote ...

You should set a reference to the ActiveX Data Objects and
ActiveX Data Objects Recordset libraries

Bob,
All that's required is the reference to Microsoft ActiveX Data Objects
(ADODB). The reference to Microsoft ActiveX Data Objects Recordset
(ADOR) isn't required if you fully declare your Recordset object as
ADODB.Recordset.

From MSDN, ADOR is:

"A lightweight version of ADO that contains only the
functionality of the ADO Recordset object. This version
of ADO is typically used only from script in a Web page
to minimize memory requirements."

Aside: I noticed your lines (typo corrected)

Range("A2").CopyFromRecordset oRS
myArray = oRS.GetRows()

will cause a run-time error on the second line because
CopyFromRecordset has caused EOF. You need e.g.

oRS.MoveFirst

between the two.

Jamie.

--

Excel & MS Sql
 

Hi Jamie,

Thanks for correcting me (grrr!).

Seriously, I keep saying that about Recordset, and keep getting corrected.
One day ...

That last bit was meant to be an either or, giving the OP a choice. I
wouldn't use both personally, so there would be no need for the MoveFirst
(see I have an answer for everything <vbg).

--

HTH

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

"onedaywhen" wrote in message
om...
"Bob Phillips" wrote ...

You should set a reference to the ActiveX Data Objects and
ActiveX Data Objects Recordset libraries

Bob,
All that's required is the reference to Microsoft ActiveX Data Objects
(ADODB). The reference to Microsoft ActiveX Data Objects Recordset
(ADOR) isn't required if you fully declare your Recordset object as
ADODB.Recordset.

From MSDN, ADOR is:

"A lightweight version of ADO that contains only the
functionality of the ADO Recordset object. This version
of ADO is typically used only from script in a Web page
to minimize memory requirements."

Aside: I noticed your lines (typo corrected)

Range("A2").CopyFromRecordset oRS
myArray = oRS.GetRows()

will cause a run-time error on the second line because
CopyFromRecordset has caused EOF. You need e.g.

oRS.MoveFirst

between the two.

Jamie.

--