Access 2007-2013
Access 2000-2003
Seminars 		Tips & Tricks
Access Forum
Course Index Insider Circle 		Collapse Menubar
 
Tutorials   News   Tips   Templates   Forums   Help   Logon   Order  
 

News      User Comments     History     Notify Me

1/1/2009
Access Database Security Seminar
 
 Learn how to properly secure your Access databases. Control and monitor everything your users can do in the database. Manage the complete flow of data into your database.

- 1. Preview of topics covered
- 2. A step-by-step Lesson Plan
- 3. Complete Walk Thru of the completed database

Permanent Link
Course Link: Access Database Security Seminar
Keywords: access database security seminar
Post Reply

Locked fields Comment from James Gray @ Fri 5/3
Richard,

I left you a question earlier today about updating of locked fields from code.  I have since tried it in my ambulance database and found that the fields get updated without issue even though they are locked...interesting. Thought I would let you know before you waste any time in answering.



Reply from Richard Rost:

Too late. :)

When in doubt... try it.
Show Just This Thread        Post Reply
Locked fields Comment from James Gray @ Fri 5/3
Richard,
If I remember correctly the On Current event for this form locks all fields including the activity dates for opened, serviced, etc.  My question is: Can these fields be changed or assigned values through the code while they are locked?  If not shouldn't our code unlock them first?  I think that in the above example, these fields are unlocked because of you being an administrator when they would normally be locked.  Is my logic flawed?

Reply from Richard Rost:

The LOCKED property only applies to direct user input into that field. If a field is set to LOCKED, then your VBA code WILL still be able to manipulate that value.
Show Just This Thread        Post Reply
Database Templates Comment from Willem Els @ 4/3/2013
Hi Richard

Where can I find the student template for this seminar?(Access Security )

Reply from Richard Rost:

I know most people skip over the INTRODUCTION videos, but that's where this information is usually presented. All of the database files are HERE.
Show Just This Thread        Post Reply
Not adding the variables Comment from Natassja Mozart @ 4/1/2013
Hi Richard, could you tell me if this would have worked:
- Not adding the variables
- Use an IF for "Is user in group 1 or 2" and an ELSE IF for "Is user in group 4" (and adding appropriate add/edit/delete permissions)
- Putting an ELSE in with the False condititions at the end


Reply from Richard Rost:

Sounds like it could work. Best way to find out is to give it a try. :)
Show Just This Thread        Post Reply
Expired Password Comment from Natassja M @ 4/1/2013
This would be great to see! Or a "forgot password" button for self sufficiency?
Show Just This Thread        Post Reply
New security features in 2013 Comment from Bruce Reynolds @ 3/27/2013
Richard,
Are there any new security features which are now part of Access 2013, which did not exist before? Also, have any existing security features been decremented or removed which existed in Access 2007 and/or Access 2010 which are no longer covered in Access 2013?

Thank you,
Bruce

Reply from Richard Rost:

To the best of my knowledge there haven't been any changes to security in 2013. The MAJOR updates in 2013 have to deal with Web Apps, which I will cover in a future seminar. I'm not going to cover them in the regular Access classes because I'm not sold on them yet. They're still in their infancy and I'm not ready to endorse them yet. But everything that you learned about 2010 security in my Security Seminar is still valid.

Show Just This Thread        Post Reply
User Defined Type Error Comment from @ 3/26/2013
Hi Richard-
I figured it out - it was the dao library reference that I had to select.  

I do have another question.  After you make it an accde file and push it out to users, you keep the 'unlocked' version for updating and making changes to?  Then when you are ready to publish again you lock that new version and push it out and so on?

I am also wondering your opinion on having 20ish people access one front end file on the network or e-mail out a copy to each of them to put in their individual drive to use?  Are there any pros/cons that I should consider?  My main concern is record locking and having someone sit in the database all day.  I tried coding in and idle timer but I couldn't get that to work so I am leaning toward each of them their own copy.

Thank you.

Lisa

Reply from Richard Rost:

Glad you figured it out.

YES! Keep your ACCDB file. If you don't, then you won't be able to make changes and send out an update. You CANNOT make changes to an ACCDE (encrypted) file.

As long as you program the front end to LINK to tables on a back end that everyone has access to (like //SERVER/DataFolder/BackEnd.ACCDB) then you can just email everyone updates and tell them to drop the front end file on the desktops, replacing the old one. I've done that before and it works just fine.
Show Just This Thread        Post Reply
User Defined Type Error Comment from @ 3/26/2013
The first line is highlighted but it does jump to the dim db as database line also.  I have reviewed all of the lessons but did not use the other stuff because I didn't need that level of security.  

Is it where you call out the database with the path and file name for the back end?  I did add that in and it didn't help so I took it back out.

Reply from Richard Rost:

No, you have to make sure your reference to DAO is above your reference to ADO.
Show Just This Thread        Post Reply
User Defined Type Error Comment from Lisa Theodor @ 3/25/2013
Lesson 36 - I am getting a compile - user defined type not defined error.

***NOTE***:  I did not need the advanced security of disabling and hiding the windows.  I am wondering if something was set then that this is trying to reference and I don't have it.  My database has a logon and hidden buttons but not disable/enable keys for links, db windows or the bypass keys.  I have checked syntax several times and I cannot figure it out.

Here is my code - first line is highlighted yellow when I debug:
Public Sub LogIt(Description As String, Optional Notes As String = "")
    
    Dim ProgramLeaderID As Long
    Dim db As Database
    Dim rs As Recordset
    
    ' on error resume next
    ProgramLeaderID = Nz(Forms!mainmenu!txtProgramLeaderID)
    Set db = CurrentDb()
    Set rs = db.Openrecordset("log", dbopendynaset)
    rs.AddNew
    rs!ProgramLeaderID = ProgramLeaderID
    rs!Description = Description
    If Notes <> "" Then rs!Notes = Notes
    rs.update
    rs.Close
    Set rs = Nothing
    Set db = Nothing

What am I missing?

Thanks.
Lisa



Reply from Richard Rost:

Which line is highlighted yellow? If it's the "Dim db as Database" line then you're probably missing your DAO reference... covered in an earlier lesson.
Show Just This Thread        Post Reply
Security Questions Comment from Lisa Theodor @ 3/13/2013
Hello.

Very thorough and helpful tutorial but I do have a couple of questions.

When you need to update a form or add forms or reports AFTER you have encrypted your front end, what is the best way to get the new objects out to the user into the front end?  

Do you open it exclusive and make the changes there?

Do you create them on your back up and copy them into the encrypted front end?

Is it okay to have the back up on your hard drive instead of on your network (private) drive.  It runs much slower via the network and that is why I am asking.

With a split database - is it more efficient to have the front end in a secured folder for the users or have a copy of the front end on each of their individual work stations?

Thank you.

Lisa



Reply from Richard Rost:

You'll have to distribute a new front-end file to the end user. You could program up something to do it automatically or just send them the file and tell them where to put it. You can't make changes to it once it's encrypted.

Sure, you could backup your database on your local hard drive, but if your drive physically FAILS then you lose both copies. If you want to do this, at least have two physical drives in your PC, or use an external USB drive or something.

I prefer to keep a copy of the front-end on each user's PC. It works faster for them... but if you can get away with keeping it in a network share and performance isn't too bad, then that's easier to maintain.
Show Just This Thread        Post Reply
Send bulk email Comment from jerry freeman @ 3/13/2013
Richard: I have a form that uses a combobox to select a teacher, then on the subreport, all students to that teacher are displayed.  I want to be able to send an email to all email addresses in the student subform, any suggestions?

Reply from Richard Rost:

You will need to loop through all of those students with a recordset and send to them individually or put their addresses in a single string. I'll be covering this in my upcoming Email with Access Seminar. Stay tuned.
Show Just This Thread        Post Reply
Comment from Scott @ 1/25/2013
Hi Richard. I am doing this seminar again, and still wrestling with a problem that we have talked about at length in the past.

My problem is that I cannot get the thing to link to the back end automatically without it prompting for the password. I have tried I-Dont-Know-How-Many fixes, none have worked.

I can, however make it work if I click the button to Create links (even programatically with VBA. If I do this, it works every time, if I take that step out, it fails every time.

I also had a hard time trying to track down the problem, because it kept making me close the database and re-open in exclusive mode. I thought this had something to do with my original problem.

I think I was wrong. It seems that if you are linked to the back end, it will not allow you to make structural changes to the front end. Things like design view, vba code, macros, etc. are off limits unless you close and re-open in exclusive mode.

Now this is fairly confusing to me because, you really dont have to close and re-open, you just have to kill the links. Am I right about this?

Thanks Scott
Show Just This Thread        Post Reply
Access Security Database Comment from Hank Mark Weier @ 1/10/2013
Hallo Richard, I've just downloaded the Access security Database, but am not able to use it, is this because I'm using Access 2010 or simply doing something wrong? Thanks in Advance.

Reply from Richard Rost:

Did you download the ACCDE or ACCDB version? One of them is LOCKED. Make sure you get the right one.
Show Just This Thread        Post Reply
Leaving in Mistakes Comment from A Lucas @ 12/17/2012
Hi Richard & Crew.
RE: Security Seminar
Thank you for leaving in your mistakes,
(and being wise enough to do so).
ALL of the thing that I am good at (English not my best),
are curtesy of the huge amount of mistakes that I have made learning them.
Not only do you teach us how to problem solve.
I find sometimes it is much more important to know the things not too do than the things too do.
I have played with Access for a few years now and came up with some very
Ingenuous ways around my lack off ability.
The SHIFT startup got me though.
That was worth every cent.
THANK YOU
Lucasade2000@hotmail.com


Reply from Richard Rost:

I'm glad you enjoyed the lessons. I've always believed in leaving in mistakes that I, myself, make (or have made in the past) while building databases.
Show Just This Thread        Post Reply
lock only the combo box Comment from Dana M @ 11/27/2012
Found solution (I think!!!)
Private Sub Form_Current()

    If IsNull(LuClient) Then
    LuClient.Locked = False
    
    Else
    
    LuClient.Locked = True
    
    End If

End Sub

It appears to be working just fine.

Reply from Richard Rost:

Looks good!
Show Just This Thread        Post Reply
already has linked tables Comment from Dana Michaels @ 11/23/2012
quick question - what if the database in question already has linked tables - how will this affect them?

Reply from Richard Rost:

You would probably want to UNLINK those tables first, apply your security, and then relink them. I'd need to know more about your specific situation to tell you more.
Show Just This Thread        Post Reply
lock only the combo box Comment from Dana Michaels @ 11/23/2012
I spoke too soon. Me Allow Edits works great but it not only locks the Customer Combo (LuClient)it locks all the data on the form. Short of changing my whole database which has been running Registration for the past three years -  I tried adding a check box and using the on click Event(in current) that would allow edits but didn't work - I see in the next chapter you are unlocking fields on the form so logically speaking I should be able to lock only the combo box.  

Reply from Richard Rost:

Yes... you can lock individual fields. You don't have to lock down the entire form.
Show Just This Thread        Post Reply
solution is so sweet and simple Comment from Dana Michaels @ 11/23/2012
After all the complicated gyrations I went through trying to work this problem out - your solution is so sweet and simple.  Please don't ever retire!!!!!  thank you so much.

Reply from Richard Rost:

Oh... I'll be doing this for at LEAST the next 20 years... :)
Show Just This Thread        Post Reply
Disable Input Comment from Dana Michaels @ 11/21/2012
HELP!!!!   I desperately want to disable the Customer drop down from changes after the order data has been completed.  For Example - the sales person types in the order - moves to a new record at this point I want to disallow any changes to the form chosen Client by sloppy data entry.


Reply from Richard Rost:

In the form's OnCurrent event you can deal with what happens when you move from record to record (or load a form). You can say something like:

If IsNull(CustomerID) then
'no record yet
Me.AllowEdits = TRUE
Else
'record exists
Me.AllowEdits = FALSE
End If

This will effectively prevent anyone from EDITING a record that already has data in it, but won't stop them from adding a new one.

I'm actually going to be making a TIP video on this concept soon.
Show Just This Thread        Post Reply
oldvalue not working reserved word Comment from Lilly @ 9/24/2012
Help!!! .Oldvalue method does not work
error msg: Method or data member not found

What did i do wrong? Please help!

S = Me.Mid.Oldvalue & vbnewline
s = s & Me.Study.oldvalue & vbnewline
logit "Changed Scorecard", s

Reply from Richard Rost:

First, you need to use a ! not a . to refer to a field on a form:

Forms!FieldName

or Me!FieldName

Next, MID is a reserved word. It's used in text string functions. You'll either have to change it (which I would recommend) or change your code to:

Me![Mid].OldValue

You can't use a reserved word like Mid, Left, Right, Date, Time, Now, etc. for a field name. If you do, you always have to remember to enclose it inside of [brackets] in your code, macros, and queries.
Show Just This Thread        Post Reply
Comment from Rose @ 9/20/2012
In lessen 5 you talk about Group Permissions to be able to open the form and Lessen 10 allows them to add, delete and/or edit records in that Group, but they can see other records of the same group.  I need the group that have Permission to open the form and open where the UserID can view and work on only their records. I need help to add the view or filter where the current UserID() is inUserGroup and can view and work on only their record.
Show Just This Thread        Post Reply
Unrecognized database format Comment from Hilary Dillah @ 9/14/2012
Hi Richard, I have one problem now.:-) My office pc cannot open the database and close back the access program after I clik ok at the message "Unrecognised database format". During initial programming I have no problem opening the database at office pc. If I not mistaken, it's only happened after I put in some security features from the seminars. I'm using Access 2007 at the office and my home pc has Access 2010. The strange thing is, I have no problem at all opening this database at home. Thanks..

Reply from Richard Rost:

Perhaps your database is corrupted a little bit. Access 2010 is a little more forgiving than 2007. Try a compact and repair and see if that helps.
Show Just This Thread        Post Reply
Expired Password Add On Comment from Steven S @ 7/27/2012
I think that would be great to have an addendum to cover this topic!
Show Just This Thread        Post Reply
Expired Password Add-On Comment from Deon Riley @ 7/25/2012
Hi Richard,

Thanks for the reply and let's hope our Access community goes for this as an addendum.

Kindest regards,

Deon

Reply from Richard Rost:

It's on my list.
Show Just This Thread        Post Reply
Expired Password Comment from Deon  Riley @ 7/23/2012
Hi Richard, I have used the security seminar to great effect in building a database for our organisation's risk management department.
My question is this: are you able to show me what's required to change the security seminar’s logon password code to include code that will allow users to change their password every three months. In other words, the code has auto password expiry notification that a user needs to change their password three days prior to the expiry date, two days and then finally if the user hasn't changed their password, a message pops up to state - "Your password has expired! Please change your password now."
Is this possible to do with Access? Perhaps this could be an addendum to the security seminar or an additional lesson to purchase like the “Split Database – surprise seminar.”  
I look forward to hearing from you. Cheers for now, Deon


Reply from Richard Rost:

Sure, this would make a cool add-on. You would basically just have to add an ExpirationDate field to the user table that goes with their password. Then add some logic to the login function to check that date. Make a password change form that handles the rest. If there's more interest, I'd be happy to make an addendum to the seminar.
Show Just This Thread        Post Reply
Problems with database Comment from Scott Adkins @ 7/7/2012
Richard, I have some screwy stuff going on. first, I put the admin menu in and I have double checked the code. Most of the time, I cannot get the admin form to open. Once in a while, I can change from design view to form view, but I am not sure why it will not open up all the time.

Second, when importing tables, I call createlinks from the button and destroy links from the button and it works fine. However, when I try it from the code, it prompts for a password on each table. Any thoughts??

Reply from Richard Rost:

This is one of those things that is very difficult to diagnose without seeing the database. Try putting the admin form in a new, blank database and see if it works there. It could be a problem in your database.

As far as the password prompt goes, I'm not sure... is it ACCESS or WINDOWS prompting you for the password?
Show Just This Thread        Post Reply
Function Parameters Comment from will powers @ 6/26/2012
can you explain the difference between putting a string in the title verse lower in the code.

ex: Public Function IsUserInGroup(GroupID as Long) as Boolean

verse

ex: Public Function IsUserInGroup as Boolean

Dim GroupID as Long

- Will


Reply from Richard Rost:

The first example is when you're SENDING A PARAMETER VALUE to your function. Someone else is calling IsUserInGroup and telling them what GroupID to check.

The second example doesn't get any outside information. It DIMs a LOCAL VARIABLE that is only used inside the function.
Show Just This Thread        Post Reply
Comment from  Sue @ 5/8/2012
Nevermind - somehow my data disappeared - so it was really getting an error that was then fixed in the next step - which I hadn't seen yet!  Sorry, new to the videos!!!

Reply from Richard Rost:

Ha ha. That happens. It's been so long since I recorded that video that I didn't even remember. :)
Show Just This Thread        Post Reply
Comment from  Sue @ 5/8/2012
HELP! I have typed the code as follows (7:35 in tutorial) but when I test it with the correct pairing of Username and Password, I get "Runtime Error 94" Invalid use of Null and the debug takes me to the X= line of code.  

