刷新时Excel数据连接错误 [英] Excel Data Connection errors on Refresh

查看：781
本文介绍了刷新时Excel数据连接错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

<强>解决！请参阅下面的解决方案！



我在Excel 2010中连接到多个独立从Excel通过数据透视表数据访问2010数据库连接。



刷新所有我的连接会导致最终刷新失败。订单没关系，我手动刷新不同的订单，同样的错误。



但是，如果我在刷新几个后保存并关闭，然后回来刷新最后一个，根本没有问题。



引导我相信，当我保存并关闭时，我打了一些重置的内存上限。 / p>

我可以通过VBA 重新创建该效果，而不实际保存/关闭？有没有更好的解决方案？



错误消息 - 这三个按以下顺序弹出：




  • 查询没有运行，或数据库表无法打开。

  • 获取数据时出现问题。

  • 数据透视表，多维数据集函数或切片器使用连接没有刷新。



当前代码 

  Private Sub CommandButton1_Click（）
错误GoTo ErrHndlr 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 Application.StatusBar =刷新数据 - 请耐心
 
 ActiveWorkbook.Connections（Connection_1）。刷新
 ActiveWorkbook.Connections （Connection_2）刷新
 ActiveWorkbook.Connections（Connection_3）。刷新
 
 Application.Calculation = xlCalculationAutomatic 
 Application.StatusBar =就绪
 [最后更新] .Value = FormatDateTime（现在，vbGeneralDate）
 Application.ScreenUpdating = True 
退出子
 
 ErrHndlr：
 Application.StatusBar =就绪
 Application.Calculation = xlCalculationAutomatic 
 Application.ScreenUpdating = True 
 [LastUpdated] .Value =更新错误
退出子
 
 End Sub

连接字符串 

  Provider = Microsoft.ACE.OLEDB.12.0 
; User ID = Admin 
; Data Source = C：\Folders\Database_1.accdb 
; Mode =分享拒绝无
;扩展属性= 
; Jet OLEDB：System database =
; Jet OLEDB：注册表路径=
; Jet OLEDB：引擎类型= 6 
; Jet OLEDB：数据库锁定模式= 0 
; Jet OLEDB：全局部分批量操作= 2 
; Jet OLEDB：全局批量交易= 1 
; Jet OLEDB：新数据库密码=
; Jet OLEDB ：创建系统数据库= False 
; Jet OLEDB：加密数据库= False 
; Jet OLEDB：不复制Compact = False上的区域设置
; Jet OLEDB：紧凑无副本修复= False 
; Jet OLEDB：SFP = False 
; Jet OLEDB：支持复杂数据= False 
; Jet OLEDB：绕过UserInfo验证= False





尝试解决方案




  • 禁用后台刷新 - 已禁用

  • 禁用自动恢复（以节省内存）

  • 清除撤消堆栈（以节省内存）

  • 'DoEvents'延迟代码执行，直到每次刷新完成，更改：



这个

  ActiveWorkbook.Connections（Connection_1）。刷新

到

 使用ActiveWorkbook.Connections（Connection_1）
选择案例.Type 
案例xlConnectionTypeODBC 
带.ODBCConnection 
 .Refresh 
 Do While .Refreshing 
 DoEvents 
 Loop 
 End with 
 Case xlConnectionTypeOLEDB 
 With .OLEDBConnection 
 .Refresh 
 Do While .Refreshing 
 DoEvents 
循环
结束
 Case Else 
 .Refresh 
结束选择
结束

解决方案！



注意，我有几个额外的连接，我不想通过这个代码更新，并添加了一些额外的，简单的逻辑来指定我想要更新的连接。这里的代码用于刷新工作簿中的每个连接：

  Dim i As Integer 
 Dim awc As WorkbookConnection 
 Dim c As OLEDBConnection 
 
设置awc = ActiveWorkbook.Connections.Item（i）
设置c = awc.OLEDBConnection 
 c.EnableRefresh = True 
 c。 BackgroundQuery = False 
 c.Reconnect 
 c.Refresh 
 awc.Refresh 
 c.MaintainConnection = False 
 Next i

我不知道为什么的具体细节是这样的，哪部分可以让Excel克服自己的限制。如果有人比较熟悉，我很乐意听到更多的信息。

解决方案

这不是一个完整的答案，而是试图帮助调试，所以希望我们可以得到一个解决方案。



