locked
What solution does MS have 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:

    Features:

     - 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. 

    • Moved by Richard MuellerMVP Wednesday, November 20, 2019 3:02 PM Not a TechNet Wiki question
    Wednesday, November 20, 2019 2:37 PM

Answers

All replies