Results 1 to 7 of 7
  1. #1
    mcahren is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    3

    DB Application GUI - Need Brainstorming Help

    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.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    we use Access as the frontend to our SQL server files.
    Some have limited access to alter data, but most only use it for queries.
    They can get their data quickly and up to date this way.

  3. #3
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    SSRS from SQL 2008R2 [or maybe 2008] on doesn't use IIS but uses it's own service so the two don't always work well together without some tweaking. We have set up such a system and yes, it could be messy.
    Personally I think that storing the DSN file on a network drive could be a security risk, especially with financial data.
    Is this an intranet site? If so, is the system under domain control? We use some cloud based solutions that the users still sign in through their domain account. If you use domain accounts, you could make it much more secure. SQL logins should be a last resort over a DC account

  4. #4
    mcahren is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    3
    Thanks Turbo, yes, that makes sense about security. It is isolated to our intranet, and I probably do need to provision specific domain users directly. I wonder if it's possible to use a trusted connection without a DSN in the Access DB?

    I could create a role or group for the Finance BAs, and maybe a grant select access to 'everyone' to some integration status views that don't contain sensitive data.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    We in our network use Domain Groups.

    For every application, one or several (when different users must have different rights) Domain Groups are defined as Login's. All such Domain Groups belong into certain OU.

    For every Login, needed database(s) are mapped and database roles are determined.

    Unless there are very specific security conditions, when a user needs access to some database, his/her username is added into proper group, and that's all.

  6. #6
    mcahren is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Feb 2018
    Posts
    3
    Quick update... my organization subscribes to Office 365, and I stumbled across "PowerApps", which is a super simple interface for developing apps for any platform (phone, tablet, computer) and the data connectivity features are pretty decent. Security is straight forward (leveraging AD). https://powerapps.microsoft.com/en-us/

    I did have to install a "gateway" inside our firewall.

    I was able to create my first test app, which included displaying a view on a "screen" with a bunch of other big button controls in less than 30 minutes, and that included installing the gateway and configuring the data source. A couple hours later, I had a pretty cool app. You can run stored procs, insert and update records, basically anything.

    I do still plan to use MS Access for two specific users (the finance owners of the financial app) who need to be able to insert/update records, run custom queries, look at other tables, etc., but the PowerApp will serve the other 95% of users well who just need integration status, and some other basic info.

  7. #7
    turbofish is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2017
    Posts
    25
    Quote Originally Posted by mcahren View Post
    Quick update... my organization subscribes to Office 365, and I stumbled across "PowerApps", which is a super simple interface for developing apps for any platform (phone, tablet, computer) and the data connectivity features are pretty decent. Security is straight forward (leveraging AD). https://powerapps.microsoft.com/en-us/
    That does look interesting. I'm working on an OLAP system but my boss only understands pretty interfaces. It has been interesting working in IT where the vast majority of my bosses don't understand the basic concepts of what I do! I had one director of IT that we had to teach him how to turn on his computer!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-27-2016, 10:59 AM
  2. Replies: 1
    Last Post: 08-27-2015, 06:05 AM
  3. Replies: 4
    Last Post: 01-05-2014, 02:09 PM
  4. Replies: 0
    Last Post: 08-26-2008, 09:22 AM
  5. Access application to Web based application
    By admaldo in forum Access
    Replies: 0
    Last Post: 06-12-2008, 06:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums