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

Run Web Applications in Application Mode : Google Chrome

Turns out the Chrome browser has implemented a really nice display option specifically for websites that have aspects of an application which allows you to remove the clutter at the top of your browser window and makes a website appear in an application like window.

Go to the web page you are interesting in accessing via an application. In my case rounduptheusualsuspects.org

In the top right corner of the web browser next to the address bar you should see three vertical dots – select it to get the drop down menu and then select More Tools and then Create Shortcut.

You should now see the following dialog – ensure the Open as Window is ticked and then hit the Create Button. You can alter its name if you wish.

Now you get a nice icon on your desktop related to the site and what’s more when you open it up there is no address bar and within the taskbar you get the correct icon for the website see below example.

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 generation function could allow you to take your table of nested queries and write them to the database.

Public Function CreateQuery()

Dim strSQL As String
Dim qdf As Variant

strSQL = "SELECT * FROM T01Contacts"

Set qdf = CurrentDb.CreateQueryDef("GeneratedQuery", strSQL)
MsgBox "GeneratedQuery Created!"

End Function

I like!

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 some code scavenged from the interweb that will allow you to do this.

Firstly create the following function in your target MS Access database.

Public Function CompactDatabase()
      Dim vStatusBar As Variant
      DoCmd.SetWarnings False
            
          If FileLen(CurrentDb.Name) > 2000000 Then
              Application.SetOption ("Auto Compact"), 1
              Application.SetOption "Show Status Bar", True
              vStatusBar = SysCmd(acSysCmdSetStatus, "The application will be compacted on close during compaction please do not interrupt")
          Else
              Application.SetOption ("Auto Compact"), 0
          End If
                
End Function

Next open up notepad paste in the following code save it as a txt file and then in explorer edit the suffix to vbs. Then simply double click on the file within explorer to run it. You should see the access database you have identified in the VB script open and then immediately close at which point it carries out the compact. The eagle eyed will have spotted that the above function doesn’t actually perform a compact. All it does is set the compact on close option to True within Access settings. The VB Script then instructs the database to close and Access compacts the database on exit.

set oAccess = createobject("Access.Application")

oAccess.OpenCurrentDatabase "C:Path\TargetDatabaseyouwantCompacted.mdb"
oAccess.visible = true
wscript.sleep 1000
oAccess.Run "CompactDatabase"
oAccess.closecurrentdatabase

set oAccess = nothing

A VB script such as this could be used to remotely run any MS Access function or functions – simply substitute the “CompactDatabase” parameter (or add further run commands) with the name(s) of the function(s) in the database you wish to trigger and then run the VB Script. Be warned the speed at which functions and commands within functions run may vary when called from a vbscript so you may have to use some experimentation to input things like pauses and waits if you start to get very imaginative with the functions run.

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 in a single SQL.

What happens if you have 20 values that you require to be changed.
Answer = you pick 13 and produce one sql statement with 13 nested if statements and another with 7 nested IIF statements.

But damn it,doesn’t that introduces yet another manual step to what was supposed to be automation. Yes it does this is why I wrote this function which allows you to define the number of nestings and will go off and write the appropriate number. And yes I hear you DBAs saying a better way would be to link in the conversion table at the beginning – absolutely but there may be occasions where you can’t link to the required database.

The following iterates through a conversion table and writes the resulting SQL into a table named T005NestedIIFs – it should be noted that the Wend statement is artificially optomised here and if you have an extremely large code conversion table you may need to work on this code to make it dynamically alter the number of WEND statements depending on the BatchFileNo you wish to create and its relation to the number of records in the conversion table. The below code goes to the last record as part of the recordset count and when at 0 will jump to exit so for smaller code conversions its semi-dynamic but for larger files the limit of values converted with be the BatchFileSize * 200 which may or may not be big enough for purpose.

To have this working you will need two tables and knowledge of a third.
T001ConversionCodeTable
This should have 5 fields
OldValue
NewValue
xFlag1
xFlag2
xFlag3

The flag fields only require a single integer value. They are used to record how far through the conversion table you have reached so that when you break from your SQL query you resume at the finish of your former position.

The queries are placed in table
T005NestedIIFs
In which I have created 3 fields
One marked SQLfield
Targtable
Targfield

Public Function CreateTableofSQL(TargetTable As Variant, TargetFieldforUpdate As Variant, BatchSizeNo As Long)
On Error GoTo Err_Writetofile

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim RecordCount1 As Long
Dim RecordCount2 As Long
Dim LCounter As Integer
Dim SQLString1 As String

LCounter = 1


While LCounter < 200

LCounter = LCounter + 1

SQLString1 = ""

'Three recordsets were testing differing flag fields because order of the integrity of the recordsets were being affected by each other
Set rst2 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag2 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag2)<>1));")
Set rst3 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag3 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag3)<>1));")
Set rst = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag1 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag1)<>1));")

'MoveLast required to ensure correct record count at first pass reset to first after this
rst.MoveLast
RecordCount1 = rst.RecordCount
rst.MoveFirst
rst3.MoveLast
RecordCount2 = rst3.RecordCount
rst3.MoveFirst

SQLString1 = SQLString1 & "UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "="
Do Until rst.EOF = True
rst.Edit
rst!xFlag1 = 1
rst.Update
RecordCount1 = RecordCount1 - 1
SQLString1 = SQLString1 & "IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'"
If RecordCount1 = 0 Then
SQLString1 = SQLString1 & " "
Else
SQLString1 = SQLString1 & ","
End If
rst.MoveNext
Loop

rst.Close

Do Until rst2.EOF = True
SQLString1 = SQLString1 & ")"
rst2.Edit
rst2!xFlag2 = 1
rst2.Update
rst2.MoveNext
Loop
SQLString1 = SQLString1 & " WHERE (("

rst2.Close

Do Until rst3.EOF = True
RecordCount2 = RecordCount2 - 1
SQLString1 = SQLString1 & "(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'"
rst3.Edit
rst3!xFlag3 = 1
rst3.Update
If RecordCount2 = 0 Then
SQLString1 = SQLString1 & " "
Else
SQLString1 = SQLString1 & " OR "
End If

rst3.MoveNext
Loop

rst3.Close

SQLString1 = SQLString1 & "));"


Set rst4 = CurrentDb.OpenRecordset("T005NestedIIFs")
With rst4
.AddNew
rst4!SQLfield = SQLString1
rst4!Targfield = TargetFieldforUpdate
rst4!Targtable = TargetTable
rst4.Update
rst4.Close
End With

Wend

Exit_WritetoFileError:
 Exit Function

Err_Writetofile:
    If Err.Number = 3021 Then
    MsgBox "All records in Translation table written out no more to translate"
    Else
    MsgBox Err.Description
    End If
 Resume Exit_WritetoFileError

End Function

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 it was obvious that this would be better accomplished joining the conversion table in the query editor and moving on from there, particularly because MS Access has a limit on the number nested IIFs allowed in a single statement. Nonetheless I publish it here as it may prove useful.

Here I create a table T001CodeConversionTable that holds the translation from one set of codes to another. This field also has to have several fields in it named
OldValue
NewValue

The variables TargetTable and TargetFieldforUpdate exist in the table that will have the resultant SQL performed on it. 

Public Function CreateNestedIF(TargetTable As Variant, TargetFieldforUpdate As Variant)

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim RecordCount1 As Long
Dim RecordCount2 As Long

Set rst = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst2 = CurrentDb.OpenRecordset("T001CodeConversionTable")
Set rst3 = CurrentDb.OpenRecordset("T001CodeConversionTable")

RecordCount1 = rst.RecordCount
RecordCount2 = rst3.RecordCount

Dim fs, TextFile
Set fs = CreateObject("Scripting.FileSystemObject")
Set TextFile = fs.CreateTextFile("C:\Users\Mark\Documents\NestedIFs.txt", True)
TextFile.WriteLine ("UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "=")
Do Until rst.EOF = True
RecordCount1 = RecordCount1 - 1
TextFile.WriteLine ("IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'")
If RecordCount1 = 0 Then
TextFile.WriteLine ("")
Else
TextFile.WriteLine (",")
End If
rst.MoveNext
Loop

rst.Close

Do Until rst2.EOF = True
TextFile.WriteLine (")")
rst2.MoveNext
Loop
TextFile.WriteLine ("WHERE ((")

rst2.Close

Do Until rst3.EOF = True
RecordCount2 = RecordCount2 - 1
TextFile.WriteLine ("(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'")
If RecordCount2 = 0 Then
TextFile.WriteLine ("")
Else
TextFile.WriteLine ("OR")
End If
rst3.MoveNext
Loop

rst3.Close

TextFile.WriteLine ("));")

TextFile.Close

MsgBox "Created NestedIFs File in C drive"

End Function

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 there may be situations where you would like to create such links. This is essentially an excercise in using spatial analysis to create the junction table to hold what are many to many relationships.

If your datasets are in any way large you will need to set aside a computer so that it can perform the calculations. When I first tried this the process took a weekend with queries running overnight.

Start by obtaining as many years of planning application polygons as you can. Here I use polygon files in shape format.

The polygon file or shape file should be in one file so if you need to merge the shape files you have together. I did this and the file I ended up with was

AllPlanningApplications.shp

Next – Delete all attribute fields EXCEPT the planning application number.

Next – Create a centroids file from AllPlanningApplications.shp I called mine
AllPlanningApplicationsCentroids.shp

The next series of iterations are about getting a unique set of polygons with which we can go forward and generate a set of SITEPKIDS that can be attached to the child records.

Step – Using AllPlanningApplications.shp ADD an additional field called area and populate it using QGIS $area calculation – save this file.

Step – this is where it becomes interesting – in most authorities there are a vast number of planning application boundaries that overlap. Performing a dissolve at this point would result in a large congealed set of polygons that could not clearly identify unique sites. Thus buffering the polygons down we can start to identify unique sites. This is particularly important where boundaries are completely contiguous to each other.

sites the buffering command is used within the geometry tools to try to separate adjacent overlapping and contiguous polygons.

Step ‐ Create two files from the AllPlanningApplications.shp one for polygons less than 4500 metres squared and one for more than or equal to 4500 metres squared. This is to allow for two differing buffering processing to be performed on each.

AllSmallLessthan4500PlanningApplications.shp

AllLargeGreaterthanequal4500PlanningApplications.shp

Now the 4500 is an empirical figure that was subjectively chosen there may be a better figure feel free to investigate.

The following 2 steps also introduce empirical figures for the buffering that can be altered as appropriate.

Step ‐ Take the file AllSmallLessthan4500PlanningApplications.shp and create a buffer polygon file of this with

boundaries of less than 2m lets call it

AllSmallLessthan4500PlanningApplicationsBufferMinus2.shp

Step ‐ Take the file AllLargeGreatethanequal4500PlanningApplications.shp and create a buffer polygon file with

boundaries of less than 20m lets call it

AllLargeGreaterthanequal4500PlanningApplicationsMinus20.shp

THIS NEXT STEP WILL TAKE SEVERAL HOURS IT MAY BE BEST TO DO EACH ONE OVERNIGHT

Step ‐ Perform dissolves on both of these new files ensuring that dissolve all is used names could be something like

Vector / Geoprocessing Tools / Dissolve /

Set input layer alternatively to the two above files and set Dissolve field to dissolve all.

Suggested file Names are

MultipartDissolvedPolygonLessthan4500PlanningApplicationsBufferMinus2.shp

MultipartDissolvedPolygonAllLargeGreaterthanequal4500PlanningApplicationsMinus20.shp

Step You should now have two shape files of a large multipart polygon you want to perform the multipart to single part operation now

Vector / Geometry Tools / Multipart to Single Part

Processing involved with this is typically quick and suggested names for these files are

DistinctPolygonsAllSmallLessthan4500PlanningApplicationsMinus2.shp

DistinctPolygonsAllLargeGreatethanEqual4500PlanningApplicationsMinus20.shp

Add area column and identify the largest polygon on the small files

Add area column and identify the smallest polygon are on the large files you may want to remember this.

Step ‐ perform merge on these two files to get

Vector / Data Management Tools / Merge

CombinedSmallandLargeDistinctPolygonsPlanningApplicationswithbuffering.shp

ONGOING investigation ‐ would Difference be better than dissolve on this and should the above files be put together before

Step ‐ perform dissolve

Vector / GeoprocessingTools / Dissolve

ensure that ‐‐Dissolve all‐‐ is selected

DissolvedCombinedSmallandLargeDistinctPolygonsPlanningApplicationswithbuffering.shp

Step now you want to split mutlipart to single

DistinctPolygonsAllPlanningApplications.shp

Step add field called SitePKID and populate it using $rownum command.

Step

Vector / Data Management Tools / Join Attributes by Location

Set Target Vector Layer as

AllPlanningApplicationsCentroids.shp

Set Join Vector Layer as

DistinctPolygonsAllPlanningApplications.shp

Ensure that Keep all records (including non‐matching target records are kept)

Output Shapefile suggestions

AllPlanningApplicationsCentroidswithSitePKID.shp

If there are centroids without Site PKIDs put them to the end and give them consecutive unique row numbers. The attribute file associated with AllPlanningApplicationsCentroidswithSitePKID.shp should now be a child table of the shape file DistinctPolygonsAllPlanningApplications.shp perform checks here to see if all centroids within a polygon defined by the distinct polygons have the same SitePKID and that it is matched by the SitePKID of the Parent shape file.

You should be able to do a join on the this file to get the PKID back into the very original file.

AllPlanningApplications.shp

Finally perform a dissolve on the corrected AllPlanningApplications.shp file but this time dissolve on the field

SitePKID

You can call this

DistinctCorrectedPolygonsAllPlanningApplications.shp

QED!!!!

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 with somewhat complicated forms that can be accessed by anyone through either IE or Chrome anywhere in the world with a simple login screen at the front to prevent simply anyone accessing the applications collecting the information into a database. What are your options for programs that will assist you in a MS Access like environment rather than going the full IDE deep dive – Visual Studio route – for what I consider to be a reasonable fee?

From my experience the unicorn of access on the web is slowly coming to fruition BUT for the vast majority of people with a budget similar to that for MS Access – lets say £200 ($250) a year for unlimited applications there is simply nothing which is quite as easy and powerful as MS Access. Some are pretty close but simply not as stable and require typically several magnitudes greater amount of configuration. WYSIWYG design isn’t quite as WYSIWYG and stability is a few orders lower than the desktop.

What you are probably looking at can typically be described as either RAD tools for the Web, a Low Coding Platform or something called a Code Generator any of those phrases can be useful for Google.

Assuming you don’t have your own servers whatever you do you will need to spend money on a web host.

The minimum this is likely to cost you is in the region of $15 a month. If you don’t want to spend the next 6 months learning about the insides and outsides of frameworks then I would suggest you go to one of the below three providers who all provide complete environments set up and ready to go with their particular generators pre-installed. This is good value it is extremely difficult to beat these guys on cloud hosting costs and unless you are very advanced and have very particular requirements its a waste of time to try. All three of the below providers will allow you to create limitless number of applications albeit you are limited by the space you hire on your web server. Similarly distribution will be limited by the quality of web server you sign up for. In all likelihood if you have few users it is unlikely that the coding front ends of your applications will be a limit to the number you create more likely the size of databases you are attaching them to and the shear time you have available to create applications.

For a period I was paying a monthly amount for a Nubuilder Pro hosted platform. This performed well and I could create an unlimited number of applications. As it was so hosted I skipped the step of learning some of the deeper parts of the initial configuration. I hope at some point to go back to this. It is open source and seems well maintained with a very dedicated developer. The developer re-wrote much of it and at March 2019 it latest re-incarnation is Nubuilder Forte.

Be warned n-tier web applications do not play as friendly as the desktop you WILL be slower to construct applications than you are on the desktop, getting into it WILL take time and a bit of commitment, you WILL have far less flexibility regards coding, there WILL be less people about to ask questions and there is far far less WYSIWYG design capabilities, error trapping is poor and errors are far more likely to be fatal and the really big warning is that on release of new web frameworks you may not necessarily be able to update without a full site re-design (A fact that comes as a nasty surprise to many CIOs and Project Managers when they realise that they are locked into front end system replacements every 4 or 5 years ) Know how to get data to your local environment out of the back end and accept that the front end is ephemeral and not likely to last in the same way as your desktop applications. (Your database will last but don’t expect to be running it through the same front end ten years from now). Accept that you will now have monthly or annual rental fees for cloud provision.

That said the design of these items is significantly faster than its ever been.

Scriptcase and ASP Runner dot net (Xlinesoft also produces a PHP equivalent generator) have free downloads that are good for getting started.

Commit to one and go for it. – I’ve got both PHP and ASP.NET solutions.. Nubuilder only connects to MySQL whereas Scriptcase and ASPRunner.NET connect to pretty much any database. I started with Nubuilder and am using ASPRunner.net as well because importantly it connects to SQL Server and it was easy to get up and running in MS Azure. Scriptcase is php based and I believe the applications it build require some kind of security program to sit on the web server this put me off – they do however have hosting that you can sign up for which is pre-configured. Their IDE is web based which could be a winning advantage. One of the great advantages of ASP runner dot net is that the program produces an open web application that should run on all stock servers. I found Nubuilder Pro (now Nubuilder Forte) really conceptually elegant which despite its rather drab looks is incredibly flexible the applications it produces are however limited to MySQL and non responsive (But being non responsive you get get more detailed forms!). I would probably be able to change it’s look if I was prepared to get my own server and install everything on it myself. That is not something I have time to do at present.
Nubuilder hosts its IDE in the browser which again is an advantage. ASPRunner.net is more traditional in that you have a program running on a desktop that creates the plumbing of your application which you then need to push to a server for publication  this has the advantage that you get to see the plumbing in the background which makes backup of the site easier but publishing slightly harder.

You may have heard of other generators / design applications out there for example – Zoho Creator / Alpha 5 / Outsystems these hold your hand even more but as a result are even more proprietary and won’t fit in that budget of £200 per year ( by quite a long way!)

Some further information on costs – nubuilder being open source in theory could scale for very little money espectially if you have your own servers already. Scriptcase and Xlinesoft ASP Runner product have an initial fee followed by annual subscription – you may be able to configure it so that you can create unlimited applications for that one fee (if you have good access to web servers ) but it is likely that initially there will be some kind of variable cost per additional application you wish to build. I am using MS Azure with ASP Runner dot net and a developer database costs me about £5 a month with each application being hosted in a web application service which again costs £5. With both Scriptcase and ASP Runner if you stop paying the annual fees your applications will continue to work you will just not get version upgrades. You will be able to step back into version upgrades but you may need to restart your subscription with an additional fee.

Nubuilder Forte Link

Scriptcase Link

ASP Runner – PHPRunner and ASPRunner.Net Link

Good luck

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 will be able to continue with a Pay as You go Account which depending on the services that you go for can start at very cheap rates.

In order for this to work you will need the following
1) Microsoft Azure account
2) An SQL Azure Database
3) SQL Server Management Studio downloaded and installed on the machine you will be uploading from, this can be obtained from SSMS download link be warned its over 800mb. Here I use SSMS 2016
4) Know your server name this is generally [Yourname].database.windows.net
5) Login and Password (I use SQL Server Authentication)

Testing things out I have been using the Web Apps Service to run a website and connect to an SQL Azure Database – both on the cheapest options.

What makes the website particularly cheap is that it can be stopped and started and by paying for it by the minute you can really get a powerful demonstration sites up and running and stop them immediately after the demonstration for very little money.

So after having created an Azure SQL database (Microsoft Create Azure Database Tutorial)I wanted to get a decent number of records into it. Which would be the starting position when taking on most work.
Here I use the Lichfield Planning Application information previously referred to in this post QGIS Import. What I did was take the 45,000 records of planning applications from the shape file. I did this by opening up the dbf file of the shape collection in Excel 2003 and then saving it in excel format. This will be used later to import into the database.

Having your excel file ready the next step is to open up SQL Server Management Studio and connect to your Azure Database. The parameters with regard to username and servername will have been setup when you created your Azure database it is important that when you create your Azure database you somehow record these details.

Next highlight the database – in my case this was DB001 and right click to get tasks.

At this point you enter the import wizard windows dialog boxes and having passed the opening welcome screen , a screen that can be turned off for subsequent navigations, you should hit your first screen that allows you to define the format of the file that should be imported.

The next step is about the only one that is slightly confusing – you are given a number of different options for the target – for me SQL Server Native Client 11.0 worked for me.

Now using the previous parameters specific to your database server and your database name complete the next dialog.

The next dialog asks you whether you want to copy all information or want to write a query to filter the information to be imported. For my example I chose the all import item. Here I select the database and then I am able to see the from and too destinations.

If you wish to import into an existing table use the drop down to select tables from the database – if you wish to import into a new table you can type in the name of the new table within the square brackets.

Here I create a new table called T010Test and import and then continue through the import wizard dialogs until on completion of import you should see a similar screen to that below. It is possible to go into the edit mappings if you are copying into a table that already exists. This will give you a preview showing to what extent the mapping will be successful and how the fields map. You may wish to alter the names of columns to match your target table at this point.

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 share. Even some projects that because of their sensitivity I would never want to share but despite the difficulty of designing really user friendly user interfaces on the web the ability to distribute your applications to everyone in the world is a very powerful attractor which is quite clearly going to be a game changer.

I have therefore started to experiment with data driven online applications and so far I am impressed.

To get started you will first want to sign up with an Azure account.
Azure Portal Sign In

I can see in the future I will probably be using Access as a desktop platform for writing queries and personal content curation with SQL Azure for projects where I need to communicate with others linked through the apps service in the Azure portal.

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 of the Office 365 subscriptions.

On Friday the 4th of November Microsoft announced that it would be included in the Office 365 Business and Business Premium subscriptions.

