Productivity – Round Up the Usual Suspects

022 Postgres – Setting up starting variables in psqlrc

So how do we adjust the defaults for the command line prompt in psql Postgres

Go to the command prompt and navigate to the following directory

c:\Users\Mark\AppData\Roaming\Postgresql\

and either find or create a file called

psqlrc.conf

This is a simple text file that you can edit or create in notepad.

--------------------------
--psqlrc set preferences--
-- Author Mark Brooks --
--------------------------

\set QUIET 1

\x auto
\timing
\set COMP_KEYWORD_CASE upper

\pset border 2
\pset pager off
\pset null <NULL>
\setenv editor 'C:\\Program Files (x86)\\Notepad++\\notepad++.exe'
\set VERBOSITY verbose

\set QUIET 0

\echo 'Welcome to PostgreSQL \n'
\echo 'Type :version to see PostgreSQL version \n'
\echo 'Type :extensions to see the available extensions'

\set version 'SELECT version();'
\set extensions 'select * from pg_available_extensions;'

This allows you for instance to set up which editor will appear when you perform the \e command

012 Postgres command line : psql : Run sql from a table

I wanted to be able to run thousands of queries or hundreds from Postgres like I can in MS Access this didn’t turn out to be too difficult.
Here’s something that works firstly lets create a new database

CREATE DATABASE sqlloopdb;

You will then need to connect to the database.

Next I will create 2 tables with; One table to be called t001sqltarget – this is the table we shall change with queries. One table called t002sqlrun – this will contain queries that we will run.

Please note the field names are important as well but I will let you study them in the code.

I then have 4 inserts that place valid SQL strings into the field sqltorun.

CREATE TABLE t001sqltarget (pkid serial primary key, fieldforupdate varchar(1));
CREATE TABLE t002sqlrun (pkid serial primary key, sqltorun varchar(1000));
INSERT INTO t002sqlrun(sqltorun) values ('INSERT INTO t001sqltarget(fieldforupdate) values (1);');
INSERT INTO t002sqlrun(sqltorun) values ('INSERT INTO t001sqltarget(fieldforupdate) values (2);');
INSERT INTO t002sqlrun(sqltorun) values ('INSERT INTO t001sqltarget(fieldforupdate) values (3);');
INSERT INTO t002sqlrun(sqltorun) values ('INSERT INTO t001sqltarget(fieldforupdate) values (4);');

First lets run the above and see what we have. Below you can see that I create the database then connect to it before opening the editor from which I run the above code I then take a look at the tables in the database and run a select to return all the records within the t001sqltarget table of which there are none.

Now lets run the following code and then take a look at t001sqltarget.

DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
SELECT sqltorun FROM t002sqlrun
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;

And after running there are 4 lines in the table

Every time I run the Do code four more records will be added to this table. Any SQL could be included in t002sqlrun and this is a nice demonstration of what I had previously able to do in MS Access and is massively powerful. It could be used for instance to calculate multiple measurements.

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.

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

SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

The first thing you need to get sorted when moving to SQL Azure is having the ability to get your information out and safe if needs be. When experimenting with MS Azure and for applications that don’t have sensitive information it is nice to have that information available in an easily accessible format. Here are a series of functions that will copy Azure Tables linked to database into local MS Access tables with the prefix ZCOPY.

The starting point in this should be an MS Access database that should be linked to your SQL Azure Database. Only those tables that are linked will be copied. Remember the 2GB limit on Access.

I think I have got all the functions here that are required to make it work and include the complete module at the bottom but first I will breakdown the modules and list describe what each of the functions do.

First create a table to store the list of tables in the Azure Database

Public Function CreateTableT0001AzureTablesGlobal()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
        & "(PKID AUTOINCREMENT, " _
        & "AzureTableName CHAR CONSTRAINT PKID " _
        & "PRIMARY KEY);"
   
End Function

Now Create a Function that will hold the SQL that takes the tables and makes them locally.

Public Function CreateTableT0002SQL()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0002SQL " _
        & "(PKID AUTOINCREMENT, " _
        & "SQL MEMO CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

A function that allows for stepping through the table

Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Step through the Linked Azure Tables and poupulate table T001 with their names

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
    ' ignore system and temporary tables and tables starting with T - personal choice option
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
       With rstList
       .AddNew
       rstList!AzureTableName = tdf.Name
       rstList.Update
      End With
    End If
    