我相信你可以通过调试Connections来解决这个问题。尝试用以下Sub替换上面的Refresh代码（和DoEvents替换）。首先，可以在刷新之间显示对话框来解决问题（如果问题是并发刷新等）。第二，每次运行时，请仔细检查没有任何改变。请报告任何发现或信息。如果您仍然收到错误信息，请通过代码返回引发错误的行。

  Sub ShowDebugDialog（）
 
 Dim x As Integer 
 Dim i As Integer，j As Integer 
 Dim awc As WorkbookConnection 
 Dim c As OLEDBConnection 
 
 For i = 1到ActiveWorkbook.Connections.Count 
'对于i = ActiveWorkbook.Connections.Count到1步-1 
 
对于j = 1到ActiveWorkbook.Connections.Count 
设置awc = ActiveWorkbook.Connections.Item（j）
设置c = awc.OLEDBConnection 
x = MsgBox（ConnectionName：& awc.Name& vbCrLf& _ 
IsConnected：& c.IsConnected& vbCrLf& _ 
BackgroundQuery：& c.BackgroundQuery& vbCrLf& _ 
MaintainConnection：& c.MaintainConnection& _bbLL& _ 
RobustConnect：& c.RobustConnect& vbCrLf& _ 
 RefreshPeriod：& c.RefreshPeriod& vbCrLf& _ 
刷新：& c。刷新& vbCrLf& _ 
EnableRefresh：& c.EnableRefresh& vbCrLf& _ 
应用程序：& c.Application& vbCrLf& _ 
UseLocalConnection：& c.UseLocalConnection _ 
，vbOKOnly，Debugging）
下一步j 
 
设置awc = ActiveWorkbook.Connections.Item（i）
设置c = awc.OLEDBConnection 
 c.EnableRefresh = True 
 c.BackgroundQuery = False 
 c.Reconnect 
 c.Refresh 
 awc.Refresh 
 c.MaintainConnection = False 
 Next i 
 
 End Sub

如果您有其他问题，您可以回答'仍然收到错误：




  • BackgroundQuery总是错误？

  • 每个对话框之间是否存在可察觉的延迟（指示Excel正在等待刷新完成）或者在最后一个对话框之后立即出现？

  • 哪一行代码引发了初始错误？如果以后退顺序刷新连接（通过取消注释步骤-1行），您是否在同一连接处获取错误？

  • 当您说可以手动更新连接时，是通过不同的宏还是通过数据>>连接>>刷新？

  • 如果您手动选择RefreshAll，任何错误？



调试恶意连接错误时，您必须考虑一切。


Solved! See below for solution!

I'm in Excel 2010 connecting to multiple, seperate Access 2010 db's from Excel through PivotTable data connections.

Refreshing all my connections causes the final refresh to fail. The order does not matter, I've manually refreshed in different orders, same error.

However, if I save and close after refreshing a few, then come back and refresh the last one, there is no problem at all.

Leads me to believe that I'm hitting some sort of memory cap that is reset when I save and close.

Can I re-create that effect through VBA without actually save/closing? Is there a better solution to this issue?

Error Messages - These three pop up in this order:

  • The query did not run, or the database table could not be opened.
  • Problems obtaining data.
  • A PivotTable, cube function or slicer using connection has failed to refresh.

Current Code

Private Sub CommandButton1_Click()
On Error GoTo ErrHndlr

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.StatusBar = "Refreshing Data - Please Be Patient"

ActiveWorkbook.Connections("Connection_1").Refresh
ActiveWorkbook.Connections("Connection_2").Refresh
ActiveWorkbook.Connections("Connection_3").Refresh

Application.Calculation = xlCalculationAutomatic
Application.StatusBar = "Ready"
[LastUpdated].Value = FormatDateTime(Now, vbGeneralDate)
Application.ScreenUpdating = True
Exit Sub

ErrHndlr:
  Application.StatusBar = "Ready"
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  [LastUpdated].Value = "Update Error"
  Exit Sub

End Sub

Connection String

Provider=Microsoft.ACE.OLEDB.12.0
;User ID=Admin
;Data Source=C:\Folders\Database_1.accdb
;Mode=Share Deny None
;Extended Properties=""
;Jet OLEDB:System database=""
;Jet OLEDB:Registry Path=""
;Jet OLEDB:Engine Type=6
;Jet OLEDB:Database Locking Mode=0
;Jet OLEDB:Global Partial Bulk Ops=2
;Jet OLEDB:Global Bulk Transactions=1
;Jet OLEDB:New Database Password=""
;Jet OLEDB:Create System Database=False
;Jet OLEDB:Encrypt Database=False
;Jet OLEDB:Don't Copy Locale on Compact=False
;Jet OLEDB:Compact Without Replica Repair=False
;Jet OLEDB:SFP=False
;Jet OLEDB:Support Complex Data=False
;Jet OLEDB:Bypass UserInfo Validation=False

