We have an Access database at work that various employees add records to, and there is a field for them to put their names after they enter a record. The idea of course was to know who had entered the data. Is there a way though to have the database pull the network login of whoever is entering the data and populate that field itself? For a variety of reasons switching to a different format for the database isn’t really an option, so if there’s not a way to do this in Access I’m kind of stuck right now with the current method of having them manually enter their names.
Depends a bit on how the access database is working right now. If users are entering data directly into the tables, or using the basic forms support to insert new records, then I’m not aware of a feature that will do this.
However, if you’re comfortable using VBA code to trigger the insert, then you can grab the windows username with a bit of code like this one. (I assume that the windows username and the network logon you’re talking about are probably the same thing.)
Option Compare Database
Option Explicit
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
fosUserName returns the value
Hope that helps. You might need to put all of this in a VBA module for the private declare thingee to work. (That’s a reference to a windows API function.)
It’s probably a bit more vba than I’m used to but I’ll take a whack at it. My VBA experience is pretty limited, but this looks promising. Thanks!
A bit of clarification is required; are they logging into the machine on which the database resides, in order to enter the data, or are they logging into their own machines and entering the data that is then finding its way to some shared back-end?
chrisk’s solution is really nice (I mean it), but I think it will have to be run on the user’s machine - in the front-end data entry application (assuming there is one).
Please pardon me if I’m stating or overlooking the obvious.
I hadn’t thought of that…currently the database resides on a network share, so the users aren’t actually logging into the machine where it resides. So does that mean I’m out of luck?
Are the users opening the database directly from the network share? If so, you probably don’t want to do it that way in any case; you would instead do it like this:
-The tables reside in a database on the shared volume; this database only contains the tables.
-The users all have their own copies of another database that contains all the user interface elements and code - this database only contains linked tables (OK, plus maybe a few local tables for temporary stuff like order entry etc) - linked to the tables in the back-end database on the network.
They are opening it from the network share. If I do it like you said, with linked tables from the local machine (I think there are only two users now anyway), will the vba code given above still work?
Yes; from the point of view of your code and forms, it doesn’t much matter whether the tables are in the same database or if they’re just links to tables in another database - the whole thing is more or less transparent.
Are the users logging into their own machines with the same login, or is this something that is ONLY required to access the network share??
If they’re logging into the machines that they’re using with this username, then I don’t think it matters whether they’re accessing the database file directly over the network or accessing a local ‘front end’ file that talks to the database.
In any event, there’s a relatively easy way to test… create a button on a form in the database that uses the above code snippet to display the username. “msgbox fOSuserName” will do that, once you’ve got the rest of that code working.
Then, if that much works, you can work on integrating that value into the actual database tables.
if it doesn’t… maybe you could try mangetout’s frontend/backend idea, but instead of using the windows API code, their front ends are just programmed to insert a specific constant value. (Let them know that they can’t switch their program files though! 
)
The other advantage of this is that the users don’t have to go anywhere near the back-end database (and so they’re less likely to delete it or something like that).
The users are logging into their own machines, not just onto the network share.
Okay, any progress update??
Unfortunately not…as so often happens, I had to put it on the back burner because another emergency came up at work that required immediate attention. I’m going to try very hard to get to this today, but it may take until tomorrow.
thanks
[
Although that will work, it’s a rather complicated method. If your users are entering data via a form, then there is an easy way to get their network logon to populate in a field. Simply right click on the field in design mode, choose properties, and on the data tab enter the following for the default value property: Environ(“username”)
Or if you’re building a front end application, you might as well go the whole hog and build user-level function into it anyway; it will come in useful later on when you want certain users to have more privileges than others.
I tried Chrisk’s solution, and it worked fine. Of course that was before I saw the (apparently simpler) solution at the end of this thread. I’m going to give that a try now as well…thanks for all the help!