• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 319
  • Last Modified:

Identify who is updating a database MS Access

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.
  • 3
  • 2
1 Solution
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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:

dbfromnewjerseyAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
dbfromnewjerseyAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now