I am using Access 2010 - should that make a difference?

My code reads:
Private Sub Login_Click()
    If IsNull(txtUsername) Then
        MsgBox "Invalid UserName"
        Exit Sub
    End If
    If IsNull(txtPassword) Then
        MsgBox "Invalid Password"
        Exit Sub
    End If

Dim X As Long
X = DLookup("UserID", "UserT", "UserName='" & txtUsername & "'AND Password='" & txtPassword & "'")

MsgBox X
DoCmd.Close acForm, "LoginF"

End Sub

Reply from Richard Rost:

I used 2007 in the class, and the two are pretty much identical, but DLOOKUP can sometimes throw up errors like this. Just enclose it inside of some error handling:

Dim X As Long
X = ""
on error resume next
X = DLookup("UserID", "UserT", "UserName='" & txtUsername & "'AND Password='" & txtPassword & "'")
on error goto 0

That will turn off the error message, and X will remain "" if nothing was looked up.
Show Just This Thread        Post Reply
Comment from  Deon @ 12/7/2011
Hi Richard,

I have downloaded this Seminar to my PC using your Amicron player programme. However, the lessons only display a frozen screen at startup of the visual and the audio track plays as it should. Why is this? Because - if I view the same seminar from the Online Theatre, it is fine. Has the Amicron software programme changes for the newer version of Access? Also, I'm experiencing the same problem with the free Access 2010 Beginner upgrade as well. But, all the 100, 200 and 300 Access series, which I originally loaded is working fine.

