Linked Table Speed - ADO Active Connection in VBA

[bs0d]

I have an Excel application that uses ADO to query an Access database with linked tables from numerous other Access databases. I've noticed the speed of the queries has slowed down with the number of records increasing each day. This has prompted me to explore optimization ideas where I came across this article: Significantly Improve the Performance of Microsoft Access Databases with Linked Tables

The article indicates you should keep a persistent connection to the linked database. With my application being Excel based, is there a way to implement this on the VBA side of Excel, or will there be any meaningful difference? I've always followed the logic to make sure connections are closed and not orphaned, so to speak.

If anyone has done this, could you show me an example in Excel, perhaps using ADO? Thanks for any feedback

 

[xenou]

How numerous is numerous and how much of an increase does increasing each day mean?
As far as would that help - you can try it. I've never seen any person say this actually worked for them although I have seen this recommendation given several times.

Also, are you saying you are linking excel to various dbs, or that you are linking excel to a db with links to various other dbs? Or both?

 

[bs0d]

I'm connecting to an Access database through Excel (ADO). The database has ~15 linked tables from ~8 databases. I have about +500 records added each day. I'm trying to figure out why it's slowing down. I can run a query directly in the Access database and one time it takes 2 seconds, another time it takes 12 seconds for the same data. If the data is local and not via linked source, the query is nearly instantaneous.

 

[xenou]

What kind of networking are you dealing with?

 

[bs0d]

What do you mean exactly? I'm not sure about the specs of the server, but I'm connected at 1Gbps. The server was rebooted last night. This morning, things are still slower. To complicate things, for requests made directly in the Access database- the very first query is slow. This is a parameter query. Then if I refresh the query and re-enter the parameter value, it's faster.

 

[bradyboyy88]

did you ever find a solution for this to speed up linked database queries? Also, did you ever consider just doing 8 ado connection and a single query?

Thanks

 

[bs0d]

did you ever find a solution for this to speed up linked database queries? Also, did you ever consider just doing 8 ado connection and a single query?

Thanks

I couldn't pass up the appeal of nearly instantaneous results with local data. So I created a scheduled task to run a query in the morning that copies the new data into a local table. Then, my Excel application has the one query to the local table. This seems to achieve the most optimal speed.

I didn't look into 8 ado connections and one query, though I'm not sure how I'd set that up either. The linked tables seemed to be a headache and using parameters helped a little but seemed to unnecessarily complicate things as well.