Author Archives: Mark

About Mark

Mark Brooks a forty something individual working and living in and around Edinburgh

Microsoft Azure – moving to the cloud

I must confess I do love MS Access and I have a number of projects that I suspect will always remain as are – these are ones that are highly complex but very personal that I really don’t need to … Continue reading

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

Microsoft shows MS Access some Love

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

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

QGIS – Import shape file into PostGIS Table

The following uses QGIS 2.14.2 Essen and PostGres 9.5 A number of local authorities have released information through the UK’s data government site. The following example uses a shape file obtained from Lichfield District Council – At 2nd of October … Continue reading

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

Manipulating MS Word Documents from MS Access 2003

The following code generates separate word documents for each parent record in a table called T001ParentRecords and places the children records relating to the parent record in a word document. It then goes on to format that word document before … Continue reading

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

Notepad ++ / AstroGrep / Autohotkey – 3 Useful Tools

Three useful tools for speeding up or automating tasks Notepad ++ Text editor that has formating for programming – I often use it for editing XML documents and writing VB scripts. Notepad ++ link AstroGREP Son of GREP – useful … Continue reading

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

Links to VBA Functions

Allen Browne Functions Index Technet list of functions

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

What’s the difference between Sub Routines and Functions

I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages? Functions return a value that is stored whereas subs don’t. The main difference is not only … Continue reading

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

Typical While Loop VBA

Function TypicalWhileLoop() ‘This performs the same as next loop but uses the while loop Dim LCounter As Integer LCounter = 1 While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function

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

Typical For Next Loop

Function TypicalForNextExample() ‘This performs the same as the while loop but uses for next Dim i As Integer For i = 1 To 9 MsgBox (i) Next i End Function

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

Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset() ‘Make sure the name of the recordset is unambiguous ‘Good practice to reference the actual library Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rs = db.OpenRecordset(“SELECT * FROM T001Main where T001Main.ValueNumber = … Continue reading

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

VBS – Pieces of code

Shutdown computer Option Explicit Dim oShell Set oShell = Wscript.CreateObject(“Wscript.Shell”) oShell.Run “SHUTDOWN -T 60 -S” ‘wait 60 seconds before shutting down Trigger speech Option Explicit Dim speechobject set speechobject=createobject(“sapi.spvoice”) speechobject.speak “Your system is setup and ready for your day” Delay … Continue reading

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

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

Not quite finished yet but place here for later correction. Public Function CountAllTablesRows() Dim rs As New ADODB.Recordset Dim rsRC As New ADODB.Recordset Dim strTbName As String Dim lngRowCount As Long Dim tbl As TableDef CurrentProject.Connection.Execute “Delete from TABLE_INFO” rs.Open … Continue reading

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

Using VBA to write Word Document

Writing to Microsoft Word Firstly a warning – this creates doc documents that can be opened in Word 2010 but are strictly speaking 03 iterations hence the doc suffix First need to load in the library for Microsoft Word (this … Continue reading

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

Using VBA and Databases to create HTML

Here’s some code I used to generate HTML for a web configuration file. It takes a database (the current open one) then looks to a query called QueryTargetInformation and places the fields – PlaceName / EastingMn / NorthingMn / EastingMx … Continue reading

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

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

The announcement in December 2014 that Microsoft was releasing a version of Visual Studio Community to virtually everyone for free and then again a further announcement in February 2016 of Microsoft’s purchase of Xamarin and subsequent release with VS community … Continue reading

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

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

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

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

Early and Late Binding

Good article on Early and Late Binding MSDN Early and Late binding Coding can appear very complicated sometimes but to simplify the difference in terms of implementation this line is very clear; The only difference between early binding and late … Continue reading

Posted in All, MS Access, VBA Code MS Access, VBA Code MS Excel | Comments Off on Early and Late Binding

nuBuilderPro – Import csv into a table of your application MySQL database (Its very easy)

I don’t know about you but for me its pretty rare to start an application without any information. At the very least there may be lookup tables or you have information collected in a spreadsheet. Thus when I came to … Continue reading

Posted in All, nuBuilderPro, Open Source, Personal Development | Comments Off on nuBuilderPro – Import csv into a table of your application MySQL database (Its very easy)

nuBuilderPro – a database driven Web Framework for simple Form Design

I have been seeking a way of designing simple web forms for a reasonable cost for some time. I was continually coming across either very professional products with really quite large annual charges (alpha anywhere) or alternatively products that charge … Continue reading

Posted in All, Continual Professional Development, Database Design, Dev Ops, nuBuilderPro | Comments Off on nuBuilderPro – a database driven Web Framework for simple Form Design

MS Access and Forms – Create a Filtered Autonumber for Child Records

The following uses a function and the before update event of a form. Sometimes it can be useful to have some kind of order field in the child records to indicate the order or version numbers of items. Although an … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access and Forms – Create a Filtered Autonumber for Child Records