005 Postgres Command Line : psql : Create a User Defined Function and Identify its creation and then Drop it.

Like all platforms it is possible to create bespoke functions in Postgres

For the following I assume;
1. Postgres 9.5 is installed with the server running (syntax should be the same for other versions)
2. A database called exampledb has been created
3. In this database there exists table called t001landparcels with some records and a field called PKID
4. You are logged into the exampledb with a username called general that has been granted CREATEDB role.
5. You are in psql

The following can be used to create a simple function

CREATE FUNCTION getrecords(int) RETURNS SETOF t001landparcels AS $$
SELECT * FROM t001landparcels WHERE pkid <= $1;
$$ LANGUAGE SQL;

If you have been careful and done this exactly the same with my initial assumptions it should return

CREATE FUNCTION

This tells us that the functioned has been created this will now exist in the schema the table is as a permanent addition.

We can identify the function by either listing all the functions and scrolling through

\df public.*

or listing the individual function

\df public.getrecords

This should return your newly created function

Now to run the function – unlike MS Access you can’t simply run the function you need to allocate it to a select statement.

SELECT * FROM getrecords(2);

This should return everything you are looking for.

Now you should be able to drop the function using the following SQL

DROP FUNCTION getrecords(int);

Note how you have to define the function with its parameter I have read (no idea whether its true) that in version 10 of postgress you can simply use

DROP FUNCTION getrecords;

Writing code in psql does require accuracy so getting things to work does usually involve some experimentation. I have removed much of this from my screenshots!

An alternative is as follows

CREATE FUNCTION getrecords(int) RETURNS TABLE (pkid integer, parcelname text) as $$
SELECT pkid, parcelname FROM t001landparcels WHERE pkid <=$1;
$$ Language SQL;

This appears to result in the same answer I am not clear what the difference is yet – note the result would have been the same if I had defined the table with the additon of a geometry column.

Note I dropped the old getrecords function before I created this one. Not sure what would have happened if I had tried to create one over the other.

I found this second method in stackoverflow when investigating functions with the following to me slightly mysterious quote

This is effectively the same as using SETOF tablename, but declares the table structure inline instead of referencing an existing object, so joins and such will still work.

Which sounds to me as important but I’m struggling at present to understand its meaning!

Stackoverflow discussion

I will investigate later but what I think he is talking about is that you don’t need to have a defined object before hand eg in setof this is
SETOF t001landparcels

I quite often like to have things broken down rather than all in functions and it might prefer to have it as a setof something that I can more clearly see.

Which leads me to my next question how do you get the definition of a function see 006

004 Postgres Command Line : psql : Create a spatially enabled table

For this you will need to have a version of Postgres Database engine installed and running and you will need to have created a database which has the PostGis extension installed.

Open psql
Login to the database you wish to create the table in

type the following

CREATE TABLE t001landparcels (PKID SERIAL PRIMARY KEY, PARCELNAME VARCHAR(50), GEOM GEOMETRY(POLYGON,27700));

Here I do this and then check on the tables with the \dt command before inspecting the columns itself using the \d command.

and here I open up QGIS and link to my local postgres instance and the exampledb database;

and here I connect to it and draw a polygon. If you are wondering where it is this is InchKeith in the Firth of Forth and island very visible from George Street in Edinburgh. If you have flown into Edinburgh you will have flown almost over it.

and here after having digitised a single polygon I look at the contents of the table

SELECT count(*) FROM t001landparcels;

Produces the more helpful count of records in the table.

002 Postgres Command Line : psql : Enabling extensions (PostGIS) to a database

So you have an existing database in PostGres that you wish to add the PostGIS extension to.

You will need to be logged in with a username that has superuser privileges.
Here let me do this using the postgres default account that on my instance has superuser access.

Here rather than logging in at the start I am choosing to change the connection to a different database

This is done by using

\c DatabaseName;

or

\connect DatabaseName;

Please note that the semicolon is vital in performing the instruction.

The way I have discovered to do this is by opening the database and then allocating the postgis extension to the database once it is open.

Let us first examine the tables in our blank database before the extension is enabled.

\dt

Now let us enable the extension.

CREATE EXTENSION postgis;

Let us now examine the database tables

\dt

Enabling the PostGIS extension adds this table along with the ability to add geometry types of varaibles within tables.

