Development [81 post(s)]

30 January 2011 - 13:18

Full outer join in Access

During a course a participant informed about Access' possibility to create a full outer join.
If you've gone into the topic of joins, you might have realised too there isn't really a feature doing that - there's an inner join, a left and right join, but no way in one go to get a full outer join.

Of course I didn't needed more to get my curiosity taking off, and I found a really well written and clarifying article from the hand of Gregory A. Larsen.

His simple device is: if it can't be done in one time, do it in 3 + one time: create the 3 distinct queries that are possible and with a 4th union query you just combine those results.
Cool !

Here's another example from the Northwind Database:

SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers INNER JOIN Employees ON Customers.City = Employees.City


SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers LEFT JOIN Employees ON Customers.City = Employees.City
WHERE (((Employees.City) Is Null))


SELECT Customers.CompanyName, Customers.CustomerID, Employees.LastName, 
Customers.City, Employees.FirstName, Employees.City
FROM Customers RIGHT JOIN Employees ON Customers.City = Employees.City
WHERE (((Customers.City) Is Null))
(1) Comments ·
29 January 2011 - 01:48

Handling multi-selection lists in Access

One of the new features in Access 2007 was the possibility to implement multivalued fields, but that's not always going to help out for various reasons.
So handling data from lists where multiple selections are accepted remains a interesting challenge.

Here are some links I found helpful in implementing a solution for this:

(0) Comments ·
28 April 2010 - 15:23

Error handling and debugging in VBA

If you're after some good explanation and highlights in terms of error handling and debugging with VBA, I would suggest these:

(0) Comments ·
15 April 2010 - 15:21

Commands from prompt, batch files and backups

I was asked recently to do a course on DOS-commands and on RUN-commands, i.e. commands you can type at the run prompt.

And although I scoped it somewhat to the most used and/or basic ones, there are people coming up with huge lists of them:

This course really felt like 'closing a cycle', as my very first IT manual ever was a DOS manual which came along with a Commodore PC - one without even a harddisk, that was, back in 1986.
And speaking of DOS commands of course brings us to batch files.
If ever you want to try some:

Actually I'm using a batch files for my daily backups to external harddisks, with log files on top of it.... All thanks to good old DOS and a double click of a shortcut on my desktop :-)
Here's what I use:

set LogPath=e:\Backups\
set LogFileExt=.txt
set LogFileName=Backup%LogFileExt%
::use set MyLogFile=%date:~4% instead to remove the day of the week
::set MyLogFile=%date%
set MyLogFile=%date%
set MyLogFile=%MyLogFile:/=-%
set MyLogFile=%LogPath%%MyLogFile%_%LogFileName%
::Note that the quotes are REQUIRED around %MyLogFIle% in case it contains a space
If NOT Exist "%MyLogFile%" goto:noseparator
echo.%Date% >>"%MyLogFile%"
echo.%Time% >>"%MyLogFile%"

xcopy d:\data g:\data /d/s/c/h/r/y/f/k >>"%MyLogFile%"
xcopy d:\2013Website g:\2013Website /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy d:\Graphics g:\Graphics /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"
xcopy d:\Images g:\Images /d/s/c/h/r/y/f/k /EXCLUDE:e:\exclude.txt  >>"%MyLogFile%"

xcopy e:\Audio h:\Audio /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\Video h:\Video /d/s/c/h/r/y/f/k  >>"%MyLogFile%"
xcopy e:\VideoAudioTraining h:\VideoAudioTraining /d/s/c/h/r/y/f/k  >>"%MyLogFile%"

(2) Comments ·
12 April 2010 - 04:50

Access development: syntax of sql queries in VBA

One of the toughest things to do when coding VBA in Access development projects is coming to terms with SQL queries.
I've found following tips and tricks helpful :

SQL2VBA form

Allen Browne, one of the main authorities on the matter, proposes a form with VBA code which actually does the hard coding into VBA of any SQL string of your choice.
You'll find the code and procedure at his site:

Another thing you might have ran into is using query parameters into SQL strings. Well you can't basically, you must 'transform' them and here is how: If you run into the error "Queries: Too few parameters, expected n" - that's exactly what I'm referring too here :-).

And this might come in handy too: some basic testing for empty recordsets - as when your SQL query doesn't return records in the recordset, you'll off into problem land as well.
I've found this one useful to solve this issue:

(0) Comments ·
03 April 2010 - 07:14

