Excel & MS Sql

D

Don Grover

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
[email protected]
 
B

Bob Phillips

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 Tools>References.

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)
 
D

Don Grover

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 said:
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 Tools>References.

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 said:
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
[email protected]
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
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 said:
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 Tools>References.

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 said:
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
Click to expand...
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
[email protected]
Click to expand...
Click to expand...
Click to expand...
 
O

onedaywhen

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

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.

--
 
B

Bob Phillips

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