001 Postgres Command Line : psql : Getting Started with Postgres

I am just getting into PostGres and here are some rough notes for my reference.

Assuming you have a postgres admin account you want to sign in first of all and create a database

To find the command line go to search in Windows and type psql

Ensure that your postgres engine is running firstly

You should be presented with the following

There are default connections to the local host keep hitting these initially until you reach the following;

You will now need to know your password enter it here and press return

I will deal with the warning message further in the post – but a lot of people experience this message so I wanted to keep it in at present.

From my initial investigations as ever it is a good idea to restrict users to particular privileges at this level I am not being particularly refined – I would like to have a poweruser role that I can allocate to people and give this a defined password.

Signing in you can check out the roles and users as follows – on starting up a new instance you may well see something like this \du

So here I try and set up a user called general and create a role called general which I give create DB rights

I would recommend something stronger than the classic password password.

Issuing the \du command again we get to see the roles

Now we can close down and go back in but this time login as username general by altering the appropriate item when asked.

Note how the =# characters have been replaced by => this appears to denote the non superuser sign in.

To identify what username you are logged in as type \c at the prompt

My investigations suggest that the # sign denotes that you are logged into the database as superuser.

So first of all lets get rid of that annoying warning message when you log in at psql

I am running Postgres version 9.5 your version may vary but you can remove the warning by editing runpsql.bat file every version of postgres has this file and it will be located in the version equivalent directory to 9.5 for me.

C:\Program Files\PostgreSQL\9.5\scripts

Add the line

cmd.exe /c chcp 1252

as per underline and save the file

Now fire up psql as usual you should get the following

It should be noted that if you REM out the SET statements you can choose login with particular server / localhost / database / port and username presets which may be useful if you find yourself constantly going into a particular database as a particular user.

Here you see that the warning note has stopped.

It should be noted that using the general username you will NOT be able to create Databases

In order to CREATE databases you will have to be signed in with a username with sufficient privileges here I am in as postgres and I create a database called ExampleDB

You can see that on carrying out a successful command we usually see a repeat of the command.

To get a list of all databases in the instance type

\l

It can be seen that despite typing the name in capitals the database has been created in lower case this seems to be a feature of commandline. If you desperately want capitals you may need to go to pgadmin tool.

I think I’ll end it there for now.

SQL Azure – Group child records by parent in order

Lets say we have two tables

A site parent table T001 consisting of two fields
PKID
Sitename

And a planning application child table T002 consisting of three fields
PKID
PKIDT001
PlanRef

We have the following records in the parent table T001
1 – Site01Green
2 – Site02Red
3 – Site03Blue

And the following related children in the planning application table T002;
1 – 1 – 2019/098
1 – 1 – 2018/032
1 – 2 – 2017/987
1 – 3 – 2015/100
1 – 3 – 2014/456
1 – 3 – 2014/657

And we would like to write a query that would create a list combining the two tables showing all the planning applications for each site listed in order.

This is the list we are aiming at
1 – 2018/032,2019/098
2 – 2017/987
3 – 2014/456,2014/657,2015/100

Using SQL Server 2017 or later (including SQL Azure) the following will work

CREATE VIEW [dbo].[View01Sitesandtheirplanapps] AS SELECT PKIDT001, STRING_AGG(PlanRef,',') WITHIN GROUP (ORDER by PlanRef ASC) as PlanRefs
From dbo.T002
GROUP BY PKIDT001;

This can then be combined in a separate view that will include the sitename should it be required – this is a tremendous example the structure of which I will be using often.

Lighthouse – Performance statistics for Web Sites

As part of the general move towards the web I continue to investigate and learn about web development. An important aspect for any developer considering how to serve programs to clients and colleagues with as little resistance as possible – is Speed – users will be clicking these things potentially tens of times a minute and waiting to go from one screen to another signifcantly impacts their productivity. No wonder then we are hearing so many stories about dramatic improvements in site success by improving load speeds – but how to measure web site speed accurately? At work and for desktop applications I have resorted to downloading a stopwatch onto my android phone which can be quite useful if there are consistent and substantial differences in speed – still however a somewhat blunt and inaccurate tool.

So the other day I was again investigating how to better improve the delivery of web sites through the installation of web sites using the new Google Progressive Web Application paradigm.

I discovered within Chrome there is an Audit feature beneath the inspection option.

