Remote Desktop Frontend backend relink

[Rachael2211]

Hi All and thanks in advance for taking a look!

Situation: split 2010 database loaded onto the server in our T drive. T drive stores our Tenants data so each tenant or client has a folder called for example RachData or JohnData etc. These folders contain only the clients backend. Also on the T drive we have a folder which stores the frontend (deployed as an accdr) which means when an update is done we have only to replace one file - Now, each time a client logs in through the remote desktop the accdr is copied to their 'My Documents' folder for that session. The session and client is determined by the logon username eg RachaelC @ RachData.BusCoName.com.au with the bit after the '@' determining their t drive tenant folder.

Problem: relinking frontend to backend
My initial thought has been to capture the 'RachData' part out of the logon username and relink each table via vba on startup with a hidden form. I can make this work when the front end and backend are both on the T drive as I can capture the frontend path and use for relinking but once the frontend is copied to MyDocs I need a way to capture the t drive and the correct client folder.

I've experimented with the environ command and can get 'RachaelC' from environ("username') and there are a whole host of commands for getting different info but am just unsure about how to get the 'RachData' part out of the username logon.

This may be a completely wrong way to go about it and would appreciate any input....thank you!

Cheers, Rach

 

[Galaxiom]

This function returns the first domain from an email address.

Public Function GetSubDomain(ByVal email As String) As String

Dim AtLoc As Integer

    AtLoc = InStr(email, "@")
    GetSubDomain = Mid(email, AtLoc + 1, InStr(AtLoc + 1, email, ".") - AtLoc - 1)

End Function

 

[Galaxiom]

Rather than getting an environment variable with Environ("username") it is better to get the actual username:

CreateObject("wscript.network").username

BTW A better place to put the front end is the user's Appdata folder. It is normally hidden so they don't see it. Its path can be returned via environment variables.

 

[Rachael2211]

Hey Galaxiom - thanks heaps for your response!

when logged onto the rdp both environ("username") and the CreateObject method you mention return the 'RachaelC' part of my logon username but what I'm wanting is to get the folder name out of the logon username and I don't know how to call up the 'rachaelc @RachData.BusCoName.com.au' (had to put gap in address, the forum doesn't like me to post an email address, not enough posts, lost my old account and had to make new one!) which is used to logon onto rdp.

I also thought that maybe I could capture the redirected folder name but not sure how to do that either - I have a senior cloud engineer who has set up the server so no questions needed in regard to the server setup - just my issue with getting the frontend to relink back to the clients data that sits on the hidden t drive in their own folder.

Maybe a better method!!!!.....not sure, not savvy with servers but learning

Kind regards, Rach

 

[Galaxiom]

CreateObject("wscript.network").UserDomain

 

[Rachael2211]

I've looped through all the possibilities and none return 'RachData' as the redirected folder name so ?? You'll see that userdomain returns STH

ALLUSERSPROFILE=C:\ProgramData
APPDATA=C:\Users\RachaelC\AppData\Roaming
CLIENTNAME=RACH
CommonProgramFiles=C:\Program Files (x86)\Common Files
CommonProgramFiles(x86)=C:\Program Files (x86)\Common Files
CommonProgramW6432=C:\Program Files\Common Files
COMPUTERNAME=STHSTS01
ComSpec=C:\Windows\system32\cmd.exe
FP_NO_HOST_CHECK=NO
HOMEDRIVE=C:
HOMEPATH=\Users\RachaelC
LOCALAPPDATA=C:\Users\RachaelC\AppData\Local
LOGONSERVER=\\STHDC02
NUMBER_OF_PROCESSORS=2
OS=Windows_NT
Path=C:\Program Files (x86)\Microsoft Office\Office14\;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Windows\System32\WindowsPowerShell\v1.0\
PATHEXT=.COM;.EXE;.BAT;.CMD;.VBS;.VBE;.JS;.JSE;.WSF;.WSH;.MSC
PROCESSOR_ARCHITECTURE=x86
PROCESSOR_ARCHITEW6432=AMD64
PROCESSOR_IDENTIFIER=Intel64 Family 6 Model 63 Stepping 2, GenuineIntel
PROCESSOR_LEVEL=6
PROCESSOR_REVISION=3f02
ProgramData=C:\ProgramData
ProgramFiles=C:\Program Files (x86)
ProgramFiles(x86)=C:\Program Files (x86)
ProgramW6432=C:\Program Files
PSModulePath=C:\Windows\system32\WindowsPowerShell\v1.0\Modules\
PUBLIC=C:\Users\Public
SESSIONNAME=RDP-Tcp#2
SystemDrive=C:
SystemRoot=C:\Windows
TEMP=C:\Users\RachaelC\AppData\Local\Temp\16
TMP=C:\Users\RachaelC\AppData\Local\Temp\16
USERDNSDOMAIN=STH.LOCAL
USERDOMAIN=STH
USERNAME=RachaelC
USERPROFILE=C:\Users\RachaelC
WecVersionForRosebud.5D8=4
windir=C:\Windows

thanks once again for taking a look!

Rach

 

[Rachael2211]

I'm thinking I need to capture the redirected folder name but not sure how!

 

[Galaxiom]

The folder redirections can be found in the registry:

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders

 

[Rachael2211]

Thanks Galaxiom for your replies - getting abit stumped with this - server setups are not my forte - not really sure how to proceed...

I tried reading the registry and could get that to work - I had to add \personal onto the registry key to get a response and got %USERPROFILE%\Documents but still not sure it helps me find a path to the clients folder on the T drive to relink the backend.

Any other ideas or pointing in a direction....

Thanks again and kind regards, Rachael

 

[Galaxiom]

Since they are all in %USERPROFILE% you have no redirected folders. I took your reference to "redirected folders" literally. There is a Group Policy that allows the user folders to be redirected. Generally it is used to put MyDocuments and Desktop on a server so that are found in the same place regardless of which Remote Desktop server the user connects to.

I am no expert at RDS but I am bewildered by the structure of your Fully Qualified Domain Name (FQDN) including a subdomain that appears to be for the specific user. It is as though your admin has made a subdomain for each user. I have never encountered this arrangement.

I suspect that the full login identity mighty not be available in the session. Probably easier to just store the path information in a table.

Otherwise, talk to your admins. Maybe there is something you can query through LDAP.

 

[Rachael2211]

Hi Galaxiom and anyone else sho checks out this thread,

Here's an update on where I'm at: Getting fairly happy with progress but would love some input into my vb code as it's not my strongest point and I've cobbled together bits and pieces - my concern is the DAO and ADODB business - I've always done the DAO thing and am not sure if that's right or not - can I just change the ADODB to DAO or ..... - anyway here's the code...

Dim objRoot As Variant
Dim LDAPdomainName As String
Dim UserName As String
Dim UserDomain As String
Dim cn As Variant
Dim cmd As Variant
Dim rs As Variant
Dim domain
Dim TenantFolder As String
Dim TenantLogon As String

Dim db As DAO.Database
Dim td as DAO.Tabledef
Dim DBPath As String
Dim BEPath As String


Set db = CurrentDb()

UserName = VBA.Environ("UserName") ' Gets Current User
UserDomain = VBA.Environ("UserDomain") 'Gets Current User's Domain

Set objRoot = GetObject("LDAP:/ / RootDSE")
domain = objRoot.Get("defaultNamingContext")
Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=ADsDSOObject;"

cmd.activeconnection = cn
cmd.commandtext = "SELECT cn, userPrincipalName FROM 'LDAP://" & domain & "' WHERE sAMAccountName = '" & UserName & "'"
Set rs = cmd.Execute

Do Until rs.EOF
TenantLogon = rs("userPrincipalName")
rs.MoveNext
Loop
rs.Close
cn.Close
Set rs = Nothing
Set cmd = Nothing
Set cn = Nothing

TenantFolder = GetSubDomain(TenantLogon) 'function to parse out tenant folder name which occurs after the @ symbol

DBPath = " \ \sthfs01\ RachCo$ \Tenants\" & TenantFolder & "\"
BEPath = DBPath & "BVDataFile.mdb"
For Each td In db.TableDefs
If Len(td.Connect) > 0 Then
td.Connect = ";DATABASE=" & BEPath
td.RefreshLink
End If
Next


thanks for any input, Rachael