Update Link on table Vs user permission

[marsss]

Hello, in my DB i have to lets user update link to my backend. In my code i did put something like this :

    Set CurDB = CurrentDb
    
    'Cycle through the database tables refreshing their links
    For Each tdfLinked In CurDB.TableDefs
        If Len(tdfLinked.Connect) > 0 Then
            tdfLinked.Connect = ";DATABASE=" & strPath1
            tdfLinked.RefreshLink
        End If
    Next tdfLinked

The litle problem here, is the user doesn t have permission to change data in my DB, he can only see. With this permissikon set, it wont lets him refresh path to backend..

Is there a way i can hardcode a "virtual" admin login in my VBA to do the re-link so i dont need to give to user more permission?

 

[Ed2020]

Open the database up in a separate workspace using the developer username and password.

Something along these lines should do the trick:

Dim wrkNew As DAO.Workspace
Dim CurDB As DAO.Database
dim tdfLinked as DAO.TableDef

Set wrkNew = DBEngine.CreateWorkspace("WrkNew", strUName, strPWord, dbUseJet)
Set dbsECCT = wrkNew.OpenDatabase(CurrentDb.Name)

For Each tdfLinked In CurDB.TableDefs
If Len(tdfLinked.Connect) > 0 Then
tdfLinked.Connect = ";DATABASE=" & strPath1
tdfLinked.RefreshLink
End If
Next tdfLinked

Please do not feed the trolls.....

 

[marsss]

/bump

SO no one know if it s possible to execute a command (tdfLinked.Connect) on a different user inside VBA code.

ie.: Current user sing DB is on restricted permission, once he click a button, i use a admin account inside VBA code to execute a command that he couldn t run by himself

 

[marsss]

Oh thank Ed2020

You did your post as same time i rediged my last reply heh

 

[marsss]

Ed2020 I did try your trick, but look like i can t edit my DB link cause the DB is already open. When i try that, it say you dont have permission to use this object.

Is it because we can t have same DB open twice in same application?, any idea how to solve this?

 

[marsss]

Ayaye.. my bad, forgot to give permission to new user.. hehe

Only thing missing is the the Set CurDB =..

i did try :

Dim wrkNew As DAO.Workspace
Dim CurDB As DAO.Database
dim tdfLinked as DAO.TableDef

Set wrkNew = DBEngine.CreateWorkspace("WrkNew", strUName, strPWord, dbUseJet)
Set dbsECCT = wrkNew.OpenDatabase(CurrentDb.Name)


Set CurDB = wrkNew.Databases(0)


For Each tdfLinked In CurDB.TableDefs
    If Len(tdfLinked.Connect) > 0 Then
        tdfLinked.Connect = ";DATABASE=" & strPath1
        tdfLinked.RefreshLink
    End If
Next tdfLinked

but isn t looking to work, it say no element inside wrkNew

 

[marsss]

Ok, think i posted too fast previously, here final, and it s working fine :

    Dim wrkNew As DAO.Workspace
    Dim strUName As String
    Dim strPWord As String
    Dim dbsECCT As Database
    
    strUName = "user"
    strPWord = "pass"

    Set wrkNew = DBEngine.CreateWorkspace("WrkNew", strUName, strPWord, dbUseJet)
    
    Set dbsECCT = wrkNew.OpenDatabase(CurrentDb.Name)

    For Each tdfLinked In dbsECCT.TableDefs
        If Len(tdfLinked.Connect) > 0 Then
            tdfLinked.Connect = ";DATABASE=" & strPath1
            tdfLinked.RefreshLink
        End If
    Next tdfLinked