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

 

[xenou]

Personally instead of repeatedly "adding" a query table (which to me leaves uncertain what is happening to the ones that already exist), it would make sense to create the query table permanently in Excel. Then you can simply refresh the existing query table (which is also a lot less code - and in fact can be set to refresh automatically every X minutes without any code at all).

That said, I've seen code like this many times before, so in general it looks okay. You have to experiment probably.