Visual Basic Programmer

Microsoft Access Database Programmer

All about Microsoft Access 2010

The latest version of Microsoft Access is now available.

Access 2010 still has a 2GB database file size limitation – but this is more than enough for most Access applications. Access 2010 requires a minimum of 256 megabytes of RAM.

The new Features of Microsoft Access 2010 are:

• The File menu replaces the Access 2007 Office Button and provides a single location for database information such as permissions, version information, templates and sharing options. You can view the Application Log, publish, compact, analyse or encrypt the database. The relationship window has been moved from the Ribbon and is now in the Backstage view.
• The Ribbon has been improved and the Backstage File menu now loads a new screen with more functionality.
• There is a Web Database type format that allows a database to be created and then published to the Web. This requires a SharePoint Server.
• Data Macros function like SQL Server triggers. They can be launched based on conditions and events in the database – when records are created, deleted, or edited. The logic is attached to the table, not the form.
• The auto format has been replaced by Office Themes. Office Themes make customisation of Databases, Forms and Reports easier and more consistent.
• The Data Type gallery replaces the Add Field task pane and contains all the common field types. Groups of fields can be added as a collection.
• Access 2010 allows conditional formatting within reports.
• Expression and the Query Builder now use Intellisense. This reduces errors with queries, expressions and field names.
• The Web Browser control enables Web content to be integrated inside an Access Form.
• The VBA editing environment is minimally enhanced.
• Visual Basic for Applications (VBA) 6.0 has been updated to VBA 7.0. VBA will be maintained in future versions of Office for Windows. There is support for 32-bit and 64-bit Windows operating systems – as well as existing ActiveX Controls and Component Object Model (COM) add-ins.

Access 2007 warning

If any of the new features are incorporated, the database will not run in Access 2007. The error message is "Unrecognized database format".

Trying to find out which incompatible feature has been added is near impossible. Until Microsoft make incompatibility problems easier to find, it is best to develop only in Access 2007.

Deprecated features in Access 2010

• The calendar control (mscal.ocx) is no longer available – use the Date Picker control instead. Note that the Date Picker can be suppressed by setting the control's "Show Date Picker" property to "Never".
• The Snapshot Format for reports is not available – use the PDF/XPS formats instead. PDF/XPS no longer requires a download.
• Data Access Pages (DAPs) can no longer be executed.

Microsoft Access project (ADP)

There do not seem to be any improvements to ADP (using SQL Server as Back-End) since Access 2003. Microsoft Access 2010 still does not implement stored procedures.

It is easy to link efficiently to a SQL Server database, and use ADO for Record retrieval. The future of ADP is rather hazy – especially with the advent of SharePoint Server.

Setting up Trusted Folders

Whenever a Macro (or VBA Function or Procedure) is run, the message "A potential security concern has been identified" appears. To get rid of these messages, the Access database will need to be saved to a Trusted folder.

• Open Microsoft Access 2010
• Click the Backstage File Menu (top left hand side).
• Click "Options"
• Click "Trust Center"
• Click "Trust Center Settings"
• Select "Trusted Locations"
• Click "Add new location"
• Enter the folder where the database is to be kept.
• Enter a description and then click OK.
• If there are Subfolders, enable the "Subfolders of this location are also trusted"

Place all the database files into the Folder that you have marked as Trusted. You should not get any Security warnings again for the database.