Power BI would be my first choice for the dashboards. It is designed for reporting, charts, drill-downs, and sharing dashboards with people off site. You can connect it to an Access database, although for a shared multiuser setup I would strongly consider eventually moving the back end to SQL Server.

Smartsheet is more of a collaborative spreadsheet/project-management tool. It can be useful for tracking tasks, volunteers, events, and simple lists, but I would not use it as a replacement for a properly designed Access database just to get dashboards. You would likely end up duplicating data and creating synchronization headaches.

The important question is whether they only need to view data remotely, or whether they need to enter and update data remotely too. Power BI is excellent for viewing and analyzing data, but it is not a general-purpose data-entry interface. For remote updates, you could look at a web front end, Power Apps, or possibly remote access to the existing Access application, depending on the number of users and budget.

For now, I would keep the Access split database, build Power BI dashboards against the data, and make sure the database is backed up and maintained properly. Then plan the remote data-entry piece separately.

I've had making some videos with Power BI dashboards on my to-do list for a few years now. The list is so long... so long... I need clones.

I would love to see a Power BI dashboard video using Access data.

Thank you - I am working to move the backend to an SQL server.   I am a volunteer but the only one who understands system that they rely on.

Currently for the off site needs I have built a Google Sheet export for their data (class lists/check ins etc) but in order to get the data back into Access someone has to enter it after the class is completed.  

Is there any better way to connect the data back into Access from Google?

Brent Me too

If they are off site but still have access to a Windows PC, then once the data is in SQL Server, the simplest option may be to give them an Access front end connected to that SQL Server database. They can enter check-ins and class information directly, wherever they are, without the Google Sheet export/import cycle.

If they need to use phones, tablets, or other non-PC devices, then Access is not the right front end for that piece. You would need something like a web interface or Power Apps connected to SQL Server. Power Apps is often a reasonable option for simple remote data-entry forms, although I would want to know more about the number of users, what data they are entering, and how complicated the forms and business rules are before recommending it.

You can import or link Google Sheets data back into Access, but it is usually a workaround rather than a good long-term workflow. You still have to deal with matching records, preventing duplicate entries, validating data, and deciding which copy wins if someone changes something in both places. Moving the back end to SQL Server is a good first step.