What's Microsoft's solution(s) for this problem ? RRS feed

  • Question

  • I work in a large corp.  We have MS Office 365 ProPlus

    I need a DB with one main table that can be edited by users in a dozen different timezones from the web.

    What I would like:


     - Gui for editing the table:  Spreadsheet-like, rows and columns, users edit cells, Looks/behaves like Access

        frontend (or ExCel).  including filtering, reordering records, etc...

     - Shared DB with conflict resolution when multiple users try to edit the same record at the same time 

        (if this is a showstopper, it can be optional deferring to "last writer wins")

     - Easily customizable pulldown menus for fields with limited/restricted options, incl date/calendar (like Access)

     - DB trigger support (like Access or even more robust would be nice)

        If trigger detects a problem, I need the ability to inform the user (with a pop-up?) exactly what is wrong. 

        (Access has this ability)

     - Auto-increment, unique, primary key integer  as a data type (like Access)

     - Custom Query Support.  Click a "saved" query and you get a subset of the main table with contents

        users can edit. (like Access)

     - Ability to recognize who is running the gui (username), storable in a variable that can be used by triggers.

     - Ability to validate user (ref LDAP server or something ?) 

     - SQL readable from outside (NOT like Access).  Probably need a DB daemon, like SQL-Server.

     - Custom graphs/tables based on query results.  E.g.(s)

        + plot as x/y graph of the results of 'select usage,date from my table where ....' 

        + Prompt user, then query, then graph.  E.g. "what project?", "what domain?", "what user?"

            Then query "select usage, date from mytable where project = '$project' and domain = '$domain' and user = '$user'

            Then plot the graph

     - Allow cron jobs to run scripts which generate data.  Probably custom macros.  Access has this ability.

        E.g. run a cron job that collects weekly stats on data in the main table and post those results in another table.  Have it run midnight every Sunday.

     - Backup/recovery support.


    Another great feature would be to auto-generate graphs using a cron job, and stash them somewhere (like maybe SP).


    What I have now:

    An MS Access DB, split, one "backend" located at one site, multiple frontends, one per user desktop/laptop all over the world (about 200 users). 

    Users invoke their frontend, it reads/writes from/to the backend.

    What's wrong:

    - slow.  It seems to need to read the DB into the frontend before doing anything.  If network is slow, this whole thing is too slow.  If user is on the other side of the globe from where the DB sits, it's even slower.

    - MS Access DB can get locked/confounded.  Attached processes need to be identified and killed.  I don't have root privs, so I have to get others in IT to do this.  Can take all day.   I want a real SQF DB, for which I am the admin, so that I can kill rogue attaches.

    - Can't run Access as a net-app on something like SP.  I read that this used to be an option, but that was yanked for some reason.  What's the alternative ?

    Basically, what I'm looking to do is migrate my Access solution to something web based.

    I'm thinking that if the web server and the DB server is the same server, or on the blade right next to it in some IT compute farm, there will be no delays as described above.


    I also have users, who write html, who might want to interact with this DB by attaching to it and throwing SQL at it. 

    Wednesday, November 20, 2019 6:48 PM

All replies

  • tl;dr

    You are asking too much. You need to identify the most critical questions of all those many questions and ask one question at a time.

    Many people would say that you need to use a cloud database. Many years before cloud databases the technology called distributed databases existed. You need something like that. Such things are not a matter of Microsoft's solutions; it is a matter of database design in general.

    Forget about Access. Don't even mention its merits and problems. Use SQL Server.

    For cron jobs, the Microsoft solution is scheduled tasks.

    Sam Hobbs

    Friday, November 22, 2019 5:30 PM