Access 2007-2013
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order  
 
 
 

Microsoft Access Security Seminar
Proper Security for your Access Databases

 
This seminar will teach you how to properly secure your Microsoft Access databases. But that's not all. You will also learn how to build proper security into the workflow of your database so that you can control every aspect of a user's experience, and monitor their work as well in a database log.

You will learn how to properly secure your Access databases to control who can log in, who can edit data, and who can make changes to the database itself. But that's only the beginning. You will learn how to build a database where you can control every step in the "work flow" process of data entry, and create a system log so you can see who is doing what.

Click here for a video showing what's covered in this seminar:

AccessLearningZone.com
Click to Play - Seminar Preview
 
There are two more videos below showing
additional details. Scroll down to find them.

 


 

 
Seminars - Access Security
Description: Learn how to properly secure an Access database and control the "work flow" of information with user and group permissions.
Versions: I use Access 2007 in the videos, however the lessons are valid for all versions of Access back to Access 2000 and work just fine with Access 2010. I will show any differences between 2007 and 2003. The lessons on the User-Level Security Wizard are specific to Access 2003.
Pre-Requisites: Access 101 - 104 a must
Access 201 - 207 highly recommended
Access 301 - 313 very helpful
Running Time: 7 hours, 4 minutes
Cost: $169

 

This seminar has several main goals. You will learn how to:

1. Create user and group security inside your database
2. Manage the "work flow" of your database
3. Control who has access to which database objects
4. Send email notices from inside your database
5. Create a detailed system log to track users
6. Encrypt and secure your database files
 

You will begin by creating a user information table (with username, password, and other data) and a secure logon form. This will allow us to control who can log on to the database.

 

Next we'll set up a Group form so we can assign users to different groups, like SalesRep, ServiceTech, Manager, Admin, etc.

 

In the next lesson, we'll define a "work flow" for our database. This will control how information is entered by our users. For example, our database will be for service work orders. Our work flow will consist of a sales rep entering a work order, a service technician performing the work, a manager reviewing the details, the sales rep follows up with the customer, and then the manager closes the work order.

 

You will be able to control which users will be able to enter data into which fields. For example, only service technicians can enter data into the "service performed" field, but not the "service requested" field, which is the job of the sales rep.

 

We will control each step of the work flow process with buttons that signify that a user is done with his work, and the work order is ready for the next person. Here you can see a "service call completed" button that will then send this work order to the manager for review. This work order will then be locked out so the service tech can't edit it any further.

 

 

You will learn how to send Email notices from inside the database when needed. This is great if you have employees that don't check the database all the time, so as to say, "your assistance is required."

 

The database will log every step in the work flow procedure. You will be able to see who did what, and when.

 

We will also create a system log table, so we can log everything that a user does: what time he logs on, what data he adds, what records he changes, etc. You name it, we can log it.

 

You will learn how to build your own internal database security. For example, do you want sales reps to be able to edit other sales reps' customers? Do you want service techs to be able to see customer data if they're not working on that customer's work order? Who can edit, delete, or add new customer records? What information should be required for new customers? Some of these are things you just can't do with standard Access User-Level Security.

 

You will be able to hide or show buttons and forms based on a user's security level. For example, the menu that a system administrator might see...

 

...will be completely different from what a simple service technician sees. This will help to control who can do what in your database.

 

We will spend a lot of time learning how to lock down your database and secure it from intruders. We will split the database into front-end and back-end files, encrypt the database with a password, turn off the Navigation Pane (database window) to keep people from poking around in your objects, set a Startup Form, and disable the Bypass Key so people can't go to the Navigation Pane directly when the database starts up.

 

You will learn how to dynamically link to tables in your back-end database using VBA code to overcome some security problems.

 

We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.

 

For our Access 2003 users, we will spend some time going over the User Level Security Wizard. This wizard will secure an Access 2003 database and provide "OK" security if all you want is to keep Joe from Accounting from poking around in form design, and seeing data he's not supposed to. It's not hack-proof, however, but it does provide "quick and dirty" security in a pinch. We'll set up users, passwords, groups, and permissions.

 

