Generate Nested IIF SQL using VBA code

I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought it was obvious that this would be better accomplished joining the conversion table in the query editor and moving on from there, particularly because MS Access has a limit on the number nested IIFs allowed in a single statement. Nonetheless I publish it here as it may prove useful.

Here I create a table T001CodeConversionTable that holds the translation from one set of codes to another. This field also has to have several fields in it named

The variables TargetTable and TargetFieldforUpdate exist in the table that will have the resultant SQL performed on it.

Public Function CreateNestedIF(TargetTable As Variant, TargetFieldforUpdate As Variant)

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim RecordCount1 As Long
Dim RecordCount2 As Long

Set rst = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst2 = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst3 = CurrentDb.OpenRecordset("T001CodeConversionTable")

RecordCount1 = rst.RecordCount
RecordCount2 = rst3.RecordCount

Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("C:\Users\Mark\Documents\NestedIFs.txt", True)
TextFile.WriteLine ("UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=")
Do Until rst.EOF = True
RecordCount1 = RecordCount1 - 1
TextFile.WriteLine ("IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'")
If RecordCount1 = 0 Then
TextFile.WriteLine ("")
TextFile.WriteLine (",")
End If


Do Until rst2.EOF = True
TextFile.WriteLine (")")
TextFile.WriteLine ("WHERE ((")


Do Until rst3.EOF = True
RecordCount2 = RecordCount2 - 1
TextFile.WriteLine ("(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'")
If RecordCount2 = 0 Then
TextFile.WriteLine ("")
TextFile.WriteLine ("OR")
End If


TextFile.WriteLine ("));")


MsgBox "Created NestedIFs File in C drive"

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Generate Nested IIF SQL using VBA code

Creation of SITE History from Planning Application Polygons using QGIS

In planning it is important to know the planning history on a site. The status and likelihood of approved permission will often relate to previous permissions. Many council planning systems do not specifically relate planning applications to each other and there may be situations where you would like to create such links. This is essentially an excercise in using spatial analysis to create the junction table to hold what are many to many relationships.

If your datasets are in any way large you will need to set aside a computer so that it can perform the calculations. When I first tried this the process took a weekend with queries running overnight.

Start by obtaining as many years of planning application polygons as you can. Here I use polygon files in shape format.

The polygon file or shape file should be in one file so if you need to merge the shape files you have together. I did this and the file I ended up with was


Next – Delete all attribute fields EXCEPT the planning application number.

Next – Create a centroids file from AllPlanningApplications.shp I called mine

The next series of iterations are about getting a unique set of polygons with which we can go forward and generate a set of SITEPKIDS that can be attached to the child records.

Step – Using AllPlanningApplications.shp ADD an additional field called area and populate it using QGIS $area calculation – save this file.

Step – this is where it becomes interesting – in most authorities there are a vast number of planning application boundaries that overlap. Performing a dissolve at this point would result in a large congealed set of polygons that could not clearly identify unique sites. Thus buffering the polygons down we can start to identify unique sites. This is particularly important where boundaries are completely contiguous to each other.

sites the buffering command is used within the geometry tools to try to separate adjacent overlapping and contiguous polygons.

Step ‐ Create two files from the AllPlanningApplications.shp one for polygons less than 4500 metres squared and one for more than or equal to 4500 metres squared. This is to allow for two differing buffering processing to be performed on each.



Now the 4500 is an empirical figure that was subjectively chosen there may be a better figure feel free to investigate.

The following 2 steps also introduce empirical figures for the buffering that can be altered as appropriate.

Step ‐ Take the file AllSmallLessthan4500PlanningApplications.shp and create a buffer polygon file of this with

boundaries of less than 2m lets call it


Step ‐ Take the file AllLargeGreatethanequal4500PlanningApplications.shp and create a buffer polygon file with

boundaries of less than 20m lets call it



Step ‐ Perform dissolves on both of these new files ensuring that dissolve all is used names could be something like