To use this open the web page you are interested in measuring using Chrome ensuring that it is a standard Chrome window (and not the PWA window)

Right click and then go for inspect then select the Audits option as shown below.

At which point you should be presented with the following

Now hit the Run audits button at the bottom

We see the statistics in the top right. From my initial running of this on several of my sites the metrics on
Progressive Web App
Accessibility
Best Practices
SEO
Seems to be fairly consistent in ranking sites.

Performance seems to vary every time you run it even if you are on the same page and url.
Here for example is me running the same audit literally five minutes after the last picture.

So all in all definitely an improvement in metrics but with some of the metrics varying so much from run to run it may still be better for giving a general indication of performance overtime than anything else. I have just upgraded this site to WordPress 5.0.1 although the theme is still from 2010. It should be noted my MS Access applications still transfer between forms within fractions of a second, so fast in fact that I am unable to measure them. Websites are getting better and there are sites now that are very fast. Still some way to go though before they can beat the blistering speed of desktop.

I have started looking at new themes for the site but I find I like a lot about this theme and am having trouble finding anything I am quite as happy with.

MS Access Function CreateSimpleStrings()

A simple function that will loop through and create strings that add a number to a simple string. This string will then be used to create update queries. The same could be done in Excel or any other spreadsheet option but this stores the queries nicely and neatly into a table.

In this instance you want to have created a table called
T002ResidentialSearch

Which has the following fields
ResidentialString
Houses

Public Function CreateSimpleStrings()

Dim i As Integer
Dim rs As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb

Set rs = db.OpenRecordset("T002ResidentialSearch")

For i = 2 To 100

With rs
rs.AddNew
rs!ResidentialString = "Erection of " &amp; i &amp; " houses"
rs!Houses = i
rs.Update
End With
Next i

rs.Close

MsgBox "Complete"

End Function

Visual Basic Script that can be used to rename a file

