Visual Basic Programmer: Access database Programmer

Visual Basic Programmer
Microsoft Access Database Programmer

Custom Built Software

Visual Basic Programming: All about Microsoft Access 2016

All about Microsoft Access 2016The design environment of Access 2016 is much the same as Microsoft Access 2013. Access 2016 has all the same features and functionalities as Access 2013, but with only a few enhancements. Development has concentrated on Access 365 and the web.

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

Note that support for Office 2010 has expired – an upgrade to Access 2013 or later is imperative.

Here is a summary of the new features of Microsoft Access 2016.

The new Features of Microsoft Access 2016 are:

  • A slightly more colourful interface
  • "Large Number" data types are now supported – the data type is compatible with the SQL_BIGINT in ODBC and the bigint in SQL Server
  • Some database templates have been updated with a modern look and feel
  • Resizable forms have an anchoring feature and controls can expand to fit the size of the form
  • Multiple objects (i.e. Tables) can now be shown as tabs – eliminating cascading and overlapping windows
  • There is now a Date Picker for Date Fields
  • A "Colourful" theme have been added to the existing themes
  • The search "Tell Me" text box shows suggestions on features or actions
  • Export linked data source information to Excel
  • The default height of the Show Table dialogue has been increased to show more table and query names

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 2016 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 database is automatically encrypted if a database Password is used

The new Microsoft Access 2016 File Types

File Type
The traditional .mdb file, using the Jet 4.0 database, has been replaced by an .accdb file, using the Access Database Engine or ACE.
This is an Access Deployment file, certified with a digital signature.
The compiled version of the .accdb file. It does not allow the user to read or modify the VBA source code.
This is a runtime Access file. Simply change the File Type from .accdb to .accdr to hide the Ribbon and the Navigation pane.
This is a database template file that can be listed on the Access 2016 Getting Started page.
ADP (Access Data Projects) was a method of connecting to a SQL Server database. The ADP file type is now obsolete. It is easier to link to a SQL Server database – and use ADO (or ADO.Net) for data handling.
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.
Access Execute Only Database. Takes the place of the .mde file of previous Access versions.
This file is an Add-In or Library. It can be linked to Access using the Add-In Manager.
This is a Database Wizard file. It can be used to create Databases.