MS Access Office 365 announcement

Not only that but it would appear that Microsoft seem to be actively embracing a more visible development strategy for the product. To be fair for many years they have had regular summits at headquarters with super users to allow them to give feedback on product.

They now have a suggestion box that gives some indication of how they are collecting information on what developments they should be working on and are working on in the future
MS Access Suggestion Box

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 2016 this was available for download from the following link

Lichfield Planning Applications

Open up QGIS and add Lichfield’s planning application shape file
qgisessen2142

Now scan along the top menu and go to Database

Select the sub menu DB Manager and then DB Manager

dbmanager

The following windows dialog should appear

dbmanagerdialog

Expand the area on the left named PostGIS – any PostGIS instances that you have created should be visisble here. Note you will have to have the PostGIS server running. Then highlight the actual instance that would like to import information into.

In this case I use the instance LocalPostGres

dbmanagerdialog

Choose the third icon from the left.
dbmanagerimportlayerfile

It should be noted that the window on the right may or may not show the correct connection to the database on the right.

importdialog

Name the table you wish to create and then hit OK – additional parameters are available.
There will be a delay before a confirmation of successful import happens – try to not issue commands during this time – once confirmation has been received go back into the PostGIS option and add the layer.

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 saving and closing and then moving to the next document and starting the process again.

As such it takes the code relating to looping through recordsets and also the code relating to generating word documents and combines the two. This could be very good for automatically generating whole host of different things.

It uses the WEND statement rather than the Do Until Loop as I was told it was better practice.

Function AutoGenerateParentChildWordDocuments()

'Make sure the name of the recordset is unambigous
'Good practice to reference the actual library
'Please ensure that you go to Tools - Refererences and select Microsoft Word 11 0 Object Library

Dim rs As DAO.Recordset
Dim db As DAO.Database

Dim rschild As DAO.Recordset

Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document

Set db = CurrentDb
'Place your SQL for parent records to be created
Set rs = db.OpenRecordset("SELECT * FROM T001ParentRecords")

