Environ("ComputerName") (1 Viewer)

[Malcolm17]

Hey All,

Can Environ('ComputerName') go in to a SQL Statement?

I'm looking to do:

DoCmd.RunSQL "INSERT INTO tbl_Net_UserLog(UserName, Action, Computer) SELECT Forms!frmBackground!txtUserName, 'User Logged Off', Environ('ComputerName')"

Thanks,

Malcolm

 

[Gasman]

I believe you would need to concatenate the controls/variables into the sql string.

 

[jdraw]

Try this code:

' ----------------------------------------------------------------
' Procedure Name: ListEnvironValues
' Purpose: Routine to list the Environ intrinsic values
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 08-Mar-21
' ----------------------------------------------------------------
Sub ListEnvironValues()
Dim i As Integer
On Error Resume Next
For i = 0 To 50
    Debug.Print i, Environ(i)
Next i
End Sub

I use this

Function BasePath() As String
    Dim p
    p = Environ("LOCALAPPDATA")
    BasePath = p
End Function

 

[Malcolm17]

Thank you, (I'm not really sure what you meant by that)

I did a wee workaround:

On my Background form I created a textbox called txtComputer and On Load of that form I added the line

Me.txtComputer.Value = Environ("ComputerName")

from there just added the link to the textbox, so I ended up with

DoCmd.RunSQL "INSERT INTO tbl_Net_UserLog(UserName, Action, Computer) SELECT Forms!frmBackground!txtUserName, 'User Logged Off', Forms!frmBackground!txtComputer"

I'm not sure how professional this was, but it did the trick for me

Thank you,

Malcolm

 

[jdraw]

(I'm not really sure what you meant by that)

My intent was to show
-how to get a list of all the Environ values, and
- how to use an Environ value in vba

Glad you have it sorted out.

 

[Pat Hartman]

No, you cannot put this into SQL. SQL does not support Access functions. You can probably create a UDF that calls the function and returns the variable and call the UDF in the SQL statement.

 

[isladogs]

As Pat has suggested, this is possible using a user defined function (UDF). Similarly for user name:

Function GetComputerName()
    GetComputerName = Environ("ComputerName")
End Function

Function GetUserName()
    GetUserName = Environ("UserName")
End Function

You can then use those functions in your query. For example

SELECT *, GetComputerName() AS ComputerName, GetUserName() AS UserName, Now() AS CurrentDateTime
FROM YourTableName;

If preferred you can use TempVars instead of UDFs in your query