Can you advise me on my next course of action.

As always, I am absolutely blown away by your instruction and an enjoying the classes tremendously.

I look forward to hearing from you.

Take care and best regards,

Deon

Reply from Richard Rost:

That's very strange. I'll contact you via email to help troubleshoot the problem.
Show Just This Thread        Post Reply
Comment from  Sissoko @ 12/7/2011
I want to be sure, that playing around, with th table connection, is secure, specialy if 5 to 6 people are using the back end.
But i think the code can help to creat a logF only in a database, then after login the code in VB will open the encrypted front end with a stable connections.
Show Just This Thread        Post Reply
Comment from  Hector @ 10/28/2011
If you are using SQL on your sever the database is secure right?


Reply from Richard Rost:

As long as the security is set up right on your SQL Server then, yes, your tables are secure.
Show Just This Thread        Post Reply
Comment from Dylan Kiner @ 8/23/2011
I love this seminar.  This is awesome and the instructor is clear, precise and right to the point.  Thank you!

As I complete this seminar I'm sure I'll have more questions but right now what I would like to know is how to push a refresh from the server or main database.  In the seminar I saw how I could refresh different objects after making chages so updates are immediately visible.  My issue is that I would need any updates and or changes to be immediately visible for all users who are logged in. Is there a way to have the database automatically refresh every couple of minutes or is there a better way to handle this? Please advise. Thanks - Dylan
Show Just This Thread        Post Reply
Comment from WILL  @ 7/20/2011
In Lesson #4 instead of having a list of groups: manager, sales rep, service tech, etc. is it possible to have all positions showing on a subform and use check boxes to select what group the user is in?

- Will
Show Just This Thread        Post Reply
Comment from  Derek @ 5/18/2011
Could we further the security of the DB by creating a hash of the users password and storing that in the table instead of a clear-text password field? We could then retrieve the hash from the table and compare that hash value to a hash value that was attempted.

I guess a problem I see with this is that the "hacker" could change their password (just as if it was a clear text) to a new password in which they created the hash for. Of course this would be providing that they know the hash algorithm that your using....

Reply from Richard Rost:

Yes, and yes. :) As I explain in the seminar, this is only "good enough" security for most offices/users. Anybody who really knows Access well can get around it... but you could encrypt the passwords in the table using a hash or some other method. Applying something simple and straightforward like a checksum value to the password would allow you to check for tampering at the table level. Again, like you said, this assumes that nobody knows your algorithm. Honestly, if you need THAT much security, however, you're better off setting up a database server.
Show Just This Thread        Post Reply
Comment from Cathleen Tarnawskicr @ 5/14/2011
When I get to 'store value to this field' I can't see the SalesRepID option.  I put it in the customerT table.  time index is 05:29