We will encrypt the front-end database into an ACCDE file (MDE file for older versions of Access). This will effectively secure the form, report, and module design capabilities and prevent people from viewing your database in design mode.

 

 

This seminar is perfect for anyone who wants to learn how to secure an Access database, prevent someone else from accessing parts of the database they shouldn't, assign permissions so you can control who does what, and create a work flow strategy in your database.

This seminar is long (over seven hours) but it's broken up into easily managed lessons of about 10 minutes each. You can sit down, watch a lesson, review the material, test the code out yourself, and experiment. Do a little bit each day. It's long, but it's comprehensive - you won't miss a single step as I've recorded everything from start to finish.

Click here for another video showing my step-by-step lesson plan guide to what's covered in each lesson of this seminar in detail.

AccessLearningZone.com
Click to Play - Step-By-Step Lesson Plan


Click here for a video walk-thru of exactly what this database does, and see how the flow of information going into the database can be tightly controlled from sales rep to service tech to manager.

AccessLearningZone.com
Click to Play - Database Walkthru

 

All of the sample database files for this seminar are available on my Web site (instructions on where to download them are in the course videos). They are available in Access 2007 and 2000 formats.

This seminar is available to view online in the Amicron Theater. You can click here to watch the first lesson absolutely free.

Of course, if you have any questions about whether or not this seminar is for you, please contact me.
 

 

Access Security Seminar Outline

00. Introduction (8:00)

01. Database Setup, Login Form (12:23)
Create a New Database
Trusted Locations
Set Overlapping Windows
Setup UserT Table
Access User Level Security
Why ULS is Not Very Secure
"Good Enough" Security
Create Login Form
Popup & Modal Forms

02. Login Form Code (11:42)
Docmd.Quit
Check for blank username/password
DLOOKUP Username and Password
NZ Function

03. Main Menu (9:43)
Exit Database Button
UserID, Username Fields on Main Menu

04. User Groups (14:35)
GroupT Table
Group to User Cross Reference Table
Junction Table
UserF Form
GroupF Subform
Adding Users to Groups

05. Checking Group Permissions (12:35)
Open User Form Button
Code to Check if User in Group
Is the User an Admin
Defining Your Own Function
Public Function
Return a Boolean
Global Modules

06. Service Process (9:06)
Define Service Processes
Create CustomerT Table
CustomerF Form
StatusT Track Work Order Status
ServiceT Detail on Service Call

07. Service Form (10:40)
Build Service Form
Create Combo Boxes

08. Service Listbox (11:23)
Listbox Service for Current Customer
OnCurrent Event
Modify SQL for Listbox to Format
Format Function in SQL

09. Sales Rep Security 1 (10:51)
Security: Who can Browse Customers
Assigning Customers to Sales Reps
Query to Show Only Sales Reps
Sales Rep Combo Box for Customers

10. Sales Rep Security 2 (10:43)
AllowAdditions, AllowDeletions, AllowEdits
Setting Properties in VB Code
Change Properties Based on User Group
Change Properties Based on SalesRepID

11. Locking Fields on Form (8:56)
Locking All Fields on a Form
For Each Control Loop
RGB Color Function

12. Unlocking Needed Fields (7:08)
Unlock Fields for Sales Reps
Unlock Fields for Service Techs

13. Create Service Order 1 (10:08)
Create New Service Order Button
Enable/Disable the Button
Get Default Value from Another Form

14. Create Service Order 2 (14:01)
Requery Service Listbox OnGotFocus
Open a Specific Service Order
Set a Default Sales Rep
Security Opening Service Orders

15. Assign to Service Tech (10:27)
MsgBox vbYesNoCancel
Lock Fields Once Assigned to Tech

16. Unassigned Service List (12:47)
UnassignedServiceQ Query
service List Form

17. Accepting Service Call (7:33)
Accept Service Call Button
Perform Service

18. Unassigned or My Calls (7:58)
Show Unassigned Service Calls
Show My Service Calls Button
Show ALL Service Calls
Dynamic SQL in RowSource of ListBox

19. Service Completed (11:32)
Mark Service Completed
Show Status on Service List
Show New, Open Service Calls