Interesting code resources during recent Access Projects

Doing lots of database development in Access brought up all kind of interesting insights and links.
Just a selection of some of these encountered goodies :

(0) Comments ·
02 April 2010 - 16:36

Random values between two figures in Access

Ingenious all those solution maths-people come up with.
Take the problem to find a random value between two integers.

Well you take the interval between the two figures you have in mind, add one, then multiply with a random value and add the minimum of the figures you had in mind. Just rendering it as an integer and you're done ! If that sounds too much like maths.... well ... it is :-)

Given that the random function in Access is rnd() ( you obtain a decimal between 0 and 1) you get something like this to obtain a number between 10 and 100

Int ((100 - 10 + 1) * Rnd + 10)
For a full explanation go to
(0) Comments ·
26 March 2010 - 17:22

Corruptions during Access development

During a quite substantial development project in Access recently, I initially started developing in Access 2007. But as downgrading to the Access 2003 version the client used posed all kind of issues, I gave up and continued the development in Access 2003.

So far so good - well I thought, but not till rapidly ran into something I had never experienced. All kind of forms suddenly didn't want to load anymore as they became corrupted.
Randomly, apparently, although frequently linked to removing labels or moving fields with their labels.
And mostly with the laconic error message: "there isn't enough memory to perform this operation" .
It became really a desperating situation.... grrr :-(

I then found a message on which really saved me and pointed me to a method to save a form as a text file (!!!) and then retrieve it again... Although it didn't always work, this and regular (obsessional ;-) ) backups made me swim across this river.

The trick goes as follows. In the VIsual Basic Immediate Windows, use following code to save the form as a text file:
Application.SaveAsText acForm, "YourFormNameHere", "C:\Path\Filename.txt"
Then delete your corrupted form and retrieve it back using following code:
Application.LoadFromText acForm, "NewFormNameHere", "C:\Path\Filename.txt"

If the issue still continues, try doing a Compact and Repair before retrieving the form.
And a further comment on the forum pointing to a possible use of this technique:

If you have to provide documentation for your application you can use this technique to create text files that contain all the information about your forms. Then should anyone modify your form at any point or it becomes corrupt you can restore it from the text file.
You can be really clever and create a support database that would be similar to the MSysObjects table that contains a list of all your objects and store the contents of the text files into a memo field.

To close: you'll find a database corruption repair guide at
And some good tips to prevent corruption at the famous Allen Browns corner:

(0) Comments ·
24 March 2010 - 17:25

Find the cell with the lowest value in an Excel sheet

The beauty of Excel is that there are many ways to achieve the same thing.
Take this topic e.g. : how to find the cell with the lowest value in an Excel sheet?

If you're after the exact location of that cell, and you can limit your search to a specific column or row with results, you could use a combination of the ADDRESS() and the MATCH() function which would give you something like:


Or you might be more picky as to which type of information you'd like to withdraw from that cell containing the minimum value -
it could be the range address, but also it's content, or it's column position, it's formatting or it's locked status. That's something you can do with the CELL() function -
it's first argument determines the type of info you'll get as a result. With our two examples:

to obtain the cell addresses::

to obtain the cell values::

to obtain the cell lock status::

If you need this approach regularly, you could also take the VBA road and insert a module with your own user defined function.
Like the one here found at :

Function MinCellRef(dsRange As Range)
Dim c As Range
Dim MinCell As Single

MinCell = Application.WorksheetFunction.Min(dsRange)

For Each c In dsRange
If c.Value = MinCell Then
MinCellRef = c.Address
Exit Function
End If
Next c
End Function

And if you want some VBA solution which highlights and formats the cell containing the minimum value, that's possible too :

(0) Comments ·
10 November 2009 - 15:13

Connection speed between front-end and back-end in Access

If you're experiencing connection speed issues or weird behaviour between your Access front-end and the back-end system, be it an Access, SQL-server or another database, here are some basic points to check out:

  • Is there a primary key set on all tables in BE?
  • Aren't there too many indexes set on some of the tables used ?
  • Can the number of queries and requests be reduced? Comboboxes & listboxes are a good place to start from.
  • Have main and subforms been linked through the master - child link interface from the form builder ? If so you try making the linking directly through SQL / recordset in the subform data source property
  • Did you try to Compact & Repair?
  • Has the autocorrect feature been disabled?
  • Have all service packs been installed?
  • Did you try it out with materialized tables in stead of (sub) queries?
  • Have you tried to see the difference when you remove subdatasheets feature in tabels?
  • Have you checked latest ODBC driver and/or some configuration options
  • Did you use Option Explicit in the VBA modules?

Just to warm up really :-)
As usual this is just the beginning, you'll find more tips and hints over here:

(6) Comments ·
04 November 2009 - 10:23

Including null values in Access

If ever you've used the 'not' operator (also '<>') in Access you might have realised you'll get unaccepted results if you're not including a specific search for 'null' values.
So always include 'OR Null' as follows:

<> "beverages" OR null

You'll find some screenshots on following site.

For certain expressions where you want to include null values, you bump against issues triggered off by these null values. In that case you can use the Nz () function.
From the Microsoft support site - you 'll find some examples there too.

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

Click here for another example.

And if you're after some VBA code to evaluate for Null values, here you go.

(0) Comments ·
02 November 2009 - 15:38

Refresh ODBC Linked Tables in Access

For an Access development job, I needed some code to make sure the linked tables in the backend should be updated automatically - and if the back-end or front-end had been moved, the users could then be prompted for a new location.

This is the code I used from Stackoverflow, the question and answer site for programmers, where Renaud Bompuis proposed a couple of solutions.

Here is the link : I just had to add some coding to exclude some funny temporary system table which triggered the code into an error and thus debuggin mode.

Function fGetLinkedTables() As Collection 'Returns all linked tables
    Dim collTables As New Collection
    Dim tdf As TableDef, db As Database
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        With tdf
             ' exlude wrong SysTable SYSObjects ~TMPCLP322681
             If Len(.Connect) > 0 And Left$(.Name, 1) <> "~" Then
               If Left$(.Connect, 4) = "ODBC" Then
                'collTables.Add Item:=.Name & ";" & .Connect, KEY:=.Name
                'ODBC Reconnect handled separately
                    collTables.Add Item:=.Name & .Connect, Key:=.Name
                End If
            End If
        End With
    Set fGetLinkedTables = collTables
    Set collTables = Nothing
    Set tdf = Nothing
    Set db = Nothing
End Function
Read on if you want more solutions. [Read more on Refresh ODBC Linked Tables in Access...]
(2) Comments ·

Mail merge with Word and Access 2007 issue

Recently I bumped into weird problem with Office 2007, whereby Word didn't recognize data from an Access database during a mailmerge.
Fairly basic but... no way.

What would we do without the exchange on the internet ?
Peter Jamieson from published the answer on, a free online training platform I consult regularly:

There are several types of query that Word 2007 cannot "see", or does not execute as you might expect, when you try to use them as a Mail Merge data source using the default connection method for Access (which is OLE DB), including:

  • a. parameter queries
  • b. queries that invoke user-defined Access VBA functions
  • c. queries that invoke some of the financial series functions and one or two other "non-user defined" VBA functions such as replace()
  • d. queries that use LIKE and wildcards

In most cases, the quickest way to get around these problems is to change the connection method of the connection to DDE - in Word 2007 - check Office button->Word options->Advanced->Confirm file format conversion on open (near the bottom), go through the connection process again, and you should see an additional Confirm Data Source dialog box. If you see an entry like "MS Access databases via DDE", select it. If you only see "OLE DB database files" (typically if you are using a .accdb rather than a .mdb source), check Show all, then locate and select that DDE option, and continue.

(3) Comments ·
30 October 2009 - 14:11

Datepicker in Access

One of the newnesses in Access 2007 is the fact that a datepicker functionality has been integrated for date/time fields.

If you don't use this version of Access and/or if you're after something with just a bit more functionalities (like an option for easy scrolling through years | months asper the need), then you're still bound to use some VBA coding.
You'll find a nice module with code over here at:

(0) Comments ·
10 October 2009 - 15:35

Customizing the Ribbon in Access 2007 and Office 2007

If you wondered how to customize the ribbons in either Office 2007 or especially also Access 2007, here are some links which could be clarifying.
Ribbons are based on XML now, and so that makes the process more technical then e.g. customizing a toolbar before.



(0) Comments ·
14 September 2009 - 12:07

Shared mode in an Access database - who’s in?

If you're using Access in shared mode, you might be wondering who's currently using the database.

