Hi guys, please help me with this issue.
I have a Database in access and try to make report on Excel.
I need to make a query first (several links between tables).
When I try to run it Error will appear."run_time Error '1004' Application_Defined or Object_defined Error",and highlight the ".Refresh BackgroundQueryTables:=False ".
Here is my code .
Code:
Sub Macro1()
   Sheets("DATA1").Select
   Sheets("DATA2").Range("a2:j65336").Clear
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=MS Access Database;DBQ=C:\Program Files\StatPakPC\Database\test_80_2nd.mdb;DefaultDir=C:\Program Files\StatPakPC\Database;D" _
        ), Array("riverId=25;FIL=MS Access;MaxBufferSize=4096;PageTimeout=100;")), _
        Destination:=Range("A1"))

        .CommandText = Array( _
        "SELECT ProductLine.LineID, LineRun.RunID, Product.ProductID, Material.MaterialName, ProductLine.UnitsPerMin, RunLineDeviceRecipe.UnitWt_SET, LineRun.ActualUnits, LineRun.ActualWt, LineRun.DateOpened, LineRun.DateClosed," _
        , _
        "ProductLine.ProductID, LineDeviceStateLog.DTReasonID, Material.MaterialID" & Chr(13) & "" & Chr(10) & "FROM  `C:\Program Files\StatPakPC\Database\test_80_2nd`.Product Product, `C:\Program Files\StatPakPC\Database\test_80_2nd`.Material Materia" _
        , _
        "l, `C:\Program Files\StatPakPC\Database\test_80_2nd`.LineRun LineRun, `C:\Program Files\StatPakPC\Database\test_80_2nd`.ProductLine ProductLine, `C:\Program Files\StatPakPC\Database\test_80_2nd`.RunLineDeviceRecipe Run" _
        , _
        "LineDeviceRecipe, `C:\Program Files\StatPakPC\Database\test_80_2nd`.LineDeviceStateLog LineDeviceStateLog" & Chr(13) & "" & Chr(10) & "WHERE Material.MaterialID = Product.MaterialID AND Product.ProductID = ProductLine.ProductID AND RunLineDev" _
        , _
        "iceRecipe.RunID = ProductLine.RunID AND RunLineDeviceRecipe.LineID = LineRun.LineID AND LineRun.RunID = RunLineDeviceRecipe.RunID AND LineDeviceStateLog.LineID = LineRun.LineID AND LineDeviceStateLog.DateOpened = LineR" _
        , _
        "un.DateOpened AND LineDeviceStateLog.DateClosed = LineRun.DateClosed" & Chr(13) & "" & Chr(10) & "ORDER BY ProductLine.LineID, Material.MaterialID, LineRun.RunID" _
        )
        .Name = "test_80_2nd Customer"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:\Documents and Settings\Administrator\Application Data\Microsoft\Queries\test_80_2nd Customer.dqy"
     '   Selection.QueryTables.Refresh BackgroundQuery:=False
     '
        .Refresh BackgroundQueryTables:=False
    End With
    Sheets("ATL").Select
End Sub
Please let me know what is wrong with this code.
Thanks
Ashkan.G