I have a procedure in VBA that i've been using for some time now to pull the data set from SQL DB.
It always took less then 1 min to populate the template but since few days it's been taking more than 10 mins. nothing changed in Database, nothing changed in my vba code either...
It stucks on :
Sheets("NEW").Range("A3").CopyFromRecordset rst
I even upgraded from office 2013 to 2016 but the issue still persists
Any guesses what could be the reason?
note:
-the same file with same procedure works fine on my colleagues pc
-table in question is about 54000 rows in 34 columns
my code
[Blocked Image: http://icons.iconarchive.com/icons/double-j-design/ravenna-3d/24/File-Copy-icon.png]
Code
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
Dim Combine As String
Dim ow As Long
Dim ok As Integer
Dim i As Long
ConnectionString = "Provider=SQLOLEDB.1;Password=xxx;Persist Security Info=True;User ID=xxxx;Data Source=xxxx;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Share_Assignment_Automation"
cnn.Open ConnectionString
cnn.CommandTimeout = 900
StrQuery = "SELECT [Organization],null as [ERP Segment 1],[Effective date of assignment to Team],[Expiration date of assignment to Team],null as [Last Modified By],null as [Last Modified Date],[Territory Type Name],[Forecastable Flag],[Inside Sales Flag] FROM [vw_SHARETeam] with (nolock) "
rst.Open StrQuery, cnn
Sheets("TEAM").Range("A3").CopyFromRecordset rst
rst.Close