Copyfromrecordset returns no data

[JohnJoeDRC]

Hi

Please help me with the code here.

I am getting nothing returned to excel

' Open the connection and execute.
    conn.Open sConnString
    Set rs = conn.Execute("SELECT idEmployee FROM table;")
 
    ' Check we have data. and yes in SQL I have 8 rows
    If Not rs.EOF Then
        ' Transfer result.
       MsgBox "We have Data"
 
       MsgBox rs.RecordCount
        ' returns -1
       MsgBox rs.Fields(0).Value
        ' returns 1
       Sheet4.Range("A1").CopyFromRecordset rs
       ' returns no data to sheet
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If

[zmbd]

+ Which version of Office/Excel are you are using.

+ We need the connection string
2: conn.Open sConnString

+ Line 6 will most likely evaluate to true because you are at the Beginning of the record set.

+Line 10 will not return an accurate record count until you have moved to the last record of the set.

[JohnJoeDRC]

Hi

Here is the full vba

' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=ME;Password=Dunno;"
 
    ' Create the Connection and Recordset objects.
    Set Conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
' Open the connection and execute.
    Conn.Open sConnString
    Set rs = Conn.Execute("SELECT idEmployee FROM Table1;")
 
    ' Check we have data. and yes in SQL I have 8 rows
    If Not rs.EOF Then
        ' Test with msgbox EOF
       MsgBox "We have Data"
 
        ' Test the count - returns -1
       MsgBox rs.RecordCount
 
        ' test the Field Value   - returns 1
       MsgBox rs.Fields(0).Value
 
        ' returns no data to sheet
        Sheet4.Range("A1").CopyFromRecordset rs
 
    ' Close the recordset
        rs.Close
    Else
        MsgBox "Error: No records returned.", vbCritical
    End If
Set Conn = Nothing
Set rs = Nothing
 

Excell is 2013
SQL is 2008 R2

[NeoPa]

Please be more specific about what it is you're asking for help with. We cannot run your code so you need to tell us exactly what does happen and also , preferably, what you expected, so where it differs from expectation. That way we have something to work with. Line numbers are important. Some people think they can explain adequately without any reference to where in the code the problems occur. It doesn't take a lot of thought to realise they're mistaken.

[ADezii]

It could be the context in which the Code is being executed, try:

ActiveWorkbook.Worksheets("Sheet4").Range("A1").CopyFromRecordset rs

[JohnJoeDRC]

Hi There

The problem is that There is data in SQL, and that the data is not returned to Excell as per line 23. I am expecting the result set of 8 lines that is in SQL to be returned.

I have also now skipped the test and I am just trying to return data.

' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=MyDatabase;User ID=ME;Password=Dunno;"
 
    ' Create the Connection and Recordset objects.
    Set Conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
 
' Open the connection and execute.
    Conn.Open sConnString
    Set rs = Conn.Execute("SELECT idEmployee FROM Table1;")
 Sheet4.Range("A1").CopyFromRecordset rs
 

The above is stating there is no data. But there is DATA in SQL 100%. Tested the Query again.

[NeoPa]

JohnJoeDRC:
I have also now skipped the test and I am just trying to return data.

That's really not a good idea. At least, if you want to do that then please don't expect us to jump around with you after spending energy trying to catch up with where you started. Don't get me wrong. Initiative is great. Try things and report them separately by all means. No one wants to work with anyone who keeps changing where they're at willy-nilly though. It's hard enough understanding what you're saying without it changing just as we work out what you were trying to say before.

Getting back to where we started and where we have some idea of what's going on, your post #3 indicates that the record count is shown as -1. That should be ringing bells. I don't do much with ADODB myself but I know when dealing with SQL Server it's often the case that the whole recordset is not transferred at once and this often leaves the record count not properly set. It may be that this needs to be made to work by going to the end of the recordset before checking the count.

Still with post #3, your checks are actually very helpful - as are the comments in the code. Once the code there has run can you press Ctrl-End in the worksheet. If it stays at cell A1 then you know the transfer has truly failed. If it goes elsewhere then we may need to look at the possibility of data being present but just not being visible. Excel does that sometimes.