Reply from Richard Rost:

You might need to SAVE the customer table, close down the customer FORM and then reopen it. Sometimes it doesn't refresh until the form is closed and reopened. Strange, I know.
Show Just This Thread        Post Reply
Comment from Rhys Thomas @ 4/29/2011
2007

Reply from Richard Rost:

It should definitely be in there. The .OldValue property has been around since at least Access 2000 (that I know of). What kind of a control type are you using it with?
Show Just This Thread        Post Reply
Comment from Rhys Thomas @ 4/29/2011
Hi Richard

This might sound completely daft but .old value does not exist in my vba code for some reason.  The only thing I can find is .value

Do you know what is happening?

Reply from Richard Rost:

What version of Access are you using?
Show Just This Thread        Post Reply
Comment from Ghassan Baghdadi @ 4/27/2011
Swear To GOD you are a Programing God. Thx

Reply from Richard Rost:

Thank you. :)
Show Just This Thread        Post Reply
Comment from Kerwyn Vincent @ 4/5/2011
Richard,

In the Global Module, the file path Global Const BE_DATABASE = "C:\DATABASE.accdb" enables the connection to the existing db.

When using an Access front-end with SQL back-end, what changes do we make to this Global Constant?

Been doing some research on connection strings with this regard.

Reply from Richard Rost:

It requires a lot more than just pointing to a file. I briefly mention it in one of the videos, but there are a couple of things you have to do. I plan on covering this in an upcoming lesson - connecting your Access database to an SQL SERVER back end.
Show Just This Thread        Post Reply
Comment from  tran @ 4/4/2011
I tried to made the same (timeindex 3:47)
I made the query with 8 fields and tried to bring this query to the list box.  I don't know why I only have 7 fields to show to the List box. Please advise.  Thanks,
Show Just This Thread        Post Reply
Comment from  Tran @ 3/31/2011
Could you please explain why we can pass the name of the combo box "SalesRepCombo" to the function CurrentUserID () ?

Thanks,
Show Just This Thread        Post Reply
Comment from  Tran @ 3/31/2011
Why the calendar is shown up when you clicked on the Opened text box (time index: 0.55) ? I did not see you build the calendar ? Thanks,
Show Just This Thread        Post Reply
Comment from  Harry @ 3/1/2011
I'm wondering if one could use a Case Statement when locking/unlocking fields depending on Group membership.  I'm thinking if there is a way to list the groupID's in such a way that the broadest permission appear first, then admins (for eg) would not see Sales related formatting (for eg).
Perhaps an additional unique integer field  could ID each group with the lower number being the highest permission. Then a dmin could find the number.
Side note: use the convention of incrementing Groups by 5 so there is space for inserting more qroups within the existing hiarchy in the future.
Does the idea have merit?

Reply from Richard Rost:

Hmmmm.... are you thinking something along the lines of nested permissions? Like ADMINS have access to everything that SALES people have plus some? That could work. This would be a little tougher to implement, but sure... it could be done. I would nest the permissions inside of a function that determines permissions, like:

Function IsSalesRep(UserID)
  If IsAdmin(UserID) then
     IsSalesRep = TRUE
  End If
  ...
End Function

Is this what you're thinking of?
Show Just This Thread        Post Reply
Comment from  Harry @ 2/28/2011
Why do you use a list box rather than sub form on CustomerF?

Reply from Richard Rost:

Personal preference, mostly. I find that list boxes are easier to control. If you just want to DISPLAY data, and don't necessarily need to edit the data right there, a list box is better. Both would work, however.
Show Just This Thread        Post Reply
Comment from Pierre Anthony Garraud @ 2/23/2011
I forgot to note the time index of  the video. My question concern the 29.locking database 4. the time index is 4.42. How can I create a link and destroy it by ADO instead of dao
Show Just This Thread        Post Reply
Comment from Pierre Anthony Garraud @ 2/23/2011
How can i create the link and destroy it with Ado instead of DAO
Show Just This Thread        Post Reply
Windows API for Logon Name Comment from Alex Hedley @ 12/1/2010
Hi Richard, and fellow students,

To obtain the Username I usually use
Environ("UserName")

(Another option - Application.UserName)
Show Just This Thread        Post Reply
Comment from  Dave Sterner @ 11/19/2010
Lesson 36, 14 min 13 secs: If you have a subform, can you include the subform fields in your main form log string so you have all of your changes in one place?  I tried this:

S = S & "Subform Field: " & Subform!SubformField.oldvalue & " to " & Subform!SubformField & vbNewLine, but I get an error message.

Reply from Richard Rost:

Try adding a Me! in front of the Subform!SubformField.oldvalue. If that doesn't work, what's the EXACT error message?
Show Just This Thread        Post Reply
Comment from  Amrik @ 10/31/2010
Hi Richard, enjoying your course Seminars, and the one on Access Security the typo for 'erviced in lesson 19 @ 01:31 for..StatusCombo = 2 'erviced.. was corrected further down in lesson 22 @ 09:05.
As you mention in your videos, it is best to follow the lessons before starting again to test the DB yourself. Some good tips learnt.
Show Just This Thread        Post Reply
Windows API for Logon Name Comment from Richard Rost @ 10/28/2010
One of my students, Julius, emailed to remind me that there is a Windows API call you can use to pull up the logon name of the person currently logged in to WINDOWS using this function:

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

