How to Return the Computer Name with Access VBA
Question details
The user needs to retrieve and record the computer name whenever a system is accessed via an Access database, requiring a VBA solution usable within an Access query.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Recording user access logs in a database query or table.
- Observed behavior
- Needs a custom VBA function that fetches the computer name because environment variables cannot be called directly within an Access query expression.
Ensure you have enabled Macros and have access to the VBA editor (ALT + F11) in your Access database before adding custom functions.
Create a Custom VBA Function using Environ
Since environment variables cannot be called directly in a table or query, you must wrap the Environ function inside a custom VBA module.
The standard method for retrieving system environment details in VBA is using the Environ function. By placing this inside a public function, it becomes accessible to all queries within your Access database.
Press ALT + F11 while your Access database is open to launch the Visual Basic for Applications editor.
From the top menu, click on 'Insert' and select 'Module' to create a blank script window.
Type the following code into the module: Function ComputerName() As String ComputerName = Environ("computername") End Function
Save your VBA module and return to the main Access window. Open your query in Design View, and create a new field by typing: TheName: ComputerName()
Use the WScript.Network Object for Better Security
If you are concerned about users spoofing environment variables, use the WScript.Network object to fetch the actual computer name from the network.
Switch to WPS Office for Powerful Data Management
While WPS Office does not include a direct relational database replacement for Microsoft Access, it offers an incredibly robust, free alternative for Excel, Word, and PowerPoint. If you manage extensive data primarily through spreadsheets, WPS Spreadsheets provides advanced VBA and macro support to handle complex automation without the heavy overhead of Microsoft Office.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package for your operating system.
- 2. Install the Software: Run the installer and follow the quick setup wizard to install the suite in minutes.
- 3. Open Your Spreadsheets: Launch WPS Spreadsheets and open your existing data files (.xlsx, .csv) to begin utilizing advanced VBA macros instantly.
Frequently Asked Questions
Why can't I use Environ directly in an Access query?
Microsoft Access restricts the direct execution of certain VBA environment functions within query expressions or table default values for security and parsing reasons. You must wrap the command in a public VBA function first so the query engine can evaluate it.
How do I log the current Windows user instead of the computer name?
You can create an identical VBA function but use Environ("USERNAME") to retrieve the currently logged-in Windows username. Alternatively, CreateObject("WScript.Network").UserName will provide the same result more securely.
Does this VBA code work in Excel or WPS Spreadsheets?
Yes, both the Environ function and the WScript.Network object are standard VBA components and can be utilized in Excel and WPS Spreadsheets VBA modules to fetch system information.
