I am writing a db which will have 4 or 5 users. On each record, I need a field which will identify who created it. It seemed to me that the simplest approach would be to set user-level security, so each user would log-in and the field could just reference who it is, rather than having the user select their identity from a drop-down list on every record. So I set the security with the wizard and the result was disastrous - back to square 1.

Then I got to thinking that if I could log-in users to the db, then what I would really like is to put an avatar of each user on the form during their session. I reckon an Iif statement on an OLE field.

Other than to meet these requirements, there is no need for security on the db - no-one's going to steal/ spoof/ corrupt data.

It has been suggested that I go down the API route rather than use access security, so what I want to know now is are both my requirements (identify field and avatar) possible? Should I then pursue API to do this, or there anything else (hopefully) simpler?

thanks in advance for any help.

Both of those requirements are possible without any APIs or IF statements. All you'd need is a USER table with a field with an OLE object for the avatar and the username and password fields.

Personally, I'd skip the avatar.

Ah yes! Then the table would be linked by a one-to-many relationship (many on the record side) and displayed in a subform on the main form.

Why wouldn't you use an avatar, it only needs 4 or 5 little images?

Thanks so much for the lateral thought.

what you want to do is possible without using the network logon. however the network logon is 'there' already. you don't need to request the user authenticates themselves. you don't allow users to select who they are from a list box.

if you allow users to select who they are from a drop down list box, aside from people being "honest" there is nothing stopping anybody from pretending to be someone else. in an ideal world everyone is honest,, but sadly there are varying degrees of honesty and openness in this world

you can use the current user function but that tells you what userid someone used to start Access, which no one can guarantee is the actual person. so form what I understand currentuser doesn't meet your requirement to identify who added the record. network logons are far harder to replicate.

if you allow editing then storing a userid against a row becomes a problem, because presumably you will want to also know who edited the record (and that may not be the same as who created the record).

if you want to store an image/avatar then the you need to define your own table/storage mechanism. its doesn't need to be a table you coudl store it in a text file, but a table will be easier

ultimately its a trade off
the nework logon is proven code, readily available and works. its requires a simple function call to a common module, it requires little testing, little development time. using current user requires you to do somehting with the workgroup security, it requires you to maintain the workgroup file. storing an avatar requires more design effort.its a trade off.

for me:if you 'need' the avatar then by all means roll your own userid. if you don't then use the network logon.

I'd suggest you look at the design of the system to see if an avatar is needed or if its just window dressing. I've seen avatars on online forums and the like, but never in an Access application as usually most people within the organisation know who the other people using the app are.

I avoid OLE object data types like the plague. They tend to blow the file size out too much and cause more than their fair share of corruption issues. The more images the worse it is. It is much better to store the PATH to an image in a db than the image itself.

You're most welcome

Security is not an issue with this db. No-one is going to pretend to be anyone else. It really is not a problem.

The avatar, is, indeed 'window dressing'. I like the idea and I think others will too. Will link it rather than import it.

Healdem, when you talk about 'current user' function, is that part of the Access user-level security?

Thanks for ongoing dialogue.

currentuser() is an inbuilt function in Access. it returns the userid of the person who logged in to the current instance of Access. if you are not using your own workgroup / security functions (effectively you are using an empty workgroup file) then it returns "admin" if you are using your own securitry functions then it returns the userid the user declared on opening the copy of access. Access always runs with workgroup security, you cnat turn it off, if you are not explicitly using your own then Access logs on using the userid of Admin.. its one of the reasons why people using Access apps often have problems when there installation is changed as Access on installation reverts back to the default workgroup file

I appreciate your comments about security not being an issue, I understand your comments that no one is going to pretend to be anyone else. that indeed may well be the case right now with the current people you expect to use the application.

however you are wanting to store the userid against specific records. presumably there is a reason for that, some specific business reason as to why you want to associate a record with a user id. if that is the case then unless you can guarantee that your app will never have users who won't meddle with the system, who won't spoof a userid, who won't do things outside the way you envisage then you have some pretty unique users.

if something can be easily circumvented, or its not reliable/dependable then your system is compromised. the column has little or no value in the db. from what you are saying at present.. its not about security its about data integrity, data value. there is abusiness reason why you want to identify who added the record.. fair enough. but unless you think through the reasons for that requirement and how to protect, or have confidence that the requirement is met depends.

currentuser() is better than requesting users select a user from a drop down box
but currentuser doesn't stop multiple people using the same app with the same userid. admittedly the network logon doesn't do that (but your network policies should however)

buy recording the userid in a table you've captured the userid, from whatever mechanism you choose, of the person at the time the record was selected.. that can be easily overwritten using an update query or in a form. heck depending on how you design your data entry form it may even be overwritten as part of the edit process.

as said before its not about security its about traceability. if the data is form an unknown or unreliable source then I'd argue it has little or no value in the db. if its there for a purpose then it should be dependable