excel vba on refresh odbc deadlock victim

crushdrinker06

New Member
Joined
May 31, 2018
Messages
2
I have an excel file in which I have made three data connections using the data connection wizard. the option first selected was "ODBC DSN". I have unchecked the "refresh every XX minutes" option since sometimes when refreshing I get a popup window with an OK button that states that there was a deadlock and the sheet was chosen as the victim. Instead I am using a macro with "ActiveWorkbook.RefreshAll" in it. Even with this I am getting the popup window saying that there was a deadlock and the sheet was chosen as the victim. I have tried to use "On Error Resume Next" before the refresh and both
Code:
For i = 0 To 100
    Debug.Print ""
Next i

and

Code:
If Err.Number <> 0 Then

afterwards. Unfortunately I am still getting the popup window. I do not have the ability to change any settings in the actual database. Please advise as to how I can automatically get the pop up window to go away so that I can retry the refresh.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
I can't figure out how to edit the previous post, but I wanted to add some of the code. "ThroughputCalculations" will manipulate the data from the connections and will call "UpdateCheckTP" after 60 minutes.
Code:
Sub UpdateCheckTP()
On Error Resume NextActiveWorkbook.RefreshAll
For i = 0 To 100
    Debug.Print ""
Next i
If Err.Number <> 0 Then
Application.OnTime Now + TimeValue("00:00:01"), "Recall"
Else
Application.OnTime Now + TimeValue("00:00:10"), "ThroughputCalculations"
End If
End Sub

Sub Recall()
Application.OnTime Now + TimeValue("00:00:30"), "UpdateCheckTP"
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top