Microsoft Access Blog

Use TryGet pattern for transient values

A number of objects may have extra properties or something where it might not exist for all instances. A good example might be finding Controlsource property on an Access.Control variable:

For Each ctl In Me.Controls
  Debug.Print ctl.ControlSource 'A potential error
Next

For Each ctl In Me.Controls

Debug.Print ctl.ControlSource 'A potential error

Not all controls have a ControlSource property and thus can fail to run. A common approach is to use OERN:

For Each ctl In Me.Controls
  On Error Resume Next
  Debug.Print ctl.ControlSource 'A potential error
  On Error GoTo 0
Next

For Each ctl In Me.Controls

On Error Resume Next

Debug.Print ctl.ControlSource 'A potential error

On Error GoTo 0

This will work…

Access Web Apps to be retired

It was announced that Access Services is going to be retired. Details of the announcement may be found here: Updating the Access Services in SharePoint Roadmap. Additional timeline information along with possible methods for exporting your data out of an Access web app may be found here: Access web apps no longer supported. While these articles…

Use properties instead of constant or magic numbers

A common programming mistake is to hard-code literals that corresponds to some property. That is particularly true with certain controls like tab controls & pages. The tab control exposes a Value property that indicates the current tab and the pages contains a PageIndex property that indicates its position in the tabs of the control. Thus,…

Enable and Disable a Form Control using VBA

I was working on a design for an Order Management Database, and one of the tasks I dealt with involved dynamically Enabling or Disabling one of the form’s Command Buttons’ using VBA. This gave me the idea for the present Access tip. The command button was located on a Customer Details form which had an Orders…

Access UPDATE query to SQL Server

How do I convert an Access UPDATE query to SQL Server? Access SQL UPDATE Cars INNER JOIN UpdateList ON Cars.Parts = UpdateList.Parts SET Cars.Price = [Updatelist].[price] SQL Server UPDATE Cars SET Cars.Price = [Updatelist].[price] FROM Cars INNER JOIN UpdateList ON Cars.Parts = UpdateList.Parts

Access 2010: Unrecognized Database Format

I’ve been seeing more and more people having problems with the Unrecognized Database Format when creating or modifying an Access 2007 (accdb) format database in Access 2010 and then trying to open it again in Access 2007. The issue appears to be that Access 2010 does not have its own file format. When you add a…

Using Microsoft Access Data in a Mail Merge

Suppose you have an Access Database containing the names and addresses of your customers. Every now and again you may want or need to send a standard letter to each customer who has opted in to such mailings. However, you are aware that these letters are going to look so much better, and work so…

Access IIf() function to SQL Server equivalent

What is the SQL Server equivalent to Access IIf() function? Access SQL IIf([ResultValue]>=1, [Result Value], Null) SQL Server CASE WHEN [Result Value]>=1 THEN [Result Value] ELSE NULL END