Access SQL Server Online

Before I dive into a migration, I am looking for practical advice as whether the migration is worth the effort.  Also, are there use cases where either Dataverse or SQL are more suited than the other.

I recall Richard saying he is starting to learn and test Dataverse and Power Platform.  I trialed Power Platform last year and made a test app with Power Apps, but I didn't use Dataverse as a data source (I used SQL Server instead).  I would say most people in this forum are (way) more familiar with SQL Server than with Power Platform and Dataverse.  The latter runs entirely online and uses proprietary tech, whereas the former can be run locally and/or online and uses industry-standard tech like SQL.  

I migrated to SQL Server in the early 2000s, first for my local office network, then for remote users.  There are overlaps but also considerable differences in table design, field types, SQL syntax, etc. between SQL Server and Access.  One big difference is transactional SQL (T-SQL) in SQL Server, which allows the use of variables, loops, etc., within SQL statements.

Using Access as a front end for an SQL Server back end is not a problem, but there are caveats.  If the back end resides on the Internet or any remote site, all data retrievals done by the Access front end have to be optimized and designed specifically for a remote connection that may only be a fraction of the speed of local hard drive access.  You can't have a user browsing 15k rows in a table and slowing everyone down on the network in such a setup.  When you first designed your Access front end, you mostly didn't anticipate such a need.  Therefore, you likely need to make considerable changes to your Access front end for it to be used with a back end that resides online.  This was certainly what happened to me when I migrated.

You may also consider making a web front end (which I also did) for the SQL Server back end.  A web interface requires HTML, ASP, VBScript, Javascript, and other components to make.  If you already wrote VBA in Access, you may be able to adapt it for VBScript (like I did) since the two languages have similarities.  A web interface can also be used locally (i.e. in an "Intranet"), which is good for some users.  Not everyone has the necessary skills to use Access, but almost everyone and their grandma can use a web browser.

Kevin took the words right out of my mouth. Personally, I recommend SQL Server. I've been using it for literally decades, and it's the tried-and-true, bulletproof solution that I would put any business on, whether a local on-prem or cloud-based solution. It integrates nicely with Access, and you can build a web interface for it. Yes, you may need to redesign some things if you're used to just working with Access tables, but in the long run, it's the best solution.

I don't consider myself an expert with Dataverse or Power Platform at this time, but from the little bit that I have experimented with it, it's nowhere near as powerful as SQL Server. To me, it seems like Dataverse and Power Platform are designed for non-database professionals who want to build an app, get their Excel data online, or do those kinds of things.

Whereas SQL Server is the tool for true database professionals. But that's just me. Like I said, I'm still learning Power Platform and Dataverse myself.

In fact, I just asked GPT to give me a comparision between them...

Key Differences and Considerations

Control and Customization: SQL Server offers more control and customization options, making it suitable for complex and large-scale applications. Dataverse and Power Platform, on the other hand, prioritize ease of use and rapid development.

Scalability: SQL Server can handle more extensive and complex databases with higher performance requirements. Dataverse is more suitable for smaller to medium-sized applications.

User Expertise: SQL Server requires more technical expertise to manage and optimize, while Dataverse and Power Platform are designed to be user-friendly for business users.

Integration: Dataverse and Power Platform provide seamless integration with each other and other Microsoft services, facilitating the creation of integrated business solutions without extensive development effort.

Deployment and Management: SQL Server involves more infrastructure management and maintenance, whereas Dataverse and Power Platform are cloud-based and managed by Microsoft.

So one of Dataverse/PP's main pros is "rapid development," but if you're working with Access, you already have the rapid development platform you need to build your database. As far as the other items, yeah, SQL Server has a steeper learning curve, but if you're already familiar with Access and SQL (tables, queries, etc.) then you won't have much difficulty adjusting.

I moved most (90%) of my data to Dataverse in March of 2023, I also move just a few tables to SharePoint list (10%) to see which one was faster on read and write of data. In the end of my transition, both data sources work with Access, subtitle differences on behavior (Microsoft added about 10 extra fields in Dataverse to your existing table when you move to Dataverse). Writing VBA is the same, there is a lot of other issues I did come across like permissions with Dataverse, however once you get a hang of that, it's easier to manage. I do miss the speed of local NAS behavior than using Dataverse/SharePoint List for my data source. The slow behavior, of Dataverse has worked for "good enough". I have worked remotely as well as in the office and so has my 4 coworkers and we have been able to work though the speed issues.  I want to learn to build PowerApps to use the same data and see if I see better performance, however I have not dedicated enough time to PowerApps.