VBA not working when running on windows schedule

[jgs224466]

Hi All,

The following code runs fine when I open the excel file on my local drive (it auto runs on open). However when run on windows server via a remote desktop, the exact same code doesn't return any records or field headers. I am trying to run it manually on windows server just now before I set up a schedule

***when I run this code on my local drive the value of rs.fields.count is 30 which is correct as there are 30 fields being returned, however when I run it from windows server the value of rs.fields.count is 0, even though the code is exact same, it is also the same for rs2 which returns the actual records***

Any help would be greatly appreciated

I am using Excel 2013 on my local machine and Excel 2008 on windows server and SQL Server 2014. Also it may not be relevant but I am using Microsoft ActiveX Data Objects 2.8 Library

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim sql As String
Dim sql2 As String

' Open empty recordset and pull through field headers
sql = "Select Top 1 * from vwStockPivotTables where 1=2"

' Pulls through all data for the manager
sql2 = "Select * from vwStockPivotTables where Manager = 'John Smith'

cnn.Open "Provider=SQLNCLI11;Server=MyServerHere;Database=MyDBHere;User Id=UserNameHere;Password=PasswordHere;"

    ' define recordset properties for field headers
    With rs
    Set .ActiveConnection = cnn
        .Source = sql
        .Open
    End With
    
    ' define recordset properties for the data pull
    With rs2
    Set .ActiveConnection = cnn
        .Source = sql2
        .Open
    End With

    ' loops through to return each field header
    For h = 0 To rs.Fields.Count - 1
    Cells(1, h + 1) = rs.Fields(h).Name
    Next h

    Sheet3.Range("A2").CopyFromRecordset rs
    
    Sheet3.Range("A2").CopyFromRecordset rs2

    Set rs = Nothing
    Set rs2 = Nothing
    Set cnn = Nothing

[jgs224466]

Hi all,

I am bumping this up in the hope that someone is able to offer a solution to this.

Thanks