Author Archives: Mark

About Mark

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

MS Access VBA Function – Create MS Access Query Object – more automation :)

In line with my general theme of automation here’s the framework of a tiny but potentially very useful function to create queries in the current database, linking this in with the loop queries and say something like the nested IF … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Create MS Access Query Object – more automation :)

Compact Database automatically using this MS Access Function and VB Script

If like me you sometimes need to run multiple SQL statements in MS Access on a regular basis maybe at a particular time some of which are deletes, you will need to find a way to automate regular compacts. Here’s … Continue reading

Posted in All, Configuration, VBA Code MS Access, VBS Scripts | Comments Off on Compact Database automatically using this MS Access Function and VB Script

MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

So in my previous post I had looked at generating single SQL Nested IF statements using MS Access but had highlighted that Access will error out indicating the SQL is too complex should there be more than 13 nested IIFs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

Generate Nested IIF SQL using VBA code

I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Generate Nested IIF SQL using VBA code

Creation of SITE History from Planning Application Polygons using QGIS

In planning it is important to know the planning history on a site. The status and likelihood of approved permission will often relate to previous permissions. Many council planning systems do not specifically relate planning applications to each other and … Continue reading

Posted in Continual Professional Development, Digital Mapping, Geographical Information Systems, GIS, QGIS 2.8.1, QGIS General | Comments Off on Creation of SITE History from Planning Application Polygons using QGIS

MS Access like development environments for the Web – 3 alternatives

So you would like to construct simple applications that you can at the moment create in MS Access but you want to do it on the web. By that I mean you would like to create a data driven application … Continue reading

Posted in All, Code Generators, Continual Professional Development, Database Design, Dev Ops, dot NET framework, General Opinion, Learning, Low Coding Platforms, MS Access, Personal Development, RAD Web Tools | Comments Off on MS Access like development environments for the Web – 3 alternatives

Upload XLS information to a specific table or new table within an SQL Azure Database

I have started experimenting with Microsoft Azure if you haven’t already you can get a free experimentation account here; Microsoft Azure Trial account with £125 credit This gets your registered on Microsoft’s cloud and after a free trial period you … Continue reading

Posted in All, Dev Ops, SQL Azure, SQL Server, SSMS | Comments Off on Upload XLS information to a specific table or new table within an SQL Azure Database

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