Transcript on Taking Over Microsoft Access Databases

By Luke Chung
President
FMS, Inc.
Copyright © 2012 FMS, Inc., www.fmsinc.com
www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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.
Copyright © 2012 FMS, Inc., www.fmsinc.com
Overview
 Introduction
 Taking Control
 Quick Wins
 Multiple Databases and Multiuser Issues
 Upsizing to SQL Server?
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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 monitoring
http://www.fmsinc.com/MicrosoftAccess/monitor.asp
 Total Visual Agent: Scheduled maintenance
http://www.fmsinc.com/MicrosoftAccess/Scheduler.html
Copyright © 2012 FMS, Inc., www.fmsinc.com
Overview
 Introduction
Taking Control
 Quick Wins
 Multiple Databases and Multiuser Issues
 Upsizing to SQL Server?
Copyright © 2012 FMS, Inc., www.fmsinc.com
Taking Control: Overview
 Understanding the Architecture
 Regular Maintenance
 Visual Source Safe
 Application Deployment
 Startup Settings
 Robust Error Handling
 Quality Assurance and Deployment Plan
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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)
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
Overview
 Introduction
 Taking Control
Quick Wins
 Multiple Databases and Multiuser Issues
 Upsizing to SQL Server?
Copyright © 2012 FMS, Inc., www.fmsinc.com
Quick Wins: Overview
 Documenting and Analyzing the Database
 Fixing Errors
 Migrating Access 2003 or Earlier Databases
 User Interface
 Forms
 Reports
 Performance Enhancements
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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

Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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.
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
Overview
 Introduction
 Taking Control
 Quick Wins
Multiple Databases and Multiuser Issues
 Upsizing to SQL Server?
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
Overview
 Introduction
 Taking Control
 Quick Wins
 Multiple Databases and Multiuser Issues
Upsizing to SQL Server?
Copyright © 2012 FMS, Inc., www.fmsinc.com
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?
Copyright © 2012 FMS, Inc., www.fmsinc.com
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/
Copyright © 2012 FMS, Inc., www.fmsinc.com
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
Copyright © 2012 FMS, Inc., www.fmsinc.com
FMS Web Site: fmsinc.com
 FREE Technical Papers
 Sign-up for FREE newsletter for news and beta




invitations
Product Information and Demos
Email: [email protected]
Facebook: http://www.facebook.com/fms.solutions
Twitter: http://www.twitter.com/fmsinc
Copyright © 2012 FMS, Inc., www.fmsinc.com