This would allow you to use it as an extra level of security to make sure that the person accessing the database is at least the person logged on to Windows... or just even use it to set the default value for your database logon form's username field.

I don't have a whole lot of time right now to give you a full explanation of its use, but if anyone is interested, post here and perhaps I'll add this as a lesson (or at least more description of how it works).
Show Just This Thread        Post Reply
Comment from Mark Heness @ 10/22/2010
Richard
I am watching your security seminar and it is very effective and a great training tool. I woudl appreciate you including an addendum for the clock use etc.
Show Just This Thread        Post Reply
Comment from Mubeezi Micah @ 8/14/2010
Dear Richard,

At 1.46, you demonstrate how to set a start up form and also lock the database at 12.00. I did this and it was working perfect on over 10 different computers. However, yesterday, i was shocked to near death! I opened a database on a friend's computer and everything was disabled! The database would open with no LogInF and all the navigation pane objects were visible! I went to Access Options>>Current database and i reaslised the Display form property was reset to blank. I set it back to LogInF and reopened the database. Again it do not appear. I disabled the bypass key as sshown in 12.00 and reopened the databse again but alas. THis is the first time i have seen this and i find it very strange. Why is this so? Anything i can do to go around this?

Thank you for clarifying

MICAH
Show Just This Thread        Post Reply
Comment from Mubeezi Micah @ 6/28/2010
Dear Richard,

I used the code shown at time index 12:57 in my database. I realised that even new records (like when i register a new patient) were being logged. This could probably congest my log table. Below is the code i used to go around it.

If PtName.OldValue <> "" Then
    'On Error Resume Next
LogIt "PatientID: " & PatientID
LogIt "Changed Patient Name: " & PtName.OldValue & " To " & PtName
End If

The code seems to work fine. Could there be another shorter way?

How can i share with you my database? I am about to finish it and i will appreciate if you as my teacher can look at it. I am certain that you will like it. Why? Because i listen to videos where "Customers" and "Products" are examples and the database i have built counts Patients and which services they receive e.g. whether they had an HIV test or not, whether they came for review etc. Learning access has reempasised what my teachers used to tell me "Don't cram. Just learn the principle of how to do it"

Best regsrds,

MICAH
Show Just This Thread        Post Reply
Lesson 26 continued Comment from David Sterner @ 5/18/2010
Follow up on my previous submittal.  I realized that it was not showing old files that I had deleted but was listing my existing tables and a replicate renamed with a 1 at the end. These replicates show a correct linkage but of course my front end is not using them but is trying to unsuccessfully access my original tables, still linked to the old directory.
Show Just This Thread        Post Reply
Spit Database Problem - Lesson 26 Comment from David Sterner @ 5/18/2010
I am revisiting so I apologize if you covered this somewhere in the seminar. In anticipation of transferring the split database to my client's server I played around with the "what if" of relinking the tables db to the front-end db, given that there will be different file locations on my clients server than on my computer. After splitting and linking, as in Lesson 26, I moved my table db to a different directory and took the same steps to re-link and it linked some but not all of the tables and I started seeing tables listed in the navigation pane that I had long since deleted.  For the unlinked tables, the front-end was still trying to find them in the original link directory.  I was able to link them one by one using the linked tables manager but this seems like a pain.
Show Just This Thread        Post Reply
Comment from Mubeezi Micah @ 5/10/2010
Dear Richard,
I dont know whether i missed this in this seminar somehow. Suppose one of the users wanted to change their password or username. Or if a manager left and we hired a new one. How do i creat a log in for him/her?

Thank you,

MICAH

Reply from Richard Rost:

I don't believe we ever actually made a form in class to change usernames and passwords. I just assume that you (the database administrator) would manually set that up by opening the user table. Now, if this is something that happens a lot in your organization, or you want to give someone else (perhaps a manager) the ability to edit user accounts, then yes, you would have to set up a form for this. It's not hard.

Now, giving individual users the ability to change their own passwords would also be something you could set up a form for (password change form). Have them enter their current password and a new password, and then make the appropriate changes with a little update query SQL or a recordset. All of the techniques you'd need are covered in the seminar... it's just about putting it together.
Show Just This Thread        Post Reply
Comment from  Dave Sterner @ 2/23/2010
Regarding the Me.Undo to revert back to the original record entry, what if you are working with a subform in a form.  How do you undo the record of the subform?

Reply from Richard Rost:

Me is just a reference to the current form, so if your FOCUS is currently IN the subform, you would still say: Me.Undo.

If you're in the PARENT form and you want to cancel the editing of the record in the subform, you could say:

MySubform.Form.Undo

However, as soon as you leave the record in the subform and click on the parent form, the data is saved, so you can't use this method. It would only work if you were programmatically modifying the data in the subform from VBA code in the parent. Make sense?

Remember, you can also refer to forms by their FULL names:

One form: Forms!FormName!FieldName
Subforms: Forms!ParentForm!SubForm.Form!FieldName

To access their Undo methods:

One form: Forms!FormName.Undo
Subforms: Forms!ParentForm!SubForm.Form.Undo
Show Just This Thread        Post Reply
Comment from Robert Fleming @ 2/23/2010
Your answer of 2/9/10 is bending the  truth. It is nice to say that your copy of the database works find. But in fact if you look at your  database  after completion of lesson 20 it does not work. You had to stand on your head and spit nickels to get the code to work properly in Lesson 25. A decent answer would have been that after completion of lesson 25 the code problems   are repaired.  You know Richard when I ask a question I would appreciate an honest answer not a brush off one. Am I asking for too much?

Reply from Richard Rost:

Robert, you are absolutely correct. Perhaps I didn't completely understand your question the first time. I apologize. It's not that I was trying to brush you off. I didn't realize that you were inquiring about a problem that I fixed in a later lesson. That's my mistake.

I would say that 95% of the time, the problems that my students have with their databases are because they didn't type something in correctly or follow a procedure exactly as I demonstrated. I prematurely jumped to that conclusion with your question. I'm sorry.

Sometimes after spending three or four hours answering questions, I have a tendency to jump to the "it's the student's mistake" answer. Instead of taking the time to fully refresh my memory on that lesson (which I should have) I gave you the EASY (for me) advice to just recheck your code. I know that the database worked perfectly for me AT THE END OF CLASS, but yes, there are often those little problems that creep in that I fix from lesson to lesson. I didn't realize at the time, that that was what you were asking about.

Again, my sincere apologies.

Now, with all of that aside (and me feeling like a jerk) that's one of the reasons I try to remember to say at the beginning of ALL of my Access classes that you should watch ALL of the lessons through once before building anything. This way you can see the direction that the course is going to go in, and what will be covered. You'll see where there are going to be problems before you try to build the database yourself.

In fact, one of the guys who writes my handbooks for me has yelled at me SEVERAL times. "I'm gonna kill you, Rick!" he said the one time because in one of my lessons I spent 15 minutes building something and then said, "and that's how you DON'T want to do it." I then proceeded to show the right way... but he already typed all that up (and usually in the handbooks I tell the guys not to bother showing the WRONG solutions).

I feel that showing mistakes is VERY important for learning how to build databases, but you don't necessarily need to make those mistakes yourself in order to know what to avoid. You don't need to catch a disease to know it's bad, right? :)

Anyhow, again, I'm very sorry for the brevity of my previous answer. Had I realized what your question was about, and remembered that this was a problem I later solved, I would have given you an even SHORTER response the first time:

"Keep watching the lessons."

:)
Show Just This Thread        Post Reply
Comment from  David @ 2/12/2010
Richard,

I have several like odbc database and want to link to each using the same forms. Can I'm trying to change on the fly the linked odbc tables in the same manner as the describe in the course.  However I'm not seeing the linked odbc tables after the split.  Any suggestions?

Reply from Richard Rost:

Dynamically linking to external ODBC sources requires some additional programming. I don't know the code off-hand, but when I get some time I'll try to find it for you. If you're in a hurry, try a Google search. It's not terribly complicated, but you have to do it right for it to work.
Show Just This Thread        Post Reply
Comment from  Dave Sterner @ 2/11/2010
Richard: I keep going back and trying things with this fantastic seminar.  One thing that I was going to mention that I thought was missing was a menu button to allow the current user to change his/her password, username and e-mail address.

So I decided to design one myself using a form based on a query of the UserT with CurrentID () as a criterion.  However, I realized it would be nice to have an exit button where you can ask the user if they want to save their changes upon exiting the form.  My question is: what if they don't want to same their changes? I don't know how to recover the previous data once they have changed the record.  Is this possible?

Thanks

Reply from Richard Rost:

It's possible, but it's a bit of a pain. You would need to make your own "close" button because otherwise whenever a user moves to a different record or closes a form, Access saves the data to the table. In your close button, ask the user if they're sure they want to save changes. If so, just close the form. If they say NO, then you can invoke the Me.Undo method which cancels any updates they've made (while the current record is still dirty).
Show Just This Thread        Post Reply
Changing the extension accde Comment from David @ 2/11/2010
Richard,
In the process of trying to figure out my last post (MS 2003 Vs MS2007),  I changed the encrypted Service.accde to a .mdb, and I noticed the tables could be imported into a blank database. However the forms & modules were shadowed out.  Is there anyway around this or is this another limitation to MS Access?

And for my previous post...
I think I should have saved the Service.accdb file to Access 2003 first before encrypting the database in 2007.  When trying to open the database in 2003 the MainMenuF only shows the service and exit buttons aftering logon.  The table defs were not linked.  Please advise because my users use both 2003 and 2007.

Reply from Richard Rost:

Once you ENCRYPT your database file (make an MDE or ACCDE) you effectively LOCK the forms, reports, macros, and modules. This is designed so you can give your database to others and not risk them getting hold of your "design" code. You'll need to use your original ACCDB or MDB file.
Show Just This Thread        Post Reply
Comment from  David @ 2/11/2010
Richard,

What are the limits if any if some users are still using Access2003 and others are using  Access 2007 for the same database?

Reply from Richard Rost:

You're going to have to keep the database in the Access 2003 (MDB) format in order to use the same file with both sets of users, so people using Access 2007 won't be able to have any of the new features available in 2007. The exception would be if you have a SPLIT database and you keep the tables in an MDB file, but upgrade the Access 2007 front-end to an ACCDB file for just those users. I don't recommend this, however, because then you have to support two different database files.
Show Just This Thread        Post Reply
Comment from David @ 2/9/2010
Richard,