Vector / Geoprocessing Tools / Dissolve /

Set input layer alternatively to the two above files and set Dissolve field to dissolve all.

Suggested file Names are



Step You should now have two shape files of a large multipart polygon you want to perform the multipart to single part operation now

Vector / Geometry Tools / Multipart to Single Part

Processing involved with this is typically quick and suggested names for these files are



Add area column and identify the largest polygon on the small files

Add area column and identify the smallest polygon are on the large files you may want to remember this.

Step ‐ perform merge on these two files to get

Vector / Data Management Tools / Merge


ONGOING investigation ‐ would Difference be better than dissolve on this and should the above files be put together before

Step ‐ perform dissolve

Vector / GeoprocessingTools / Dissolve

ensure that ‐‐Dissolve all‐‐ is selected


Step now you want to split mutlipart to single


Step add field called SitePKID and populate it using $rownum command.


Vector / Data Management Tools / Join Attributes by Location

Set Target Vector Layer as


Set Join Vector Layer as


Ensure that Keep all records (including non‐matching target records are kept)

Output Shapefile suggestions


If there are centroids without Site PKIDs put them to the end and give them consecutive unique row numbers. The attribute file associated with AllPlanningApplicationsCentroidswithSitePKID.shp should now be a child table of the shape file DistinctPolygonsAllPlanningApplications.shp perform checks here to see if all centroids within a polygon defined by the distinct polygons have the same SitePKID and that it is matched by the SitePKID of the Parent shape file.

You should be able to do a join on the this file to get the PKID back into the very original file.


Finally perform a dissolve on the corrected AllPlanningApplications.shp file but this time dissolve on the field


You can call this



Posted in Continual Professional Development, Digital Mapping, Geographical Information Systems, GIS, QGIS 2.8.1, QGIS General | Comments Off on Creation of SITE History from Planning Application Polygons using QGIS

MS Access like development environments for the Web – 3 alternatives

So you would like to construct simple applications that you can at the moment create in MS Access but you want to do it on the web. By that I mean you would like to create a data driven application with somewhat complicated forms that can be accessed by anyone through either IE or Chrome anywhere in the world with a simple login screen at the front to prevent simply anyone accessing the applications collecting the information into a database. What are your options for programs that will assist you in a MS Access like environment rather than going the full IDE deep dive – Visual Studio route?

From my experience the unicorn of access on the web is slowly coming to fruition BUT for the vast majority of people with a budget similar to that for MS Access – lets say £200 a year for unlimited applications there is simply nothing which is quite as easy and powerful as MS Access. Some are pretty close but simply not as stable and require typically several magnitudes greater amount of configuration. WYSIWYG design isn’t quite as WYSIWYG and stability is a few orders lower than the desktop.

What you are probably looking at can typically be described as either RAD tools for the Web, a Low Coding Platform or something called a Code Generator any of those phrases can be useful for Google.

Assuming you don’t have your own servers whatever you do you will need to spend money on a web host.

The minimum this is likely to cost you is in the region of $15 a month. If you don’t want to spend the next 6 months learning about the insides and outsides of frameworks then I would suggest you go to one of the below three providers who all provide complete environments set up and ready to go with their particular generators pre-installed. This is good value it is extremely difficult to beat these guys on cloud hosting costs and unless you are very advanced and have very particular requirements its a waste of time to try. All three of the below providers will allow you to create limitless number of applications albeit you are limited by the space you hire on your web server. Similarly distribution will be limited by the quality of web server you sign up for. In all likelihood if you have few users it is unlikely that the coding front ends of your applications will be a limit to the number you create more likely the size of databases you are attaching them to and the shear time you have available to create applications.