Well this information is actually stored in the *.ldb file (pre Access 2007) or *.laccdb file (Access 2007).
This file automatically opens when you open your Access database in shared mode (this is the default - you can open a database in Exclusive Mode, through the options of the File/Open window) - it manages users and record locking in shared mode.
And you can actually read this file, you only need a more advanced text editor. However, Microsoft offers a special tool, called Jet utilities, containing a LDB viewer which withdraws more exact information about your connected users; you can download it here : It also seems to working under Access 2007.

For any problems with the lockfile, you might go through this article:

And if you want to list users and access through VBA code, this is possible to; for a nice module on this topic go here:

(1) Comments ·
09 September 2009 - 04:39

Default database & templates in Access 2007

When you'd like to create a default database in Access 2007, containing your preferences, settings, tables, forms, modules and so on of your choice, you can do so more or less as before in previous versions of the program.
Basically you'll have to create a new database, save it in the system's or your profiles' Templates folder for Access and just make sure you've made all changes in preferences and imported all objects you want by default.

You'll find the procedure nicely described on the Office Online Section for Access or also at Allen Brown's pages:

templates folder and database in Access

But new in Access and a really cool feature is the possibility to use Database Templates (accdt files). Those Templates files are available from the Access Getting Started Page and are presented in different Template categories in the left navigation area.
To create such Database Templates files you'll need the Access Developer Extension tools (ADE), which is not included in the default install files. You can download this package at the Microsoft Download center.

Once installed you'll find a link on the Office Button at the bottom: Developers / Save As Template - once you've saved your template in one of the dedicated Templates folders (see previous section), your templates become automatically available from the Getting Started window.

(1) Comments ·
07 September 2009 - 04:03

Deploy Trusted Locations in Office 2007 system

If ever you need to deploy Trusted Locations in one of the Office 2007 programs and / or want to understand a bit more about the 'Group Policy Object Editor' here's the official Technet link:

And following discussion might throw some light as well:
(0) Comments ·
09 August 2009 - 15:38

Using wildcards in IIf() statements in Access objects

If you want to use criteria against one or more fields, depending on different scenarios, be it in queries or forms, the most logical step is of course to think in terms of using the IIf() function in Access.

This might be a solution, but it's not always necessary or the best solution. One thing and pitfall is definitely NOT to use any operators like 'Like' or 'Is not null' inside the arguments of your function.


E.g. these are some examples which are NOT going to work if used as Query criteria:

IIf([Forms]![Simple Selector]![OptGrpMemType]=1;"MRD";
IIf([Forms]![Simple Selector]![OptGrpMemType]=2;"NRD";Like "?RD" ))

The last argument has the Like operator

IIf([strPassedFunder] Is Null;Is Not Null;[strPassedFunder])

Is Not Null as a standalone argument is not going to work

IIf (checkbox=true;listbox value; "Like *")

Again the Like operator, moreover in between quotes

IIf([Forms]![frmLookup]![TypeSelect]=1;[Security TypeID]; 

'<>3' is the problem here

[Read more on Using wildcards in IIf() statements in Access objects...]
(0) Comments ·
09 July 2009 - 16:21

How To Extract Flash or SWF from Office files

If ever you're after a nice procedure, here it I've found one:

And the other way round: to embed SWF in Microsoft Office documents: Excel - Word

(0) Comments ·
14 June 2009 - 09:02

Check for duplicate values in Access tables

How to check doubles or duplicate values in Access tables?
Voilà another question which comes up regularly.

To start I'd like to point out to Microsoft Office Online which actually published a good article on this topic : Not only it explains about the use of Access' inbuild Find Duplicates Query Wizard, but it also shows SQL, illustrates different scenarios and clarifies the Unique Records and Unique Values properties.

The famous Allen Brown with his many tips for different users also has a strategy:

The VBA road then.

Here is a good approach to avoid duplicates being entered in a form:

The book ‘Building Access Applications’ by J. Viescas proposes a whole module on the topic of duplicates. In one chapter there's the description of a support database, Membership.mdb with a form ”frmCustomers” haing a vba module to check doubles for multiple fields.
Not that easy perhaps if you're not fluent with VBA programming, but really worth a visit and consideration. John Viescas developed a Public Function fctCheckSaved() called through an event onFormUnload – the function calls for another function soundex() which even checks for names with similar sound or pronunciation, but which can be omitted. The essence of this modules follows here as an example but you can download the database with the functions from his site.or see more code on MSDN

[Read more on Check for duplicate values in Access tables...]
(0) Comments ·
27 February 2009 - 16:26

Excel 2007: the xlsb format? And formats basically…

Have you heard already from the xlsb format?

In case I made you wonder: the 'b' stands for 'binary' and here are two links explaining a bit more about this new file format in Excel 2007:

And here is a very nice scheme and article explaining how to 'decode' the excel file formats:

file formats in Excel
(0) Comments ·
20 February 2009 - 12:48

Multiple Substitute in Excel

During my excel courses I generally explain about the substitute() function.
But what if you wanted to change many characters, like e.g. many accented characters to their non-accented equivalents?

You could of course nest many substitute() functions and end up with something like this.

But here you'd probably best implement the use of a socalled user defined function, a 'udf'. Not sure where I found this one ...

Function BigSubstitute(CellToChange As Range, _
NameNumber As String) As String

  Dim X As Long
  Dim Data() As String
  Data = Split(NameNumber, ",")

  If (UBound(Data) + 1) Mod 2 Then
      BigSubstitute = "#MISMATCH!"
      Exit Function
      BigSubstitute = CellToChange.Value
      For X = 0 To UBound(Data) Step 2
      BigSubstitute = Replace(BigSubstitute, Data(X), _
      Data(X + 1), , , vbTextCompare)
   End If
End Function

So you'll end up with something like this:


P.S. The mismatch in the function is used to make sure you always have couples of data, in this case an accented and non-accented character

(2) Comments ·
03 February 2009 - 07:36

Good old DOS

One of the nice things of the Windows platform is that it still runs under DOS - so to say of course -:)
Point is that you're still able to call for the command prompt window (windows key + r, or start / run, then type cmd and you're in). With a bit of prehistoric DOS knowledge you can then still do wonders.

