VBA code not working after going from Windows XP to Windows 2007

[makeitwork09]

My work laptop was recently replaced. The new laptop uses Windows 7. The old laptop used XP. Both laptops use Excel 2007.

Today when I went to execute a Macro that was created on the old laptop and that ran without an issue.

The bold line below is where a receive the "Method or data member not found" error. What could be causing this?

    'make sure data is returned
    If Not rsData.EOF Then
        ' Add headers to the worksheet.
        With ThisWorkbook.Sheets("SFP Data Dump").Range("A1")
            For Each objField In rsData.Fields
                [b][COLOR=#EF2929].Offset(0, lOffset).Value = objField.Name[/color][/b]
                lOffset = lOffset + 1
            Next objField
            .Resize(1, rsData.Fields.Count).Font.Bold = True
        End With

Thanks

 

[SkipVought]

hi,

How are your variables declared?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[makeitwork09]

As follows

    Dim cmdl As ADODB.Command
    Dim objConn As ADODB.Connection
    Dim rsData As ADODB.Recordset
    Dim objField As ADODB.Field
    Dim lOffset As Long
    Dim sConnect As String
    Dim sSQL As String
    Dim ServerName As String
    Dim DBName As String
    Dim EndDate As Date
    Dim DateEntered As Date
    Dim aDay As Integer

    BkName = ThisWorkbook.Name
    
    ServerName = "YELIM-LMSDBPRO"
    DBName = "LMS_YEL"

 

[makeitwork09]

As well as

'create connection string
    sConnect = "Provider=SQLOLEDB;" & _
               "Data Source=" & ServerName & ";" & _
               "Initial Catalog=LMS_YEL;" & _
               "Integrated Security=SSPI"
        
    'create the connection and recrodset objects
    Set objConn = New ADODB.Connection
    
    'Set CONNECTION timeout property
    objConn.CommandTimeout = 0

    'Create a new command object to process the stored proc
    Set cmdl = New ADODB.Command
    
        With cmdl
            .ActiveConnection = sConnect
            'set COMMAND timeout property - query can time out on either the connection OR the command
            .CommandTimeout = 0
            .CommandText = sSQL
            .CommandType = adCmdText
'            .Refresh False
            Set rsData = .Execute()
    End With
    
    'Delete Query Results sheet if it exists
    On Error Resume Next
    Application.DisplayAlerts = False

    ThisWorkbook.Sheets("SFP Data Dump").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    'Add Query Results sheet
    ThisWorkbook.Sheets.Add(After:=Worksheets(Worksheets.Count)).Name = "SFP Data Dump"
[/code[

 

[makeitwork09]

I found this and am wondering if this has something to do with the problem.

Apparently, Microsoft now has two ODBC management programs (32b & 64b)

By default, Windows uses the 64bit program to add datasources (which apparently Excel doesn't like).

I used the 32bit ODBC program to add our System DSN and Excel found it immediately.

For those that run into this problem on 64-bit boxes, the 32-bit ODBC program can be found here:

C:\Windows\SysWOW64\odbcad32.exe

I have to wait until tomorrow to see what my IT folks say.