I have a cloud hosted provision with Nubuilder Pro – I pay a monthly amount and I can create as many applications as I can fit within my space ( which is more than I can use even on the lowest version ). As at February 2018 Nubuilder Pro doesn’t seem to be available to sign up to although I signed up before it was removed and still have access. The developers of Nubuilder Pro are at February 2018 in process of releasing the next version of Nubuilder – Nubuilder Forte I would expect they will set up an environment that people can sign up to at some point in the future. Other providers have paid environments that you can sign up for now. At February 2018 you may need to build and configure a server for yourself if you want to use Nubuilder forte. Otherwise it would be worth paying approaching Nubuilder or going to one of the other providers. For people starting out having a pre built environment is probably recommended.

Be warned n-tier web applications do not play as friendly as the desktop you WILL be slower to construct applications than you are on the desktop, getting into it WILL take time and a bit of commitment, you WILL have far less flexibility regards coding, there WILL be less people about to ask questions and there is far far less WYSIWYG design capabilities, error trapping is poor and errors are far more likely to be fatal and the really big warning is that on release of new web frameworks you may not necessarily be able to update without a full site re-design (A fact that comes as a nasty surprise to many CIOs and Project Managers when they realise that they are locked into front end system replacements every 4 or 5 years ) Know how to get data to your local environment out of the back end and accept that the front end is ephemeral and not likely to last in the same way as your desktop applications. (Your database will last but don’t expect to be running it through the same front end ten years from now). Accept that you will now have monthly or annual rental fees for cloud provision.

That said the design of these items is significantly faster than its ever been.

Scriptcase and PHPRunner have free downloads that are good for getting started.

Commit to one and go for it. – I’ve got both PHP and ASP.NET solutions.. Nubuilder only connects to MySQL whereas Scriptcase and ASPRunner.NET connect to pretty much any database. I started with Nubuilder and am using as well because importantly it connects to SQL Server and in particular I can host everything in MS Azure. I find Nubuilder Pro really conceptually elegant which despite its rather drab looks is incredibly flexible. I would probably be able to change it’s look if I was prepared to get my own server and install everything on it myself. That is not something I have time to do at present.
Nubuilder hosts its IDE in the browser. is more traditional in that you have a program running on a desktop and then you publish your application up to the web server this has the advantage that you get to see the plumbing in the background which makes backup of the site easier but publishing slightly harder.

Zoho Creator / Alpha 5 / Outsystems hold your hand even more but as a result are even more proprietary and won’t fit in that budget of £200 per year ( by quite a long way!)

Nubuilder Forte Link

Scriptcase Link

ASP Runner – PHPRunner and ASPRunner.Net Link

Good luck

Posted in All, Code Generators, Continual Professional Development, Database Design, Dev Ops, dot NET framework, General Opinion, Learning, Low Coding Platforms, MS Access, Personal Development, RAD Web Tools | Comments Off on MS Access like development environments for the Web – 3 alternatives

Upload XLS information to a specific table or new table within an SQL Azure Database

I have started experimenting with Microsoft Azure if you haven’t already you can get a free experimentation account here;

Microsoft Azure Trial account with £125 credit

This gets your registered on Microsoft’s cloud and after a free trial period you will be able to continue with a Pay as You go Account which depending on the services that you go for can start at very cheap rates.

In order for this to work you will need the following
1) Microsoft Azure account
2) An SQL Azure Database
3) SQL Server Management Studio downloaded and installed on the machine you will be uploading from, this can be obtained from SSMS download link be warned its over 800mb. Here I use SSMS 2016
4) Know your server name this is generally [Yourname]
5) Login and Password (I use SQL Server Authentication)

Testing things out I have been using the Web Apps Service to run a website and connect to an SQL Azure Database – both on the cheapest options.

What makes the website particularly cheap is that it can be stopped and started and by paying for it by the minute you can really get a powerful demonstration sites up and running and stop them immediately after the demonstration for very little money.

So after having created an Azure SQL database (Microsoft Create Azure Database Tutorial)I wanted to get a decent number of records into it. Which would be the starting position when taking on most work.
Here I use the Lichfield Planning Application information previously referred to in this post QGIS Import. What I did was take the 45,000 records of planning applications from the shape file. I did this by opening up the dbf file of the shape collection in Excel 2003 and then saving it in excel format. This will be used later to import into the database.

Having your excel file ready the next step is to open up SQL Server Management Studio and connect to your Azure Database. The parameters with regard to username and servername will have been setup when you created your Azure database it is important that when you create your Azure database you somehow record these details.

Next highlight the database – in my case this was DB001 and right click to get tasks.

At this point you enter the import wizard windows dialog boxes and having passed the opening welcome screen , a screen that can be turned off for subsequent navigations, you should hit your first screen that allows you to define the format of the file that should be imported.

The next step is about the only one that is slightly confusing – you are given a number of different options for the target – for me SQL Server Native Client 11.0 worked for me.

Now using the previous parameters specific to your database server and your database name complete the next dialog.

The next dialog asks you whether you want to copy all information or want to write a query to filter the information to be imported. For my example I chose the all import item. Here I select the database and then I am able to see the from and too destinations.

If you wish to import into an existing table use the drop down to select tables from the database – if you wish to import into a new table you can type in the name of the new table within the square brackets.

Here I create a new table called T010Test and import and then continue through the import wizard dialogs until on completion of import you should see a similar screen to that below. It is possible to go into the edit mappings if you are copying into a table that already exists. This will give you a preview showing to what extent the mapping will be successful and how the fields map. You may wish to alter the names of columns to match your target table at this point.

Posted in All, Dev Ops, SQL Azure, SQL Server, SSMS | Comments Off on Upload XLS information to a specific table or new table within an SQL Azure Database

Microsoft Azure – moving to the cloud

I must confess I do love MS Access and I have a number of projects that I suspect will always remain as are – these are ones that are highly complex but very personal that I really don’t need to share. Even some projects that because of their sensitivity I would never want to share but despite the difficulty of designing really user friendly user interfaces on the web the ability to distribute your applications to everyone in the world is a very powerful attractor which is quite clearly going to be a game changer.

I have therefore started to experiment with data driven online applications and so far I am impressed.

To get started you will first want to sign up with an Azure account.
Azure Portal Sign In

I can see in the future I will probably be using Access as a desktop platform for writing queries and personal content curation with SQL Azure for projects where I need to communicate with others linked through the apps service in the Azure portal.

Posted in All, Dev Ops, SQL Azure | Comments Off on Microsoft Azure – moving to the cloud

Microsoft shows MS Access some Love

Although MS Access had been upgraded with each new version of Office. The desktop side of the product had appeared to have lacked development and sometimes suffered from some strange marketing decisions. In particular its omission in 2014 from some of the Office 365 subscriptions.

On Friday the 4th of November Microsoft announced that it would be included in the Office 365 Business and Business Premium subscriptions.

MS Access Office 365 announcement

Not only that but it would appear that Microsoft seem to be actively embracing a more visible development strategy for the product. To be fair for many years they have had regular summits at headquarters with super users to allow them to give feedback on product.

They now have a suggestion box that gives some indication of how they are collecting information on what developments they should be working on and are working on in the future
MS Access Suggestion Box

Posted in All, MS Access | Comments Off on Microsoft shows MS Access some Love

QGIS – Import shape file into PostGIS Table

The following uses
QGIS 2.14.2 Essen and
PostGres 9.5

A number of local authorities have released information through the UK’s data government site. The following example uses a shape file obtained from Lichfield District Council – At 2nd of October 2016 this was available for download from the following link

Lichfield Planning Applications

Open up QGIS and add Lichfield’s planning application shape file

Now scan along the top menu and go to Database

Select the sub menu DB Manager and then DB Manager


The following windows dialog should appear


Expand the area on the left named PostGIS – any PostGIS instances that you have created should be visisble here. Note you will have to have the PostGIS server running. Then highlight the actual instance that would like to import information into.

In this case I use the instance LocalPostGres


Choose the third icon from the left.

