Results 1 to 8 of 8
  1. #1
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33

    Best solution to share a Database online with a team of collegues

    Hi everyone,
    I'm trying to share a database with my collegues.
    At the moment I have one single .ACCDB file which contains tables, queries, forms and reports.
    We've been sharing this .ACCDB file on OneDrive, and 4 collegues occasionally open it to read or edit the database using the main form.

    I know this is a really bad practice: infact, whenever two of us try to edit the database at the same moment, OneDrive creates another copy of the file and the edits cannot be merged.

    Since the number of users that have to access this database is increasing over time, I need to find a way to properly share the database online.

    I've watched this video that is really well made:





    It is clear to me that I'll have to split the database into a backend and a frontend, but I'm still trying to figure out which is the best platform where to migrate the tables.

    I've tryed successfully with SharePoint (converting the table in SharePoint Lists on my organization SharePoint website), but the database becomes really slow, especially for heavier tasks, so I'm trying to find another solution.

    Regarding Microsoft Dataverse or SQL Server, would I get better performance than SharePoint? If with SharePoint the process was pretty simple, I really don't know where to start in order to setup a SQL Server online...

    Thank you in advance

  2. #2
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    Insert a module in Access, even Excel if you want. Then run this code:
    Code:
    Sub JustSendRequest()
        Set oRequest = CreateObject("MSXML2.ServerXMLHTTP.3.0")
        oRequest.Open "GET", "https://something-something-default-rtdb.firebaseio.com/"
        oRequest.Send
        MsgBox oRequest.responseText
    End Sub
    If the message box showed up, that was a web database talking to you and that's the speed you can expect from it.

    Adapting to it will require changes to the way you're currently working, but it's very reliable.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    One of the problems with that sort of solution is that you need to populate ado recordsets. These have their uses but you cannot use them for reports and if used on forms, many of the built in features such as filter and sort don’t work with ado - you have to write your own equivalents.

    not saying it’s not an option but you will virtually need to start from scratch.

    easiest solution is to use a terminal server host. Each user has their own profile much as they do locally so both fe and be are stored on the sever. Performance is fast since the only things travelling across the web is screen updates one way and keyboard mouse transactions the other. No changes need to access other than relinking the back end.

    another alternative is to use sql azure. Some changes required such as moving queries to azure.

    or look at using vpn

  4. #4
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    There are many challenges with it. But we can get creative, a small team could benefit from a workflow like
    request records,
    then empty a local table,
    then populate table with records on form load

    Records already come with a random id, so a lot can be done if we get creative and we can still use access tools, it's like a disconnected database

  5. #5
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33
    I reopen this post after several tries.

    1) I checked the SharePoint method and there was an error in the code; after some tweaks, the performances wasn't that bad; the real issue is that SharePoint Lists don't allow table relationships, so I decided to give up using SharePoint;
    2) I tried with Microsoft Azure SQL Database, but in this case the slow performances were really unsustainable with the free subscription plan that my company is currently offering.
    3) I tried launching my own MySQL Server, and the speed of course was the best among all of this solutions; the only issue is that I would have to run the server myself 24/7, exposing me (and probably also my company) to potential threats; furthermore, in order ro connect Access to MySQL, a connector driver must be installed, and the PCs of my company often doesn't have admin rights and it is a long process to get the stuff installed.

    After all of these tries, I can think of only two last solutions:
    1) Oracle SQL database
    2) Microsoft Dataverse

    Considering that maybe I can have access to a corporate account of Dataverse, what will be the performance compared to the previous methods I mentioned? Will I be able to execute SQL queries as usual after migrating the backend to Dataverse?

    Do you have any other suggestions?
    Last edited by Trial4life; 05-30-2023 at 11:58 PM.

  6. #6
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    Did you try Firebase Realtime Database? Are you willing to set up a VPS?

    the only issue is that I would have to run the server myself 24/7, exposing me (and probably also my company) to potential risk threats
    That depends on what you're going to use.

  7. #7
    Trial4life is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2022
    Posts
    33
    I'v never heard about it, I don't have much experience about online services. The man thing Is that probably it will be more easy to get Microsoff platforms instead of external services, since the organization where I work at is really big and I'm creating this database on my own...

  8. #8
    Edgar is online now Competent Performer
    Windows 8 Access 2016
    Join Date
    Dec 2022
    Posts
    272
    Good luck, hope you find a suitable solution.

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

Similar Threads

  1. Run Racing Team Database
    By awallace in forum Database Design
    Replies: 2
    Last Post: 09-08-2021, 07:28 AM
  2. Team Points tracking database
    By Daisy509th in forum Access
    Replies: 10
    Last Post: 01-19-2018, 08:16 AM
  3. team building database
    By ldypurple in forum Access
    Replies: 6
    Last Post: 03-06-2013, 04:34 PM
  4. Replies: 8
    Last Post: 07-09-2012, 07:19 AM
  5. Sports Team database
    By Patriot7470 in forum Database Design
    Replies: 2
    Last Post: 03-17-2012, 01:50 PM

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