Slow Datasource Connection

[jonnyp138]

Hi all, I have a weird issue that I just cannot put my finger on, basically I have an excel dashboard which when I run a macro it will connect to an Access DB and pull out requested data, the issue I am having is the time it takes to connect to the datasource, for example I can run it once and it will connect immediately and pull the data in seconds, however I can run the macro again and it takes up to 5 minutes to connect to the datasource? Also if I leave the dashboard open after running once and leave it for 10 minutes or so it will connect instantly again, does anyone know what the issue could be? The code for the macro is as follows:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=\\SERVERNAME\Database$\Analysis.accdb;" _
, _
"Mode=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password" _
, _
"="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transact" _
, _
"ions=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;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=Fal" _
, "se"), Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"Select * From myTable" & Chr(13) & "" & Chr(10) & "WHERE Customer = """ & FCustomer & """" & Chr(13) & "" & Chr(10) & "AND" & Chr(13) & "" & Chr(10) & "[Device Name] = """ & FHost & "" _
, """;")
.CommandType = xlCmdSql
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "Table_ExternalData_1"
.Refresh BackgroundQuery:=False
End With

With ActiveWorkbook.Connections("Connection")
.Name = "Thresholds"
.Description = ""
End With



Thanks in advance - I also posted this in the Access forum but not sure where it lies as it uses both Excel and Access so apologies if this is in the wrong place.