Access 2007-2016
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   Contact Us  

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.

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: $199


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

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

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


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:

"", ";", ""

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.


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"
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?


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:


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,


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,


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


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

(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 () ?


 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,


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


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,


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:


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:


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
'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


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.

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.


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!ProgramLeaderID = ProgramLeaderID
rs!Description = Description
If Notes <> "" Then rs!Notes = Notes
Set rs = Nothing
Set db = Nothing

What am I missing?


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.


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,

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.

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:

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!


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.



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!
Richard Lanoue on 4/18/2014: How do 2010 access encrypt database? it's not in database tools?

Reply from Richard Rost:

In 2013 it's under File > Save As.

John Miller on 5/27/2014: Hello Richard! Hope this finds you well. In the 29th lesson around 04:57 video marker. where you begin to work on the code for the creation and destruction of the table the original code (using the original lines for the docmd.TransferDatabase) works but is slow. When in the code is changed on video 30 around 14:17 the code seems too stop working. (When the 2 line are changed to 4 four lines of code the link does not seem to be working until a ctrl+s is used then the tables shows up. I am running Windows Access 2010 as well as windows*
David Koehler on 6/12/2014: Thank you very much for leaving your mistakes in the videos. A great sign that you are one helluva teacher.

Reply from Richard Rost:

Thanks. I find that people learn from the mistakes of others, and if I don't show you the mistakes that I make, then you'll be bound to make them yourselves.

David Koehler on 6/12/2014: Is there a download sample of this security database that I may get a copy of.

Reply from Richard Rost:

All of the databases available for download are HERE.

Robert Rivera on 6/23/2014: Time index 1:26 or so....Richard, I want to use a check box to turn users on and if I want to totally restrict a user for one reason or another i can do so by checking a box....Thanks

Reply from Richard Rost:

Yep. That's totally possible. Just take that checkbox into account when looking up their permissions.

chanelle west on 7/13/2014: Hi Richard, for the database I have created the main menu has a student button(browse customers)which opens to a list of students (customers), where i can click on a student (customer) and open the student form(customer) to the specific record. The student (customer) form has tabs in it for different data, i have a request tab(service list) to track all of the requests.When i added this code to the overall student (customer) form nothing happens it is still open for all users. I am not sure what is wrong can you help. thanks
Robert Rivera on 7/14/2014: Hi Richard.

Im watching the Access Security Seminar and i was wondering if their is a way to use one log in for 2 main menus. One for users and one for admin. they will both have different buttons but admin will be able to access both.

Thanks for any suggestions

Richard Lanoue on 8/3/2014: I don't have an encrypt password in my ribbon under database tools... I Have Access 2010...

Reply from Richard Rost:

I don't even have 2010 installed anymore, so I can't tell you for sure... but I think it was under your File > Save options, or under Access Options.

Robert Rivera on 9/8/2014: Hi Richard,

I have 20 reps that need assess to their own customers. We have assigned each rep 50 customers to follow-up with. How can we set this up so that they only see the customers that have been assigned to them. First i want them to log in. then after they put the correct log in info in I need a customer list form to open, then they can search customer ect....but if a customer calls in I need them to be able to see that customer form in the even the rep that belongs to that customer that calls in is not available.

Reply from Richard Rost:

Well, I'd set up a user logon system like I show in my SECURITY SEMINAR, which I see you already have.

Once you have that set up, you'll have a USERNAME for each of your reps. Have their Client List form by default show only THEIR clients (perhaps a combo box at the top to limit the list). But, allow them to change that if they need to look up another rep's clients.

simon baynton on 10/3/2014: Richard
I have been playing with the database re the getting tables out of the front end,if i set all my linked tables hidden attribute to true and then try to link to the front end to get the table information the import box comes up blank and you can't get at them, is this just as good as your "On The Fly" method or is there a problem with doing it this way?

Reply from Richard Rost:

Once you hide the table, you generally can't link to it. You'd have to unhide it, link to it, then hide it again.

Frank Fish on 11/3/2014: Hi Richard,
I'm interested in setting a time limit to user passwords as well.

Deon on 11/3/2014: Hi Richard,

I'm just as interested in setting time limits to passwords. Perhaps you could run a short addendum to the Security Seminar to support this. Also, maybe you could combine this with showing us how run the encryption module to scramble/unscramble the password when you run the Recordset in snapshot mode to check the password on the login form of the Security Seminar. Just a thought that would be fab for our Access development.

Best regards,


David Darilek on 11/23/2014: Richard,
I have a user with a single quote in their last name and part of their username (O'Donnell) When testing with their login the Main Menu form doesn't accept the username. What is the best work around for this issue?

Reply from Alex Hedley:

I'd use the REPLACE function to swap a single quote (') with 2 single quotes ('')

i.e. Replace(Username, "'", "''")

Tareq Salloum on 12/22/2014: I have a question and hope to get an answer from you. I have created a database with user names and passwords. Basically, all users can have access to the records (the way I set it up). Is there a quick and dirty way to log the name of the user who changes any field in a record? I don't care what the change is, I just want to know that there was a change made by a user. I have watched your Access Security Seminar and you have showed how to log activities of users for each filed. But is there a way to just check all of the fields of a record? Thanks a lot!

Reply from Alex Hedley:

You could use a combination of the BeforeUpdate event and the .oldValue property to check for changes and log them.

Bonnie Staib on 5/23/2015: Richard, I have just finished working through your outstanding Security Database. In a rating of 5 starts, it gets 10! I do have two questions on the later lessons.
In Lesson 32 re ACCDE file. I am working with Access 2010 which may be why I have had this experience. I had repeated difficulty in making the ACCDE file. Finally, after searching the web for clues, I compiled the database first, then tried to make the ACCDE file and it worked then. Have any other of your users found it necessary to compile before creating the ACCDE file in Access 2010?
Question 2. In Lesson 36, at 9:35 you logon and make a comment that says notice for me I ve got all my full menus because of the db properties in the admin button we created. I see that in the video. But when I logon as Richard 123 my menus are limited and if I show the DBW I still can t work with the forms (or even queries) even if I unlock the database without exiting. I can create links but I still have limited menu and no working ability with queries and forms.
What will work: at this point I unlock the database, exit and return. Whoever next returns no matter what their permission level sees the navigation pane and can access objects and use full menus. When I as an admin open the db, I can work with it and then lock it again. Then the ACCDE file will work fine for others without giving them access to full menus and nav pane.
Does this behavior surprise you? Anyone else have this problem? Thanks so much for such superb teaching! Bonnie

Reply from Alex Hedley:

It's been while since I've done it but I thought compiling the db was covered, maybe this is in another one. You're right though, you need to before it'll work.

Are you unlocking the db then creating the accde as that would leave it unlocked for everyone.

Bonnie S on 5/27/2015: Alex, thank you for your reply to my questions. I appreciated you saying I did need to compile the database before making the ACCDE file. Re my second question: You asked if I was unlocking the database and leaving it open for others. I only unlocked it because I could not replicate the actions that Richard was getting in the video (as Admin signing in to the ACCDE file, and at Lesson 36 @ 9:35, he commented "notice for me I ve got all my full menus because of the db properties in the admin button we created. I could not get that without unlocking the db. However, I would immediately do what I needed to, then relock it. I understand that the normal place to continue development is with an unlocked development copy. I was simply trying to follow the video.

These courses are the best!

Reply from Alex Hedley:

Can you add some logging to the unlock methods and then lock the db, then when you try and unlock it does it log your new messages.

Brian Merrick on 7/31/2015: Does it affect your database if you don't set the field length and leave it 255?

Reply from Alex Hedley:

Maybe years ago but not so much now.
Although if you know it's always going to be a certain length then optimise it by making it smaller.

Brian Merrick on 8/4/2015: How can i create it so that the open call shows up on the Main Menu, until i close it. In order words, i want to have my open calls show up on main menu as a list form until its closed.

Reply from Alex Hedley:

You could add a SubForm which lists them

Brian Merrick on 8/12/2015: I get the error message invalid Logon when i enter the correct password. What am i doing wrong?
Here is my code: Private Sub Command5_Click()

If IsNull(UserName) Then
MsgBox "Invalid username"
Exit Sub
End If
If IsNull(Password) Then
MsgBox "Invalid password"
Exit Sub
End If

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

If X > 0 Then
'We have a valid user
DoCmd.Close acForm, "LoginF"
MsgBox "Invalid Logon"
End If

End Sub

Reply from Alex Hedley:

Try MsgBox Username and MsgBox Password to see they are correct.
Try Debug.Print Nz(DLookup("UserID", "UserT", "Username='USER' AND Password='PASS'")) with your actual username and password copied from the UserT

Brian Merrick on 8/24/2015: I am using Access 2010, when i try that VB code:
If Ctl.ControlType = acTextBox Or Clt.ControlType = acComboBox Then

Error message: Run time error '424':
Object required

Reply from Alex Hedley:

I've just tested it in 2010.
I got the same error if I didn't have the following:

Did you include Dim Ctl As Control
and have

For Each Ctl In Me

Brian Merrick on 8/24/2015: I get this error message when i open the service form:

Compile error:

Method or data member not found"

It points to ServiceRequested and
Private Sub Form_Current() is highlighted.

Reply from Alex Hedley:

Are other Forms opening ok?
Which order are you trying to open?

Brian Merrick on 8/24/2015: Never mind i figured it out

Reply from Alex Hedley:

What was wrong?

Brian Merrick on 8/24/2015: When i go to a blank record in the customer form and double click on the list box which is empty i get a message that says this is not your customer.

Reply from Alex Hedley:

You could add a

If Not IsNull(ServiceListbox) or ServiceListbox <> "" Then
End If

Brian Merrick on 9/1/2015: =[Forms]![ServiceListF]![StatusCombo] Does it matter if the equal symbol goes away after you save it. I get the error message: Enter parameter value


Reply from Alex Hedley:

Are you putting this as the Default Value?

Brian Merrick on 9/4/2015: When i trying to encrypting my backend database i get an error message. Encrypting with a block cipher is incompatible with row level locking. Row Level locking will be ignored. what does this mean.

Reply from Alex Hedley:

Why are you encrypting the backend? You normally encrypt the front end and you can use techniques in Split Db or Data Encryption Seminars for securing the backend. Or move it to MS SQL Server.

Brian M on 9/4/2015: Yes i am.

Reply from Alex Hedley:

Can you check the spelling of your Control and the Field in your Table

Brian on 9/5/2015: Ok I will check.
Ray White on 10/10/2015: How can you make the Password
Case Sensitive ?

Reply from Alex Hedley:

It should be, are you saying the matching isn't working?

Brian Merrick on 11/30/2015: How do i archived the closed files in the database for the closed calls?

Reply from Alex Hedley:

Expert 17 explains the Archive method.

Brian Merrick on 11/30/2015: I get a error message when i log on: Ambiguous name detected

Reply from Alex Hedley:

MS Article

Do you have the same name for a function twice?

Brian Merrick on 12/8/2015: I want to create a query so when i close my calls they don't show up in the listbox. How do i do that?

Reply from Alex Hedley:

Do you have a Flag in your Table for IsClosed, or a Date Field you are filling in when completed.
Just make that part of your WHERE clause when showing records in your Listbox

Anne Burlas on 12/23/2015: The passwords using this code are not case sensitive. What am I missing?

Reply from Alex Hedley:

Which code?

Vannak Hou on 3/22/2016: Richard, you think you're going work on Access 2010 or 2013 Security Access soon?

Reply from Alex Hedley:

Why, the principles from this seminar will work exactly the same as in 2007
Is there something you are wanting added?

Vannak on 3/23/2016: At the end of the video, all the sudden there is an Exit button without any explaination. Took a moment to figure it out. I know the video was made a while though.

Reply from Alex Hedley:

Must have been edited out, just copy and paste the login button and the next video address the code for that button.

Vannak on 3/25/2016: Thanks,Richard. You're awesome
Mohamed Salim on 5/24/2016: Hello Richard
I'm really facing a serious problem with my Access 2016, its legit and new. However, every time I try to drop a dropbox a strange msg pop put which says "the code contains a syntax error, or a Microsoft Access function you need is not available. If the syntax is correct, check the controls wizards subkey or the libraries keys in the Microsoft Access section of the windows registry to verify that the entiers you need are listed and available. If the entries are correct, either you must correct Microsoft Utility Add-in, or the file acWzlib or this wizard has been disabled. To reenable this wizard, run Microsoft Access or Microsoft Office setup again to reinstall Microsft Access. Before you reinstall Microsoft Accesss, delete the Windows Registry keys for the Microsoft Access Utility Add-in and Acwizlib"
I searched everywhere and did the most what it says here but still, 2 weeks now for this issue and I cannot build any database. I'am sorry if its too long issue but even if its out of the subject can you throw a hint since its also might be related to the Trusted Locations ?
Thank you.

Reply from Alex Hedley:

Microsoft Fix it Link - Use at your own risk, this is just for informational purposes.

Brian Merrick on 5/31/2016: I get an error message when i double click on the service call within the listbox.

Private Sub ServiceListBox_DblClick(Cancel As Integer)

DoCmd.OpenForm "ServiceF", , , "ServiceID=" & ServiceListBox

End Sub

There is my code. I double check everything on the service form. What am i doing wrong?

Reply from Alex Hedley:

What error message do you get?

Anonymous on 6/3/2016: Hello.

After I created an ACCDE file, everything is fine except that I cannot work with the records. I can't Add/Delete, its like if its in Read-Mode only. I want to sell my work to somebody who can work with it smoothly.


Reply from Alex Hedley:

Did you add your own save buttons?
Does a continuous Form not have the record selectors along the bottom?

Brian Jensen on 7/13/2016: After adding this feature, which works, however I am still able to see the privacy option under file that allows the user to get into all the DB settings. In the video it show no options. What needs to be done to prevent the user from seeing the privacy options?

Reply from Alex Hedley:

Do you have the approx time?

Which version of Access are you using, this was 2007 so few versions from the current, I can't see much changing but you never know.

Brian Merrick on 9/2/2016: I have a products database, i have made a products table, Materials Table and a Magazine Table. I have multiple product that use multiple materials. I created a list box to show the materials for each product, but it's double each materials twice or three times. some help please.

Reply from Alex Hedley:

Sounds like a cartesian query, have you created a junction table that shows materials for product and that's the one you're using?
Or do you need to filter the query with the material id?

Brian M on 9/6/2016: Yes, i created the junction table for product and material. I also query with the product and material table and used a vb code =[Forms]![ProductF]![MaterialID]. I did not used the junction table.

Reply from Alex Hedley:

You'd need to use the Junction table and going the Product and Material to that

Joseph Risse on 10/1/2016: Alex, I think Brian Jensen (07/13/16), is referring to Access 2013. I see the same issue. There will be File and Home for the menu at the top. When you click on File you should see a circle with arrow in the left side to bring you back. Print , Privacy Options, and Exit. On the right side, you will see Quick Print, Print, and Print Preview.
File > Privacy Options will bring you in the Access Options Current Database, etc.

Dan Gettinger on 1/4/2017: When I create the QUERY for the list box as you have done, I end up with a line for each status. Any idea why and what I need to change? Using Access 2013 if that matters.

Reply from Alex Hedley:

Why would there not be one for each Status?

Dan Gettinger on 1/4/2017: Disregard my previous question. Figured it out. Needed to add the relationship between the StatusT and the ServiceT.
Chris Thompson on 1/22/2017: Just as a note, you speak of Access 2003 Back Color 12615680 @8:07. There is a relationship to this number and the numbers used in the later versions of Access. The above decimal number is equivalent to hexidecimal #C08000. You need to reverse the bytes to #0080CO or 00 80 C0. These bytes are then translated to decimal 0, 128, 192 respectively. If you put these three numbers into the RGB function as in RGB(0, 128, 192), you will end up with the same resulting colour.

Reply from Alex Hedley:

Thanks for this Chris, very interesting, I always wondered but never looked into it.

Chris Thompson on 1/24/2017: Using Access 2010... I'm having difficulty with the ServiceListBox. While I seem to have all the same underlying properties set the same (although I may have missed one :) ) later in lesson 14@4:52, I attempt to select an item in the list box. However, for some reason when I do, I only get a faint border around the fist line in the box - that is, I cannot select (and see the black background) of the actual item that I have selected. When I Googled this I found one instance where they found the reason was that the listbox is unbound on a bound form. Unfortunately, they don't detail the solution. Seems to me that this listbox should work regardless that the form is bound to CustomerT. Any Ideas?

Reply from Alex Hedley:

Have your tried deleting the listbox and re-adding?

Chris Thompson on 1/24/2017: Richard / Alex, never mind about the list box... Somehow it is working again. I suspect I may have had a tech selected in the MainMenuF which makes the CustomerF.AllowEdits to be false.

Reply from Alex Hedley:

That would stop it.

Chris Thompson on 1/27/2017: Just a note.

In Lesson 24 class discussion forum, the email seminar is mentioned in future tense by Richard. Since it now exists, you may want to change that comment to present tense with a promotional link to its description for those taking the Access Security Seminar in present time.

Reply from Alex Hedley:

Thanks for the idea Chris, I don't think the video will be updated for this single thing, hopefully people will read this post and know it's out.

Chris Thompson on 1/27/2017: At 5:00 when we are creating the database Links sub, shouldn't we be placing quotes around the password as in: "MS Access;PWD='" & BE_PASSWORD & "'" or does it matter?

Reply from Alex Hedley:

The quotes surround the whole string, it's not needed for the password, unless the password contains a quote.

Chris Thompson on 1/27/2017: At 12:00 when I create my database links, I get all of my tables as expected; but I also see (Using Access 2010) 12 more tables prepended with "MSys"... I assume if I just leave them, they will also get destroyed later. I also assume that I can use the td.Name to find them as we are creating the links and bypass including them - with the proviso that we do not name any of our own tables starting with "MSys"

Reply from Alex Hedley:

MSys are hidden System tables, you can show them in the Access Options, Navigation Pane.
It's unlikely you'd need them and don't want to be messing.
I've written up a course of Import/Export of Access Objects which uses these tables to be able to get to all the Tables/Forms/Reports to do some cool things.

Michael C. on 1/27/2017: Excellent seminar! I started this last year, but wasn't where I needed to be in terms of VBA, so I started working through the Access 300 series and this time around, the videos made much more sense. I've replicated the DB through video 25 and will finish it off this weekend. Thank you for this amazing seminar.

Reply from Alex Hedley:

This is great to hear :)

Chris Thompson on 1/28/2017: Hi Richard / Alex, just a followup on my feedback I wrote yesterday regarding system tables being also grabbed from my BE tables... Well, I should always check twice before running off. The issue was that I Spelt the variable Attrib incorrectly (atrrib). Once fixed, the code ran as demonstrated by Richard. Thanks for all your efforts in helping us through the myriad of tasks that finally make an Access app somewhat useful.

Reply from Alex Hedley:

Glad you figured it out.

Chris Thompson on 1/28/2017: Using Access 2010, I don't seem to have the Make ACCDE button on my ribbon; but I thought I would share where it is found:

FILE> SAVE & PUBLISH> then in the opened main window under Save Database As, it is listed under the Advanced heading.

Reply from Alex Hedley:

Did it used to be on the Ribbon, I used to go to the place you listed second.

Alecia Oswald on 3/22/2017: Hello -

Your lessons have helped me tremendously! For some reason I can't get the correct buttons to show when the form opens - I always have to hit the refresh button. It almost seems like the UserID/Username are registering on the MainMenu form after the OnCurrent event code runs. I can get around this by building the "IsUserInGroup" module using the LogIn form, but I would like to be able to figure out what I'm missing - I'm using Access 2010. Any suggestions?

Reply from Alex Hedley:

So what code are you currently using?

Shallena Ayers on 4/2/2017: Hello,

For the database I am building... i will be entering data etc here in my office. I will also need to have 2 other company owners be able to access the database from their locations (where ever the jobsites are). Right now i have the database saved on our Dropbox and they can see what i am doing. But i know it is not going to do what i want it to. I will want all of us to be able to access and add information to the database at the same time and have the added information show up in each of our versions as we navigate around. I want to limit me to seeing the financial info. Can you tell me which seminar(seminars) will help me make this happen? I cant tell if this security seminar will accomplish this. Thanks in advance,

Reply from Alex Hedley:

Split Database
Data Encryption

You will need to split the db, leave the Tables in Dropbox and each have a Front End with the forms on your own machine.
Not sure how quickly dropbox will sync to not cause issues.

Alecia O on 4/6/2017: I have the "If IsUserInGroup() Then .visible = True _ Else .visible = False _ End If" code set up for various groups & controls in my OnCurrent event. The login form is set up the same as the example in the video. If I log in as a user with limited permissions, it shows all buttons unless I click the Refresh button.

Reply from Alex Hedley:

You could add a Me.Refresh in your code after you complete the login and the Form is open.

Shallena A on 4/17/2017: Dropbox keeps creating a conflicting instance of the database. I saw a comment somewhere that said Microsoft SQL Server Express would work better. What do you think would be the easiest way to share my database with my other people?

Reply from Alex Hedley:

Would they have access to the network at these locations?

Shallena Ayers on 4/17/2017: When i create an accde file nothing in my database works any longer. When i open the database my login window pops up but when i push the button to login nothing happens. if i leave the login boxes blank and push a button nothing happens. if i right click on the login box i have an option to close... when i choose that the login box closes and my database is available but nothing in it works other than to look at it... i can open tables and forms etc from the Navigation Pane but nothing inside the tables, forms etc works. Everything works fine before creating the accde file. I am using access 2013. What is happening here? I follow exactly what you do. Thanks in advance,

Reply from Alex Hedley:

Try adding it to a trusted location. Beginner 2 covers this.

Shane Johnson on 5/9/2017: Richard, thanks for the video's. My issues spans or I think it spans several video's. I am using Access 2016 and when I built the UnAssignedServiceQ you had us put (is Null) in the ServicedBy section. When I ran the query it reveled nothing. As I tried to diagnoss before I bothered you I had replaced the (is Null) with a 0 and it worked. So I went back to the table to see what we set there and noticed it was defaulted to 0 and could not delete so deleted table and tried again and it set default to 0 again. Why is this a problem is because in video 16 you write (is Null) in the code and problem back so tried NZ to fix. please advise
Shane Johnson on 5/9/2017: Richard sorry it was what I thought just a glitch. Went back and deleted the 0's noproblem but did compact and clean up yesterday before I quit.
Valerie on 7/9/2017: Hello, My GroupSubFormF will not display in continuous view as shown in 14:34. I have set the form to continuous view but it is only showing the first record, Manager.

Reply from Alex Hedley:

Is the form row all the way up from the bottom of the detail part?

Valerie Fontenot on 9/20/2017: Lesson 31 7:55. Is there any advantage of having a login the same login process in your back end databases as in your front end instead of using a password for your backend?

Reply from Alex Hedley:

Have you seen Access Split Database Seminar?
It depends how you permission the data.

Valerie Fontenot on 9/20/2017: Lesson 32 1:30. How would I add a text box to the Admin form that would tell me if the bypass key was enabled or disabled?

Reply from Alex Hedley:

When you set up the bypass option you could then either update a new label on the form, or you could update a value in a settings table and show that value on the form.

Valerie on 9/20/2017: What is the difference between splitting a database and creating an .accde file? Don't you split the database when you create an .accde file?

Reply from Alex Hedley:

An accde file encrypts the database as one file, this stops people from getting into the VBA etc.
Splitting doesn't encrypt it just separates.

Valerie Fontenot on 9/24/2017: When will questions be posted that were submitted after April 2017?

Reply from Alex Hedley:

Hi Valerie,
I've approved all posts before this date and I see a number of posts I've approved from yourself, which ones are missing?
You should be getting an email when one gets actioned.

Valerie F on 10/4/2017: Thank you Alex. Messages have been going to my Junk folder.
Valerie F on 10/4/2017: What code would I use for the label option?

Reply from Alex Hedley:

Label option to do what?

Valerie F on 10/7/2017: The label option you referred to in your reply to my original question, "When you set up the bypass option you could then either update a new label on the form, or you could update a value in a settings table and show that value on the form." What code would I use to update the new label on the form?

Reply from Alex Hedley:

You can reference controls on your forms, just use it's name, when you login you'll have the data you need to change the text of the label.

Travis Butler on 1/11/2018: Have a question- Now that I have split the database, encrypted the back end with password and set up a table to designate the location of the back end to link and un-link- how can I designate which back end location to append and make table queries? If I am on the primary, the queries work with the designation from the query wizard but if connected to the secondary, the queries wig out....


Reply from Alex Hedley:

Are you linking the correct queries from the backend to the front end?

Rhys Thomas on 1/19/2018: Hi Richard

I am getting a Run-time error 3051.

"The Microsoft Access database engine cannot open or write to the file (File name). It is already opened exclusively by another user, or you need permission to view and write its data."

No matter where I put the file and change the code - this message always comes up!

Reply from Alex Hedley:

Local machine or Server?

Travis Butler on 1/23/2018: Alex,
The issue is the Make Table and Append Queries. In the wizard, I designated the location of the back end. How can I designate that location with VBA, SQL or Macro? On my AdminF, I can select via a table which BE to link-Primary or Alternate (for on the road laptop). The make table and append queries transfer the on the road changes to the primary when I return.

Reply from Alex Hedley:

So are these Queries stored as Objects in the database?
How are you running them, via a Macro or VBA?
If you look at the SQL where does the IN '' point to?
You could use a QueryDef to dynamically make this SQL and run it.

Eric Michalek on 3/3/2018: Brian asked about field length (security seminar about 4 min in) reply was not so much now. So what/why is it still there? Does it speed up the database? Will it take up memory? Could you please expand on this? Thanks.

Reply from Alex Hedley:

If you set a length then that amount of memory will be allocated, so 1 is less than 10 so less space is used.

todd Kaplan on 3/14/2018: Rich, I am using Access 2016. I not able to create an ACCDE file. It gives me an error. When aski9ng the Google machine, one forum tells me to compile the VBA code in debug, if there are no issues, it will allow the creation of an accde file. When I try this, i get an error to compiling with the use of Form_Current. Did this change in Access 2016?

Reply from Alex Hedley:

What line in Form_Current is highlighted?

todd K on 3/19/2018: I found the issue and fixed it, thanks.

Reply from Alex Hedley:

What fixed it, sharing may help others with the same problem.

todd K on 5/29/2018: I replaced Forms Current with On Load code
HUGO M on 11/14/2018: Reply to [Mubeezi Micah on 6/28/2010] post:

This worked for me:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim S As String

On Error Resume Next
If ID.OldValue <> "" Then

S = "ID:" & ID.OldValue <> "" & " to " & ID& vbNewLine
S = S & "LastName: " & LastName.OldValue <> "" & " to " & LastName& vbNewLine

LogIt "Changed Member's Info in the Personnel Data Form", S

End If

End Sub

Sue Bartlett on 1/14/2019: Hi Richard, I'm running Office 365 and coding this. It keeps "gagging on the Ctl.Locked and Ctl.Backcolor when I try to run it. Is there a change in the VBA code now? I THINK I checked my code against yours and it looks the same.

For Each Ctl In Me
If Ctl.ControlType = acTextBox Or Ctl.ControlType = acComboBox Then
Ctl.Locked = True
clt.BackColor = RGB(200, 200, 200) 'gray

End If

Reply from Alex Hedley:

Do you Dim up your Ctl?
Dim ctl As Control

Have you missed the "Next" statement at the end of your "For Each"?

For example info on the Textbox properties

TextBox.ControlType property (Access)

TextBox.Locked property (Access)

TextBox.BackColor property (Access)

Sue Bartlett on 1/14/2019: Gosh.... missed the "e" in textbox


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


Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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