Using stored procedures to store repetitive functions/subs (1 Viewer)

Derek · May 31, 2018

Hi Guys

I write down the following code within Access module to get computer name and username . So for every new Access database I will need to write down the same code to retrieve computer name and user name.

As we have now moved on to sql server so we are using Access frontend and sql server backend. At the moment sql server stores only the tables and all the procedures/functions/queries etc resides in Access frontend.

So I am thinking of a way to use stored procedures in sql server to store code to get computer name and user name . How the below piece of code can be written as stored procedures within sql server:

Code:

Option Compare Database
Option Explicit

Public Declare Function GetComputerName Lib "kernel32" _
  Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
  As Long

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


Public Function NameOfComputer()
'   Returns the name of the computer
    Dim ComputerName As String
    Dim ComputerNameLen As Long
    Dim Result As Long
    ComputerNameLen = 256
    ComputerName = Space(ComputerNameLen)
    Result = GetComputerName(ComputerName, ComputerNameLen)
    If Result <> 0 Then
        NameOfComputer = Left(ComputerName, ComputerNameLen)
    Else
        NameOfComputer = "Unknown"
    End If
End Function

Function NameofUser() As String
'   Returns the name of the logged-in user
    Dim Buffer As String * 100
    Dim BuffLen As Long
    BuffLen = 100
    GetUserName Buffer, BuffLen
    NameofUser = Left(Buffer, BuffLen - 1)
End Function

Any help will be much appreciated. Thanks

isladogs · May 31, 2018

You could replace all the above code in Access with just 2 lines:

Code:

NameOfUser = Environ("UserName")
NameofComputer = Environ("ComputerName")

No API declarations required

Minty · May 31, 2018

Another issue with using SQL to do this is that unless you use windows domain logins you can't tell particularly easily who is connected in SQL. You can get the connected computer , but not the user if they are connected by a universal login.

Uncle Gizmo · May 31, 2018

Minty said:
Another issue with using SQL to do this is that unless you use windows domain logins you can't tell particularly easily who is connected in SQL. You can get the connected computer , but not the user if they are connected by a universal login.

I didn't even know it was possible!

I'm glad I didn't reply earlier saying it couldn't be done!

Sent from my SM-G925F using Tapatalk

Minty · May 31, 2018

Something like

Code:

Declare @UserName nvarchar(50)

SET @UserName = (user_name())

Will return the sql user name as will suser_name() or SYSTEM_USER , I believe all work

WayneRyan · Jun 1, 2018

Derek,

Look at the system view: sys.dm_exec_sessions

It'll give you user/database/hardware/performance data for the current connection.

Note that if you connect/disconnect often; the stats are ONLY for current session.

hth,
Wayne

Using stored procedures to store repetitive functions/subs (1 Viewer)

Derek

Registered User.

isladogs

MVP / VIP

Minty

AWF VIP

Uncle Gizmo

Nifty Access Guy

Minty

AWF VIP

WayneRyan

AWF VIP

Similar threads

Users who are viewing this thread