Here I am trying to automatically load log files into a MS Access file. Log files are actually txt files which need their extension changed if like me you wish to automate their import into MS Access. The following Visual Basic Script takes a file called FS_LS.log and changes it to FS_LS.txt. (I have another function that then takes this file and imports it into an MS Access application.

For safety this script creates a copy of the FS_LS log and renames it with a txt extension combining hours and minutes into the title. Thus it needs to be run one minute apart otherwise it will throw an error.

For testing purposes I have the following line

Fso.Copyfile "C:\Users\mark\Documents\vbs\" & vardatefile, "C:\Users\mark\Documents\vbs\" & vardatefile3

If you don’t have a feed to some kind of constant stream of log files this will copy the txt back to a log file ready to be copied again.(something I found useful for testing). Now next you want to call this from within your MS Access application prior to your data import function using the previously posted function.

Dim Fso
Dim vardatefile
Dim vardatefile2
Dim vardatefile3
Dim varhoursleft
Dim varminsright
Dim varnow

varnow= FormatDateTime(now,4)
varhoursleft = left(varnow,2)
'msgbox varhoursleft

varminsright = right(varnow,2)
'msgbox varminsright
Set Fso = WScript.CreateObject("Scripting.FileSystemObject")
vardatefile2 = "FS_LS.txt"
vardatefile3 = "FS_LS.log"
vardatefile = "FSLS-" & varhoursleft & varminsright & ".txt"
Fso.MoveFile "C:\Users\Mark\Downloads\FS_LS.log", "C:\Users\Mark\Downloads\" & vardatefile
Fso.Copyfile "C:\Users\Mark\Downloads\" & vardatefile, "C:\Users\Mark\Downloads\" & vardatefile2
'Fso.Copyfile "C:\Users\Mark\Downloads\" & vardatefile, "C:\Users\Mark\Downloads\" & vardatefile3

Set FSO = nothing

Chrome polishes its menu setup – Chrome Shortcuts now run web pages in application mode

PS – UPDATE AT 10 NOVEMBER 2018 – I MAY TAKE THIS POST DOWN AS MY CHROME VERSION ON WINDOWS 10 NO LONGER GIVES ME THE OPEN IN NEW WINDOW TICK AND JUST CREATES A SHORTCUT ON THE DESKTOP NOW.

THANK YOU STACK OVERFLOW UPDATE appears others have figured out how to get round this see
Link

PPS UPDATE AT 19 APRIL 2019 – Google have re-instated open in new window Icon. Great that common sense has prevailed and an interesting example of how quickly software development changes… Below should be broadly valid again.

Much like Google Maps changing the projection to a proper globe on zoom out. I noticed Google made a very slight change to their browser application which is a nice demonstration of how Google are constantly trying to improve their interfaces to make them more intuitive. At some point they altered the menu setup for creating web pages in application mode – I had written a post about the former method (see my post here) For those unfamiliar with this it is a really nice way of displaying web pages in what appears an application like window (ie no url bar or tabs). Comparing the two you can see that they have simply changed the menu from Add to desktop to Create Shortcut. Its a nice demonstration of the small improvements that are continually being made to the browser.

I had noticed that all my icons that I had formally created using the former application mode had stopped working.
Didn’t think much of it at the time but I think that must have been the date when Chrome changed over their code.

It should be noted that simply dragging the web page onto the desktop from the chrome browser window still gives the old windows shortcut what we want here is the Google shortcut. So here is how I do it.

Then select the following

Select create shortcut and a new dialog should appear

Name it and there you go

Now you get a nice icon on your desktop related to the site and what’s more when you open it up there is no address bar and within the taskbar you get the correct icon for the website see below example.

MS Access Function – import all CSV files from a directory with the same structure into a single table

This is a really nice function that can be used to place all data from multiple CSVs (with the same structure) into a single table.

Here I use the Ordnance Survey’s excellent Code Point data set that gives postcodes in the UK along with eastings and northings as an example – This lists each postcode in the UK along with further administrative categories. Apologies to anyone from outside of the UK that may not be able to access these files I hope the demonstration is still useful. For those wishing to try pleased follow the links.

After download you will see the problem each postcode is in a separate CSV

Ordnance Survey Open Data Code Point UK Postcodes

After a short process to request the download including filling out your name you should be sent an email to download the data. This will consist of a zip file of two directories one named DOC one named DATA the DATA directory contains a subdirectory called CSV which at May 2018 for my download consisted of 120 csv files.

Opening a single file ( in this case Edinburgh eh ) we see

I’ve already figured this out here , but there are 10 fields here (some are blank in my example)

Here I create a table called T01CodePointCombined with 10 fields marked
F1 through to F10
Note if you don’t create the table this function is so powerful it will do it for you

I then create a module and ensure that all the CSV files I wish to import are in a single directory here “C:\Users\Mark\Documents\CodePoint\Data\CSV\”

Public Function ImportAllFiles()

        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean

        ' Change this next line to True if the first row in csv file
        ' has field names
        blnHasFieldNames = False

        ' Replace C:\Users\Mark\Documents\CodePoint\Data\CSV\ with the real path to the folder that
        ' contains the csv files
        strPath = ""

        ' Replace tablename with the real name of the table into which
        ' the data are to be imported
        strTable = "T01CodePointCombined"

        strFile = Dir(strPath & "*.csv")
        Do While Len(strFile) > 0
              strPathFile = strPath & strFile

              DoCmd.TransferText _
                TransferType:=acImportDelim, _
                TableName:=strTable, _
                filename:=strPathFile, _
                HasFieldNames:=blnHasFieldNames

        ' Uncomment out the next code step if you want to delete the
        ' csv file after it's been imported
        '       Kill strPathFile

              strFile = Dir()
        Loop

        MsgBox "Finished"

End Function

Points to note make sure all csv are closed when you run it. That’s about it takes less than 5 minutes to move all the records from those 120 files into a single table within an MS Access Database.
After import if it’s gone correctly you should have in the region of 1.7 million records in T01CodePointCombined.

MS Access Function – Scan through a directory and write list of files to a table

Pretty much as the title

Ensure you have a table called tblFiles with one field called Filename

Here I am finding all files in folderspec = “C:\Users\Mark\Documents\CodePoint\Data\CSV”

Alter as appropriate

Public Function ShowFolderList()
Dim fs, f, f1, fc, s
Dim rs As DAO.Recordset
Dim folderspec

Set rs = CurrentDb.OpenRecordset("tblFiles")

folderspec = "C:\Users\Mark\Documents\CodePoint\Data\CSV"

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files

For Each f1 In fc
    rs.AddNew
    rs.Fields("FileName") = f1.Name
    rs.Update
Next

Set rs = Nothing

MsgBox "Finished"

End Function

MS Access – Code Snippets for Navigating using Internet Explorer

Waiting for a web page to load

While ie.Busy
DoEvents
Wend

Selecting differing radio buttons

Dim ieRadio As Object
Set ieRadio = ie.Document.all
ieRadio.Item("datetype")(1).Checked = True

A function that can be used to delay action before an action in code (Not web specific). Here I set to 1 second

Public Function Await1()
Dim time1 As Variant
Dim time2 As Variant

time1 = Now
time2 = Now + TimeValue("0:00:01")
    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End Function

Split a string of text into a 1 dimensional array with splitting occurring at returns.

Dim strArray() as String
Dim strtoParse as String
Dim intCount as Long

'here I taken an imaginary recordset and set the string to parse from the text value in the recordset
strtoParse = rs!Text
strArray = Split(strtoParse, vbNewLine)

'a simple loop that will count the number of values in the array (1 dimensional)
For intcount = LBound(strArray) To UBound(strArray)
      MsgBox Trim(strArray(intCount))
   Next

Find a string in an array note the complete string needs to be found not a part within one of the array parts.

Private Function WhereInArray(vArrayName As Variant, vStringtoFind As String) As Variant
Dim i As Long
For i = LBound(vArrayName) To UBound(vArrayName)
    If vArrayName(i) = vStringtoFind Then
        WhereInArray = i
        Exit Function
    End If
Next i
'if you get here, vStringtoFind was not in the array. Set to null
WhereInArray = Null
End Function

Show whats in which Array cell – where n is a number equal to the value of a cell
So eg strArray(60) would show the value in 60th cell note arrays start from 0

MsgBox strArray(n)

Copy the HTML of a web page to a variable

my_StrVariable = ie.Document.body.innerHTML

Filter out much of the HTML formatting and only put inner text into a variable

my_StrVariable = ie.Document.body.innerText

Replace with spaces with less spaces till 2 and 3 spaces are replaced by 1 space

Function ThreeTwo(ByVal parmString As String) As String
    Dim strTemp As String
    strTemp = parmString

    Do Until InStr(strTemp, "   ") = 0
        strTemp = Replace(strTemp, "   ", " ")
    Loop

    Do Until InStr(strTemp, "  ") = 0
        strTemp = Replace(strTemp, "  ", " ")
    Loop
    ThreeTwo = strTemp
End Function

Click on a button if all you know is its class name

Dim e
Set e = ie.Document.getElementsByClassName("button primary")(0)
e.Click

Identify sites and how often they are visited
labpix.online/rounduptheusualsuspects.org
Look at any site
http://labpix.online

Take a look at the robots.txt of a site
https://www.theregister.co.uk/robots.txt

https://www.ebay.co.uk/robots.txt

MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

As I have indicated before MS Access makes a brilliant ETL tool. Important in this is being able to connect to different databases. I have set out how to connect to MySQL and SQLAzure before – the following sets out how to connect to PostGres.

To follow along you will either need PostGres installed on your local computer or alternatively all required connection parameters to a database in the cloud or on your lan.
PostGres Download

You will also need to Download and install a PostGreSQL ODBC driver – these are available at the following(March 2018).

PostGres ODBC Drivers

Scroll down the list – here I went to the bottom and obtained x64 version – MSI are downloaded (Microsoft Windows Installer files) – Install and then move to the next step.

Next create a blank database and right click in the white area to reveal a menu – select link tables.

You should now be presented with the Select Data Source dialog. Here I hit New…

This brings up the Create New Data Source dialog which lists database drivers scroll down through the list to PostgreSQL Unicode and select

Give your DSN an appropriate name and then go back to link table but this time rather than hitting the new button navigate to where you saved your DSN and select it and press OK.

The ever important parameters – you just need to know these – if you set up PostGres and you can get in through PG Admin selecting properties on the database should reveal the panel that will give you some guidelines. You may wish to double click on the image below so you can get a closer link of how I place my parameters in. I have opened the PGAdmin dialog here and placed it alongside the MS Access database window to show the properties I am transferring across.

Hitting OK should present you with the tables in your database.

And here is a demonstration with the link in place along with the table open and a simple form shown.

MS Access Function Collection that can be used to Generate Housing Forecast Figures

Apologies if you are coming here for the first time. This post is a somewhat dense domain specific holding post for some work I did at the weekend to pull together some thoughts.

I was thinking that if I had a list of all sites in the UK I should be able to generate a phasing for each of them based on maybe the area of the site. This would automatically create a general housing land audit for any site that I should put in. I thought I’d try and see if I could write a set of functions that might generate phasing by site. Given that you can get an estimate of the housing boundaries from open street map and that you can get their area it would be possible to get an estimated number of houses per site which could then be used to phase. Going further if you were ever to know planning permission dates you could more accurately use this as a date from which to start phasing.

Ensure you have three tables
T01Sites (PKID, TotalNoHouses, DecisionDate)
T02HousePhasing (PKID, SiteFKID, Year, Completions)
T03 (PKID, TotalNoHouses, DecisionDate, YearofDecision, YearofStart, YearSpread)

T02 is the phasing child table of T01 and T03 is a holding table for a make table that will hold a randomised spread over which you wish to phase the total no of houses. It also randomly predicts when the housing will start on site.

And three queries

Q01 – Make Table Query

SELECT T01Sites.PKID, T01Sites.SiteName, T01Sites.TotalNoHouses, T01Sites.DecisionDate, Year([DecisionDate]) AS YearofDecision, CalculateintYearStartFULLPP([YearofDecision]) AS YearofStart, intYearSpread([TotalNoHouses]) AS YearSpread INTO T03
FROM T01Sites;

Q02 – Select Query

SELECT T03.PKID, T03.SiteName, T03.TotalNoHouses, T03.DecisionDate, T03.YearofStart, T03.YearSpread, Int(T03!TotalNoHouses/T03!YearSpread) AS PerYearSpread, [TotalNoHouses] Mod [YearSpread] AS Remainder
FROM T03
WHERE ((([TotalNoHouses] Mod [YearSpread])=0));

Q03 – Select Query

SELECT T03.PKID, T03.SiteName, T03.TotalNoHouses, T03.DecisionDate, T03.YearofStart, T03.YearSpread, Int(T03!TotalNoHouses/T03!YearSpread) AS PerYearSpread, [TotalNoHouses] Mod [YearSpread] AS Remainder
FROM T03
WHERE ((([TotalNoHouses] Mod [YearSpread])>0));

VBA Function list
The first function randomises the spread in years over which construction might happen on an individual housing site based on the total number of houses on the site.

Public Function intYearSpread(TotalNoHouses As Integer) As Integer

If TotalNoHouses < 2 Then

intYearSpread = 1

ElseIf TotalNoHouses = 2 Then

intYearSpread = Int((TotalNoHouses) * Rnd) + 1

ElseIf TotalNoHouses > 2 And TotalNoHouses < 9 Then

intYearSpread = Int((TotalNoHouses - 2 + 1) * Rnd + 1)

ElseIf TotalNoHouses >= 9 And TotalNoHouses <= 40 Then

intYearSpread = Int((4) * Rnd + 1)

ElseIf TotalNoHouses >= 41 And TotalNoHouses <= 80 Then

intYearSpread = Int((8 - 4 + 1) * Rnd + 4)

ElseIf TotalNoHouses >= 81 And TotalNoHouses <= 200 Then

intYearSpread = Int((8 - 4 + 1) * Rnd + 4)

ElseIf TotalNoHouses >= 201 And TotalNoHouses <= 400 Then

intYearSpread = Int((10 - 6 + 1) * Rnd + 6)

ElseIf TotalNoHouses >= 401 And TotalNoHouses <= 800 Then

intYearSpread = Int((12 - 8 + 1) * Rnd + 8)

Else

intYearSpread = Int((20 - 10 + 1) * Rnd + 10)

End If

'MsgBox intYearSpread

End Function

The first of the next three functions is used in the query to identify a year from which phasing on site will start. I wrote two further functions with the thought that in the future I could create a switch that would allow alternative site starts depending on whether a site has planning permission and depending on the type of planning permission. For example full planning permission would mean starting within three years of the granting of planning permission whereas outline would push it to between 3 and 6 years. A site with an LDP allocation would start further into the future.

Public Function CalculateintYearStartFULLPP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartFULLPP = intDecisionDateYear + (Int((3 - 1 + 1) * Rnd + 1))

End Function

'Not used at present
Public Function CalculateintYearStartPPPP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartPPPP = intDecisionDateYear + (Int((6 - 3 + 1) * Rnd + 3))

End Function

'Not used at present
Public Function CalculateintYearStartLDP(intDecisionDateYear As Integer) As Integer

CalculateintYearStartLDP = intDecisionDateYear + (Int((20 - 8 + 1) * Rnd + 8))

MsgBox CalculateintYearStartLDP

End Function

Function to create phasing IF housing IS perfectly divisible by Year Spread
GRH is an acronym for Generate Randomised Housing

Public Function GRHZero() As Variant

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsPhasing As DAO.Recordset
Dim intrsSourcePKID As Integer
Dim intrsSourceYearofStart As Integer
Dim intYearSpread As Integer
Dim intPerYearSpread As Integer
Dim i As Integer

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("Q02")
Set rsPhasing = db.OpenRecordset("T02HousePhasing")

If Not (rsSource.EOF And rsSource.BOF) Then
'There are no records if End of File and Beginning of File are both true

rsSource.MoveFirst

Do Until rsSource.EOF = True

intrsSourcePKID = rsSource!PKID
intrsSourceYearofStart = rsSource!YearofStart
intYearSpread = rsSource!YearSpread
intPerYearSpread = rsSource!PerYearSpread

For i = 1 To intYearSpread

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intPerYearSpread
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

Next i

rsSource.MoveNext

Loop
Else
MsgBox "No Records"
Exit Function
End If

rsPhasing.Close
rsSource.Close

Set rsPhasing = Nothing
Set rsSource = Nothing

Set db = Nothing

End Function

Function to create phasing IF housing IS NOT perfectly divisible by Year Spread and a remainder is put on end

Public Function GRHRemainder() As Variant

Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsPhasing As DAO.Recordset
Dim intrsSourcePKID As Integer
Dim intrsSourceYearofStart As Integer
Dim intYearSpread As Integer
Dim intPerYearSpread As Integer
Dim intRemainder As Integer
Dim i As Integer

Set db = CurrentDb()
Set rsSource = db.OpenRecordset("Q03")
Set rsPhasing = db.OpenRecordset("T02HousePhasing")

If Not (rsSource.EOF And rsSource.BOF) Then
'There are no records if End of File and Beginning of File are both true

rsSource.MoveFirst

Do Until rsSource.EOF = True

intrsSourcePKID = rsSource!PKID
intrsSourceYearofStart = rsSource!YearofStart
intYearSpread = rsSource!YearSpread
intPerYearSpread = rsSource!PerYearSpread
intRemainder = rsSource!Remainder

For i = 1 To intYearSpread

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intPerYearSpread
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

Next i

With rsPhasing
rsPhasing.AddNew
rsPhasing!SiteFKID = intrsSourcePKID
rsPhasing!Year = intrsSourceYearofStart
rsPhasing!Completions = intRemainder
rsPhasing.Update
intrsSourceYearofStart = intrsSourceYearofStart + 1
End With

rsSource.MoveNext

Loop
Else
MsgBox "No Records"
Exit Function
End If

rsPhasing.Close
rsSource.Close

Set rsPhasing = Nothing
Set rsSource = Nothing

Set db = Nothing

End Function

And the Script to run both the above functions

Public Function GeneratePhasingRecords()

Call GRHZero
Call GRHRemainder

MsgBox "Finished"

End Function

AHK – Useful AutoHotKeyScripts for Outlook specifically

Another set of very useful scripts mainly dealing with creating hotkeys for common Outlook tasks.

Note that if you are on a laptop you may have pre-mappings for the function keys in which case you will need to quite possibly go for alternatives.

F8::
Run C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE /c ipm.note
return

F9:: ;Inbox
keystroke = ^1
parameters = 
Gosub open
Return

F10:: ;Calender
keystroke = ^2
parameters =  "outlook:calendar"
Gosub open
Return

F11:: ;Contacts
keystroke = ^3
parameters = outlook:contacts
Gosub open
Return


F12::
PROCESS, EXIST, OUTLOOK.EXE
PID := ERRORLEVEL
IF ERRORLEVEL <> 0
{
       LOOP,
      {
            WINSHOW, AHK_CLASS rctrl_renwnd32 
            WINACTIVATE, AHK_CLASS rctrl_renwnd32 
            WINWAITACTIVE, AHK_PID %PID% AHK_CLASS rctrl_renwnd32
            WINGETACTIVETITLE, TITLE
            WINCLOSE, %TITLE%
            WINWAIT,, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?,3
            CONTROLSEND, , {ENTER}, AHK_CLASS #32770, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?
            IF A_INDEX > 30
                  PROCESS, CLOSE, OUTLOOK.EXE

            PROCESS, EXIST, OUTLOOK.EXE
            IF ERRORLEVEL = 0
                  BREAK
      }
}
return

DetectHiddenWindows, On
Process, Exist, outlook.exe
If !ErrorLevel
   Run outlook.exe
Return

open:
Process, Exist, outlook.exe
If (ErrorLevel != 0)
{
	WinActivate ahk_class rctrl_renwnd32
	WinWaitActive ahk_class rctrl_renwnd32
	Send %keystroke%
}
else
	Run outlook.exe %parameters%
Return

AHK – Useful AutoHotKeyScripts

F2::
; Close all windows (open/minimized, browsers) but not pwr off
	WinGet, id, list,,, Program Manager
	Loop, %id%
	{
	this_id := id%A_Index% 
	WinActivate, ahk_id %this_id%
    	WinGetClass, this_class, ahk_id %this_id%
	WinGetTitle, this_title, ahk_id %this_id%
	If(This_class != "Shell_traywnd") && (This_class != "Button")  ; If class is not Shell_traywnd and not Button
	WinClose, ahk_id %this_id% ;This is what it should be ;MsgBox, This ahk_id %this_id% ; Easier to test ;)
	}
