Create Microsoft Access Query or Speed Up Refresh Time for OLE DB Type Queries

Sure. My methodology was to set up about 15 connections (the highest number of tables I am importing from Access into a single sheet, which is all notably fom the same Access file), then I wrote a macro that loops through the refresh and records the time the refresh took. I let the loop run overnight, getting around 1000 refreshes. On average, the new method using the "OLE DB" connection took around 20.169 seconds. I did the same thing with the legacy wizards with another thousand refreshes, and got an average of 1.359 seconds.

Each table has anywhere between 15 rows or 50 rows, all of them with exactly 6 columns (which I eliminate 3 using the query editor, so I'm really importing 3 columns). I am not importing into the datamodel, I am only importing tables so I can get pricing on the items in these tables. It's not a lot of information, I think it just takes forever because each connection file is separate so it has to connect to the Acess file, import the data, then disconnect, connect again, import, and so on 15 different times. Whether this is different with the legacy wizards, I don't know, but there is a pretty significant time difference.

If it impacts import speed, here's some more information:

• The Access file is around 3.5 MB with 38 tables.
• The sheets that the tables are imported into calculate pricing based on the imported values.
• There are no major changes in the import, only 3 removed columns.
• The Access file is located on a server, however, the templates for the sheets importing the data are also located on the same server. I also tested just on my desktop and got similar results as if the files were on the server.

Right now, my solution is to use "Microsoft Query," which seems to be working just fine. I can also edit similarly to the Query Editor in the new wizards, so I'm pretty happy with that, however, just like with the legacy wizards, my fear is that this may eventually be removed or become obscelete in future versions of Excel. I would really like to be able to use the new wizards or a wizard specifically for Access files. 

Anyway, thanks for the reply! I would definitely like to see some change in the way data is imported into Excel from Access, so I hope we can figure something out. 

Hi Guy,

Will gives much more details. I'm not working with Access, but for my interest tested the connection on some tiny demo database i found in Internet (file size ~800KB and 10 small tables insides).

If load into the data model there we have lot of relationships (built automatically or imported from database, i don't know) Refresh takes more than a minute.

Legacy connector also adds everything into the data model, but refresh takes just few seconds.