Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / If Business Central is...
Small and medium business | Business Central, N...
Suggested answer

If Business Central is in the cloud, can I still use MS Access to query the cloud data?

(0) ShareShare
ReportReport
Posted on by Jus_Me 10

If Business Central is in the cloud, can I still use MS Access to query the cloud data? 

If so, how to I connect it(Access) to the BC cloud data?

  • DW-31100439-0 Profile Picture
    DW-31100439-0 4 on at
    If Business Central is in the cloud, can I still use MS Access to query the cloud data?
    Natively you can't connect the BC in the cloud with an on-premise relational database or link it to MS Access tables. Think of BC in the cloud as a walled garden that you don't have direct access to the underlying database. The BC does have has an externally facing web service interface that you can add query tables to and you can run web commands to query this data and download on premise. It does get complicated pretty quickly, you need a programmer on hand and they need to overcome some fundamental challenges. For example, querying the BC web service is slow and you need to work how you are going to handle delta loads.
     
    There are several integration vendors who provide connectors to BC and these pre-built connectors sound like and easy solution but they can be quite expensive. Have a look at CDATA for reference. If you are looking for a relatively cheap solution, have a look at Sharperlight. It will synchronise a SQL Server reporting database on-premise and you can easily access these synchronised tables. Sharperlight is a self service reporting tool that has an Excel add-in for query formulas in Excel and web reporting and dashboarding. To give you fast and reactive reporting it synchronises the cloud data locally into an easily accessible SQL Server database (you could use SQL Server Express if you like). It isn't reliant on third party connectors, it has its own integration to query BC web service end-points and it comes with a scheduling tool that regularly delta refresh the data.
     
    Hope this helps.
     
     
  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 5,053 Super User 2024 Season 1 on at
    If Business Central is in the cloud, can I still use MS Access to query the cloud data?
    Absolutely not. In BC Saas you do not have access to the database directly.
    Instead use APIs, and if Ms Access supports reading/importing APIs then try that. 
  • Suggested answer
    UbootMike Profile Picture
    UbootMike 14 on at
    If Business Central is in the cloud, can I still use MS Access to query the cloud data?
    hello, does this solution work from JAngle ? or is there since then a better solution for that ? 
     
    i have nearly the same problem
  • Suggested answer
    JAngle Profile Picture
    JAngle 46 on at
    RE: If Business Central is in the cloud, can I still use MS Access to query the cloud data?

    WebServices can be consumed by Access so I think you can still use it: 

    https://docs.microsoft.com/en-us/office/vba/api/access.webservices

    search for the web services page in BC and publish the required data. On the user record set a web service key. Use basic authentication with the username and web service key as the password.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,784 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,476 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans