Information About Databases & MS Access
Here are the topics covered on this page:
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





Some good reasons to use a database:


  • • 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
Why use Microsoft Access?

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




Limitations of Microsoft Access
  • 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)


Microsoft Access versions

Common
name(s)
Internal
version no
Release
date
Notes
Access 958Sep 1995 no longer supported by Microsoft
Access 9791997 
Access XP/200210Nov 2001 
Access 2003112003 
Access 200712Nov 2006 
Access 2010 14 Jul 2010 
Access 2013 15 Jan 2013 
Access 2016 16 Sep 2015 MS website



Useful tips for end-users

  • 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 dataform fields
 ESCundo form fields 
 CTRL+Popens the print dialog box forms and reports 
 F4opens a combo box (drop-down list) form combo boxes 
 SHIFT+F2opens the Zoom window form text fields 
 SHIFT+TABmoves to the previous field form fields 
 CTRL+TABmoves from a subform to the following field on the parent form subforms 
 Sopens the page setup dialog box reports in preview 
 SHIFT+ENTERsaves data changes forms 
 SPACEtoggles 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
    EditSymbol.jpg• 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.


Front-end, back-end distributions
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!



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

Copyright 2006 Databases By Design. Read Terms of Use and Privacy Policy.
Designed and deployed by Interspeed Web Solutions Limited.