Taking over an Existing Microsoft Access Application

1. Taking over an Existing Microsoft Access Application By Luke Chung President FMS, Inc. www.fmsinc.com

2. FMS Background Founded in 1986 We’re experienced commercial software developers: first product in 1987 In 1993, we introduced one of the first Microsoft Access products for Access 1.1 We’re the world’s leading developer of 3rd party products for Microsoft Access We currently offer 13 products for Microsoft Access users and developers

3. FMS Professional Services Offering custom consulting services since 1987 Services include design, development, testing, deployment; system review and optimization Wide range of solutions for commercial and government agencies Customize Microsoft technology such as Access, SQL Server, VB6, Visual Studio .NET, LightSwitch, Exchange/Outlook, ASP, IIS, Mobility, Azure, etc.

4. Overview Introduction Taking Control Quick Wins Multiple Databases and Multiuser Issues Upsizing to SQL Server?

5. Why There Are So Many Access Databases? Access debuted in 1992 Access is the most popular desktop database in the world Lots of people have created Access applications over the years As part of Office, people created lots of Access databases with various levels of success Databases have longer life spans than Word or Excel documents Most of these need to be maintained and enhanced Most do not need or cannot justify the cost of a complete rewrite on a new platform

6. Most Organizations Don’t Know How to Deal with Access Too many databases to manage Don’t know what they do Weren’t professionally developed originally People who created them are gone Don’t have Access expertise on staff Don’t know or not convinced that Access can really create professional applications Not part of the .NET, Java or SQL Server goals of the IT department Lots of business pressure to enhance existing applications

7. Understanding Objectives and Tradeoffs • Professional Access applications exist and can be created by people who know how to do so • Make sure objectives are within the capabilities of Access • Database size • Number of simultaneous users • Not a web application • Make sure everyone understands the tradeoffs and advantages of Access • Saving time: most of the application is done • Saving money: some features are easier to implement • End user flexibility: queries, reports, exports • Not going to be perfect • Rapid application development solution

8. Working with Decision Makers • Define what it means to be successful • Technology is a tool, not the objective • Establish trust • First Win: Triage and address most pressing needs • What are the critical pain points? • Stability, specific features, response to requests, accuracy, performance, etc.? • Prioritize objectives/goals • Agree it will never be perfect or done • Recognize and celebrate milestones • Make sure it’s collaborative • You can’t read minds • You can’t know their work as well as they do • Establish rapid design, build, and deployment cycles

9. Are You Personally Responsible? • Do you think you are? • Does your boss/client think you are? • If you created the database or took it over from someone else, you’re responsible and liable • Recommend a good disaster recovery plan (DRP) • Create a good DRP • If there’s “no budget or time”, get written verification that someone else is responsible for the DRP

10. Create a Disaster Recovery Plan • Critical to backup the back-end database containing the latest data • Determine how often to make backups • Depends how often the data changes • Depends on the cost of losing data • If it’s on the network, there may already be network backup routines running • Rolling backups are important • Know how many versions of the database are kept • Determine if that’s adequate • Test and make sure backups work

11. Create a Database Compact Plan • Access/Jet databases need to be periodically compacted for optimal performance • Compact at least once a day • Requires exclusive lock to the database • AutoCompact when the database closes does not work on back end databases • Backup as often as you’re willing to lose work • Data extracts may help if exclusive lock is not possible • Watch out for table relationships (referential integrity) • Consider from FMS: • Total Access Admin: Real-time monitoringhttp://www.fmsinc.com/MicrosoftAccess/monitor.asp • Total Visual Agent: Scheduled maintenancehttp://www.fmsinc.com/MicrosoftAccess/Scheduler.html

12. Overview • Introduction • Taking Control • Quick Wins • Multiple Databases and Multiuser Issues • Upsizing to SQL Server?

13. Taking Control: Overview Understanding the Architecture Regular Maintenance Visual Source Safe Application Deployment Startup Settings Robust Error Handling Quality Assurance and Deployment Plan

14. Understanding the Database Architecture • Is the application single user or multi-user? • How the data goes in and out of the application? • Is it all from data entry? • Is data shared by other applications? • If so, what do those applications do to the data? • Is it all in Access (Jet)? • If so, is it broken up into a front-end and backend database? • Does it use data from other sources? • Linked tables (Jet, SQL Server, etc.) • Imports from download files, Excel, etc. • Does it export data to other sources? • Exports to Excel or Word • Exports for other applications • Is there workgroup or password security? • Do files violate Windows security (User Access Control)? • Access files which require editing, cannot be installed in C:\Program Files • Use the User’s Application Data folder for temporary files

15. Front-End vs. Back-End Database • Fundamental to supporting multiple users and application enhancements • Back-End database resides on the network and has all the data that’s shared among users • Front-End database includes: • Links to the back-end database tables • Application objects (queries, forms, reports, code) • Temporary/intermediate tables that are part of processing output to avoid multi-user collisions • Front-End database can be easily replaced without impacting the data • Avoids getting the users’ latest data • Is local to each user (performance gain)

16. Splitting a Database • Automated • Use the Access Database Splitter feature • Manually • Create the back-end database • Copy the existing database • Delete all the queries, forms, reports, DAPs, macros, and modules • Put the database on a shared network drive • Create the front-end database • Copy the existing database • Delete all the tables • Link to the tables in the back-end database

17. Code Changes to a Split Database • Most code works identically • May need to add code to relink tables to support deployments to different drives • Seek commands need to be adjusted to point to the back-end database explicitly • Seek commands are used to search tables on primary or secondary indexes which is much faster than Find. • Code: SeekLinkedExample with DAO • For SQL Server and ADO, use query to retrieve the record rather than the seek command

18. Working with What You’ve Got • Assumes the database is split • Avoid Disaster • Establish a backup and recovery plan • Test it • Compact the back-end database regularly • Use Microsoft Visual Source Safe • Simplify deployments of new front end databases make it easier to: • Make updates without interrupting end users • Deploy fixes to minimize upset end-users

19. Microsoft Visual Source Safe (VSS) • Included in MSDN • Provides source code control for Access development (and other platforms) • Manages individual queries, forms, reports, macros, and modules • Bad: All tables are managed as one object • Critical for multi-developer environments • Check-in and check-out objects to avoid writing over other people’s work • Valuable for individual developers too

20. Using Visual Source Safe • Avoids problems when using multiple machines • See historic versions of each object • Easily shows differences between object versions • Easy to review and rollback mistakes • Share the same object across Access databases • Consolidate utility modules in one place • Makes it easy to create latest version • Tips • Make sure compiled state is always checked in • Turn off automatic add for new objects • CRITICAL: Make sure the VSS repository is backed up

21. Simplifying Application Deployment • Users should click on a shortcut and start the application • Avoid having them know your database name • Avoid users loading Access, then opening your database • Make sure the right version of Access is launched with the database – do not trust Windows to just launch an MDB file • Recognize that what you build will have bugs and updates will be necessary • Simplifying the deployment process reduces the pain of each update and makes you look better • Make it transparent to users

22. Startup and Deployment Plan • Need to install front-end database and any dependencies on each desktop • Need to be able to deploy application updates efficiently • When updates are ready, each desktop needs a new copy • Need to make sure people launch your Access application with the right version of Access • Use a table in the front-end and back-end databases with the version number and code to know when they are out of sync • We use our Total Access Startup program to manage this across the network (zero deployment) • Application distribution via shortcuts to everyone • Automatically checked each time it’s started and front-end updated if it’s new • http://www.fmsinc.com/MicrosoftAccess/VersionLauncher.asp

23. Database Startup Settings • Lock down the application • Keep users out of your database • Don’t let them bypass your startup routines • Database “AllowBypassKey” property • Disable the Access window close button • Hide Everything! • Hide the database window/navigation pane • Hide all the objects in case they unhide the database • Hide the Access menus and ribbons • Suppress special keys • Make sure Error Handler is set properly • Application.SetOption “Error Trapping”, 1

24. Applying Robust Error Handling • When a crash occurs, users feel the whole application is broken • No difference between minor vs. major problems • Robust error handling let’s you know everything that’s failing and how • Eliminates blaming end users for their mistakes • Forces developers to take responsibility • Simplifies analysis and reproduction of crashes • Let’s you fix problems not explicitly reported • Automatically disappears in next build • Critical to improving the stability and reliability of the application over time • Let’s you deploy ACCDE/ADE/MDEs

25. Understanding Existing Error Handling • Review the code to see how On Error is handled • Cascading error handling • Errors should be handled in each procedure • Sometimes people handle errors from parent • Need to understand this before applying global error handling • Is there an existing global error handler? • How are errors presented to the user? • Is there documentation of errors in text files or a table? • Determine if error handling needs to be established in procedures that lack it

26. Robust Error Handling • Global error handler that: • Prompts the user with a friendly form rather than the default Cancel/Debug dialog • Tells the end user what to do next • Documents info to a text file or error table with • Error number, description, and line • Procedure call stack • Other Access or Windows information • Version of the application and Windows • Location of database, user name, etc. • Procedure call stack is not automatically available and requires documenting the procedure names as they are called • Need to make sure all procedures exit at end

27. Total Visual CodeTools from FMS • Code Cleanup • Adds your custom error handling to all procedures that lack error handling • Indents code for programming logic and loops • Before deployment, Code Delivery can add line numbers to all lines to pinpoint exactly where a crash occurs • New Procedure Builder to create new code with your error handling and comment structures in place • Lots of other code builders to simplify writing code • Supports VBA and VB6 • http://www.fmsinc.com/MicrosoftAccess/VBACodingTools.html

28. Establishing Quality Assurance Process • Create a way to test your application before deployment • Someone other than the developer needs to test • Production vs. development environments • Create checklists of what it means to “finish” • Hold developers responsible for finishing • Never bypass the QA procedures • Automate process to deliver a new version • Reset/empty temporary tables and settings • Make sure error handling is active • Turn off debugging code • Add line numbers to code • Create the ACCDE, ADE, or MDE

29. Overview • Introduction • Taking Control • Quick Wins • Multiple Databases and Multiuser Issues • Upsizing to SQL Server?

30. Quick Wins: Overview • Documenting and Analyzing the Database • Fixing Errors • Migrating Access 2003 or Earlier Databases • User Interface • Forms • Reports • Performance Enhancements

31. Documenting and Analyzing the Database • Basic object documentation available in Access • To understand relationships across all objects and code analysis, we use our Total Access Analyzer • Performs documentation and analysis of your databases • Generates cross-references for tables, queries, forms, reports, macros, and VBA code • Detects over 300 types of errors, design suggestions, and performance enhancements • If there are errors, you’re not ready to deploy • Lots of other features to learn and apply best practices • http://www.fmsinc.com/MicrosoftAccess/BestPractices.html

32. Quick Wins: Fixing Errors • Decompile the database to make sure there’s no VBA junk • Run MSACCESS.EXE with the /decompile option on the command line • Verify library references • Add Option Explicit to all modules and code behind forms and reports • Get compile errors rather than runtime errors • Fix compile problems that arise

33. Quick Wins: Fixing Errors • Make sure there are no broken dependencies across queries, forms, and reports • Trap for Null problems • Use NullToZero Function: NZ • Make sure all tables have primary keys • Add Autonumber field for primary key where necessary

34. Quick Wins: Migrating Access 2003 or Earlier Databases • New Options for the User Interface • Current Database • Document Window Options for Tabbed view • Disable design changes for tables in datasheet view • Picture Property Storage • Option for smaller file format if you’re not going back to older versions • Datasheet • Set Alternate Background Color • Recommend: Light Background Color • Default font: Calibri • Totals Row

35. Quick Wins: Access 2007/2010 Form Enhancements • Support form resizing • Anchor objects, or use PopUp to avoid tab view • Split Forms • Add Navigation Caption to Datasheets • Editable ComboBox lookup list • Access Options, Popular category: Color scheme • Support Color Schemes on Forms • Main Form: Background Form • Header and footers: Background Light Header • Labels: Text Dark (dark blue) • Buttons: System Button Text • Support New Buttons • Graphics and text together • Cursor on hover set to Hyperlink Hand • Back Style set to Transparent

36. Quick Wins: Access 2007/2010 Report Enhancements • Learn about Report View • Allows ad hoc filters of your reports • Totals are automatically recalculated • Use acViewReport option with DoCmd.OpenReport • Send reports to PDF • Improved layout designer

37. Quick Wins with the User Interface • Consistency in look and feel across all the objects the user experiences • Forms and Reports • Explicit Caption so object name doesn’t appear in title • Same font and colors across objects • Can be different fonts based on type (e.g. buttons vs. labels), but be consistent • Same info in same place, for example: • Buttons always at the top or bottom • Report footers with page number, etc. • Spell check labels, captions, validation text, status bar text, message boxes, etc.

38. Quick Wins with Forms • Turn XP Themed Form Control on • Same form size (height/width) for similar forms • Smooth navigation between forms • Do not allow users to switch between views (form, datasheet, and pivot views) • Improve ComboBoxes • Make Limit to List is Yes • Increase List Rows is greater than 8 (use 25+) • AutoCenter = Yes • AutoResize = Yes • Add BeforeUpdate event for record validation • Helps with data entry • Avoids problems with bad data later

39. Quick Wins with Reports • Make sure all reports have Default Printer set • Grouping KeepTogetherProperty should not be No • Use First Detail or Entire Group • Add NoData event to handle empty reports • One procedure to preview and/or print reports • Reports should be previewed in maximized mode • Handles NoData event closing the report

40. Quick Wins Improving Performance • Keep database handles open to all linked databases • If not used, switch the SubdatasheetName of tables from [Auto] to [None] • Replace multi-field keys with AutoNumber • Significantly improves secondary indexes • Test adding indexes to fields in tables that are filtered • For slow forms and reports, change RecordSource from SQL to a saved query • Make sure Queries with Group By are not using Queries with Group By • Replace infinite DoEvents loops with Sleep • Watch for code to wait for form to close that could be replace by opening in Dialog mode

41. Other Enhancements • Get rid of unused objects • Junk/old tables and queries often litter applications • Eliminate unused code • Unused procedures • Unused constants, variables, and parameters • Review unreferenced variables • Scope code narrowly • Make procedures, variables, and constants private if they don’t need to be public/global

42. Overview • Introduction • Taking Control • Quick Wins • Multiple Databases and Multiuser Issues • Upsizing to SQL Server?

43. Managing Multiple Databases • There may be multiple copies of the same or “similar” databases • Need to determine which copy is the master • Consolidate objects and code • Consider Total Access Detective from FMS to compare databases or objects for differences in object design, code, and data • http://www.fmsinc.com/MicrosoftAccess/DatabaseCompare.html

44. Review Table Structures • Existing databases often have non-optimal database structures • Determine if data is properly normalized • Structures should not change over time • Do not blame original developer because objectives change over time • Changing table structures is painful and potentially destabilizing • Need to balance working with a bad structure to the gain for fixing it • Make sure all tables have primary keys • Make sure referential integrity is set where necessary and defined properly

45. Review Multiuser Issues • Multiuser issues can arise on one machine • Optimistic vs. pessimistic locking • Make sure forms opening one after the other don’t lock the same record • Make sure temporary tables are in the front end database so two users running the same thing at the same time don’t collide or create wrong results • If data changes continuously, decide if consistent reports are required • Data snapshot for end of day (yesterday) reports

46. Overview Introduction Taking Control Quick Wins Multiple Databases and Multiuser Issues Upsizing to SQL Server?

47. Deciding to Upsize to SQL Server • What is the expected size of the database? • < 2 GB, Access may be acceptable • How many simultaneous users are expected? • Access can handle 255 but 20-50 is more realistic • Depends on what they are doing; more for read-only (e.g. reporting) • How important is data integrity? • SQL Server offers a complete audit log, automatic recovery and system maintenance • File server databases require backup & compact • How important is performance? • SQL Server applications do more, but tend to be slower than file server applications • Are there interfaces to other applications such as the web?

48. Upsizing Access/Jet to SQL Server • May be a considerable investment of time and effort depending on the complexity of the application • Put back-end tables on SQL Server • Consider SQL Azure for $10/month • Choose MDB/ACCDB vs. ADP approach • MDB/ACCDBs offer more front-end flexibility with the use of local tables • ADPs are more friendly for supporting design changes on SQL Server • Access enhancements focused on MDB/ACCDBs • Complex topic, visit our web site for more information • http://www.fmsinc.com/MicrosoftAccess/SQLServerUpsizing/

49. Conclusion Taking over an existing application is challenging in any environment and not unique to Access The number of issues to address with an existing database application are numerous Building new features are only part of the solution Make sure system administrative requirements are met because users will not want them until a disaster occurs Establish what it means to be successful Work with end users and decision makers to show your commitment to their needs

50. FMS Web Site: fmsinc.com FREE Technical Papers Sign-up for FREE newsletter for news and beta invitations Product Information and Demos Email: fms@fmsinc.com Facebook: http://www.facebook.com/fms.solutions Twitter: http://www.twitter.com/fmsinc