Attempted Solutions

  • Disable Background Refresh - Already disabled
  • Disable Auto-Recover (to save memory)
  • Clear "Undo Stack" (to save memory)
  • 'DoEvents' to delay code execution until each refresh is concluded, changing:

this

ActiveWorkbook.Connections("Connection_1").Refresh

to

With ActiveWorkbook.Connections("Connection_1")
  Select Case .Type
    Case xlConnectionTypeODBC
      With .ODBCConnection
        .Refresh
        Do While .Refreshing
          DoEvents
        Loop
      End With
    Case xlConnectionTypeOLEDB
      With .OLEDBConnection
        .Refresh
        Do While .Refreshing
          DoEvents
        Loop
      End With
    Case Else
      .Refresh
  End Select
End With

SOLUTION!

Side note, I have a couple extra connections that I didn't want updated through this code, and added some additional, simple logic to specify which connections I wanted updated. This code here works to refresh every connection in your workbook:

Dim i As Integer
Dim awc As WorkbookConnection
Dim c As OLEDBConnection

Set awc = ActiveWorkbook.Connections.Item(i)
Set c = awc.OLEDBConnection
c.EnableRefresh = True
c.BackgroundQuery = False
c.Reconnect
c.Refresh
awc.Refresh
c.MaintainConnection = False
Next i

I don't know the specifics of why this works, which part of this allows Excel to get over its self-limitation. I'd love to hear more if anyone is more familiar!

解决方案

This is not a full answer, but an attempt to help debug, so that hopefully we can arrive at a solution.

I believe you can solve this issue by debugging the Connections. Try replacing your Refresh code above (and the replacement with DoEvents) with the following Sub. First, it is possible that displaying the dialog between Refreshes will fix the problem (if the problem is concurrent refreshes, etc). Second, each time it runs, check carefully that nothing has changed. Please report back with any discoveries or info. If you still get the errors, step through the code and report back the line that raises the error.

Sub ShowDebugDialog()

   Dim x As Integer
   Dim i As Integer, j As Integer
   Dim awc As WorkbookConnection
   Dim c As OLEDBConnection

   For i = 1 To ActiveWorkbook.Connections.Count
   'For i = ActiveWorkbook.Connections.Count To 1 Step -1

      For j = 1 To ActiveWorkbook.Connections.Count
         Set awc = ActiveWorkbook.Connections.Item(j)
         Set c = awc.OLEDBConnection
         x = MsgBox("ConnectionName: " & awc.Name & vbCrLf & _
              "IsConnected: " & c.IsConnected & vbCrLf & _
              "BackgroundQuery: " & c.BackgroundQuery & vbCrLf & _
              "MaintainConnection: " & c.MaintainConnection & vbCrLf & _
              "RobustConnect: " & c.RobustConnect & vbCrLf & _
              "RefreshPeriod: " & c.RefreshPeriod & vbCrLf & _
              "Refreshing: " & c.Refreshing & vbCrLf & _
              "EnableRefresh: " & c.EnableRefresh & vbCrLf & _
              "Application: " & c.Application & vbCrLf & _
              "UseLocalConnection: " & c.UseLocalConnection _
              , vbOKOnly, "Debugging")
      Next j

      Set awc = ActiveWorkbook.Connections.Item(i)
      Set c = awc.OLEDBConnection
      c.EnableRefresh = True
      c.BackgroundQuery = False
      c.Reconnect
      c.Refresh
      awc.Refresh
      c.MaintainConnection = False
   Next i

End Sub

Additional questions you can answer if you're still getting errors:

  • Was BackgroundQuery always false?
  • Was there a perceptable delay between each set of dialogs (indicating Excel is waiting for a refresh to complete) or did they all come up immediately after the last one?
  • Which line of code raises the initial error? If you refresh the Connections in backwards order (by uncommenting the "Step -1" line) do you get the error at the same connection?
  • When you say you can update the connections manually, is this through a different macro or through Data >> Connections >> Refresh?
  • Any errors if you manually select "RefreshAll"?

Sorry for all the questions but you have to think of everything when debugging nasty connection errors like this.

这篇关于刷新时Excel数据连接错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Office最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