Next

Set tdf = Nothing
Set db = Nothing

End Function

The next function is required to strip out additional spaces in names

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

We can now write the VBA that will write the make table SQL that once run will put one make table query into the maketableSQL table for each Azure table.

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1
While LCounter < 9000
LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")

SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"

Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
    Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
 Case 3021
   Resume Exit_CreateMakeTableSQL
  Case Else
  Resume Exit_CreateMakeTableSQL
  End Select
 
End Function

And finally Run all the queries

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

And a script to pull all of this together

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable("T0002SQL")
DoCmd.SetWarnings True

End Function

The complete module

Option Compare Database
Option Explicit

Public Function GetAzureScript()

DoCmd.SetWarnings False
Call CreateTableT0001AzureTablesGlobal
Call CreateandPopulateListofDBOTableNames
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", " ", "")
Call FindXReplaceY("T0001AzureTablesGlobal", "AzureTablename", Chr(10), "")
Call AddByteColumn("T0001AzureTablesGlobal", "XFLag1")
Call CreateTableT0002SQL
Call CreateMakeTableSQL
Call FindXReplaceY("T0002SQL", "SQL", " ", "")
Call FindXReplaceY("T0002SQL", "SQL", Chr(10), "")
Call FindXReplaceY("T0002SQL", "SQL", "SELECT*INTOCOPY", "SELECT * INTO ZCOPY")
Call FindXReplaceY("T0002SQL", "SQL", "FROM", " FROM ")
Call RunQueriesFromTable("T0002SQL")
DoCmd.SetWarnings True

End Function

Public Function CreateandPopulateListofDBOTableNames()

'These will typically be the names of the SQL Server tables this should work both with SQL Server and SQL Azure

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rstList As DAO.Recordset

'Call CreateTableT0001AzureTablesGlobal

Set rstList = CurrentDb.OpenRecordset("T0001AzureTablesGlobal")
Set db = CurrentDb

For Each tdf In db.TableDefs
    ' ignore system and temporary tables and tables starting with T - personal choice option
    If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*" Or tdf.Name Like "T*") Then
       With rstList
       .AddNew
       rstList!AzureTableName = tdf.Name
       rstList.Update
      End With
    End If
    
Next

Set tdf = Nothing
Set db = Nothing

End Function

Public Function FindXReplaceY(FixTable As String, FixColumn As String, X As String, Y As String) As Variant

Dim strSQL As String

