none
general odbc error 1004

    Question

  • Hi all,

     

    I have an Excel query that's giving me an error.

    Error opening query  error = 1004  General ODBC Error.

    I believe that this is due to the ODBC driver in use.  I have a spreadsheet that I developed on my Excel 2003 machine, and it works fine.  When another user uses the same spreadsheet on an Excel 2000 machine they get the error.

     

    The machines are set up for multiple DSN's and I let the user choose which database to access via a menu.  Then I use a different query for one particular database:

     

        If sDSN = "Client1" Then
            strSql = "SELECT DbControlNumber, StartDate, InsuranceCoName, InsuredName, " & _
                 "(SELECT 'Recommendation' WHERE DbControlNumber IN (SELECT NewDbControlNumber FROM Recommendations))" & _
                 "FROM AuditPrimary " & _
                 "WHERE StartDate between '" + sDate1 + "' and '" + sDate2 + " 23:59:59'"
        Else
            strSql = "SELECT DbControlNumber, StartDate, InsuranceCoName, InsuredName " & _
                     "FROM AuditPrimary " & _
                     "WHERE StartDate between '" + sDate1 + "' and '" + sDate2 + " 23:59:59'"
        End If

     

    This is then sent to a query table:

     

        With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
            "ODBC;DSN=" + sDSN + ";Network=DBMSSOCN;" _
            ), Array("Trusted_Connection=Yes")), Destination:=rStart)
            .CommandText = Array(strSql)
            .Name = "Query from database"
            .FieldNames = False
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlOverwriteCells
            .SavePassword = True
            .SaveData = True
            .AdjustColumnWidth = Adjust
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .Refresh BackgroundQuery:=False
        End With

     

    For any client but "Client1", the query works fine on the Excel 2000 machine. 

    If I change the query for "Client1" to match that in the Else, then "Client1" works fine, so the DSN is OK. 

    So, there must be a problem with the query itself, but again, it works fine as is on my Excel 2003 machine.

    Am I right in thinking that it's the ODBC driver that is the problem, and that upgrading the version of Office on that machine will resolve it?

     

    Thanks,

    Jack

    Friday, December 28, 2007 9:11 PM

All replies

  • Update:

     

    We upgraded the Excel 2000 machine to Excel 2003.  Same error.  Anyone have any clue as to what will solve this one?

     

    Thanks,

    Jack

     

    Friday, January 11, 2008 10:37 PM