It should be noted that the window on the right may or may not show the correct connection to the database on the right.


Name the table you wish to create and then hit OK – additional parameters are available.
There will be a delay before a confirmation of successful import happens – try to not issue commands during this time – once confirmation has been received go back into the PostGIS option and add the layer.

Posted in All, Geographical Information Systems, GIS, PostGIS, Postgres, QGIS General | Comments Off on QGIS – Import shape file into PostGIS Table

Manipulating MS Word Documents from MS Access 2003

The following code generates separate word documents for each parent record in a table called T001ParentRecords and places the children records relating to the parent record in a word document. It then goes on to format that word document before saving and closing and then moving to the next document and starting the process again.

As such it takes the code relating to looping through recordsets and also the code relating to generating word documents and combines the two. This could be very good for automatically generating whole host of different things.

It uses the WEND statement rather than the Do Until Loop as I was told it was better practice.

Function AutoGenerateParentChildWordDocuments()

'Make sure the name of the recordset is unambigous
'Good practice to reference the actual library
'Please ensure that you go to Tools - Refererences and select Microsoft Word 11 0 Object Library

Dim rs As DAO.Recordset
Dim db As DAO.Database

Dim rschild As DAO.Recordset

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set db = CurrentDb
'Place your SQL for parent records to be created
Set rs = db.OpenRecordset("SELECT * FROM T001ParentRecords")

If Not (rs.EOF And rs.BOF) Then
'There are no records if EOF and BOF are both true you are at the end and at the beginning

While (Not rs.EOF)
Set wrdApp = CreateObject("Word.Application")

'Create the new document
Set wrdDoc = wrdApp.Documents.Add
'The following line can be altered to open the document on the screen
wrdApp.Visible = False
'Next setup the margins of the document
wrdDoc.PageSetup.LeftMargin = CentimetersToPoints(1.27)
wrdDoc.PageSetup.RightMargin = CentimetersToPoints(1.27)
wrdDoc.PageSetup.TopMargin = CentimetersToPoints(1.27)
wrdDoc.PageSetup.BottomMargin = CentimetersToPoints(1.27)

With wrdDoc

.Styles(wdStyleHeading1).Font.Name = "Algerian"
.Styles(wdStyleHeading1).Font.Size = 14
.Styles(wdStyleHeading1).Font.Bold = True
.Styles(wdStyleHeading1).Font.Color = wdColorBlack

.Styles(wdStyleHeading3).Font.Name = "Courier"
.Styles(wdStyleHeading3).Font.Size = 12
.Styles(wdStyleHeading3).Font.Bold = False
.Styles(wdStyleHeading3).Font.Color = wdColorBlack
.Styles(wdStyleHeading3).NoSpaceBetweenParagraphsOfSameStyle = True
.Styles(wdStyleHeading3).ParagraphFormat.Alignment = wdAlignParagraphJustify

.Styles(wdStyleHeading2).Font.Name = "Arial"
.Styles(wdStyleHeading2).Font.Size = 12
.Styles(wdStyleHeading2).Font.Bold = True
.Styles(wdStyleHeading2).Font.Color = wdColorRed
.Styles(wdStyleHeading2).NoSpaceBetweenParagraphsOfSameStyle = True
.Styles(wdStyleHeading2).ParagraphFormat.Alignment = wdAlignParagraphJustify

.Styles(wdStyleNormal).Font.Name = "Arial"
.Styles(wdStyleNormal).Font.Size = 10
.Styles(wdStyleNormal).Font.Color = wdColorBlue

'Better to set style before insert
.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading1)
.Content.InsertAfter ("Sitename:" & rs!Sitename)

.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading3)
.Content.InsertAfter ("Town:" & rs!Town)

.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading3)
.Content.InsertAfter ("Postcode:" & rs!Postcode)

Set rschild = db.OpenRecordset("SELECT * FROM T002ChildRecords WHERE FKID = " & rs!PKID)

