\nWe’ve recently migrated from an on-prem Microsoft Dynamics NAV environment to the cloud-based Microsoft Business Central. We had developed a number of MS Access databases that would read data out of NAV to allow us to generate reports and do other business specific tasks. Business Central does not have the same “ease” of access as a local SQL server. The recommended course of action was to use dataflows (in PowerApps) to extract the tables we needed into Dataverse. MS Access has an API link for Dataverse and we are able to “see” the tables we’ve created in Dataverse.
\nHere’s the problem:
\nWhen we try to copy down data from the Dataverse table to a local table, records show up as missing or duplicated regularly. If we check the table in Dataverse, the records are not<\/em> duplicated nor missing. But when we check in Access, it’s a mess.
\nMy developer and I are both very “unversed” in Dataverse. But searching online for help is proving futile. It seems no one else is trying to do what we’re doing or they’re all making much simpler requests (simple “address books” and such). We’re pulling in transaction tables and sales orders and invoices and many other tables. We’re then processing these tables with SQL to provide multiple different views of the data and appending our own custom data to allow us to manage our business processes that Business Central doesn’t.<\/p>\n
I’m reaching out in desperation to see if anyone else has experience working with Access and Dataverse and Business Central. Also, if anyone knows of what could be causing Access to see the data as duplicated or missing when it is clearly not.<\/p>\n
Thanks,<\/p>","upvoteCount":3,"answerCount":1,"datePublished":"2024-10-14T21:26:57.747Z","author":{"@type":"Person","name":"cdstarkey","url":"https://community.spiceworks.com/u/cdstarkey"},"suggestedAnswer":[{"@type":"Answer","text":"
Forgive the long explanation: I’m reaching out in desperation to see if anyone else has experience working with Access and Dataverse and Business Central. Also, if anyone knows of what could be causing Access to see the data as duplicated or missing when it is clearly not.<\/p>\n Thanks,<\/p>","upvoteCount":3,"datePublished":"2024-10-14T21:26:57.813Z","url":"https://community.spiceworks.com/t/reading-from-dataverse-into-access/1129812/1","author":{"@type":"Person","name":"cdstarkey","url":"https://community.spiceworks.com/u/cdstarkey"}}]}}
\nWe’ve recently migrated from an on-prem Microsoft Dynamics NAV environment to the cloud-based Microsoft Business Central. We had developed a number of MS Access databases that would read data out of NAV to allow us to generate reports and do other business specific tasks. Business Central does not have the same “ease” of access as a local SQL server. The recommended course of action was to use dataflows (in PowerApps) to extract the tables we needed into Dataverse. MS Access has an API link for Dataverse and we are able to “see” the tables we’ve created in Dataverse.
\nHere’s the problem:
\nWhen we try to copy down data from the Dataverse table to a local table, records show up as missing or duplicated regularly. If we check the table in Dataverse, the records are not<\/em> duplicated nor missing. But when we check in Access, it’s a mess.
\nMy developer and I are both very “unversed” in Dataverse. But searching online for help is proving futile. It seems no one else is trying to do what we’re doing or they’re all making much simpler requests (simple “address books” and such). We’re pulling in transaction tables and sales orders and invoices and many other tables. We’re then processing these tables with SQL to provide multiple different views of the data and appending our own custom data to allow us to manage our business processes that Business Central doesn’t.<\/p>\n