Return ADO recordset to worksheet

Any help would be appreciated.
I am trying to return a recordset to a worksheet using ADO. I want to go
dsn-less.
I confident that my recordset returns data, I've tested this in Access and
can return records there. I am a new excel programmer.
I am trying to use CopyFromRecordset to return the records. When I am in
the VBA editor I choose Run Sub but no records are returned to Sheet1.
I'd like the recordset to be refreshed every time the sheet is activated.
I've created a reference to Microsoft ActiveX Data Objects 2.8 library.
Am I missing something?
Sub ImportData()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=MYSERVER;INITIAL CATALOG=MYDATABASE;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Open "SELECT * FROM MyTable"
Sheet1.Range("A1").CopyFromRecordset rs
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End SubSorry wrong newsgroup please ignore.
"Terri" <terri@.cybernets.com> wrote in message
news:e6c2n6$vlf$1@.reader2.nmix.net...
> Any help would be appreciated.
>sql

Retrieving from SQLServer to Word

Please,
how can I execute a SQL command into SQLServer from Word/VBA code using ADO? I want to execute a comand and work with resulting recordset.
Best regards.Please see the following link:

dbforums link (http://dbforums.com/t390641.html)

If this does not help, please respond.

Good luck !|||Thanks a lot. It works fine.

Retrieving Data using ADO

I need help retrieving data using ADO (executing a select statement)
From a SQL Server 2000 database and directly populate a table in a SQL Server Database on a different server. Does anyone have an idea on how to accomplish this task?
Thanks in advance.
RegardsR U Sure U need to do it manually with ADO ?

There are many ways of doing this type of thing|||Yes, I would like to know if anyone has ever accomplish this task using ADO. I need to incorporate the solution of this task into a project I'm working on. Thanks inadvance.

Rgeards

retrieve records affected count from ADO?

Hello,

If I run an action SP from MS Access using ADO:
...
cmd.execute

where the SP is something like Create...
Update tbl1 set fld1 = 'something' where...

how can I retrive the count of records affected like from Query
analyzer?

Thanks,
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Rich Protzel (rpng123@.aol.com) writes:
> If I run an action SP from MS Access using ADO:
> ..
> cmd.execute
> where the SP is something like Create...
> Update tbl1 set fld1 = 'something' where...
> how can I retrive the count of records affected like from Query
> analyzer?

The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your reply.

>>The first parameter to cmd.execute is RecordsAffected.

You must not have submitted SET NOCOUNT ON, to get the count.
<<

May I ask how I go about retrieving the Count of records affected back
into MS Access?

Dim CountRecsAffected As Long
...
cmd.Parameters("@.bDate").Value = sDate
cmd.Execute
CountRecsAffected = cmd.?
or
CountRecsAffected = ?
or
CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
the count of parameters being used?

Thanks again for your reply.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Rich Protzel (rpng123@.aol.com) writes:
> May I ask how I go about retrieving the Count of records affected back
> into MS Access?
> Dim CountRecsAffected As Long
> ..
> cmd.Parameters("@.bDate").Value = sDate
> cmd.Execute
> CountRecsAffected = cmd.?
> or
> CountRecsAffected = ?
> or
> CountRecsAffected = cmd.Parameters.Count? Wouldn't this one just give me
> the count of parameters being used?

cmd.Execute CountRecsAffected

Assuming that Access works like Visual Basic.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Sun, 3 Aug 2003 16:08:14 +0000 (UTC) in
comp.databases.ms-sqlserver, Erland Sommarskog <sommar@.algonet.se>
wrote:

>Assuming that Access works like Visual Basic.

For most things, including ADO, it does.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)|||Thank you all for your replies. I think I get the idea now about how to
retrieve the count of records affected from an action sp.

One more question if I may:

If I set my sp to

SET NOCOUNT ON

would that improve the performance of my sp? It is not critical for me
to retrieve the count of records affected, mostly just a check. But if
the sp works consistently, and setting

SET NOCOUNT ON

significantly improve performance, then maybe I should consider that.
Most of my action sp's are affecting over 100,000 records of tables with
nearly 200 fields (no redundant fields) with over 1,000,000 records.

Thanks again,

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!