Multiple Queries - Getting error

I recorded the following macro to pull an excel tables as a query on multiple tabs. I get errors randomly from week to week on some of these sections. Thanks so much in advance!...

.ListObject.DisplayName = "Table_Query_from_Excel_Files"

------------------------------------------------------------------
THE ERROR IS 1004 GENERAL ODBC ERROR
------------------------------------------------------------------


I have about 8 of them and about 4 give me the error.

Sub ODBC_ANDOVER()
Sheets("ANDOVER").Select

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=P:\_ChemUpload\Forcasts\Individual\ANDOVER.xls;DefaultDir=P:\_ChemUpload\Forcasts\Individual;DriverId" _
        ), Array("=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `Sheet1$`.`Deliv# Date`, `Sheet1$`.`Deliv# Time`, `Sheet1$`.Z1, `Sheet1$`.Product, `Sheet1$`.`Ch/ Nw`, `Sheet1$`.`Order Doc# No#`, `Sheet1$`.`Item No#`, `Sheet1$`.`SL No#`, `Sheet1$`.`Prod#Desc" _
        , _
        "#`, `Sheet1$`.`Unit of Measure`, `Sheet1$`.`Document Qty#`, `Sheet1$`.`Notified Qty#`, `Sheet1$`.`Delivered Qty#`, `Sheet1$`.`Due Qty#`, `Sheet1$`.`Plant Desc#`, `Sheet1$`.`P&G MRP Controller`, `Sheet" _
        , _
        "1$`.`Last Updated`, `Sheet1$`.`Ship-From Loc#`, `Sheet1$`.`Customer Loc#`" & Chr(13) & "" & Chr(10) & "FROM `P:\_ChemUpload\Forcasts\Individual\ANDOVER.xls`.`Sheet1$` `Sheet1$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Sub ODBC_BLOIS()
Sheets("BLOIS").Select

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=P:\_ChemUpload\Forcasts\Individual\BLOIS.xls;DefaultDir=P:\_ChemUpload\Forcasts\Individual;DriverId" _
        ), Array("=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `Sheet1$`.`Deliv# Date`, `Sheet1$`.`Deliv# Time`, `Sheet1$`.Z1, `Sheet1$`.Product, `Sheet1$`.`Ch/ Nw`, `Sheet1$`.`Order Doc# No#`, `Sheet1$`.`Item No#`, `Sheet1$`.`SL No#`, `Sheet1$`.`Prod#Desc" _
        , _
        "#`, `Sheet1$`.`Unit of Measure`, `Sheet1$`.`Document Qty#`, `Sheet1$`.`Notified Qty#`, `Sheet1$`.`Delivered Qty#`, `Sheet1$`.`Due Qty#`, `Sheet1$`.`Plant Desc#`, `Sheet1$`.`P&G MRP Controller`, `Sheet" _
        , _
        "1$`.`Last Updated`, `Sheet1$`.`Ship-From Loc#`, `Sheet1$`.`Customer Loc#`" & Chr(13) & "" & Chr(10) & "FROM `P:\_ChemUpload\Forcasts\Individual\BLOIS.xls`.`Sheet1$` `Sheet1$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files1"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Sub ODBC_CAYEY()
Sheets("CAYEY").Select

    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=P:\_ChemUpload\Forcasts\Individual\CAYEY.xls;DefaultDir=P:\_Upload\Forcasts\Individual;DriverId" _
        ), Array("=1046;MaxBufferSize=2048;PageTimeout=5;")), Destination:=Range( _
        "$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT `Sheet1$`.`Deliv# Date`, `Sheet1$`.`Deliv# Time`, `Sheet1$`.Z1, `Sheet1$`.Product, `Sheet1$`.`Ch/ Nw`, `Sheet1$`.`Order Doc# No#`, `Sheet1$`.`Item No#`, `Sheet1$`.`SL No#`, `Sheet1$`.`Prod#Desc" _
        , _
        "#`, `Sheet1$`.`Unit of Measure`, `Sheet1$`.`Document Qty#`, `Sheet1$`.`Notified Qty#`, `Sheet1$`.`Delivered Qty#`, `Sheet1$`.`Due Qty#`, `Sheet1$`.`Plant Desc#`, `Sheet1$`.`P&G MRP Controller`, `Sheet" _
        , _
        "1$`.`Last Updated`, `Sheet1$`.`Ship-From Loc#`, `Sheet1$`.`Customer Loc#`" & Chr(13) & "" & Chr(10) & "FROM `P:\_ChemUpload\Forcasts\Individual\CAYEY.xls`.`Sheet1$` `Sheet1$`" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Excel_Files2"
        .Refresh BackgroundQuery:=False
    End With
End Sub