Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Wheaton, IL
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC runtime 1004 (Excel 2000 9.0.4402 sr1)

    I am trying to run a query against an existing sheet named "trackerExtract" of a workbook containing a named range (SCRData) and get error 1004.

    1. What causes this
    2. How do I debug it?

    The MS site has not been terribly helpful. The macro code is below

    Sub jbTryme3()

    xQuery = "select * from SCRData "

    xPath = ActiveWorkbook.Path & ""
    xFile = ActiveWorkbook.Name
    xSheet = "!trackerExtract"
    xFull = xPath & xFile
    xConn = "Driver={Microsoft Excel Driver (*.xls)};" & _
    "DBQ=" & xFull & ";ReadOnly=False;"

    Dim qt As QueryTable
    With ActiveSheet.QueryTables.Add(Connection:=xConn, _
    Destination:=Range("b2"), Sql:=xQuery)
    .FieldNames = True
    .Refresh
    End With

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ODBC runtime 1004 (Excel 2000 9.0.4402 sr1)

    I'm not familiar with this type of connection, so I can't answer your questions 1 and 2. Does the following work?

    xConn = "ODBC;DSN=Excel Files;DBQ=" & xFull & ";"

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ODBC runtime 1004 (Excel 2000 9.0.4402 sr1)

    Try to report the error details using something like this: (I picked this up from a Google search)

    Excel.Application.ODBCErrors.Item(1).ErrorString

    Also, if you are connecting to the open workbook, I would think there's a simpler way, but I'm not an Excel person.

  4. #4
    New Lounger
    Join Date
    May 2002
    Location
    Wheaton, IL
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC runtime 1004 (Excel 2000 9.0.4402 sr1)

    To complete the thread, I used the macro recorder to get the connection. xConn is probably a misleading variable name. The query is stored in variable xQuery. After cleaning up the generated code, the function query is below:

    xPath = ActiveWorkbook.Path & ""
    xFile = ActiveWorkbook.Name

    xODBC = "ODBC;DSN=Excel Files;DBQ=" _
    & xPath _
    & xFile _
    & ";DefaultDir=" _
    & xPath
    xConn = xPath _
    & ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;"

    With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y(xODBC), Array(xConn)), Destination:=ActiveCell)

    .CommandText = xQuery

    .Name = "Query " & xFile
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = True
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .Refresh BackgroundQuery:=False
    End With

    For debugging, the hint from google was valuable, thanks. Among other things, it told me when I induced an SQL syntax error

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •