MS Access – Separate Number from a combined Number Street Field

For the Street Number

Left([No_and_Street_Field],InStr([No_and_Street_Field]," ")-1)

For the Street

Right([No_and_Street_Field],Len([No_and_Street_Field])-InStr([No_and_Street_Field]," "))

Posted in All, MS Access | Comments Off on MS Access – Separate Number from a combined Number Street Field

MS Access front end – SQL Azure back end Link to Configuration Set UP FMS Group

This is an excellent article on linking MS Access to SQL Azure – which is just great if you are wanting to use your VBA skills direct on your SQL Database.

FMS Professional Solutions Group – Luke Chung MVP article

It should be noted that the Primary Key will need to be set to Integer in the SQL Database rather than Big Int otherwise it will appear as #DELETED# when you go in and view the data. This may not be an issue going forward for some users of Office 365 Access 2016 as Microsoft are introducing support for Big Int in MS Access. Congratulations to Microsoft on that small but important change.

Posted in All, SQL Azure, SQL MS Access, SQL Server | Comments Off on MS Access front end – SQL Azure back end Link to Configuration Set UP FMS Group

MS Access VBA Function – create UID starting at prescribed number

Useful function if you are taking two tables with overlapping identity key to be placed in a table that will have a further child record and you wish to separate the new keys.

Public Function WriteUID(LCounter As Long) As Long

Dim rstC As DAO.Recordset
Dim LCountStart As Double

LCountStart = LCounter

Set rstC = CurrentDb.OpenRecordset("TABLEREQUIRINGUNIQUEID")

Do Until rstC.EOF = True

rstC.Edit
rstC!UID = LCounter
rstC.Update

LCounter = LCounter + 1

rstC.MoveNext

Loop

MsgBox "Finished UNIQUEID write"

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – create UID starting at prescribed number

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.

Posted in All, MS Access, Productivity, Programming | Comments Off on Linking to SQL Server / Oracle – don’t skip the set key field on linking

MS Access VBA Function – Loop through Query Objects and write SQL to Table

Continuing the theme of tools that assist the use of MS Access as a platform for transferring data between systems. Here is a small function that will allow you to write the pure SQL syntax of all queries in a database to a table. I personally used this in a system transfer project. The business had given us something called a field mapping plan that identified the table and fields in one system and where they were to be migrated in the other system. Having written the queries I then wanted to go back through and reconcile the original mapping to the SQL to ensure that absolutely every field had been taken across. Writing the SQL into a table allows for table and field combinations to be methodically searched. Quite useful.

Create a table called T001SQLCollection with at least 2 fields – QueryName and SQL. This is where the recordset writes the SQL to.

This is very much a reverse of the previous post function.

Public Function ListQueries()

Dim rstList As DAO.Recordset

Dim i As Integer
For i = 0 To CurrentDb.QueryDefs.Count - 1
Set rstList = CurrentDb.OpenRecordset("T001SQLCollection")
With rstList
.AddNew
rstList!QueryName = CurrentDb.QueryDefs(i).Name
rstList!SQL = CurrentDb.QueryDefs(i).SQL
rstList.Update
End With
Next i

rstList.Close

MsgBox "Finished"

End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Loop through Query Objects and write SQL to Table

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

Posted in All, MS Access, Productivity, Programming, VBA Code MS Access | Comments Off on MS Access VBA Function – Automated Multiple Query Object Creation from previously created table of SQL

Chrome v:56.0.2924.87 – Run Web Applications in Application Mode

Seek and you shall find – was wondering if I could do anything to get rid of the bookmark bar in Chrome.
Then I thought – hold on if I am getting rid of the bookmark bar why not get rid of the address bar as well.

Turns out this is very easy and produces a really nice polished look particularly good for web applications can be used with any website.
So here I will demonstrate it using the blog. I am Running Google Chrome Version 56.0.2924.87

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 Add to Desktop.

You should now see the following dialog – ensure the Open as Window is ticked and then hit the Add 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.

Posted in All, Chrome, Configuration | Comments Off on Chrome v:56.0.2924.87 – Run Web Applications in Application Mode

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!

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

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 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

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 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

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 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!!!!

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 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?

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

I have a cloud hosted provision with Nubuilder Pro – I pay a monthly amount and I can create as many applications as I can fit within my space ( which is more than I can use even on the lowest version ). There are a number of providers that do this. With nubuilder the software is free. With other providers the software that helps manage the construction of the site is not free. Whatever you go for you will need a webserver. Paying one of the guys below means they set up the environment for you. Which for people starting out is a BIG step forward.

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 PHPRunner 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 in particular I can host everything in MS Azure. I find Nubuilder Pro really conceptually elegant which despite its rather drab looks is incredibly flexible. 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. ASPRunner.net is more traditional in that you have a program running on a desktop and then you publish your application up to the web server 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.

Zoho Creator / Alpha 5 / Outsystems 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!)

Nubuilder Pro Link

Scriptcase Link

ASP Runner – PHPRunner and ASPRunner.Net Link

Good luck

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

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

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 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

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

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 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

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 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

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