Results 1 to 6 of 6
  1. #1
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11

    Efficiency of Design Question (backend/frontend)

    I'm using access as a front end to a SQL database to extract and de-normalize data for use in reporting and export to various departments for their needs. I have (currently) all the base table extracts built to pull out the data daily and even extract some spreadsheets and auto-email them on defined schedules different people need for their reports.



    I'm now looking at finding the best process for taking my daily query executions (mostly make table queries) and pushing them to a front-end access dbase that others can then be given access to (cutting them off from the SQL server and my stored credentials in the base file to avoid "complications").

    My current thought process is to just run the make table queries from the back end Access dbase to the public front end with the refined tables.

    I have to do this from a local machine to a remote network drive access file that has substantial network lag (long story and working with IT on solutions there).

    I'm curious if running the make tables to the remote access file is the best/most efficient process or if running the make tables locally and then copying the tables over makes more sense rather than running them direct to the other file..... OR is there another even better/more elegant solution I'm not realizing?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you give us more info on the need to do these extracts? Why can't users run predetermined or parameter based reports?

    Not sure that I understand this
    My current thought process is to just run the make table queries from the back end Access dbase to the public front end with the refined tables.
    With Access and sharing should be done based on a common Back end and with each user having a copy of the front end on his/her own PC.

    Sounds like you could repopulate back end tables and have users work with that database. But, as usual, the devil is in the details of your needs and processes.

    I have set up Monthend/quarter end read only databases which we referred to as "point-in-time" databases. These were populated from an online system at appropriate times, The "PIT" databases included predefined queries, forms and reports.

  3. #3
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    We're a recently "merged" organization spanning 5 states. Each state still has it's own IT infrastructure, and backend for almost all processes, and we're on a 5 year road-map towards creating a fully contiguous data backend.

    We have ONE new system that through multiple interfaces pulls together mos of the data needed for a lot of our reporting, but by and large the consumers are VERY non-tech savvy. So for many getting an excel file is a stretch, beyond that all of our networks don't inter-operate very well, so we effectively have a single network drive that is the only point of access in all 5 states currently. We DO have a brand new sharepoint site, but that hasn't been fully implemented or rolled out everywhere yet either.

    The goal of the front-end is to allow analysts and others who want the raw data to be able to access the stuff they need without giving them access to the SQL database of the one unified system (it's also a production system being accessed by a front-end application of it's own constantly).

    My goal is to be able to give everyone the data they need in the easiest to digest format for them, and we have some users in a few places that have a desire to get as much raw data as possible. IT doesn't want to compromise the access levels to the SQL database and were gracious enough to give me that level of access so I'm trying to stitch it together to a functional format for consumption.

    Basically we have no viable intranet with consistent connections across all regions, so my sharing options are the one network drive, emailing items, or (soon) sharepoint/onedrive.

  4. #4
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    I guess to expand a little, I'm also the data delivery "guy" for this system by and large. So, for example, I have one state's contact center needed a simple file weekly with names, codes and addresses that I could set up to email out routinely and they loved it. While other departments are trying to do deeper analyses of their performance across multiple measures and need LARGE data sets, but we also want to keep them constrained to within the "go-live" of this one system we're pulling from because the historic imported information the system contains isn't complete or entirely reliable and hence not good for making business decisions.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Since the main server db is production data, I'd suggest that a DEE (Decision Enabling Environment) database be created. This would be a nightly backup from production data, and all Access fe connections be based on this copy with read only access. I'm surprised you were given such access to production data. It would be VERY easy for this to be severely compromised by access to it via Access, so I have to suspect that the IT person(s) who gave such access don't know Access very well.

    If you're looking for 5 state access to a centralized data source, Citrix or a Terminal Services platform might be the way to go but each user fe would have to be placed on a Citrix or Terminal Services server located in the same ethernet domain as the source data. Access over wifi is a sure recipe for disaster. Sharing a be over cloud services such as OneDrive is no good either (where the be is on the cloud and users are trying to access the same be). My meager experience with Citrix is working in Access from home, but the database was not local to me. Citrix would provide remote access to be and fe databases located on the same server or ethernet.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    dbc23 is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2019
    Posts
    11
    One of our states is using Citrix but that's being deprecated. We use VMWare for a virtualization solution, and that's been discussed as a possible option (creating a persistent VMWare login for those accessing the data). We're VERY aware of the risks, which is also why I'm the only person with this level of access to the SQL server and why i'm trying to segregate a front-end.

    The real problem is a demand for near real-time data access and nightly extracts won't give us the level of currency we need. I suppose I could set it up to extract every few hours to accomplish a similar goal though.

    Truthfully we need a data warehouse but our IT department is under-resourced at the moment so we're working with what we've got in the mean time until we can get a real data cube, there's other databases we desperately want to integrate with this data but they're mostly hosted (off-site) solutions which makes pulling full extracts problematic, if not impossible.

    I've limited myself to only select and make tables from the data, and am only working with the tables linked as read-only from the server.

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

Similar Threads

  1. Access DB Backend / Excel Frontend
    By Entellex in forum Database Design
    Replies: 5
    Last Post: 11-08-2017, 10:51 AM
  2. Frontend to Backend Databases
    By raychow22 in forum Access
    Replies: 6
    Last Post: 08-11-2017, 04:47 PM
  3. Frontend Backend Database Questions
    By data808 in forum Access
    Replies: 29
    Last Post: 03-26-2014, 11:58 PM
  4. Replies: 1
    Last Post: 08-01-2013, 09:11 AM
  5. Access Switching to frontend and backend
    By caliskier in forum Access
    Replies: 4
    Last Post: 11-19-2012, 11:58 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