20. Hide Buttons on Main Menu (14:30)
Show/Hide User Form Button
Show/Hide Browse Customers Button
Open Service List Show Different Things
Private v. Public Form Functions

21. Fixing Some Problems (8:01)
Fix Problem with Sales Reps
Fix Missing Button Problem
Force Service Details to be Entered

22. Manager Review (12:29)
Manager Review 1 Button
Show Service Calls to be Reviewed
Assign For Followup Button
Hiding All Buttons on ServiceF

23. Followup, Closing Call (11:38)
Sales Rep Followup Completed Button
Manager Closed Button

24. Sending Email Notices (13:00)
Sending Email using Microsoft Outlook
Docmd.SendObject

25. Admin User Combo (11:51)
Make the UserID a Combo Box
Quick Change to Another User

26. Locking the Database 1 (9:43)
Split the Database
Front End v. Back End Databases
Encrypt Database with Password
Open Exclusive
Linked Table Manager
Get External Data
Create Links to Backend Tables

27. Locking the Database 2 (13:28)
Backup Your Database
Turn off Navigation Pane
Turn off Database Window
Show Startup Form - LoginF
Application Title
Display Navigation Pane
Allow Full Menus
Allow Shortcut Menus
Hold SHIFT Key to Bypass Security
VBA Code to Shut Off Bypass Key
AllowBypassKey
Database CreateProperty
Properties Collection

28. Locking the Database 3 (10:09)
Import Linked Tables Bypasses Security
Create Admin Menu

29. Locking the Database 4 (13:55)
Global Const
Dynamically Link to Tables
Destroy Links to Tables

30. Locking the Database 5 (14:45)
Create a Recordset
OpenDatabase
OpenRecordset
Read User Info Before Linking Tables

31. Locking the Database 6 (17:09)
OnClose Event
Terminate Table Links
The Navigation Pane Shows
The Database Window Shows
Link Tables using TableDefs

32. Creating an ACCDE File (9:11)
Make ACCDE File
Make MDB File
Source Code (Design) is Gone
VB Debug Compile to Find Errors

33. AC2003 User Level Security 1 (10:36)
User Level Security Wizard Part 1

34. AC2003 User Level Security 2 (12:43)
User Level Security Wizard Part 2
Create a Workgroup File
Securing Objects
Selecting Security Groups
Creating User Accounts
Creating an Unencrypted Backup
Logging on with User Permissions
User and Group Permissions
Add or Delete Users
Add or Delete User Permissions

35. Other Database Properties (8:40)
Show All Database Security Properties
VBA Code to Change Properties
StartupShowDBWindow
StartupShowStatusBar
AllowBuiltinToolbars
AllowFullMenus
AllowShortcutMenus
AllowDefaultShortcutMenus
AllowToolBarChanges
AllowBreakIntoCode
AllowSpecialKeys
AllowBypassKey

36. Database Log (17:02)
Track Employees with a Database Log
Create LogT Table
Create LogIt Function
Track User Logons
Track Data Changes
OldValue Property

37. Review (2:59)
Review Topics



MAIN CONCEPTS COVERED

Creating your own security inside the database
- Making a user table
- Logon form
- User groups with permissions

Controlling the "work flow" in your database
- Sales rep enters a work order
- Service tech performs the work
- Manager approves it
- Sales rep follows up
- Control who can enter data into what fields
- Buttons to move to the next step in the process
- Locking access to data once that step is completed
- Sending an Email notice with Outlook for the "next step"
- Creating a system log to watch everything your users do

Determining which users can access what forms, fields, data
- Sales reps can't edit each others customers
- Service techs can't see customer data
- Users can't bypass the "work flow"
- Managers can edit everything
- Admins can supervise and change access rights
- Who can browse customers?
- Who can see service reports?
- Showing or hiding buttons/forms based on access rights
- Preventing record edits, deletions, or additions
- Forcing users to enter required data

Properly Securing Your Database
- Split the database into Front-End and Back-End files
- Encrypt Back-End database with a password
- Turn off Navigation Pane, full menus, toolbars, etc.
- Disable the Bypass Key for startups
- Dynamically link to tables using VBA code on database startup
- Destroy those links when database closes
- Compile your database into an ACCDE (MDE) file
- Access 2003 User Level Security Wizard
- Working with Access 2003 users, groups, and permissions
- Advanced database properties in VBA such as AllowBreakIntoCode

Programming Topics
- Using DLOOKUP to lookup usernames & passwords
- Many-to-many relationships with junction tables (users/groups)
- Locking fields on a form based on user rights
- For Each control loops
- Creating your own global modules with public functions
- Dynamic SQL rowsource for listboxes
- AfterUpdate, OnCurrent, OnGotFocus events
- Use the RGB function to set color in VBA
- Getting a value from a form using Forms!Formname!Field notation
- Create and edit databases in VBA
- Define global constants
- Create and work with RecordSets to read user info
- Use a recordset to read from an external database with a password

 


 

 
 

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



Mubeezi Micah on 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.

David Sterner on 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.
David Sterner on 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.
Mubeezi Micah on 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

Mubeezi Micah on 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

Mark Heness on 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.

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

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

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

Alex Hedley on 12/1/2010: Hi Richard, and fellow students,

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

(Another option - Application.UserName)

Pierre Anthony Garraud on 2/23/2011: How can i create the link and destroy it with Ado instead of DAO
Pierre Anthony Garraud on 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
 Harry on 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.

 Harry on 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?

 Tran on 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,
 Tran on 3/31/2011: Could you please explain why we can pass the name of the combo box "SalesRepCombo" to the function CurrentUserID () ?

Thanks,

 tran on 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,

Kerwyn Vincent on 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.

Ghassan Baghdadi on 4/27/2011: Swear To GOD you are a Programing God. Thx

Reply from Richard Rost:

Thank you. :)

Rhys Thomas on 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?

Rhys Thomas on 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?

Cathleen Tarnawskicr on 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.

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

WILL  on 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

Dylan Kiner on 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

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

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

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

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


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

will powers on 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.

Scott Adkins on 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?

Deon  Riley on 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.

Deon Riley on 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.

Steven S on 7/27/2012: I think that would be great to have an addendum to cover this topic!
Hilary Dillah on 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.

Rose on 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.
Lilly on 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.


Lilly N on 9/24/2012: Richard, thx so much for your help.

Dana Michaels on 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.


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

Dana Michaels on 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.

Dana Michaels on 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.

Dana M on 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!

A Lucas on 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.

Hank Mark Weier on 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.

Scott on 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

jerry freeman on 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.

Lisa Theodor on 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.


Lisa Theodor on 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.

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

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


Bruce Reynolds on 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.


Natassja M on 4/1/2013: This would be great to see! Or a "forgot password" button for self sufficiency?
Natassja Mozart on 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. :)

Willem Els on 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.

James Gray on 5/3/2013: 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.

James Gray on 5/3/2013: 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.

Eleanor Mason on 5/13/2013: Time Index 14:20 Lesson 36
The LogT Table will only show where the company name has changed and the customerID. And they show up on line 40 for the company name change and 41 for the customerID. I changed the First Name, Last Name, Address and Phone. But none of those show up in the LogT. I have checked my Public Sub LogIt and My Before Update String, I have even copied and pasted your String just to make sure it was correct. What could be causing it not to log everything that has been changed. I am using Access 2010

Reply from Richard Rost:

Eleanor, it's impossible for me to tell what's wrong without seeing your database. I know this code works just fine for ALL versions of Access.

Bruce Reynolds on 5/22/2013: Richard,

In my opinion, Microsoft Project is nothing more than a very customized database with some cool scheduling features which comply with the PMBOK. Have any of your customers requested a seminar where the scheduling information in a workflow Access database can be seamlessly exported and/or integrated with a master schedule in Project?

Just curious.
Bruce

Reply from Richard Rost:

Nope. Honestly, I've done VERY little with MS Project. All of the scheduling databases I've built in the past were just 100% Access.

Bruce Reynolds on 5/28/2013: If you inadvertantly destroy your system tables, is there a recovery procedure to restore the system tables?