strSQL = "UPDATE [" & FixTable & "] SET [" & FixTable & "].[" & FixColumn & "] = REPLACE([" & FixColumn & "]," & Chr$(34) & X & Chr$(34) & "," & Chr$(34) & Y & """);"

DoCmd.RunSQL strSQL

End Function

Public Function CreateTableT0001AzureTablesGlobal()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0001AzureTablesGlobal " _
        & "(PKID AUTOINCREMENT, " _
        & "AzureTableName CHAR CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

Public Function CreateTableT0002SQL()
 
     Dim dbs As Database
     Set dbs = CurrentDb
 
        dbs.Execute "CREATE TABLE T0002SQL " _
        & "(PKID AUTOINCREMENT, " _
        & "SQL MEMO CONSTRAINT PKID " _
        & "PRIMARY KEY);"
 
   
End Function

Public Function AddByteColumn(TblName As String, FieldName As String)
'Just use byte data type as only going to use this for a flag

DoCmd.RunSQL "AlTER TABLE [" & TblName & "] ADD COLUMN " & FieldName & " BYTE;"

End Function

Public Function CreateMakeTableSQL()

On Error GoTo Err_CreateMakeTableSQL
Dim rstSQL As DAO.Recordset
Dim rstSQLx As DAO.Recordset
Dim dbc As DAO.Database
Dim SQLStringAdd As String
Dim LCounter As Long

Set dbc = CurrentDb

LCounter = 1</code>
<code>While LCounter < 9000</code>
<code>LCounter = LCounter + 1
Set rstSQL = CurrentDb.OpenRecordset("SELECT T0001AzureTablesGlobal.PKID, T0001AzureTablesGlobal.AzureTableName, T0001AzureTablesGlobal.XFLag1 FROM T0001AzureTablesGlobal WHERE (((T0001AzureTablesGlobal.XFLag1) Is Null));")</code>
<code>SQLStringAdd = "SELECT * INTO COPY" & rstSQL!AzureTableName & " FROM " & rstSQL!AzureTableName & ";"</code>

<code>Set rstSQLx = CurrentDb.OpenRecordset("T0002SQL")
With rstSQLx
.AddNew
rstSQLx!SQL = SQLStringAdd
rstSQLx.Update
rstSQLx.Close
End With

With rstSQL
rstSQL.Edit
rstSQL!XFLag1 = 1
rstSQL.Update
rstSQL.MoveNext
rstSQL.Close
End With

Wend

Exit_CreateMakeTableSQL:
    Exit Function

Err_CreateMakeTableSQL:
Select Case Err.Number
 Case 3021
   Resume Exit_CreateMakeTableSQL
  Case Else
  Resume Exit_CreateMakeTableSQL
  End Select
 
End Function

Public Function RunQueriesFromTable2(SQLSource As String)

DoCmd.SetWarnings False

Dim StartTime As Date
Dim EndTime As Date
Dim rstZ As DAO.Recordset
Dim strSQL2 As String

StartTime = Now()

Set rstZ = CurrentDb.OpenRecordset(SQLSource)

Do Until rstZ.EOF

strSQL2 = rstZ!SQL
DoCmd.RunSQL strSQL2
rstZ.MoveNext

Loop

DoCmd.SetWarnings True

EndTime = Now()

MsgBox "Finished ALL SQL queries! Process started at " & StartTime & " and finished at " & EndTime

End Function

Linking to SQL Server / Oracle – don’t skip the set key field on linking

After writing Update queries with multiple joins on linked Oracle Databases I was receiving an error on running particular update queries on linked tables that had no Primary Key identified.
On linking the tables I had been asked to identify a field with a unique value. Not thinking I had passed on this and initially this didn’t seem to be an issue. Subsequently I identified that the error associated with the update queries originated from this lack of key identification. Re-linking required tables and ensuring that I identified a unique key allowed for these queries to be processed. This of course is because Access is wanting to use the unique key to identify the field for update.

The message

Operation must use an updateable query.

Just one to be mindful of if you are linking to enterprise grade backend databases.

MS Access VBA Function – Automated Multiple Query Object Creation from previously created table of SQL

This completes the task of taking automatically generated SQL previously placed in a table and writes the SQL therein to Query Objects naming them automatically. This has several advantages to cut and paste –

1) Its Lightning Quick
2) Completely consistent naming
3) Cut and Paste can be awkward with the windows
4) Its just fun

To created the NestedIIfs table see this post

MS Access VBA Function – Creating NestedIIFs

This is the post on writing Query Objects directly

MS Access – Automated Single Query Object Creation

It requires that you have a table called T005NestedIIFs
with the populated fields
SQLField
TargTable
TargField

Public Function WriteNIFQueryObjects(LCounter As Long) As String

Dim rstX As DAO.Recordset
Dim QName As String
Dim qdf As Variant
Dim strSQL As String
Dim LCountStart As Long

LCountStart = LCounter

Set rstX = CurrentDb.OpenRecordset("T005NestedIIFs")

Do Until rstX.EOF = True
qdf = rstX!SQLField
QName = "Q" & LCounter & rstX!TargTable & "-" & rstX!Targfield & "-Update"
LCounter = LCounter + 1
Set qdf = CurrentDb.CreateQueryDef(QName, rstX!SQLField)

rstX.MoveNext

Loop

MsgBox "Query objects written to Database numbers starting" & LCountStart

End Function

AutoHotKey : Navigation between Satellite Applications to improve Work Flow

A while back I wrote a post about how allowing parameters to be passed to URLs is a big benefit in increasing the speed with which you can navigate to individual records in apparently non-connected web applications.

But what do you do if you are faced with a satellite application whose vendor has not implemented this URL friendly facility. Users are left with the very jarring break to the flow of their work when they have to leave the application they are in and navigate to another application sometimes manually having to link to the other application records form via a search field. This searching task when multiplied many times can be really tedious, repetitive, demotivating and time consuming not to mention pointless.

How can we better serve our users?

The other day I came across an open source program called AutoHotKey that allows me to improve this task.

AutoHotKey

Autohotkey is an open source project that allows the creation and compilation of simple or complicated scripts that can be used to navigate anything on a computer. That means desktop OR web applications. The following is something that I worked out last week to be able to navigate a web application by triggering a script from MS Access vba. The great thing is that you can pass parameters from a database application to a middle layer and trigger a set of commands to be run.

Let us take the example of a recent problem I faced. Many councils throughout the United Kingdom have bought an application from a company that manages the information associated with making planning applications, it consists of both desktop and web applications that help manage the submission and decision making associated with development. The vendor recently “upgraded” the application resulting in it no longer accepting planning application numbers to its URL as a method of going straight to the record. This was meaning that users of one of my satellite applications were faced with being dropped into a search screen and then needing to manually type a field from one application into the field of another application. QED dull and repetitive task.

There follows and overview of my solution. Firstly download the following programs
1)AutoHotKey

AutoHotKey

2)iWB2 Learner – which is a small program for identifying element names and id in INTERNET explorer.
iWB2 Learner
iwebbrowser2 Download

My script for Autohotkey was as follows.

FindRecordReference.ahk (written in plain old notepad and saved to a known location with the suffix changed to ahk)
=====================

APPLICATION = %1%

URL := "https://onlinerecordset/"

WB := ComObjCreate("InternetExplorer.Application")
WB.Visible := True
WB.Navigate(URL)
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy ; wait for page to open
	Sleep, 10
wb.document.getElementById("simpleSearchString").value := Application
wb.document.getElementsByTagName("INPUT")[4].Click()
While wb.readyState != 4 || wb.document.readyState != "complete" || wb.busy
	Sleep, 10

return

===================

Using iWB2 Learner to identify the element names on the web page
This video shows iWB2 Learner being used it unfortunately does not have any sound.

VIDEO Using iWB Learner with AutoHotKey

—-
Next you will need to trigger the AHK – You will need design access to the program that is sending the instruction to do this. In my MS Access application I have the following code that triggers the script in the above.

Private Sub Command43GoToOnlineRecord_Click()
 
    Dim strRecordNo As String
    Dim strAHKname As String
 
    strPlanApp = "LIVE/" & Me.RecordNo
 
    strAHKname = "\\[YourServerName]\FindRecordReference.exe"
    Call Shell(strAHKname & " " & strRecordNo, vbMaximizedFocus)
 
End Sub

Notes:
The computer that holds the AHK script need not have AutoHotKey installed if it doesn’t you can compile your script into an executable that will not require installation. Here I created the executable on another computer and transferred it to the \\server1-cluster\ahk location ready to be called by the VBA

Consecutive parameters passed to Autohokey are consecutively named %1% %2% etc.. In my script I pass the planning application as %1% and rename it APPLICATION immediately.

Compiling the AHK is done by moving to a computer with AHK installed and navigating in Explorer to the file and then right click and Compile will be an option. Note the processor architecture is important when compiling. If your target machine is 32bit then you need to compile on a 32bit machine – same with 64.

Appropriate User Interfaces

I have three guitars 10 windsurfers and 4 bikes (5 if you count a static exercise bike) I have three regularly used computers (tablet , desktop and laptop) – four if you count my phone. My bikes are not duplicates one is for racing, one is for training, one is for commuting and the final one for mountain biking. I often use my commuting bike instead of my car. I try my hand at DIY and have got to the point of building a cupboard containing drawers only for tools I have so many tools I cannot list them all from memory.

I get the impression that companies are pushing for all applications to be pushed to the web. Fine but if you have invested in an amazing building with an amazing Local Area Network why push absolutely everything to the web? Milk your assets often the longer you leave it the more stable the web environment will be – it still continues to experience massive change. Web has its advantages but flexible cheap very rich User Interface design doesn’t seem to be it. The benefit of database driven applications today is that the backend is totally independent of the User Interface. Developing an internal local area based UI can be totally independent of building a web based user interface.

Carefully consider and don’t be afraid to use the full range of tools available to you get the backend right and then work on a very very usable front end. Users want quick usable design that fit their needs they really don’t care about the technology.

Guidelines for E-mails

Not the most exciting of subjects but everyone is involved in communication whether its sorting out lunch with a friend through to organising massive engineering projects. Confusion and misinformation is always an ongoing risk resulting from bad communication which results in individuals either not getting on board with a topic or going off and doing needless work. Today I really rallied against the endless stream of emails I had been cc’d into. I realised that I am probably as guilty as others in doing this so I decided I should figure out some guidelines for tightening up my e-mail etiquette

My guidelines;

1)If you are replying delete generic titles and write a title which is more specific about what is contained in the e-mail. (Try not to use Re: if you have to state Reply )
2)Spend time making and crafting a really relevant reply which is as targeted as possible.
3)If possible delete the train wreck of replied e-mails – most of them are not read by anyone and quite often have all sorts of tags, addresses and generic images which are totally irrelevant.
4)Be economical in cc e-mails if you want to introduce others into a project – write a specific introduction e-mail for that individual explaining everything don’t just copy them into a massive e-mail and expect them to go back through and figure out what is going on.
5)If you have questions that you need answered really start with the question and explain the context.
6)Use pictures – They are useful and e-mails really support pictures very very well now.
7)If you have simple calculations from excel spreadsheets contain them as images in the e-mail attachments are annoying and take time to open up.
8)If you are working with people on a project you really need to have met the people. If they are in your building why not go and introduce yourself?
9)Hey use the phone its there it costs nothing again more important if you don’t know the person.
10)Don’t just write with questions – if you have an outcome that you think is important or you’ve received some positive feedback after project completion let the others on the project know about it.
11)Generally aim to be positive and supportive
12)If you make commitments try to remember them and stick to them.

Comparative Advantage

My opinion on fixing things is that if someone has to sit around and watch you fix something the cost of the job is not just your time in fixing it but it’s also the cost of the time of the person sitting around and watching you fix the thing and the unhappiness that they feel in being pretty helpless.

Hence another reason to empower users.

How do you truly asses the cost of a job – if you are in private business it tends to be everything that you do to undertake the work. How long it is going to take you to get to the location to do the work , how much it will cost you to get to that location. In some large businesses things like time to get to a location to fix the item tends to be totally overlooked – this can be a not insignificant amount of time even if there is no revenue cost. I don’t care who you are I’m of the opinion you want to be reducing wasted time as much as possible.

Developer Operations and Empowering Users My Opinion

Recently I was listening to a Channel 9 Microsoft podcast talking about how their products could be used in developer operator environments. Interestingly the podcast talked about Developer Operations being a subdivision of the central IT department and how once an application was up and running the developer would never hand over support to a separate team to maintain the code. Regards this definition I would go further and suggest that organisations should encourage super users aka citizen programmers to develop processes and applications themselves. A mid-way strategy might be to station professional developers with the applications within the sections for whom they do the work and responsible to the divisional head and NOT the IT section. I suggest the benefits of this approach are as follows;

Geographical location – Not all users work in offices where you have access to 47 inch screens with instant Skype, team viewer access and 24/7 high bandwidth internet access. Those supporting an application sometimes end up talking to Betty the cleaner who is on a landline 20 metres from the computer with the issues, the only reason you are speaking to her is because she just happened to pick up the phone, on further questioning she does have an e-mail account but hasn’t used it for a couple of weeks, she politely tells you everyone else has gone home but is sure if you phone back on Monday someone will help out. You think damn if I could just sit at the machine and see what’s actually going on. Also describing what can be UI graphical issues in text or speech is incredibly inefficient 95% of the useful information is often lost in translation. It can be impossible to articulate or describe UI problems except by demonstration. A Developer operator that is local doesn’t have to rely on understanding the problem second hand their first experience of the problem is often first hand even if it’s not they often immediately familiarise themselves with it first hand. What’s better than having an excellent logging system for tracking problems? Yes you bet, not needing one in the first instance.

Aligning your staff with Corporate Objectives – there is a reason rental houses tend to be more run down than privately owned properties. If something goes wrong it its not going to be the occupiers cost to fix it – its not my job to make it work well. This is rarely the case with people who build and operate their own systems their motivation quickly aligns with the organisations objectives – if they can make it work better by predicting problems and fixing them before they arise, their life is made easier so, they tend to get in gear.

Motivation – if they can make it work better by predicting problems and fixing them before they arise their life is made easier, that is a powerful motivator to get things right and to predict problems before they arise. The people who fight for systems are usually the users. I’ve come across a lot of IT support where they get it to an “acceptable” level which usually means it gets a completion tick often the minimum is done to get the thing working. I was once on a project and was trying to get an icon working for the web application. I got the icon from the central team but wasn’t very happy with the look of it as I thought it looked bad. I asked the project manager he agreed it looked crap but his closing comment was “who cares it was authorised”

Education – Hey guess what you learn things about your work if you know how your tools work. You start making connections that feed back to improved practices. There’s an actual name for this Constructionism proposed by among others Seymour Papert – actually the ideas been around for about as long as people have been able to think.

A quote attributed to Aristotle ;

“For the things we have to learn before we can do them, we learn by doing them”

I believe people to be naturally intelligent with the ability to pick things up – especially if they are immersed in the problem – case in point learning a language. Everyone learns it by immersion and because they have to. I don’t like the implication that because most people aren’t going to be the next Einstein they can’t be taught things I have yet to come across a colleague, myself included who hasn’t been more useful because they know more rather than less.

Reaction Time – for insurance purposes people are duty bound to mitigate damage. Now they do better job at this if they are actually on site when things happen. Central developers often have very little visibility on some of their projects and it may be days before they hear about something. Often the time to reaction can be critical in reducing damage. A water leak spotted in five minutes is fixable – left for a day and its a disaster. Granted the majority of applications aren’t that important however the same principle still applies.

An on hand developer operator doesn’t preclude additional support whereas central support requires it – they do the easy stuff they call for help for the difficult stuff. Additionally they are reliable contact for accurate description and dissemination of important information. This can be very useful. It is easy to get outside help in when things are needed it is not easy to suddenly train up local staff to accurately describe a difficult problem.

Good pedigree – hang on here’s some famous developer operators – Mark Zuckerberg programmer entrepreneur / Jeff Bezos entrepreneur and computer scientist / Bill Gates entrepreneur and programmer / David Braben entrepreneur and programmer / Sergey Brin computer scientist and entrepreneur. You might know what companies they founded. Do they promote dev-ops at their companies – I think they do. Oh and I even left out Elon Musk

Prioritisation of Work Load – Local users are far better at judging importance. The importance of a job may not relate to its difficulty or complexity. This makes it a requirement to understand the domain when deciding on what problems need to be solved first. Here’s an example – a company is going to close on a deal if they can print out and deliver a tender document. The users were a bit rushed and left it to the last minute. IT support know roughly what is going on but aren’t completely ear to the ground on tendering so the main printer support guy is off on holiday. Besides he thinks in the normal course of events a dropped printer is not an emergency. Extreme example but this kind of reward / problem ranking happens all the time. Ok computer support systems allow you to rank problems – that kind of fuzzy logic is exactly the kind of things that never really works out well and usually doesn’t allow for altering of priorities after submission. This can lead to businesses adopting the everything is important or nothing is important default setting. The first has people shouting at each other for totally unimportant tasks while the latter leads to speed of a sloth.

Security is a phony excuse – you have no defense other than vigilance against a sustained attack from an internal person who is talented. Setting ridiculous security levels on everything to protect against a few valuable parts is totally counter productive. The de-motivation of staff and subsequent inefficiency of process is a far greater danger to the viability of your business than fraud is likely to be. Resulting reduced levels of visibility which ironically can increase the opportunity for fraud and likelihood of major errors.

Internal Development Good or Bad

Elite Dangerous from Frontier Developments an example of a game built on internally developed game engine – and they are seriously kicking ass as a result.

It is tempting having been burnt with IT projects to say right that’s it I don’t trust consultants anymore I’m going to try and do everything by myself we cannot trust outside companies with our valuable processes – we are after all primarily purely a process company and our processes are golden to us. I must admit I have had periods of my working life where I have been sympathetic to this view. I have found Internally developed systems great because – they motivate internal teams, they increase knowledge of systems design, they can be completely market leading, they can be incredibly flexible and reactive, they really engender responsibility and accountability, they can be very incremental and adaptable and certain individuals can develop systems often using existing IT infrastructure for solely labour costs. (Why employ capable people if you don’t want to use them?)

Against this there are some fairly big black marks which for some are insurmountable.
They tend to be very person dependent with a lot of power resting with certain individuals
Often those individuals are not necessarily chosen by management and often management really don’t like this.
People move on
Most systems will take a year to at least get up and running and sometimes solutions are needed quicker than this.

As a result I would always suggest a mixed strategy of allowing talented individuals to develop those areas for which there are no good products on the market while encouraging buy in of good tools and good products where tools and products do exist. It really should not be an either or and going down solely down either path could lead to problems. It is of course rare to go solely down the all internal route but I am aware of companies only going down the externally produced route.

But be aware even if you are going down the open source and internal development path – be prepared to invest. Buy good IDEs – don’t skimp on database support. Hire consultants (but please give them focused tasks non delivery of results from consultants is often because they’ve been hired without any real idea of what is required of them) Buy products because they look interesting. Financially support open source projects that are actively contributing – not because your liberal with your money but because value is value chances are you can still choose a cheaper path that benefits you and the providers by not leaving yourself open to the kind of consultancy that costs but does not provide. Most of all its your chance to buy in and vote on the future of your software. Open source providers will sit up and notice pay attention and give real weight to your requests.

Be warned though this kind of imagination and vision requires allowing good visibility and control across large parts of the network something that seems to contradict the general trend towards tighter formal security (at least where I work). I would argue however that tighter security often leads to loss of accountability and responsility (a lack of people who can track through all the programs of an issue) resulting in people and especially management being completely blind sided by problems and counter intuitively greater risk of negilgence and greater opportunity for fraud. [Financial Crisis and the Accounting profession anyone?]

I would add that if you really want to be world class you are going to have to take control of your software.

Why you and your Organisation should make Web Bespoke part of your operations.

Don’t get me wrong as a platform for communication I consider things like facebook and Linkedin as necessary evils for what is essentially cloud service market places but my preferences are always…

No adverts
No demands to have login prior to viewing site
As much annonymity for visitors as possible
Long form writing!!!!

I guess the thing that irks me the most is the light manipulation that occurs on these sites. They gently nudge you towards their wishes not your own. I must say as an exercise in communication I am much happier with this format where I have greater editorial control. (albeit a bit less traffic)

Its why I really love bespoke ( or as bespoke as possible ) if I could I would be designing my own operating systems.
Maybe one day.

Why?

2 words

Greater Understanding.

Leading to improvements in

1) Flexibility
2) Control
3) Ability to anticipate
4) Adaptibilty
5) Automate tasks ( Given time in certain cases I have been able to completely design out tasks )
6) Improved long term planning
7) Increased reliability
8) Faster response times
9) Reduce requirement for others time ( always something which is a complete premium )
10) Better timing
11) Massive coordination improvements ( yes why ask someone to tell you about something why not give them the ability to edit the information themselves – I can stay at home, kind of)
12) Improved motivation

It allows me to experiment which is when I learn the most and I think leads to the holy grail of increased productivty and reduced cost.

Want to increase the productivity of you and the people around you? Give them as much flexibility as possible and open up your design environment. I can’t see anything but advantages resulting from this both personally and for the organisation.

A good example of an organisation going bespoke with their web design …

http://www.theregister.co.uk

Details of their setup.

http://www.theregister.co.uk/about/company/website/

Configuration vs Programming

I used to find configuration kind of frustrating – highly repetitive unintuitive and often changing as software versions change.

I now consider it a simple exercise in rote learning for which there is no solution except perseverance, patience and determination and I try to find out the names and numbers of the key administrators.

This change in perception means I no longer feel the kind of frustration I previously felt. I am also much more likely to refer to manuals than trying to guess my way through a menu system which was in retrospect the impatience of youth. As a result I am much more organised in documenting and keeping documentation and I think more productive as a result.

The amount of setups that are hindered by insufficient security privileges being available to the individual doing setup must amount to millions of lost hours. Please for those giving configuration tasks to individuals build in large amounts of time for configuration. If you don’t you’ll probably just be burnt.

Programming by comparison seems gloriously imaginative and logical. Makes me think that a fundamental reason why users hate changes in Operating Systems is because of configuration. They have to re-learn quite a few sets of obscure unintuitive procedures no matter how nice the UI is, they last sorted out X years ago when they bought their previous device. As for the XP, Win 7, Win 8 debate – personally I like Win 8.1, got it on my surface. Maybe because the configuration of Win 8.1 seems like a complete doddle to the kind of obscure software packages I normally have to deal with.

VBA Function to allow alteration of Key Mapping for Multi-lingual support

Important
Proviso : bear in mind that different Windows operating system versions have different support for languages – with Windows 7 you require ultimate version of the OS and then you have to ensure that the required language pack is installed – happy to say that Win 8 has language support as standard although you will need to specifically request certain regional input options within the settings. Windows 10 is similar, language support is free although it is likely you will have to specifically configure languages if there is more than one. My environment when I first solved this was Windows 7 ultimate.

So the problem – You are multi-lingual (or trying to be) and you regurlaly need to change mapping of your keyboard between alphabets (you can also touch type in both alphabets). You can do it manually everytime you need to change but it’s a pain, you have a database with fields some of which are in one language the others of which are in another language. You would like to alter keyboard mapping to specific languages on entering particular fields but how do you do it?

The following uses the Windows API to change the keyboard language globally. This can be done through VBA in MS Access no problem remembering the provisio that your OS must support your chosen language.

Firstly place the following in a module (note no end function required)

Public Declare Function ActivateKeyboardLayout Lib "user32.dll" (ByVal myLanguage As Long, Flag As Boolean) As Long 'define your desired keyboardlanguage

Then you can call the function from any form event.
Eg on field GotFocus and LostFocus

'1049 Russian keyboard language layout
'2057 English(United Kingdom)keyboard language layout
'1033 English (United States) keyboard language layout

Private Sub A_GotFocus()
Call ActivateKeyboardLayout(1049, 0)
End Sub

Private Sub A_LostFocus()
Call ActivateKeyboardLayout(2057, 0)
End Sub

You can find the LCID decimal codes here
Microsoft Windows Location Identifier Codes LCID

Wax on Wax off

One of the most useful and easy improvements I ever made to working was simply learning how to touch type. Its never been particularly necessary for programming as I find anytime I’m programming I write relatively little and the combination of tabs, weird characters, automatic code generation, intellisense and strange syntax results in a fairly uniform speed, touch typing or otherwise. No were it tends to be useful is simply for smashing out quick communications with individuals. If things are going well things tend to change quickly. Yes I need that no hang on things have changed cancel that have you got this? That kind of thing. This is were it helps to have a work colleague who is also a quick typist and a quick responder.

So I went out and bought what is called a mechanical keyboard from Das Keyboard (link to their site below) a name I might add was quickly noted as somewhat inaccurate. A colleague immediately pointing out that a mechanical keyboard really has no electronic parts whatsoever like the old key and lever ones you sometimes see in old black and white films.

http://www.daskeyboard.com/model-s-ultimate/

I settled on a the S Ultimate model which has no labels on the keys. I figured hey I’ve started a bit of Russian if you change the key mapping to cyrillics any letters are useless anyway so lets go hard core. It arrived at my work and female colleague no 1 enjoyed watching me eagerly unpack and plug it in. Now I thought I was a touch typist. Turns out that wasn’t quite true. To register the keyboard I had to re-start the OS. Fine except I immediately had to re-enter my password to get to desktop. Three times later I had to phone support to unblock my password. Yep you guessed it I wasn’t quite the Ninja touch type artist I had fully convinced myself I was.

I’m down with it now but it did require me upping my game. I am particularly careful using some of the USB sticks as those that are encrypted have a three strikes and your out policy with NO recovery – that did slightly concern me.

So what is it like as a keyboard.

I was immediately about 4% faster I have raised my average speed from about 59 words per minute to something like 66 words per minute. On the rare occasion that I don’t make any typing mistakes I can get up to 75. Think of the discipline some must have had to use the old mechanical keyboards I’m sure that the requirement to use typex would have concentrated the mind on not making mistakes.

It makes me think that maybe Das Keyboard should do a special keyboard with the option for electrifying the delete key.

I am writing a letter now.