Execute VBA Function via View Query in MS Access 2013 from JS ActiveX ADO

Question

How to execute a VBA macro via a view query in MS Access 2013 from JS ActiveX ADO?

The VBA function is to get the currently logged in user using:

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

The view query is a select statement that return the VBA function value.

I want to execute that view query from JS ActiveX ADO over LAN (same domain policy). The query throws an error saying that I need at least one table or query. I also noticed that if I read from a view query that does not execute a VBA function, it passes with no problems. Is this a security control by design? Any way to get the user ID in this setup?

Thank you!

Answer 1

You probably can't call the API function directly from a query, but if you put it in an Access module, and create a function that returns a string it should work for you.

You haven't posted any other code or SQL so I'm not sure if you've tried that yet.

Here's what I use in queries with same API sub you're using

Public Function NetworkUserName() As String
    Dim strBuffer As String * 255
    Dim lngBufferLength As Long
    Dim lngRet As Long
    Dim strTemp As String

    lngBufferLength = 255
    lngRet = GetUSer(strBuffer, lngBufferLength)
    NetworkUserName = Left$(strBuffer, InStr(strBuffer, Chr$(0)) - 1)
End Function

Then your query would just look like

SELECT NetworkUsername AS CurrentUser

EDIT - Add possible option to use an ActiveX object inside JScript

Instead of using ADODB call you could try pulling environmental variable %USERNAME% using Wscript.Shell ActiveX object and the ExpandEnvironmentStrings method.

But you're probably looking for Network username - maybe this would suffice inside JScript

var winNet = new ActiveXObject("WScript.Network");
alert(winNet.UserName);