excel - ADODB 到查询表：运行时错误 1004

Question

我目前正在尝试将一个从 VBA 查询 SQL Server 的项目从 ODBC 移动到 ADODB。引用了 Active X 数据对象和 Recordset 2.8。

ADODB 连接有效并且记录集被填充。由于某种超出我理解的原因，recorset 和 querytable 似乎彼此不喜欢。我在连接对象上尝试了任何类型的 With。

我遇到了运行时错误“1004”应用程序定义或对象定义错误。

这是代码的样子：

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

标签： excelvbaadodb

Answer 1

本质上，您在不同的方法上遇到了两个问题：

• .Close：因为QueryTable对象没有.Close方法，直接去掉调用即可。

• .Debug.Print：当您打电话时，Debug.Print rs(0)您显然会消耗记录集，因此无法使用QueryTables.Add()似乎需要未触及的记录集。

  要解决此问题，请考虑在查询表将光标推到末尾时移动Debug.Print after QueryTables.Add()并MoveFirst在之前调用 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)
  

  注意：我确实尝试过MoveFirst无济于事Debug.Print。QueryTables.Add()无论光标位置如何，它似乎都需要一个未触及的记录集。这可能与 ODBC 驱动程序不同。

  Debug.Print rs(0)
  rs.MoveFirst
  
  Set qtData = ActiveSheet.QueryTables.Add(rs, Destination:=Range("A1"))
  
  With qtData
      ...
  
      .Refresh
      .Delete
  End With