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…

#LateBind Constant

In standard code, we make use of #LateBind conditional compilation constant, which is typically defined via project’s Conditional Compilation Argument in the dialog: NOTE: The dialog can be found via Tools → <project name> Properties, which is located below the Options menu item. The menu item caption will be different as it includes the project…

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,…

Pivoting a dynamic set

SQL Server does not allow for a dynamic set, which can be problematic if you need to pivot based on the data that comes from rows and you can have various values in the column. In general, you need a stored procedure and dynamic SQL in order to handle the dynamic pivots. This requires uses…

Access NZ() function to SQL Server equivalent

What is the SQL Server equivalent to Access NZ() function? Access SQL NZ([Event Timestamp]) SQL Server Equivalent COALESCE([Event Timestamp],0) ISNULL([Event Timestamp],0) * do not confuse this with the Access isnull() function.

Use controls, not fields

Sometimes we need to use fields from a RecordSource that aren’t shown on the form. It is legal to reference them directly even if they don’t have a control. For example, we can have a form bound to a RecordSource like so:

SELECT
  c.CompanyID,
  c.CompanyName
FROM Companies;

SELECT

c.CompanyID,

c.CompanyName

FROM Companies;

And only display CompanyName in a textbox but not show the CompanyID….

Using Custom Functions in Calculated Controls

Custom functions work the same way as MS Access built-in functions such as DateAdd, DatePart and DSum, but are instead created ourselves as database developers. We do this by creating a public function with the VBA programming language and save it inside a global module within the database. Today I am going to explain how custom functions can be…

Creating a Make Table Query

Before we create the Make Table Query, you might like to create a new blank database file. This is so we have a separate database into which we can paste the new table from the query. In this exercise I have called it HistoricData.accdb. Here is the procedure to create a Make Table Query: Open…

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…

Designing a lookup table with logic

It is very common for us to embed some kind of logic based on a lookup table or in some cases, a column of a table. Because we have application logic connected to it, the code are fragile and subject to changes as the requirements develops. We want to avoid this situation where we might…