If Not (rschild.EOF And rschild.BOF) Then
'There are no records if EOF and BOF are both true you are at the end and at the beginning


While (Not rschild.EOF)

'Again better to set style before insert
.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading1)
.Content.InsertAfter ("Consulting Body:" & rschild!Body)

.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading2)
.Content.InsertAfter ("Consultation response : " & rschild!Comment)

.Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleNormal)
.Content.InsertAfter ("Consultation Date: " & rschild!DateUpdated)

End If


.SaveAs ("c:\temp\Auto-Generated-WordDoc-" & rs!Town & rs!PKID & ".doc")
.Close ' close the document

End With ' With wrdDoc
Set wrdDoc = Nothing

wrdApp.Quit ' close the Word application
Set wrdApp = Nothing



MsgBox "No Records Available for updating exit sub"
Exit Function
End If

MsgBox "Looped through the records and updated the value number field"

Set rschild = Nothing
Set rs = Nothing
Set db = Nothing

End Function

Download an example database HERE

Boilerplate code demonstrating simple Recordset manipulation

Posted in All, VBA Code MS Access | Comments Off on Manipulating MS Word Documents from MS Access 2003

Notepad ++ / AstroGrep / Autohotkey – 3 Useful Tools

Three useful tools for speeding up or automating tasks

Notepad ++
Text editor that has formating for programming – I often use it for editing XML documents and writing VB scripts.
Notepad ++ link

Son of GREP – useful Text searcher particularly useful for identifying parameters within web files within applications. When you need to customize a web page
Astro Grep link

Automation of tasks program that allows creation of executables that can navigate browsers really very powerful
AutoHotKey link

Posted in All, Personal Development, Problem Solving, Useful Links | Comments Off on Notepad ++ / AstroGrep / Autohotkey – 3 Useful Tools

Links to VBA Functions

Allen Browne Functions Index

Technet list of functions

Posted in All, MS Access, VBA Code MS Access | Comments Off on Links to VBA Functions

What’s the difference between Sub Routines and Functions

I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages?

Functions return a value that is stored whereas subs don’t. The main difference is not only the return value, it seems that subs are faster than functions (at least in .net) because the MSIL code of subs is much shorter when no value is returned. so overall subs are faster when no value is returned.

MSIL stands for Microsoft Intermediate Language – which is the a programming language that has been standardized later as the Common Intermediate Language

Functions vs Sub Routines

Posted in All, MS Access, VBA Code MS Access | Comments Off on What’s the difference between Sub Routines and Functions

Typical While Loop VBA

Function TypicalWhileLoop()
'This performs the same as next loop but uses the while loop

Dim LCounter As Integer
LCounter = 1

While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical While Loop VBA

Typical For Next Loop

Function TypicalForNextExample()
'This performs the same as the while loop but uses for next

Dim i As Integer

For i = 1 To 9
MsgBox (i)
Next i

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical For Next Loop

Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset()

'Make sure the name of the recordset is unambiguous
'Good practice to reference the actual library

Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM T001Main where T001Main.ValueNumber = 0")

'the data source can be a Table Name a query name or an sql string
'it would be possible to change the SQL to set to another set of records
'Check to see if there are any records in the set

If Not (rs.EOF And rs.BOF) Then
'there are no records if End of File and beginning of file are both true


Do Until rs.EOF = True
rs!ValueNumber = 300
MsgBox "No Records available for updating exit sub"
Exit Function
End If
MsgBox "Looped through the records and updated ValueNumber field"

Set rs = Nothing
Set db = Nothing

'libraries for DAO can be found on AllenBrowne site
'remember to break an infinite loop press ctrl + break

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical DAO.Recordset VBA for looping through and altering

VBS – Pieces of code

Shutdown computer
Option Explicit
Dim oShell
Set oShell = Wscript.CreateObject("Wscript.Shell")
oShell.Run "SHUTDOWN -T 60 -S" 'wait 60 seconds before shutting down

