Everyone gets beaten by Complexity

I don’t do that many strategic posts these days (compared with my first posts) but this is really a reminder to myself to always seek out the simplest solution especially when configuring systems and writing code. Less is generally always better. The above post was a DALL E production and the below graph is the simpler one which QED actually makes sense. I would suggest that the line moves to the right with improved education of staff / time / money and number of staff but eventually productivity dramatically drops with complexity no matter how much money , time or people.

Chrome UPDATE – Running a web site in Application mode using a BAT file

I’m running Google Version 84.0.4147.135 (Official build) (64-bit) and last week I noticed that Chrome seemed to update the way you create applications on the desktop. And when setting up new applications there seems to be an ugly introduction of the URL sometimes. Annoying.

I found a source that indicated that you could run a website in application mode by running the following at command line.
This results in a really nice chrome window with your chosen web site within it.

start Chrome.exe --app=https://bbc.co.uk

So I went old school to get a link on desktop!

The good old bat file

@ECHO OFF
start Chrome.exe --app=https://lindoresproduction.azurewebsites.net

Nubuilder Forte – How to? Install on Windows – VIDEO INSTRUCTION

In a previous post I had mentioned that if you wanted a platform focused on database driven (MYSQL only) web application development, Nubuilder FORTE was an excellent choice.

On visiting their site the other day I noticed that they had published a new video out which describes how to install it on Windows. Configuration of Nubuilder is the hardest part and although I haven’t done it this video looks like an excellent instruction. So get yourself across there and have a go.

That link is here

  • How to Install Nubuilder on Windows

    Other links

  • Nubuilder main website
  • Nubuilder FORUM
  • 022 Postgres – Setting up starting variables in psqlrc

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

    Set up your psqlrc defaults

    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

    MS 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 RunQueriesFromTable2("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
    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
    
    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

    20_Type9Target
    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.

    themessage565171

    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