Reply from Richard Rost:

Yeah, it's called creating a new database and importing your objects from the bad one. :) I don't think there is.

Bruce Reynolds on 5/28/2013: I wonder how much Microsoft charges for Microsoft SQL server? It's gotta' be in the thousands of dollars per seat.


Reply from Richard Rost:

It's not cheap. SQL Server with 10 clients is $2000 (or $2699 direct from Microsoft). You can probably find it cheaper at some discount sites, but it's still going to cost you $1500.

Bruce Reynolds on 5/28/2013: When you set rs = Nothing, are you making rs = 0, or rs = Null?

Reply from Richard Rost:

Neither. You're destroying the object completely and removing all references to it from the system memory. See, when you DIM a variable, Access reserves a certain amount of system RAM to hold that value. This memory is supposed to get cleared when you exit the function you're in, however that's not ALWAYS the case, leading to what's sometimes referred to as "memory leak". Now for your average variables, that's not usually a big problem: a byte here, 2 bytes there... but when you're dealing with objects like a Database or Recordset, they can be pretty big, so you want to make sure to MANUALLY destroy (undeclare) the object by setting it to NOTHING. You don't HAVE to, but it's wise.

Bruce Reynolds on 5/28/2013: This was a VERY comprehensive seminar, and to me represents another Best Value.

Reply from Richard Rost:

Thanks, Bruce. I'm glad you enjoyed it.

While the techniques in THIS seminar teach you how to lock down your database to prevent people from messing with your design, we really didn't cover protecting your DATA from tampering. So, I created a FOLLOWUP seminar to this one which teaches you a technique for securing the data in your BACK-END TABLES without having to use SQL Server. Check it out: ACCESS SPLIT DATABASE SEMINAR.

James Gray on 7/25/2013: Richard,

Is there a way to have the database open such that the Access toolbar and other Access items in the background are hidden? Eg. when you open the database, all you see is the login form and then the Main Menu form after log in? The end user shouldn't really need to see all of the Access developer tools, menus should they?

Reply from Richard Rost:

You still need to have the Access application window open. I saw some code online somewhere a few years back that allowed you to load the database and minimize the App window, but I can't remember where I found it or what it was called. I haven't played with the 2013 Runtime Edition yet (the one I just posted about - I've downloaded it, but I haven't played with it yet) but one of the older versions (2003, I think) allowed you to pretty much disable ALL of those menu items.

Bobby Petty on 8/7/2013: Hi richard,
I have entered the code for lesson 27 but it is not working. Can you help me figure out why? Here is the copy of the code I entered:
Private Sub DisableByPass_Click()

On Error Resume Next
Dim DB As DOA.Database
Dim PR As DOA.Property
Set DB = CurrentDb
Set PR = DB.CreateProperty("AllowBypassKey", dbBoolean, False)
DB.Properties.Append PR
DB.Properties("AllowBypassKey") = False
Set DB = Nothing
MsgBox "ByPass Key Disabled"


End Sub



Reply from Richard Rost:

What's not working? Are you getting an error message? Does DEBUG stop on a particular line? What version of Access are you using?

Colette on 8/11/2013: Great Lessons. I have created login form for users with group permissions. Now, I wonder how to permit end users to change their passwords.

Reply from Richard Rost:

You'd have to set up your own form for that. If you understood all of the other concepts in this class, that shouldn't be too hard for you to do.

Kenneth Breig on 8/23/2013: Yeah i want to see the time clock thing too that sounds interesting
Larisa Kiseleva on 9/11/2013: I am very happy with all topics you covered with this seminar. It helps me a lot. But you miss to show how to do limited acess to the form main navigation with tabs. I did figure out.
Now I need to work on another project to do schedule for technician and sending them email and text messages on their iPhones via gmail account and if possible to do synchronize data with Google calendar? So they can see their schedule on-line.
Can you do another seminar or maybe you have already this topic cover in any of your classes?
Maybe you can help me for additional cost just to do these functions.


Reply from Richard Rost:

