Can I capture the user computer name with a query?

[MikeFL]

Is there a way to capture the users computer name when they are adding a record to a shared split database with a query so I can save it in a table record? I’m using MS Access 2003.

 

[PHV]

In a standard code module create a function like this:

Public Function getPCname() As String
getPCname = Environ("ComputerName")
End Function

Then in the query you may grab the value with getPCname().


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

I would not use the environ function. There is an API function I feel is more reliable at

http://www.mvps.org/access/api/api0009.htm.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MikeFL]

I’ve installed a Module below and named it fOSCompName that PHV (MIS) suggested and it works just fine by calling it from within my Query Field with GPCN: GetPCName().

Public Function getPCname() As String
getPCname = Environ("ComputerName")
End Function

I’ve also installed 2 other Modules, one which was suggested by dhookom (Programmer) below and another one I found on the same website from the

http://www.mvps.org/access/api/api0009.htm

&

http://www.mvps.org/access/api/api0008.htm

website and I can’t seam to make either one work in my Query. Can someone explain to me how I can call them from with in my Query to see how they work?

Name: fOSMachineName

Option Compare Database
'
' Retrieve the current ComputerName.
'
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
    "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
    lngLen = 16
    strCompName = String$(lngLen, 0)
    lngX = apiGetComputerName(strCompName, lngLen)
    If lngX <> 0 Then
        fOSMachineName = Left$(strCompName, lngLen)
    Else
        fOSMachineName = ""
    End If
End Function
'******************** Code End **************************

Name: fOSUserName

Option Compare Database
'
' Retrieve the UserName with which the user is logged into
' the network.
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

 

[dhookom]

can’t seam to make either one work" isn't very descriptive. What is your SQL view and what were your results?

I just copied the code from your posting into a new, blank module and tried a query like:

SELECT fOSMachineName() AS CompName, tblMyTable.*
FROM tblMyTable;

It worked as expected. Your module name must not be the name of a function or procedure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MikeFL]

Duane:

Thank you for the quick response! You’re correct, the code did work after I tried it again and found out that my test table did not contain data and that seams to be why the code did not return the expected response. After I added some data it working just fine!

The SQL view(s) I finally used are below:

Name: fOSUserName

SELECT [TA-Master_Prg_Data].Contact_Name, fOSUserName() AS Expr2
FROM [TA-Master_Prg_Data];

Name: fOSMachineName

SELECT [TA-Master_Prg_Data].Contact_Name, fOSMachineName() AS Expr3
FROM [TA-Master_Prg_Data];

Thanks Duane MS Access MVP!