Hi! I've built an access application for my organization to use managing project records.The situation is that the only one that have promotions to edit the data in the tables is me, and many other users need to have access to 'read only'. The best solution we thought of is storing it in a shared folder in MS OneDrive and giving read only access to anyone who needs it. The ideal situation is to store both application and DB files in the cloud and each user can open it on their workstation. Or another option is to keep only the DB on the cloud and save the .accde file on each workstation, linked to the DB on the cloud.But for both ways, we ran into some problems:I can't put it on every computer in advance and I need each one will do it by their self. The problem is linking to the database. Is there a smart way to link to say the file URL on the cloud or any other solution? or if I keep both files in the cloud- can I link keep it linked to the sub-folder? I couldn't find any suitable solutionIf I ask everyone to save the application file locally on their computer, every time I will need to change something in the design, everyone will have to change the application file on their computer (and like it to the DB). This is something we would like to avoid. Please keep in mind 2 things:We are planning to work on a shared point in the future (6 months from now), but we need an immediate solution and my organization have 365 and OneDrive license.Most workers are not handling well with technology, so the solution must be as simple as possible. I'll appreciate any help I can get!

Copper Contributor

Nov 20, 2020

Solved

MS Access Stored on a Cloud

Hi!

I've built an access application for my organization to use managing project records.

The situation is that the only one that have promotions to edit the data in the tables is me, and many other users need to have access to 'read only'.

The best solution we thought of is storing it in a shared folder in MS OneDrive and giving read only access to anyone who needs it.

The ideal situation is to store both application and DB files in the cloud and each user can open it on their workstation. Or another option is to keep only the DB on the cloud and save the .accde file on each workstation, linked to the DB on the cloud.

But for both ways, we ran into some problems:

I can't put it on every computer in advance and I need each one will do it by their self. The problem is linking to the database. Is there a smart way to link to say the file URL on the cloud or any other solution? or if I keep both files in the cloud- can I link keep it linked to the sub-folder? I couldn't find any suitable solution
If I ask everyone to save the application file locally on their computer, every time I will need to change something in the design, everyone will have to change the application file on their computer (and like it to the DB). This is something we would like to avoid.

Please keep in mind 2 things:

We are planning to work on a shared point in the future (6 months from now), but we need an immediate solution and my organization have 365 and OneDrive license.
Most workers are not handling well with technology, so the solution must be as simple as possible.

I'll appreciate any help I can get!

We found a solution for our problem:

We used the Application.CurrentProject.Path to get the file location and then added the path to the sub folder with the DB file.

Then we used- Set dbs = CurrentDb()

and for each table we did:

dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
dbs.TableDefs(strTable).RefreshLink

Full solution:

Dim LnkDataBase As String
LnkDataBase = Application.CurrentProject.Path & "\rest of path\DB.accdb"
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim strTable As String
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
    If Len(tdf.Connect) > 1 Then 'Only relink linked tables
        If tdf.Connect <> ";DATABASE=" & LnkDataBase Then 'only relink tables if the are not linked right
            If Left(tdf.Connect, 4) <> "ODBC" Then 'Don't want to relink any ODBC tables
                strTable = tdf.Name
                dbs.TableDefs(strTable).Connect = ";DATABASE=" & LnkDataBase
                dbs.TableDefs(strTable).RefreshLink
            End If
        End If
    End If
Next tdf

It appeares it solved it.

3 Replies