Identify who is updating a database MS Access

Posted on 2012-09-11
Last Modified: 2012-11-19
I didn't really know how to word this question.  Also, if this is something very complicated or impossible to do, then don't worry about.

Here is my situation:

I have a questionnaire database that I will be sending to multiple recipients.  I'm wondering if there is a way to identify who updated the database.  I need to have about 20 people complete this thing, so I'm trying to avoid receiving 20 databases.  I'd rather just send out an email to everyone with the interface of a split database attached, have them answer the questions and be able to identify in the backend table  who answered what.  

So, for example, let's say there are 5 questions to be answered and I send the interface to 2 people. I want to be able to see in the table who answered what.  So, in the table, there will be a record containing the answer to a given question. I'd like to have included in the record, the name or ID or some other identifying piece of information of who answered the question. Thanks.
Question by:dbfromnewjersey
    LVL 84
    You can build a login system, and then "stamp" the records with the users name. Or you can capture the Windows username and use that.

    There is no builtin facility to manage this, however. You'd have to build your own. Allen Browne has a great writeup on creating an audit utility:

    Or you can just "stamp" the record when the user begins to edit. You would do this in the Dirty event, perhaps. Add fields to your table for UserName and LastUpdated, and then do this:

    Me.UserName= YourUserName
    Me.LastUpdate = Now()

    The link to Allen's site has code to get that username:

    Author Comment

    Using the "stamp" method, what is to prevent someone from signing in under someone else's username and entering data?   What I'm trying to do is ensure that the person supposed to be answering the questions is actually the one answering them.  In a situation like this, would I be the one to send out the usernames to the individuals and those usernames would serve as a passwords?
    LVL 84
    What I'm trying to do is ensure that the person supposed to be answering the questions is actually the one answering them.
    Other than physically monitoring the session, you can't ensure this. Even if you send me a username/password that is tied my "identity", I could still get my wife to enter the data for me by giving her that information. Or I could open a session on my machine with the username/pass, and leave to get coffee (without logging off) and someone else could take up the session and enter/modify data.

    In other words, machine based authentication and data security can only go so far, regardless of the platform you're using. If you think that a user would knowingly try to enter data under another login - and if that is important to you or your business - then I would respectfully submit that your troubles are deeping that data auditing.

    Author Comment

    I was expecting a response like that.   Unfortunately, I'm finding out on here that I have spell out my explanations in complete detail instead of leaving certain things to be inferred.

    Of course a user may give his user name or password to someone else.  Of course, a user may leave a session open and someone else sits down and takes over.  That doesn't concern me.  I want to send an email with an interface attached to a recipient and be able to identify that that recipient (or anyone associated with him or access to his equipment) is the one answering the questions.

    As an example, let's say I sent an interface to Manager Joe Smith. He opens it and says to his secretary "come here and sit down at my desk and complete this questionnaire for me."  I couldn't care less that his secretary is actually the one completing the questionnaire. As far as I'm concerned, the email was sent to Joe Smith and therefore Joe Smith answered the questions. And I want to be able to record that Joe Smith answered them.  One thing I can say is that everyone is on a network computer and therefore has to sign into the main system with a user ID. Can't that user ID be picked up and written to a file?   Or maybe the email address the interface was sent to be picked up and use that as an identifier?
    LVL 84

    Accepted Solution

    have spell out my explanations in complete detail instead of leaving certain things to be inferred.
    Umm ... yes? Obviously you'd need to give us the full explanation. We have no idea of your project, your needs or expectations, etc etc. The more information you give us, the more likely we are to help you reach a solution. We're not working with you, and we don't know what you need or want, or your skill level, etc etc.

    With that said: You can certainly pick up the Windows Username, using the API calls from the link I provided you earlier. Here's one simple example:

    Option Compare Database
    Option Explicit

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

    Function UserName() As String
    Dim sUser As String
    Dim lLength As Long
    Dim lRet As Long

    sUser = Space(100)
    lLength = 100

    lRet = GetUserName(sUser, lLength)

    UserName = Trim(Left(sUser, lLength))

    End Function

    Copy that into a new Standard Module (name it something like basUserName), and then call it like this:

    Msgbox UserName

    You could then use that function to write the UserName to the audit fields. How and when you do that depends on exactly what you're trying to audit, but in general you could do this in the Dirty event of the Form.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now