Category Archives: All

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

This is a really nice function that can be used to place all data from multiple CSVs (with the same structure) into a single table. Here I use the Ordnance Survey’s excellent Code Point data set that gives postcodes in … Continue reading

Posted in Address Matching, All, Geographical Information Systems, GIS, MS Access, Open Source, VBA Code MS Access | Comments Off on MS Access Function – import all CSV files from a directory with the same structure into a single table

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

Pretty much as the title Ensure you have a table called tblFiles with one field called Filename Here I am finding all files in folderspec = “C:\Users\Mark\Documents\CodePoint\Data\CSV” Alter as appropriate Function ShowFolderList() Dim fs, f, f1, fc, s Dim rs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function – Scan through a directory and write list of files to a table

MS Access – Code Snippets for Navigating using Internet Explorer

Waiting for a web page to load While ie.Busy DoEvents Wend Selecting differing radio buttons Dim ieRadio As Object Set ieRadio = ie.Document.all ieRadio.Item(“datetype”)(1).Checked = True A function that can be used to delay action before an action in code … Continue reading

Posted in All, Data Mining, VBA Code MS Access | Comments Off on MS Access – Code Snippets for Navigating using Internet Explorer

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

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

Posted in All, MS Access, PostGIS, Postgres | Comments Off on MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

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

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

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function Collection that can be used to Generate Housing Forecast Figures

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 … Continue reading

Posted in All, AutoHotKey, E-mail, Personal Development, Productivity | Comments Off on AHK – Useful AutoHotKeyScripts for Outlook specifically

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”) … Continue reading

Posted in All, AutoHotKey, Chrome, Productivity | Comments Off on AHK – Useful AutoHotKeyScripts

Add Open Street Map to Background QGIS Project and then Digitise against imported Raster

The following is a workflow that can be used to get a raster basemap of anything into QGIS which you then reference to Open Street Map Layers ready for digitising against. This will be useful for approximate digitising of masterplans … Continue reading

Posted in All, QGIS General | Comments Off on Add Open Street Map to Background QGIS Project and then Digitise against imported Raster

Links to GIS information for test system design

If you are trying to design software that includes a Geographical element it is easier if you are working with data that makes some kind of sense. The following are a list of sites where you can get good and … Continue reading

Posted in All, Geographical Information Systems, GIS | Comments Off on Links to GIS information for test system design

MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

Had to move to MS Access 2010 to do this as no facility for direct to PDF print in MS Access 2003 Where the fields in the QUERY-ListofIDtoPrint includes ID / Field02 / Field03 / Field04 Public Function LoopandPrint() Dim … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

I came across an interesting issue with a web application I have been trying to create. DEFAULTS in SQL Server didn’t seem to be registering when new records were inserted through the web application. How come? Surely the web application … Continue reading

Posted in All, Database Design, SQL Azure, SQL Server, TSQL scripts | Comments Off on SQL SERVER – Create TSQL Trigger to add date to existing record after INSERT or UPDATE

MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

Going forward there are more and more systems that have somewhat un-formated text or memo fields. It can be useful to tag fields. Here’s a collection of 2 functions with a script to pull them together designed to create a … Continue reading

Posted in All, Database Design, VBA Code MS Access | Comments Off on MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

MS Access – VBA – Open Form and go to record from unbound List Box

Very clean and simple Private Sub List0_Click() Dim stFormName As String Dim stLinkCriteria As String stFormName = “Form1” stLinkCriteria = “[ID]=” & Me.List0 DoCmd.OpenForm stFormName, , , stLinkCriteria End Sub

Posted in All, Database Design, VBA Code MS Access | Comments Off on MS Access – VBA – Open Form and go to record from unbound List Box

Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Simple but can be useful Useful if you are wanting to open multiple tabs in a browser window at the same time @echo off start “Simple Search” “https://planning.westlothian.gov.uk/publicaccess/search.do?action=simple&searchType=Application” start “Google Maps ” “https://www.google.co.uk/maps/@55.8625775,-3.6759593,17z” start “WLC Spade” http://gis.westlothian.gov.uk/wml/spade/” start “idoxEDRMS Login” … Continue reading

Posted in All, Configuration, Dev Ops, General Opinion | Comments Off on Simple Bat File to open multiple Web Pages in One Browser Window with alternate Tabs

Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

There are a whole host of applications in which you may wish to record the biological or legal relationships between individuals. Here is a short investigation of some of the subtleties related to the options you have to model this … Continue reading

Posted in All, General Opinion, MS Access, Normalisation | Comments Off on Pivoting Relationships from Many to Many – One to Many – One to One – and then Key Value – Relationships and Schemas

SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Following on from the previous post I wanted to know the syntax for SQL to do the same but in SQL Azure. SELECT * FROM dbo.T02AttendanceGame T1 WHERE Attendance = ( SELECT max(Attendance) FROM dbo.T02AttendanceGame T2 WHERE T1.FKID=T2.FKID ); I … Continue reading

Posted in All, SQL Azure | Comments Off on SQL Azure – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

So we have a table of Stadiums and a table of attendances. We would like to create a query that shows an individual child record for each stadium of the highest attendances. Quite often you seek the latest or earliest … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access – SQL to Select Distinct list of Child Records based on a Maximum or Minimum Child Field Value

Javascript – Nubuilder Specific to save Date to DateUpdated field on Record Change (not subform)

From the admin screen go to Develop / Forms / Form of choice / Custom Code / Javascript To place focus on the search button function nuLoadBrowse(){ $(‘#nuSearchButton’).focus(); } Function to Get System Date function GetTodayDate() { var tdate = … Continue reading

Posted in All, Javascript, nuBuilderPro | Comments Off on Javascript – Nubuilder Specific to save Date to DateUpdated field on Record Change (not subform)

SQL Azure link to managing permissions

Just a note to myself to go off and study the following link. SQL Azure Permissions

Posted in All, SQL Azure | Comments Off on SQL Azure link to managing permissions

SQL Azure – Take Complete Backup of Azure Database (Structure and Data)

SQL Azure as part of the service offers a number of differing back up options as standard – however you may wish to take additional backups which for instance you can load onto a local version of SQL Server (Express … Continue reading

Posted in All, Backup, Dev Ops, SQL Azure | Comments Off on SQL Azure – Take Complete Backup of Azure Database (Structure and Data)