Nov 20 2020 06:11 AM
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:
Please keep in mind 2 things:
I'll appreciate any help I can get!
Nov 20 2020 11:47 AM - edited Nov 20 2020 11:48 AM
One solution would be to migrate the data to Azure SQL and relink your front-end to the Azure instance. Then you can distribute the front-end to your users and they have nothing to do but use it.
What about using Citrix, Terminal Services?
Nov 21 2020 08:26 PM - edited Nov 21 2020 08:29 PM
hank you for your help but Unfortunately this is a semi-government organization, we have strict information Security Protocol.
For some reason saving files in OneDrive was cleared as fine, but we can't use Azure SQL or similar
Nov 22 2020 06:26 AM
SolutionWe 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.
Nov 22 2020 06:26 AM
SolutionWe 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.