Trigger speech
Option Explicit
Dim speechobject
set speechobject=createobject("sapi.spvoice")
speechobject.speak "Your system is setup and ready for your day"

Delay for Seconds (10)
Option Explicit
Dim dteWait
dteWait = DateAdd("s", 10, Now())
Do Until (Now() > dteWait)

Sleep Function for Delay – Count in Milliseconds (5 mins below)
WScript.Sleep 5*60*1000

Posted in All, Configuration, VBS Scripts | Comments Off on VBS – Pieces of code

MS Access VBA Function – Count Numbers of Records in Tables and list.

Not quite finished yet but place here for later correction.

Public Function CountAllTablesRows()

Dim rs As New ADODB.Recordset
Dim rsRC As New ADODB.Recordset
Dim strTbName As String
Dim lngRowCount As Long
Dim tbl As TableDef
CurrentProject.Connection.Execute "Delete from TABLE_INFO"
rs.Open "TABLE_INFO", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
For Each tbl In CurrentDb.TableDefs
Select Case Left(tbl.Name, 4)
Case "mSys"
Case Else
rsRC.Open "Select count(*) as The_Count from [" & tbl.Name & "]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
rs.Fields("TBL_NAME") = tbl.Name
rs.Fields("TBL_ROWCOUNT") = rsRC.Fields("The_Count")
Set rsRC = Nothing
'Debug.Print tbl.Name
End Select
Set rs = Nothing

MsgBox "Counted Numbers in Table"

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Count Numbers of Records in Tables and list.

Using VBA to write Word Document

Writing to Microsoft Word

Firstly a warning – this creates doc documents that can be opened in Word 2010 but are strictly speaking 03 iterations hence the doc suffix

First need to load in the library for Microsoft Word (this is 2003 version)


Then you are free to open and manipulate the items in Microsoft word..

Private Sub Command_Click()
On Error GoTo Err_Command_Click

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set wrdApp = CreateObject("Word.Application")
Set wrdDoc = wrdApp.Documents.Add ' create a new document
wrdApp.Visible = True
‘this line can be altered to not open the document on the screen

With wrdDoc

With .Styles(wdStyleHeading1).Font
.Name = "Arial"
.Size = 16
.Bold = True
.Color = wdColorBlack
End With
With .Styles(wdStyleHeading2).Font
.Name = "Arial"
.Size = 12
.Bold = True
.Color = wdColorBlack
End With
With .Styles(wdStyleNormal).Font
.Name = "Arial"
.Size = 10
.Color = wdColorBlack
End With

.Content.ParagraphFormat.LineSpacingRule = wdLineSpaceExactly
.Content.ParagraphFormat.LineSpacing = 10

.Range(0).Style = .Styles(wdStyleHeading1)
.Content.InsertAfter "ThIS SHOULD BE HEADING1"

.Range(.Characters.Count - 1).Style = .Styles(wdStyleHeading2)
.Content.InsertAfter "THIS SHOULD BE HEADING2"

.Range(.Characters.Count - 1).Style = .Styles(wdStyleNormal)
.Content.InsertAfter "THIS SHOULD BE NORMAL"

.SaveAs ("C:\CreatedWordDoc.doc")
.Close ' close the document
End With ' With wrdDoc

wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing

Exit Sub

MsgBox Err.Description
Resume Exit_Command5_Click

An article on libraries specifically related to MS Access is available here

Posted in All, MS Access, VBA Code MS Access | Comments Off on Using VBA to write Word Document

Using VBA and Databases to create HTML

Here’s some code I used to generate HTML for a web configuration file. It takes a database (the current open one) then looks to a query called QueryTargetInformation and places the fields – PlaceName / EastingMn / NorthingMn / EastingMx / NorthingMx in a HTML Structure and creates a file called CodeGeneratedHTML.txt place it on the C drive.

I put around 1,000 repeated links in HTML configuration file using this.

