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

[Will Golay]

Check out my suggestion on UserVoice.

Suggestion:

Using the legacy wizards, it is possible to refresh a "Microsoft Access Database" connection type in under a second, regardless of the number of tables being imported.

However, the new query imports from Access using connection type "OLE DB connection" take around 20 times (or more with my testing) longer to refresh the imported tables.

Future versions of Excel should either a) provide an explicit option for Microsoft Access, where it is still possible to enter the Query Editor and/or other features associated with the new wizards, b) guarantee access to the legacy wizards, or c) simply accelerate the refresh time for OLE DB connection type queries when more than one table is imported from an Access file.

It may be possible to accelerate refresh time when more than one table is being imported from an Access file by only connecting to it once, pulling every table requested, undergoing the appropriate edits indicated by the user through the query editor or some other method, and then disconnecting from the Access file. Currently, a separate connection file is formed for each imported table, however, it may be more efficient to only use a single connection file for each Excel file.

[#2]

Hi Will,

Can you please elaborate on your tests? How large is the dataset? Did you choose to create relationships? Are you importing into the data model or grid?

Guy

- Excel Team

[#3]

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.

[#4]

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.