Problems with Migrating MS Access db to dataverse

Question

I have a MS Access database with about 20 tables that I want to migrate to dataverse.

The initial time I tried to migrate the database I created a short list of items that needed to be fixed such as changing a field of type double to a decimal field. I thenmade these changes.

When I next migrated the tables only 5 tables out of 20 successfully migrated. There were no error messages about why the other 15 tables were not able to migrate. The total number of records across all the tables is quite small around 20,000.

I'm wondering if anyone has successfully migrated a relatively complex set of MS Access tables into dataverse and if you can share some tips.

Answer 1

I'm sorry. I no of now definitive guide for Dataverse and Access. It's too new, for one thing.

You're probably not going to find equivalents for stored procs, although you probably can define objects similar to views. I haven't tried that.

I've not seen ADO for Dataverse yet.

The best resources I know are probably YouTube videos such as this one.

Although not Access related, there isa lot of documentation on Dataverseyou might find useful.

Answer 2

Is there a guide for using Dataverse to maximize speed?

For instance when using a MS Access Front End with AzureSQL I use a combination of stored procedures, views, and ADO to ensure the Access Front End is very fast. There is a delay of approximately a second or two but it is still quick enough.

I do not know how to use the equivalent of stored procedures with dataverse. Also I'm not sure if it is possible to use ado to access dataverse tables.

If you have a link you could provide to using dataverse with more data access details that would be great.

Thanks

Answer 3

Treat it the same way you would any other remote server based database. Assume that dragging records back and forth across the remote connection will be slower than using a local accdb or local database. Optimize by reducing the number of records in recordsets. If you MUST display records in a datasheet, expect it to be slower. Otherwise, limit returned records to the smallest usable number.

Minimize connections needed to retrieve records for forms by limiting combo and list boxes and subforms.

Answer 4

Thanks you for the reply. I was delayed working on this a couple of weeks but I was able to finally migrate the MS Access database tables to Dataverse. After doing some data cleanup, I ended up moving each table one by one instead of using the built in tool within MS Access.

There is a different issue which I ran into. The program I transferred is an old MS Access database. The forms are written in a manner that a form opens a recordset which accesses all the records. The underlying tables are not that large - roughly 2,000 - 5,000 records.

The problem I ran into is speed. Scrolling through 2 - 5 thousand records via a datasheet is very slow. Are these speeds typical? The next step is rewrite the application to only access a small number of records at a time.

Are you finding speed an issue when using MS Access as the front end and dataverse as the backend? What are some solutions used to keep a Access/Dataverse program not too slow?

Answer 5

I was on the beta test team and I have not seen this problem.

There are some recent bugs with Decimal data type, so just for testing, change them to long integer. I know you will lose the decimal part. This is only to test if my hunch is correct that the Decimal issues are what's causing the problem.