Datasource Connection Responding Very Slowly

[brooksc57]

I have a bit of code that is simply importing data from access into MS Excel. On my laptop it always worked instantly. The whole code ran in about 1 or 2 seconds. It worked this well for a very long time. About 7 months ago the time pushed out to 25 seconds or so. I thought it was a server problem and have been asking the IT Server staff to look into server performance. Recently I acquired a desktop to go along with my laptop. Today I ran the code on my desktop and it gave me my old performance of 1 or 2 seconds.

Looking closely I've noticed that it isn't the code but my datasource connection that is slow. It sits there trying to connect for 24 seconds and only runs for 1 second.

I've searched high and low on sites looking for a possible cause. I also struggled with where to post this issue.

Though I'm sure not necessary, here is the connection code.

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=\\lewcat01\catalog\_Departments\_ProductDataServices\SRP Master\PDM_PartDescriptionMaster.mdb;DefaultDir=\\lewcat01\catalog\_Departm" _
), Array( _
"ents\_ProductDataServices\SRP Master;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))

Thanks,
Brooks

 

[genomon]

On my laptop it always worked instantly

Have you tried disk cleanup, defrag, and maybe CCleaner?
Could be you picked up some junk on the laptop. Also if you recently updated antivirus (especially Symantec Endpoint) it could possibly have an impact.

If quizzes are quizzical, what are tests?

 

[SkipVought]

Why would you ADD a querytable each time you execute this code. Each time you run this code, another object is added to your workbook.

execute this code to tell you how many you already have...

sub CountQTs()
  msgbox activesheet.querytables.count
end sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brooksc57]

Thanks genomon

will try that.
I guess it has been awhile since my last cleaning.
I get so many anti-virus things from my office network in addition to things like Super Anti-Spyware that I run myself.

------------------------------------------------------------
Hi Skip,

I know the code is pretty immature.
The whole code is about 50% excel generated code.
I haven't bothered to change it becuase I rewrote with an access front end anyway...and will migrate it to a Broswer later. The XLS version is still used since many colleagues run in fear at the very mention of the word Access.

Anyway, due to the way it is used it isn't racking up objects. This is only used as a quick way to pick-up alot of information about one part number and then the sheet containing the objects is deleted. I ran your test and came up with a result of 0 objects once the full code is run.

 

[SkipVought]

Well, sure, if the sheet is deleted, there are no querytable objects on the activesheet.

I guess I use querytables ubiquitously in my daily work, like eating and breathing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}