Visual Basic and Access Database Programmer
  Neville
Silverman

Visual Basic Programmer, Sydney
Microsoft Access Database Programmer

Custom Built Software
System Optimisation
Office Automation
Website SEO
Phone me with your Visual Basic programming queries Phone Australia
(02) 9453-0456

All about Microsoft Access 2010 and the VBA language

All about Microsoft Access 2010The design environment of Access 2010 is very different from Microsoft Access 2003 – but the Visual Basic Integrated Development Environment (IDE) is unchanged.

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.

Note that support for Access 2003 ends in 2014 – an upgrade to Access 2010 or later is imperative.

Here then is a summary of the features and idiosyncrasies of Access 2010.

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 Ribbon has been improved and the File menu now loads a new screen with more functionality.
  • 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 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.
  • The VBA editing environment is minimally enhanced.
  • Visual Basic for Applications (VBA) 6.0 has been updated to VBA 7.0

Backward compatibility: Access 2007 warning

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

Deprecated features in Access 2010

  • The calendar control (mscal.ocx) is no longer available – use the Date Picker control instead.
  • The Snapshot Format for reports is not available – use the PDF/XPS formats instead. PDF/XPS no longer requires a download.

Setting up Trusted Folders for Macros

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
  • 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 Sub-folders, 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.

Upgrading using .mdb database files.

Access 2010 accepts the .mdb files from Access 2003, and the upgrade is painless – with a few exceptions.

  • Code derived from Access 97 may not work – like File Dialogues. The code will need to be upgraded.
  • Controls derived from Access 97 may not work. The not very helpful message is "There is no object in this control".
  • You may have to turn off UAC (User Account Control), in order to get some features (like SendKeys) to work.

Upgrading using .accdb database files

  • The file size on converting from the old format to the new is unchanged.
  • A new Switchboard can be created using Macros.
  • There are now complex data types – attachments and multivalue fields.
  • The .accdb format supports the new Access Themes.
  • User-level security is not supported.

Why Convert to the new .accdb format?

  • The .mdb format will soon become an historical relic
  • New features are available – Attachment data types, Multi Valued Lookup Fields and Rich Text fields
  • Microsoft Outlook blocks the old .mdb databases due to security concerns
  • The .accdb format allows the database to be encrypted

The new Microsoft Access 2010 File Types

File Type
Description
.accdb
The traditional .mdb file, using the Jet 4.0 database, has been replaced by an .accdb file, using the Access Database Engine or ACE.
.accdc
This is an Access Deployment file, certified with a digital signature.
.accde
The compiled version of the .accdb file. It does not allow the user to read or modify the VBA source code.
.accdr
This is a runtime Access file. Simply change the File Type from .accdb to .accdr to hide the Ribbon and the Navigation pane.
.accdt
This is a database template file that can be listed on the Access 2010 Getting Started page.
.adp
ADP (Access Data Projects) was a method of connecting to a SQL Server database. There have been no improvements to ADP since Access 2003. It is easier to link to a SQL Server database, and use ADO for data handling.
.laccdb
This record locking file is automatically created when an .accdb file is opened. It ensures that there is no data corruption when two users change data in the same record. When all users have exited Access, the file is automatically deleted.
.accdu .accde .accda
These files are Add-Ins or Libraries. They can be linked to Access using the Add-In Manager.