I show you how to HIDE the database window which is the same thing as the navigation pane. You don't want your users anywhere hear the navigation pane, so just hide it completely and make your OWN menus using forms - as I show in the lessons. Those forms are the only way your users should be interacting with your database, and you can control exactly who can click on what.

Sending email will be covered in my upcoming Email Seminar which I expect to have ready in a few weeks. You can use email to SMS gateway addresses to send text messages. I'll cover that in class.

Synchronizing with Google Calendar is another beast altogether. I may also cover that in a future seminar as I use Google Calendar myself and would like this feature for my own stuff (the easiest way to get me to make a seminar is when it deals with something that *I* use, ha ha).



Boston S on 9/22/2013: In lesson #3 you created a main menu form that showed the logged in user. How can I add a list of projects to this form so only the logged in user sees only the projects he is related to. In addition, how do you use this value for other forms?

Thanks for the help

Reply from Richard Rost:

Boston, have you finished the seminar yet? Later on I show you how to control what data users can see.

Khalid on 9/25/2013: So I've done pretty much every thing in the seminar except for dynamic table linking and the system log as i can't get my set my DAO refrence
When I head to Tools then Prefrences then selecting Microsoft DAO 3.6 Object Library then click OK, i get an error message that reads "Name conflicts with existing modules, project or object library"
So, I'm wondering if you can figure out how to get the DAO refrenced!
I'm using Access 2010, by the way.
Thanks in advance!

Reply from Richard Rost:

You have something else conflicting with DAO. Remove EVERY other reference that you can and see if that works. The required references shouldn't let you remove them. If worse comes to worst, try using a BLANK NEW DATABASE and import your objects from the old one (tables, forms, etc).

Inderjeet Puaar on 12/3/2013: Hi Richard,
Im getting a run-time error 91 (Object variable or with block variable not set) on the create links vba code. Not sure as to why. Can you have a look please. Heres my screenshot: http://cl.ly/image/061b0M2X3x3x

Inderjeet Puaar on 12/3/2013: Sorry i managed to figure it out the create links error. Simple typo error on my part.
Kevin Robertson on 12/10/2013: Going by your screan shot, you have declared one of your Global Constants as BE PASSWORD but you refer to it later in your code as BE_PASSWORD. Appears to be a simple typo!
MUBEEZI M on 12/10/2013: Dear Puaar,

Try these two options;
1. Set db = ws.openDatabase(BE_DATABASE, False, False, "MS Access;pwd='" & BE_PASSWORD & '"")
Notice the single quotes near the equal sign. This is because the BE_PASSWORD is a string and not a number.

2. When I open the backend, i normally just write like below and it works for me;
Set db = OpenDatabase(BE_DATABASE, False, False, ";pwd=Hello!")

Good luck!

MICAH

Joni Moore on 1/4/2014: What if my users do not have a full version of access so they can't tell Access which folders are trusted for them?
Lorne Nelson on 1/30/2014: Hi Richard,

Have utilized many of these examples you've provided in your security seminar here, but was wondering how to 'disable' the "X" button on Access so that the user is forced to use the "EXIT" button I've put on the main menu form. Users can simply click on the "X" button and LOGIT function does not record that close of Access. I need the user to be forced to use the "EXIT" button only.

Thx,

Lorne

Vannak Hou on 2/4/2014: Richard, I right clicked on the "Exit" button and type in VB DoCmd.Quit. Close, Save. Then Reopen, try to Exit nothing happen. I made it's on the actual button. Am I doing something wrong b/c the form is not exiting?
Vannak Hou on 2/4/2014: 0850 .. this is a pretty course Richard. Since I do not have knowledge of Visual Basic Course. Any suggestions?
Uriel Ramirez on 2/12/2014: I have notice that if you encrypt with password the front end database, you will be prompt for password when trying to link or import tables from it.
Elissa Peacock on 2/23/2014: Richard, I enjoyed your Access Security Seminar very much. I put a button on my MainMenuF form to allow users to change their password. It works. The only problem is that I have to select the right UserID number from the record selector. If there code I can put in my password form to do this automaically based the User who logged in. THANKS!
 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
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
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP