Exporting Access Tables to Dataverse - Extreme Slowness

[JacobKing]

I was recently tasked with coming up for a solution for a widely used Access database which performs poorly over the VPN. Current "solution" is to RDP to a machine on prem. Decided to try and migrate the tables to Dataverse and then slowly develop a Power App for the front end. The database itself isn't necessarily large (about 250 MB) but it does have quite few tables (about 150) some of which are extremely poorly normalized (a full 255 columns for some tables). As with many Access databases, this has been floating around for upwards of 20 years.

My issue is that the export of the data to Dataverse is taking an incredibly long time. I kicked off the export at around 9 AM one day this week and it was still running 12 hours later. It wasn't frozen either, tables were slowly popping into the database of the Trial environment I created for this project and the progress bar in Access was creeping along. At maybe the 13 or 14 hour mark I started getting errors that system resources had been exceeded and the process wouldn't move along any further. This database is 32 bit and can't opened with 64 bit access. This is probably contributing to the issue in some way.

My question is... has anybody else experienced this when migrating Access tables? Did you find any workarounds or tricks to speed things up?

[ben-thompson]

That issue after about 12 hours sounds like you've used the resources for the day so best to stop and start again tomorrow with the rest of the data...

Beyond that I don't think there is much you need to worry about 256 columns shouldn't be a problem as Dataverse can handle up to 900 or so (the hard limit is 1024 columns but many dataverse types need/use 2 columns

[JacobKing]

@ben-thompson Thanks. I didn't realize there was such a limit. Unfortunately I wasn't able to gracefully end the process as Access was not responding by that point.

I did start another export earlier today with only a subset of tables so it sounds like I'm already headed down the path you suggested. I'm still curious if others have seen such long export times. Maybe most other Power Platform users have just been lucky enough to not have to deal with Access...

[ben-thompson]

I've had migrations take days - so I wouldn't be that concerned - your best approach is just do what you are doing and to migrate things bit by bit in the most logical order...

[AhmedSalih]

Hello, @JacobKing , It is not workaround but another approch that work smoothly from most of the Datasource migration to Dataverse is you can use DataFlows to load the Data from Access to Dataverse.  

Let me know if you have any question about the process if you want to give it a try.

 

If my reply helped you, please give a 👍 , & if it solved your issue, please 👍 & Accept it as the Solution to help other community members find it more.

I am primarily available on weekdays from 6-10 PM CT and 5-10 PM CT on weekends.

Visit my Blog: www.powerplatformplace.com

[ben-thompson]

this is an initial load using the data export option in Access to create a new Dataverse version of the Access database so it sounds to me a very much one time transaction..

Also Dataflows won't fix any issue where daily resources limits have been hit which given the size of the database (250mb is large for Access) seems to be the issue here.