none
Run-time error '1004' when importing data from SQL Server 2008 Express RRS feed

  • Question

  • Hi,

    I'm working on an Excel 2010 spreadsheet which has to import data from a table in a SQL Server database.  When trying to run the code, i get the following error:

    Run-time error '1004':  Application-defined or object-defined error

    The strange thing is that I can import data from one table in the DB, but not another.  I'm thinking along the lines that it might be due to the size of the dataset being returned, but I haven't come across this problem before.

    Here's my code:

    Public Sub TestClick()
    Dim objDialog   As Object
    Dim strFileName As String
    Dim intNumRows  As Long
    
    Sheets("FaultLog").Activate
        ActiveSheet.Cells.Select
        Selection.ClearContents
        Selection.Delete Shift:=xlUp
    
       With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
       "OLEDB;Driver={SQL Server};Server=ustr-erl-3421.na.uis.unisys.com\SQLEXPRESS;Database=LBGFL_T;UID=LBGFL_T;PWD=xxxxxxxx"), Destination:=Range("FaultLog!$A$1")).QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array("faultlogincident")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .MaintainConnection = False
            .ListObject.DisplayName = "Table_LBGFL"
            .Refresh BackgroundQuery:=False
        End With
        
        intNumRows = ActiveSheet.Range(ActiveSheet.Range("A1"), ActiveSheet.Range("A65535").End(xlUp)).Count
        
        ActiveSheet.Cells.Select
        Selection.Copy
        ActiveSheet.Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Application.CutCopyMode = False
        ActiveWorkbook.Worksheets("FaultLog").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("FaultLog").Sort.SortFields.Add Key:=Range("C2:C" & intNumRows) _
            , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("FaultLog").Sort
            .SetRange Range("A1:BI" & intNumRows)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        
        ActiveSheet.Range("A1").Select
       
        Sheets("Sheet1").Activate
        
        Exit Sub
    End Sub

    When I hit debug, it always points to the .Refresh BackgroundQuery:=False line.  I've tried changing this to True and even omitting the BackgroundQuery:=False part but i get another error - 'Data could not be retreived from the database'.

    The strange thing is that similar code current works when trying to import the same data from an MSAcess database using the following connection details.  However, since we're migrating our data from Access to SQL Server, I need to try and get this working

        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
            "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Range("G3").Value & ";Mod" _
            , _
            "e=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:E" _
            , _
            "ngine Type=4;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLED" _
            , _
            "B:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale o" _
            , _
            "n Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLED" _
            , "B:Bypass UserInfo Validation=False"), Destination:=ActiveSheet.Range("FaultLog!$A$1")). _
            QueryTable
            .CommandType = xlCmdTable
            .CommandText = Array("faultlogincident")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .MaintainConnection = False
            .SourceDataFile = Range("G3").Value
            .ListObject.DisplayName = "Table_LBGFL"
            .Refresh BackgroundQuery:=False
        End With

    I'd appreciate any help you could give me.

    Hamant.


    Friday, May 8, 2015 10:21 AM

All replies