If Not (rs.EOF And rs.BOF) Then
    'There are no records if EOF and BOF are both true you are at the end and at the beginning
    
    rs.MoveLast
    rs.MoveFirst
    While (Not rs.EOF)
        Set wrdApp = CreateObject("Word.Application")
        
        'Create the new document
        Set wrdDoc = wrdApp.Documents.Add
        'The following line can be altered to open the document on the screen
        wrdApp.Visible = False
        'Next setup the margins of the document
        wrdDoc.PageSetup.LeftMargin = CentimetersToPoints(1.27)
        wrdDoc.PageSetup.RightMargin = CentimetersToPoints(1.27)
        wrdDoc.PageSetup.TopMargin = CentimetersToPoints(1.27)
        wrdDoc.PageSetup.BottomMargin = CentimetersToPoints(1.27)
                
        With wrdDoc
        
            .Styles(wdStyleHeading1).Font.Name = "Algerian"
            .Styles(wdStyleHeading1).Font.Size = 14
            .Styles(wdStyleHeading1).Font.Bold = True
            .Styles(wdStyleHeading1).Font.Color = wdColorBlack
            
            .Styles(wdStyleHeading3).Font.Name = "Courier"
            .Styles(wdStyleHeading3).Font.Size = 12
            .Styles(wdStyleHeading3).Font.Bold = False
            .Styles(wdStyleHeading3).Font.Color = wdColorBlack
            .Styles(wdStyleHeading3).NoSpaceBetweenParagraphsOfSameStyle = True
            .Styles(wdStyleHeading3).ParagraphFormat.Alignment = wdAlignParagraphJustify
                        
            .Styles(wdStyleHeading2).Font.Name = "Arial"
            .Styles(wdStyleHeading2).Font.Size = 12
            .Styles(wdStyleHeading2).Font.Bold = True
            .Styles(wdStyleHeading2).Font.Color = wdColorRed
            .Styles(wdStyleHeading2).NoSpaceBetweenParagraphsOfSameStyle = True
            .Styles(wdStyleHeading2).ParagraphFormat.Alignment = wdAlignParagraphJustify
                        
            .Styles(wdStyleNormal).Font.Name = "Arial"
            .Styles(wdStyleNormal).Font.Size = 10
            .Styles(wdStyleNormal).Font.Color = wdColorBlue
        
            'Better to set style before insert
            .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading1)
            .Content.InsertAfter ("Sitename:" & rs!Sitename)
            .Content.InsertParagraphAfter
                  
            .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading3)
            .Content.InsertAfter ("Town:" & rs!Town)
            .Content.InsertParagraphAfter
            
            .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading3)
            .Content.InsertAfter ("Postcode:" & rs!Postcode)
            .Content.InsertParagraphAfter
            
            Set rschild = db.OpenRecordset("SELECT * FROM T002ChildRecords WHERE FKID = " & rs!PKID)
    
            If Not (rschild.EOF And rschild.BOF) Then
                'There are no records if EOF and BOF are both true you are at the end and at the beginning
    
                rschild.MoveLast
                rschild.MoveFirst
    
                While (Not rschild.EOF)
                
                    'Again better to set style before insert
                    .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading1)
                    .Content.InsertAfter ("Consulting Body:" & rschild!Body)
                    
                    .Content.InsertParagraphAfter
                    .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleHeading2)
                    .Content.InsertAfter ("Consultation response : " & rschild!Comment)
                    .Content.InsertParagraphAfter
                                                                                
                    .Content.InsertParagraphAfter
                    .Paragraphs(.Paragraphs.Count).Style = .Styles(wdStyleNormal)
                    .Content.InsertAfter ("Consultation Date: " & rschild!DateUpdated)
                    .Content.InsertParagraphAfter
                    .Content.InsertParagraphAfter
                    .Content.InsertParagraphAfter
                            
                    rschild.MoveNext
                Wend
            Else
            End If
    
            rschild.Close
            
            .SaveAs ("c:\temp\Auto-Generated-WordDoc-" & rs!Town & rs!PKID & ".doc")
            .Close ' close the document
            
        End With ' With wrdDoc
        Set wrdDoc = Nothing
        
        wrdApp.Quit ' close the Word application
        Set wrdApp = Nothing
        
        rs.Edit
        rs.Update
        rs.MoveNext
        
    Wend
    
    rs.Close
Else
    MsgBox "No Records Available for updating exit sub"
    Exit Function
End If

MsgBox "Looped through the records and updated the value number field"

Set rschild = Nothing
Set rs = Nothing
Set db = Nothing

End Function

Download an example database HERE

Boilerplate code demonstrating simple Recordset manipulation

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 Text searcher particularly useful for identifying parameters within web files within applications. When you need to customize a web page
Astro Grep link

AutoHotKey
Automation of tasks program that allows creation of executables that can navigate browsers really very powerful
AutoHotKey link

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 the return value, it seems that subs are faster than functions (at least in .net) because the MSIL code of subs is much shorter when no value is returned. so overall subs are faster when no value is returned.

MSIL stands for Microsoft Intermediate Language – which is the a programming language that has been standardized later as the Common Intermediate Language

Functions vs Sub Routines

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 = 0")
    
        'the data source can be a Table Name a query name or an sql string
        'it would be possible to change the SQL to set to another set of records
        'Check to see if there are any records in the set
        
        If Not (rs.EOF And rs.BOF) Then
        'there are no records if End of File and beginning of file are both true
        
            rs.MoveFirst
            
            Do Until rs.EOF = True
            rs.Edit
            rs!ValueNumber = 300
            rs.Update
            rs.MoveNext
            Loop
            Else
            MsgBox "No Records available for updating exit sub"
            Exit Function
            End If
            MsgBox "Looped through the records and updated ValueNumber field"
            
            rs.Close
            Set rs = Nothing
            Set db = Nothing
            
            'libraries for DAO can be found on AllenBrowne site
            'remember to break an infinite loop press ctrl + break

End Function

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 for Seconds (10)

Option Explicit
Dim dteWait
dteWait = DateAdd("s", 10, Now())
Do Until (Now() > dteWait)
Loop

Sleep Function for Delay – Count in Milliseconds (5 mins below)

WScript.Sleep 5*60*1000