CODEWITHSUNDEEP
excelvbaadodb
alexolb
alexolb

Reputation: 21

ADODB to querytable : Run-Time Error 1004

I currently am trying to move a project that queries SQL Server from VBA from ODBC to ADODB. Active X Data Object and Recordset 2.8 are referenced.

The ADODB connection works and the Recordset gets populated. It seems that the recorset and querytable for some reason beyond my understanding don't like each other. I tried any type of With on the connection object.

I run into a Run-time Error '1004' Application-defined or object-defined error.

This is what the code looks like:

Dim cn As Object
Dim rs As Object

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

With cn
    .Open "Driver={SQL Server};Server=xxx;Database=xxx;UID=xxx;PWD=xxx;"
    .CommandTimeout = 0
    Set rs = .Execute("Select 1")
End With

Debug.Print rs(0)

Dim qtData As QueryTable
Set qtData = ActiveSheet.QueryTables.Add(rs, Destination:=Range("A1"))
With qtData
     .Name = "DTBase"
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .BackgroundQuery = False
     .RefreshStyle = xlOverwriteCells
     .SavePassword = False
     .SaveData = False
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .PreserveColumnInfo = False
     .BackgroundQuery = False
     .EnableEditing = False
 End With

'ActiveSheet.ListObjects.Add(xlSrcQuery, rs, Destination:=Selection).QueryTable.Refresh

qtData.Refresh
qtData.Close
qtData.Delete

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

Upvotes: 1

Views: 623

Answers (1)

Parfait
Parfait

Reputation: 107567

Essentially, you are encountering two issues on different methods:

  • .Close: Because the QueryTable object does not have the .Close method, simply remove the call.

  • .Debug.Print: When you call Debug.Print rs(0) you apparently consume the recordset and thus is not available for QueryTables.Add() which appears to require an untouched recordset.

    To resolve, consider moving Debug.Print after QueryTables.Add() and call MoveFirst just before as query table pushed cursor to the end where EOF = True. 

    Set qtData = ActiveSheet.QueryTables.Add(rs, Destination:=Range("A1"))

With qtData
     .Name = "DTBase"
     .FieldNames = True
     .RowNumbers = False
     .FillAdjacentFormulas = False
     .PreserveFormatting = True
     .RefreshOnFileOpen = False
     .BackgroundQuery = False           ' REMOVED REPEATED LINE AFTER THIS ONE
     .RefreshStyle = xlOverwriteCells
     .SavePassword = False
     .SaveData = False
     .AdjustColumnWidth = True
     .RefreshPeriod = 0
     .PreserveColumnInfo = False
     .EnableEditing = False
     .Refresh                           ' MOVED TO INSIDE With BLOCK
     .Delete                            ' MOVED TO INSIDE With BLOCK
End With

rs.MoveFirst
Debug.Print rs(0)

    Note: I did attempt to run MoveFirst after Debug.Print to no avail. It seems QueryTables.Add() requires an untouched recordset, regardless of cursor position. This may differ with ODBC drivers.

    Debug.Print rs(0)
rs.MoveFirst

Set qtData = ActiveSheet.QueryTables.Add(rs, Destination:=Range("A1"))

With qtData
    ...

    .Refresh
    .Delete
End With

Upvotes: 1

Related Questions