I often need to prepare course materials - in the case of a Dreamweaver CS3 course that implies also copying those materials onto all the participants' file and/or webservers. So why not use a batch file (*.bat file, i.e. simple text file with *.bat extension) which contains several DOS copy commands and can be executed from the command prompt window doing the whole thing in one go?

the good old DOS window

Following nostalgy does the trick:

xcopy Z:\Empty\Docs\*  Z:\Student1\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student2\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student3\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student4\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student5\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Student6\Docs\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\Docs\*  Z:\Teacher\Docs\* /E /H /I /K /Q /V /Y

xcopy Z:\Empty\CSS\*  Z:\Student1\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student2\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student3\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student4\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student5\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Student6\CSS\* /E /H /I /K /Q /V /Y
xcopy Z:\Empty\CSS\*  Z:\Teacher\CSS\* /E /H /I /K /Q /V /Y

In case you're wondering about all the parameters or switches at the end: this seems a good link.

(0) Comments ·
30 January 2009 - 15:50

Refreshing an Excel worksheet when cell content changes

Recently I sat with a chess player who had found his way through table and pivot table functionalities in Excel in order to sort, filter and display chess results for his chess association.

His next step actually consisted in automating some of his sheets presenting the chess results. One thing he wanted to achieve is an automatic sorting of the table with the main results along with the automatic update of the pivot table based on those results. So far the operation consisted out of manual resorting of the data based on 3 or 4 fields, along as clicking the refresh button in the pivot table options.

The solution here consisted out of 3 steps.

Firstly, of course a simple macro could be made to trigger of the sorting instantly. However as the results change all the time during a chess happening, the macro ideally would have to be triggered off by each entering or updating of the results in the sheet. Actually this second step is possible by using some embedded Excel VBA worksheet events. Worksheet events, just as workbook events, or a kind of prefixed or pre-named procedures which you call in the module sheet behind the worksheet object in the VBA editor:

worksheet events

Amidst the possibilities are following triggers:

Private Sub Worksheet_Activate() - when the worksheet is activated
Private Sub Worksheet_Calculate() - when the worksheet is recalculated
Private Sub Worksheet_SelectionChange(ByVal Target As Range) - when the selection is changed
Private Sub Worksheet_Change(ByVal Target As Range) - when a change is made to a cell in the worksheet

It's the last one which offers the possibility to solve the riddle here; following code triggers off the macro doing the sorting to the table whenever a cell value is updated or entered:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     mkSortCK 'macro which is being called
End Sub

Finally a simular worksheet event construction offers the possibility to refresh the pivottable automatically, when you activate the sheet it's located in:

Private Sub Worksheet_Activate()
End Sub
(0) Comments ·
01 October 2008 - 10:11

programming resources for Access




SQL Server

Some Books

(2) Comments ·
Page 1 of 1 pages