I inserted the following line of code at the end docmd.close acform, "LoginF"
Endif
DoCmd.RunCommand acCmdWindowHide
End Sub
It hides the navigation pane.  My question is will it effect anything anywhere else?  Ref: Video 31 Time 10:50

Reply from Richard Rost:

Well, that should work OK as far as I can see. You don't need to have the navigation pane visible for the database to work (and as you've seen in the seminar, I don't leave it open for my end-users). Bottom line: try it and see. :)
Show Just This Thread        Post Reply
Comment from  David @ 2/9/2010
Richard,
Good course I really enjoyed it and learned alot.  I would like to see a followup course on how to change the color of the buttons on the main menu when the status changes.

Thanks

Reply from Richard Rost:

David, changing the foreground color of a button is something that Access doesn't do well. You can change the FONT (text) color, or use an ActiveX button control (which I don't recommend). Personally what I do is put little red rectangle controls around, but behind my buttons. Then, if I want to highlight a button, I just make that rectangle visible and it highlights the button. If you really want to see how to do this, let me know.
Show Just This Thread        Post Reply
Comment from Robert Fleming @ 1/26/2010
How do I get the main menu to open and only show to buttons that the logon user is allowed to see.
It appears that any buttons that are hidden does not appear on the main menu, regardless of the sign in group that you are in, in order to get the proper main menu you need to hit the refresh button

Reply from Richard Rost:

Robert, if this is the case, something's not right with your code. The OnOpen event for your form (or the code in your button - whichever way you chose to do it) should hide/show the proper buttons depending on the security you have set up. Recheck your code.
Show Just This Thread        Post Reply
Comment from Paula Stroud @ 1/6/2010
Richard, at Time Index of 4:30. It appears an "End If" is missing. Unless I missed it. There are 2 places it could go ... at the end of the If Msgbox() statement, or at the end of the Sub.  Doesn't work at the end of the Sub.  
Show Just This Thread        Post Reply
Comment from  Monty @ 12/18/2009
On lesson 26 in Access Database Security Seminar, when I click on my database and then hold the shift key down and then double click back on it, it doesn't bypass the startup options. I have Access 2007 version. This is a good thing I guess. I just thought to mention it to see if it's happened to anyone else.
Show Just This Thread        Post Reply
E-mail, CC separator Comment from Richard Rost @ 11/20/2009
If you want two CC addresses, then they should both be inside the same string. For example:

"sendtoAddress@somewhere.com", "cc1Address@xyz.com;cc2Address@abc.com", "bccAddress@amicron.com"

Keep in mind, however, that multiple addresses like this INSIDE a string might cause Outlook to open the SendTo window and cause you to hit Send. Minor inconvenience.
Show Just This Thread        Post Reply
E-mail, CC separator Comment from Dave Sterner @ 11/20/2009
No a comma doesn't work - doesn't that just set it to the next property to the right?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 11/19/2009
Hmmm... interesting. Have you tried just a comma?
Show Just This Thread        Post Reply
Comment from  David Sterner @ 11/18/2009
Hi Richard: For lesson 24 I want send an e-mail notice and I want to cc two individuals also so I created string variables E, CC1 and CC2. Access help says that CC1 and CC2 should be separated with a ";".  But I get an expected: end of statement error. Is there a different separator I should use?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 11/6/2009
David, in 2003 and earlier, the EASIEST way to sort a combo box is to make a QUERY that's sorted, and then base the combo box on the QUERY. Otherwise you have to manually add the "ORDER BY" clause to the SQL (or invoke the query builder inside the combo box, which is a pain too).
Show Just This Thread        Post Reply
Comment from  David Sterner @ 11/3/2009
In Access 2007 when creating a list or combo box you can select the sorting order as part of the wizard.  In 2003 what is the easiest way - write code in the row source?
Show Just This Thread        Post Reply
Comment from Richard @ 11/1/2009
Now that you're finished with this seminar, make sure to check out my ACCESS SPLIT DATABASE SECURITY SEMINAR and ACCESS DATA ENCRYPTION SEMINAR for even MORE ways to protect your Access databases.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Shown
  Your Email:  NOT Shown
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+7:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be. As always, I promise to never give away your personal information to anyone else, ever.

 

 

You may want to read these articles from the 599CD News:

5/14/2013Access Tip: Many-to-Many Relationships
5/14/2013Access Expert 5 Handbook Ready
5/10/2013Access Tip: Email Using Outlook
5/10/2013Microsoft Access Expert 5
5/2/2013Access Amort Handbook
4/29/2013Microsoft Access Expert 4
4/28/2013Access Tip: Toggle Button Colors
4/27/2013Access Expert 4
4/23/2013New Access Tip: Conditional Format Expression
4/15/2013New Access Tip: Grid Lines v. Dots
   
Learn
 
Microsoft Accessindex
Microsoft Excelindex
Microsoft Wordindex
Microsoft Windowsindex
Microsoft PowerPointindex
Adobe Photoshopindex
Visual Basicindex
Active Server Pagesindex
Online Seminars
More...
Customers
 
 Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
 Info
 
 Latest News
New Releases
User Forums
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
 Help
 
 Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
 About Us
 
 Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
 Order
 
 Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
 Contact
 
 Live Chat
General Contact Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey