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:
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
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!
So I had a similar error when I was trying to create a VBA script to refresh an excel workbook automatically at a given time, and there were a few things that I did in my VBA script to get this to work. One of them being disabling background refresh. That could be your issue and you can easily do disable it by going to your connection properties and disabling background refreshing.
Here is what I did in VBA when I was getting this error, although I will say I was not using it with an MS access db. I had one excel workbook that I used as a 'runner' and it opened up the other books one by one and refreshed their connections. Basically I had a variable for the path
and extension
and put the names of each workbook into an array and looped through the array.
I combined the path and extension to give me the full filename of the file, you will see that in the loop.
This is what my loop looked like :
For i = LBound(testArray) To UBound(testArray)
Dim wb As Workbook
Set wb = Workbooks.Open(path & testArray(i) & ext, 0, False)
'Next I checked to see if the workbook was in protected view and allowed for editing.
If Application.ProtectedViewWindows.Count > 0 Then
Application.ActiveProtectedViewWindow.Edit
End If
'Now comes the part that I believe should help for your case
wb.Connections(testArray(i) & "This is your connection name").OLEDBConnection.BackgroundQuery = False
wb.RefreshAll
wb.Connections(testArray(i) & "This is your connection name").OLEDBConnection.BackgroundQuery = True
wb.SaveAs fileName:= "Thisbook.xlsx"
wb.Close
Next i
To get the connection name there are a few ways, including just looking to see what it is manually. For me because I wanted to make it so that I didn't need to manually put in every connection name I used the inherent pattern that I saw with the connections names.
In my case that was the baseNameOfWorkbook & " POS Report"
I do believe that you may be getting the errors due to background refreshing. So if you don't need to do this in VBA I would just suggest going to connection properties and disabling it.
Let me know if this works.