none
Microsoft Visual Basic Run-time error '1004' : General ODBC Error RRS feed

  • Question

  • The following project worked well in Excel 97 before ugrading to a new version of Office, but now I get the above error when trying to run this in Excel 2003, 2007 or 2010.  When I click on the debug it points to .Refresh BackgroundQuery:=False.  I know that this error is generic and I was hoping that I could get some assistance with what could be wrong with the code.

     

    Option Explicit

    Type StyleType
        StyleName As String
        FontSize As Variant
        FontStyle As String
        TextBackColor As String
        RowHeight As Variant
        ColumnWidth As Variant
        Alignment As String
        TextWrap As String
        NumericStyle As String
        DateStyle As String
        Border As String
    End Type


    Public Type typMenuItem
        item_uno As Integer
        item_code As String
        item_name As String
        item_desc As String
        item_sample_url As String
    End Type

    Public Type typMenuItem2
        item_code As String
        item_name As String
    End Type

    Public UseProd As Boolean, _
           ProdServer As String, _
           ProdDatabase As String, _
           ProdLogin As String, _
           ProdCompanyCode As String, _
           ProdPadClient As String, _
           ProdPadMatter As String, _
           ProdPadEmployee As String, _
           ProdPadJENumber As String, _
           UseReport As Boolean, _
           ReportServer As String, _
           ReportDatabase As String, _
           ReportLogin As String, _
           ReportCompanyCode As String, _
           ReportPadClient As String, _
           ReportPadMatter As String, _
           ReportPadEmployee As String, _
           ReportPadJENumber As String, _
           RunProd As Boolean, _
           ProdDisplay As String, _
           ReportDisplay As String
          
          
    Public _
        ConnectString As String, _
        AllClients As Boolean, _
        AllMatters As Boolean, _
        AllTimekeepers As Boolean, _
        AllOffices As Boolean, _
        AllDepartments As Boolean, _
        AllPeriods As Boolean, _
        aryStyles() As StyleType, _
        arySheets() As Worksheet, _
        CodeOutput As String, _
        NameOutput As String, _
        TempARBuckets As Boolean, _
        ARBuckets As String, _
        Report As Workbook, _
        FirstRun As Boolean, _
        Term As String, _
        ShortDateCaption As String, _
        BulkRunFile As String, _
        BulkRunErrorFileName As String, _
        Monochrome As Boolean, _
        BoldAllColors As Boolean
       
       
           
    Sub GetData(ByVal strCommand As String, Optional ByVal HorzOffset As Long = 0)

    ThisWorkbook.Activate

    Application.Cursor = xlWait

    If strCommand = "" Then

        Exit Sub

    Else

        Select Case Sheets("Settings").Cells(41, 1).Value
       
        Case 0

            strCommand = strCommand
       
        Case 1
       
            strCommand = "set language " & "us_english " & strCommand
       
        Case 1
       
            strCommand = "set language " & "British " & strCommand
       
        Case 3
       
            strCommand = "set language " & "Dansk " & strCommand
       
        Case 4
       
            strCommand = "set language " & "Deutsch " & strCommand
       
        Case 5
       
            strCommand = "set language " & "Español " & strCommand
       
        Case 6
       
            strCommand = "set language " & "Français " & strCommand
       
        Case 7
       
            strCommand = "set language " & "Italiano " & strCommand
       
        Case 8
       
            strCommand = "set language " & "Nederlands " & strCommand
       
        Case 9
       
            strCommand = "set language " & "Norsk " & strCommand
       
        Case 10
       
            strCommand = "set language " & "Português " & strCommand
       
        Case 11
       
            strCommand = "set language " & "Svenska " & strCommand
       
        End Select
       
    End If

    Call BuildConnectString(1)


    Dim i As Long, CurrentSheetName As String, PassString As String

    If HorzOffset = 0 Then

        CurrentSheetName = ActiveSheet.Name
       
        If CurrentSheetName = "RawData" Then
       
            For i = 1 To Sheets.Count
                If Sheets(i).Name <> "RawData" Then
           
                    CurrentSheetName = Sheets(i).Name
                   
                    Exit For
                   
                End If
            Next i
           
            If CurrentSheetName = "RawData" Then
       
                Sheets.Add After:=Sheets(Sheets.Count)
                ActiveSheet.Name = "Sheet1"
                CurrentSheetName = ActiveSheet.Name
           
            End If
       
        End If
       
        For i = 1 To Sheets.Count
            If Sheets(i).Name = "RawData" Then
       
                Application.DisplayAlerts = False
                Sheets("RawData").Delete
                Application.DisplayAlerts = True
               
                Exit For
           
            End If
        Next i
       
        Sheets("Run FASR").Activate
       
        Sheets.Add
        ActiveSheet.Name = "RawData"
        ActiveSheet.Cells.Font.Color = ActiveSheet.Cells.Interior.Color

    End If

    Sheets("Run FASR").Activate

    If UseProd = True And RunProd = True Then

        PassString = ";PWD=" & D28339675(Sheets("Settings").Cells(5, 1).Value)

    Else

        PassString = ";PWD=" & D28339675(Sheets("Settings").Cells(13, 1).Value)

    End If

    With Sheets("RawData").QueryTables.Add(Connection:= _
        ConnectString & PassString _
        , Destination:=Sheets("RawData").Range("$A$" + CStr(1 + HorzOffset)))
        .Sql = strCommand
        .FieldNames = False
        .RowNumbers = False
        .HasAutoFormat = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = False
        .RefreshStyle = xlOverwriteCells
        .SavePassword = False
        .SaveData = True
        .Refresh BackgroundQuery:=False
    End With

    Application.Cursor = xlDefault
      
    End Sub

    Any help will be appreciated.

    • Moved by kleinma Wednesday, August 10, 2011 8:11 PM (From:Visual Basic General)
    Wednesday, August 10, 2011 8:09 PM

All replies