Access 2007 Tutorial 12 Securing a Database

1. Access 2007Tutorial 12Securing a Database (Access Developer)

2. Setting Database Properties and Startup Options • Access allows us to specify Startup Options • Open the database, Click the Office Button, Click Access Options Results of Startup Options set on following slides

3. Displayed at top of window Replace Access Icon with another Startup form when program opens Suppress Status bar Overlapping Windows are preferred for forms Disable special keys like F11 (open Navigation Pane) Compact on Close can take too much time, do on demand Good idea Hide the layout View No designing allowed Show partial numbers in fields Suppress Navigation Pane

4. We can create our own Ribbon and replace Access Ribbon We can do the same with right clicking as well Suppress Full Menus Suppress right clicking Optional: Access stores the information needed to correct naming errors

5. Bypassing the Startup Options • Access users can bypass the Startup • Hold the SHIFT key when opening the database will do the trick Startup has been bypassed and full use of the database if a user can remember to hold the SHIFT key

6. Saving a Database as an ACCDE File • Saving a database as an ACCDE file will • Save the database with an extension of ACCDE as opposed to (ACCDB) • Prevent people from viewing or changing the VBA code • Not be able to view, modify or create forms, reports or modules • An ACCDE database is still be vulnerable to bypassing the Startup • Better than nothing due to the removal of VBA, forms, reports, modules capability Make ACCDE will allow us to create a more protected database

7. Disable the Bypass Key • Disabling the Bypass Key (holding SHIFT when opening a database) • There is a way to disable the Bypass Key • A Macro, Module and 2 Functions must be created to do this • Start with creating a Module and giving it a name • In this case, the Module has been saved as basFunctions We need to create two functions in here

8. Disable the Bypass Key • Creating the fCheckBP() function • This function will contain the logic to either Enable or Disable the Bypass Key Enter the Function as you see it here This is the InputBox statement with a password try entered (correctly)

9. Disable the Bypass Key • Creating the ChangeProperty Function • This function will perform the action of Enabling or Disabling the ByPassKey Enter the Function as you see it here

10. Disable the Bypass Key • Creating the AutoKeys Macro • This is a special Access Macro that will execute actions based upon key strokes entered by the user • The Macro must be saved with the name: AutoKeys • Create the Macro Name ^{T} (stands for pressing Control Key and T together) • Select RunCode for the Macro Action • Enter fCheckDP() as the Function Name When the user Presses the Control and T keys together at anytime when the database is open the Macro will call the fCheckBP() function which will prompt for a password and either Enable or Disable the ByPassKey

11. Disable the Bypass Key • Pressing Control + T while in the database • This will invoke fCheckBP funtion requesting the password • A successful password will allow the ByPassKey to work (SHIFT)

12. No Data in Report VBA Example • When query source for a report selects no records Access will produce an inaccurate report • rptCustomerDetail has qryCustomerDetails as the record source • qryCustomerDetails has a criteria set for the CustomerNum field • If a CustomerNum is entered on those records will be selected (Like “104*”) • If OK is clicked all CustomerNum records will be selected (Like “*”) What happens a CustomerNum entered does not exist?

13. No Data in Report VBA Example • rptCustomerDetail using CustomerNum 104 • Results in a successful report

14. No Data in Report VBA Example • rptCustomerDetail using CustomerNum 105 (not a valid CustomerNum) • Results in blank report • When this occurs we should have VBA intercept this and stop the process

15. No Data in Report VBA Example • Design View of rptCustomerDetail • Report Properties • OnNoData Event • Create an Event Procedure

16. No Data in Report VBA Example • rptCustomerDetail using CustomerNum 105 (not a valid CustomerNum) • Try again but have VBA intercept this and stop the process • Results in: • Click OK and control returns to the application as if the report never happened • User can try again but enter valid criteria