• Some good reasons to use a database
• Why use Microsoft Access?
• Limitations of Microsoft Access
      • size
      • users
      • backups
      • replication
      • audit trail of changes
      • network speed
• Microsoft Access Versions
• Useful Tips for End-Users
      • keyboard shortcuts
      • saving data
      • date formats
• Front-end, back-end distributions
• Compacting Databases

• 
  • spreadsheets not coping with data complexities
• • spreadsheets not coping with multiple users
• • manual system limiting growth
• • need to make staff more productive
• • mechanical tasks need to be automated
• • too many mistakes being made
• • need to have electronic interfaces
• • need to have more security

• • it's a fully relational database
• • very quick development time
• • variety of user interface styles available
• • very friendly for the end-user
• • large number of developers
• • widely used
• • on-going support and improvements
• • very good interfaces for importing and exporting data
• • integration with other Office applications
• • easy migration path to SQL Server
• • optional security (and if used, very powerful)

• Size: MS Access can safely cope with data volumes up to 250 Mg. We have clients with databases over 200 Mg, single tables with over 1 million records and databases with over 100 tables.
• Users: Concurrent users limit is about 20. Some sites see a degradation of performance after 10 users, but there's usually other factors involved (usually network or server performance).
• Backups: MS Access does NOT do backups itself, so relies on other file backup systems. The Access database is a single file and if someone has it opened, it's locked. This may stop some backup systems, so check your backups regularly and schedule them when there's no-one on the database. While database corruptions can occur, Access has a very good repair mechanism that recovers most corruptions. After that there's a variety of 3rd-party tools that will recover almost everything. So backups are more-often used to recover from deletion mistakes. Daily backups are recommended with weekly or monthly permanent copies.
• Replication: Replication and syncronisation in MS Access is not reliable or robust. I have experienced many problems and data corruptions; so would not recommend using this feature.
• Audit trail of data changes: MS Access does NOT record any sort of audit trail of data changes automatically. If auditing information is required, then additional programming would need to be done.
• Network Speed: Access requires fast network speeds - 10 meg or better. I would not recommend opening an Access database over a WAN. The best solution for WAN access is Terminal Server (e.g Citrix)

Common name(s)	Internal version no	Release date	Notes
Access 95	8	Sep 1995	no longer supported by Microsoft
Access 97	9	1997
Access XP/2002	10	Nov 2001
Access 2003	11	2003
Access 2007	12	Nov 2006
Access 2010	14	Jul 2010
Access 2013	15	Jan 2013
Access 2016	16	Sep 2015	MS website

• Keyboard shortcuts (some little-known shortcuts that users have found very useful)

key combination	action	context
CTRL+:	inserts the current date	form fields or table/datasheet cells
CTRL+'	repeats the previously entered data	form fields
ESC	undo	form fields
CTRL+P	opens the print dialog box	forms and reports
F4	opens a combo box (drop-down list)	form combo boxes
SHIFT+F2	opens the Zoom window	form text fields
SHIFT+TAB	moves to the previous field	form fields
CTRL+TAB	moves from a subform to the following field on the parent form	subforms
S	opens the page setup dialog box	reports in preview
SHIFT+ENTER	saves data changes	forms
SPACE	toggles a checkbox between YES/NO	form checkboxes

• Saving data on forms

MS Access does not have any 'Save Data' buttons or actions. It saves data automatically! This means, if you do NOT want your changes saved, you need to undo your changes (ESC key or Undo button on the toolbar). 

On forms, datasheets and tables data will be saved automatically when:
• the form, datasheet or table is closed
• the mouse cursor is moved to another record
• on a form, the mouse cursor is moved to a subform
• the record selector bar is clicked (this is the button or bar to the left of the row or form - it has the editing symbol on it when data has changed)
• SHIFT+ENTER keys pressed

• Date formats for entry

MS Access applies a few rules with date entry that allow for a lot of flexibility:
• if the year is not entered, the current year is added
• leading zeroes are optional
• either the forward-slash (/) or the space can be the delimiter character between the day, month and year
• the month can be entered as a number (1 - 12), or a short name (Jan, Feb, etc) or full name (January, etc)
• the century of the year can be dropped. In this situation, the century is determined using the MS Windows rules.
• if the regional date format is d/m/yyyy and the digits after the 1st delimiter are 12 or less, then they are the month, else the 1st digits are the month
• if the regional date format is m/d/yyyy and the digits before the 1st delimiter are 12 or less, then they are the month, else the 2nd set of digits are the month

e.g. assuming the PC regional date format is d/m/yyyy and the current year is 2006, entry of 14 August 2006 could be:
• 14/8/2006
• 14/8
• 14 8
• 14 Aug 06
• Aug 14 2006
• 14 August
• 8/14/06

Odd results can occur if the PC's regional date format is not what the user is expecting. So if the 5th August needs to be entered and the 8th of May is the result, then the PC regional setting is likely to be incorrect.

MS Access database applications should be split into a front-end database (containing the forms and reports of the user interface i.e. what you see) and the back-end database (containing the data tables and the relationships).

This split serves 2 purposes:
• a developer can make modifications to the front-end outside the live environment and simply replace the file to release a new version
• multiple users can each have their own front-end, all talking to the single back-end end database

There are several ways to position (or distribute) the front-end database in a multi-user network:
• have both the front-end and the back-end databases located on the data server.
  advantages:
  · everyone is using the same version of the front-end
  · easy relaese of new versions
  · easy setup of new PCs (just need the shortcut)
  disadvantages:
  · potential conflicts on temporary tables used for special processing
  · if users have different 'paths' to the server, some uses may experience occassional problems accessing the application
  · slight increase in network traffic
  · everyone has to exit the system when a new front-end version is released
  this method is recommended for a small number of users and for applications with only infrequent users

• have the front-end on each user's local drive and just the back-end on the data server.
  advantages:
  · slightly faster
  · upgrades to new versions can be to selective users
  disadvantages:
  · more work to release new versions as each user has to replace their own copy
  · some users could be running out-of-date versions
 This is the recommended method!

Database compaction is necessary because MS Access does not recover disk space after data deletion and object design changes. The compaction process rebuilds the database with just the current data and objects.

This is reason why a newly released front-end database could be significantly smaller than the database it's replacing.

The recommended frequency of back-end database compaction depends on the data 'turnover' - how much data is being regularly deleted. If there's never any data deletion, there's very little to gain from a compaction apart from the efficiencies of reorganising table indexes - so 3 monthly to annually depending the volume a data being added. With databases with regular data deletion, weekly to monthly compaction is recommended.

The compaction tool is found under the Tools menu in Database Utilities. A compaction can also be triggered with the /compact command line switch.

NOTE:
• take a copy of the database before compaction
• open the database afterwards and make sure the table msys_compaction_errors does NOT exist
• all users will need to out the database while the compaction is being done