This was for a web mapping application – the eastings and northings were obtained from Ordnance Survey Open Source shape files from Ordnance Survey and then QGIS to get the eastings and northings of a variety of locations. These were transferred into the relevant columns of a database and this code triggered from the onclick event of a form command.

Private Sub Command_Click()
On Error GoTo Err_Command_Click

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("QueryTargetInformation")
Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("c:\CodeGeneratedHTML.txt", True)
Do Until rst.EOF = True
TextFile.WriteLine ("")
TextFile.WriteLine (" ")
TextFile.WriteLine (" " & rst!EastingMn & "")
TextFile.WriteLine (" " & rst!NorthingMn & "")
TextFile.WriteLine ("
TextFile.WriteLine (" ")
TextFile.WriteLine (" " & rst!EastingMx & "")
TextFile.WriteLine (" " & rst!NorthingMx & "")
TextFile.WriteLine ("
TextFile.WriteLine ("

MsgBox "Created CodeGeneratedHTML File in C drive"

Exit Sub

MsgBox Err.Description
Resume Exit_Command_Click

End Sub

Posted in All, MS Access, VBA Code MS Access | Comments Off on Using VBA and Databases to create HTML

The Economics of Immortality (part 2) and a real reason for moving platform.

The announcement in December 2014 that Microsoft was releasing a version of Visual Studio Community to virtually everyone for free and then again a further announcement in February 2016 of Microsoft’s purchase of Xamarin and subsequent release with VS community edition suggests to me that my theory that cost of development tools should steadily reduce to next to nothing is on the button. Still it comes as a surprise that it seems to be happening so quickly. This can only be a good thing especially when considering that increasingly, any application can be developed locally and sold globally or at least published internationally.

In my opinion it is looking more and more likely that the best reason for moving legacy systems to new platforms will in the future not be cost savings but rather revenue possibilities of resulting systems. In a word Software as a Service. But why will anyone one want to create their own Services rather than paying to be a customer of others? Two reasons – number one – at present the field is wide open and in many fields there is a complete lack of rich software out there applicable to individuals problem, secondly the caution with which some individuals will relinquish their information and knowledge of their tools – in that respect revenue generation might just be a nice optional bonus. I do think that different categories of software will progress at different rates and maybe in that respect we can look to Accounting software to see the future for other categories. Everyone is interested in money and the shear ubiquity of the requirement for accounting support has focused resources heavily on tools resulting in a healthy market of options for customers for online software. There are still sticking points in the form of cost especially when being charged on a per seat basis but it is no longer really practical for any individual organisation to develop their own accounting software especially when you can choose both on quality and price in the wider national markets. But outside of accounting the choice of products seem to be somewhat scarce.

So it seems its early days for Software as a Service but certainly maybe the question should be not what legacy systems need replacing but…

What tool do you have at your work which you think with a redesign could be used by others by publishing to the web?

Posted in All, Dev Ops, General Opinion | Comments Off on The Economics of Immortality (part 2) and a real reason for moving platform.

Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7

This is hardly ground breaking but windows Task Scheduler can be used to run VBS scripts on a variety of events to automate repetitive and boring tasks. Backups for example or refreshing caches on web browsers before users come in.

Hit Window button

Type “Task Scheduler”

Press return you should get the following screen.


Make sure Task Schedule Library is selected in the tree on the left then within Actions sub window on the right click Create


Give the Task a name in the area above marked with a red arrow and then go to the actions Tab where you can use the New… button to navigate to the vbs file you wish to run.


Next use the browse button OR if you know the path and file name simply type in the path to your visual basic script you would like to run. Here I have put in an imaginary backup script. Next go to the Trigger tab and again hit the New… tab

The Begin Task selection list gives a good indication of when you want to trigger whatever tasks.


If we continue with the theme and run a vbs script to do a backup at 11:48 everyday


Posted in All, Backup, Configuration, Dev Ops, VBS Scripts | Comments Off on Using Task Scheduler and Visual Basic Scripts to automate pretty much anything Win 7