I support a large financial app, and we recently moved to the cloud version of that software, and part of that involved creating a lot of custom integration, and data staging in MS SQL Server (hosted on an AWS instance). It's a fairly large and complex system.
I need to expose several tables/views to the finance BAs (2), and I have started down the path of using Access 2016 with SQL Server linked tables and custom code (to invoke stored procedures, etc.), I'm just getting started, but I think it's a really nice solution.
I need to architect and develop the rest of the app, My questions:
1. My Access app currently uses a file DSN that's stored on a network drive. It uses a saved account and password. Is that the best way to go? I haven't deployed this Access app to the users yet, so I want to make sure I make this as simple and reliable as possible.
2. I have the need to create a "dashboard" webpage that shows the results of several different queries/views (data integration history, dataflows enabled/disabled, backup status, etc.). I need this dashboard to be current, i.e., when a user refreshes the webpage, it reruns the queries. It would be a huge bonus if users could update records (change an 'enabled' field on a record from 0 to 1). What are my options here? My AWS instance (Windows 2016) is running Microsoft SQL Server Reporting Services and IIS, so I do have those at my disposal, but I was curious if Access had this capability. I want to K.I.S.S. as much as possible, as I have very limited time to develop and support this.