Return

If you don’t know or can’t find the executable for the program you wish to AHK to you can place a link on the desktop or somewhere else and trigger the link using a mapped key as follows;

F3::
;Open QGIS
path = "C:\Users\brooks.mark\Desktop\QGIS Desktop 2.14.8.lnk"
;MsgBox, %path%
 
Run, %path%
Return

Open Chrome using CTRL Z – (^ is the sign for CTRL)

^z::
Run, C:\Program Files (x86)\Google\Chrome\Application\chrome.exe
Return

Here I have a key to open a work Uniform program – line send types into the username field usernamevariable – alter to your actual value

F6::
path = "C:\Users\brooks.mark\Desktop\Uniform LIVE.lnk"
;MsgBox, %path%
Run, %path%

Sleep, 5000

ControlFocus, Edit4,Uniform Spatial - LIVE Database
Send, usernamevariable

Return

Navigate to Website directly from MS Access using Internet Explorer

One of the major suppliers of planning software to the United Kingdom is a company called Idox Group plc. They produce probably the most popular back office software that runs all aspects of administering planning permission. As such their public access web pages usually have the same fundamental structure. What if rather than holding information about a planning application you would like to create a command button that would take the user directly to the planning application details.

Unfortunately the url bears no relation to the planning application reference so it is necessary to go to a search page enter the planning application number and then trigger search which if you have a completely accurate planning reference will take you to the individual record. Here’s a function for City of Edinburgh Council complete with relevant application number. Previously I had achieved this using AHK but here is an elegant solution using VBA code. As ever there are multiple ways to achieve the same thing.

Public Function GotoWebPage()

Dim ie As Object
Dim strapplication As String

strapplication = "18/00488/TCO"
Set ie = CreateObject("Internetexplorer.application")

ie.Visible = True

ie.navigate "https://citydev-portal.edinburgh.gov.uk/idoxpa-web/search.do?action=simple&searchType=Application"

While ie.busy
DoEvents
Wend

ie.Document.getElementbyID("simpleSearchString").Value = strapplication

ie.Document.Forms(0).submit

End Function

And here’s one for City of London Council

Public Function GotoWebPageCityLondon()

Dim ie As Object
Dim strapplication As String

strapplication = "18/00152/FULEIA"
Set ie = CreateObject("Internetexplorer.application")

ie.Visible = True

ie.navigate "http://www.planning2.cityoflondon.gov.uk/online-applications/search.do?action=simple&searchType=Application"

While ie.busy
DoEvents
Wend

ie.Document.getElementbyID("simpleSearchString").Value = strapplication

ie.Document.Forms(0).submit

End Function

Further useful reading
How to Navigate IE using VBA