SQL with Excel

[VICKEL1]

I have created an ADODB connection to a sql server 2000 database. However, when I use the copyfromrecordset command I don't get all the data back.

The field which the command doesn't like is a field declared as text with a length of 16.

Is there any other way I could get the data?

 

[SkipVought]

Hi,

...I don't get all the data back.

Do you mean you are missing ROWS of data or COLUMNS of data?

I'm guessing COLUMN(s).

How many columns is it not returning?

How are you accessing the data?

Please post your code.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[VICKEL1]

Missing the Columns

Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


con.Open "Driver={SQL Server};Server=Server;Database=Database;Uid=USERID;Pwd=Password;"


If rs.EOF = False Then

'get field headings from db
For i = 0 To rs.fields.Count - 1
Cells(1, i + 1) = rs.fields(i).Name
Next 'i

'return recordset in one lump to Cell A2
Cells(2, 1).CopyFromRecordset rs

End If

 

[PHV]

And where is rs open ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[VICKEL1]

rs.open is before

'cmdText = "Execute sp_test 116,'16-May-2005',30,60,90,120"


'Open connection
rs.Open cmdText, con

 

[SkipVought]

What sql does Execute sp_test 116 execute?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[BobBob10]

I've had the same problem before which I was unable to fix where I had a simple select statement but the field was declared as text and the recordset only brough the first record back for that column - very frustrating.

So if anyone has the solution then please let me know.