Microsoft Access Database Security Seminar Tutorial

Student Interaction: Access Database Security Seminar

Richard on 1/1/2009: 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

�David Sterner on 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?

Richard Rost on 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).

�David Sterner on 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?

Richard Rost on 11/19/2009: Hmmm... interesting. Have you tried just a comma?

Dave Sterner on 11/20/2009: No a comma doesn't work - doesn't that just set it to the next property to the right?

Richard Rost on 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.

Dave Sterner on 11/20/2009: Got it, thanks.

�Monty on 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.

Paula�Stroud on 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.

Robert�Fleming on 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.

�David on 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.

David on 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. :)

�David on 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.

David on 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.

�Dave Sterner on 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).

�David on 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.

Robert�Fleming on 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." :)

�Dave Sterner on 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

Add Your Questions or Comments