0

I would like to get the username in VBA of the person who launched the RemoteApp MS Access application. I have set it up to have everyone log into the application using a service account (called "svcactuser") so that Access/SQL Server can manage the connections without table locks and to run SPs. Typically in any Access application I would use: 

Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As 
Long) As Long

Now I need another way to get the actual user username (not the service account username "svcactuser"). Can you tell me how in VBA I can get this information?

Thanks

Conrad Addo
  • 424
  • 1
  • 5
  • 15
  • 1
    `...everyone log into the application using a service account ..` unless you have secondary login method, not possible. – Krish Sep 27 '18 at 12:09
  • If you want to identify each individual user, you should not let the users run your application all using the same account.Do you actually know all people that have the password? You could as well remove all security measures. – Wolfgang Kais Sep 27 '18 at 17:45
  • There is `Environ$("CLIENTNAME")` environment variable in remote session which should show name of user's host but it can be changed or unset. – 4dmonster Sep 28 '18 at 05:54
  • @WolfgangKais - no they don't have the service account password. It's an AD account with only SQL Authentication in the DSN and they're just added to the group. Using the users Windows account we were getting massive table lock errors for >1 concurrent users. – Conrad Addo Sep 28 '18 at 23:03
  • I don't see what causes the massive problems, but I think that I understand your setup. The clients run MS Access as a RemoteApp using a dedicated account (a "service" account) which is as individual as the client's local account, right? Looking at a specific user, if only that user has the password for his/her "service account", you could just omit the first the letters ("svc"). The terminal server has no knowledge about the original user, so your task seems to be impossible to solve. Also, I can't see how using individual "service accounts" can solve your concurrency problems. – Wolfgang Kais Sep 28 '18 at 23:56
  • @WolfgangKais the issue arises when we have a single MS application connected through remoteapp. To be able to link the tables inside MS Access I have to link on the user's Windows login. However, this is messing up other's connections to the table because the change is being made on the single application. So if user 2 logs in after user 1 then the tables are no longer lin ked to user 1 and then they cannot run queries. Do you know a workaround? – Conrad Addo Oct 01 '18 at 14:22
  • Probably you have same VBA code that refreshes the links of the tables each time that a user opens the database. I suggest to not do this when multiple users use that same Access database file. You can link the tables with the Windows Integrated Security setting and without the need to use a DSN. The refresh of linked tables should be seen as a management task that can be executed in a single user environment (where each user has his own copy of the database file) but not in a file that multiple users need to access concurrently. Here, the linked tables have to be prepared in advance. – Wolfgang Kais Oct 01 '18 at 23:21

0 Answers0