Category Archives: All

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SQL Azure link to managing permissions

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

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

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

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

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

Connect MS Access 2003 to MySQL

It is an incredible feature of MS Access that it is so easy to connect to different databases and use as a Management Studio. Having a unified platform across all the different backends is very very useful. Setting up those … Continue reading

Posted in All, MS Access, MySQL | Comments Off on Connect MS Access 2003 to MySQL

VBA : Scripting in MS ACCESS to run multiple Queries consecutively

It can be necessary for many reasons to want to run queries consecutively without supervision. This was particularly necessary for me when I was doing a system transfer project. The production Oracle server was being used on a daily basis … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on VBA : Scripting in MS ACCESS to run multiple Queries consecutively

VBA Code : Run a Function and Link Functions together ( or Scripting in MS Access)

MS Access can be used as an extremely powerful scripting environment that can tie together manipulation of data using VBA functions and SQL. Before you can really use this power however you need to know three things 1.Where to put … Continue reading

Posted in All, VBA Code MS Access | Comments Off on VBA Code : Run a Function and Link Functions together ( or Scripting in MS Access)

Upload Shape Files into SQL Azure using OGR2OGR – Explanation of SQL Azure Connection String to be placed within OGR2OGR Command Line

Lets say you have a SQL Azure Server with the following parameters SQL Azure Instance : azureinstance1 Database name within Instance : TouristDB1 Your User Name is : tom Password is : Edinburgh The SQLAzure connection string would be MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1; … Continue reading

Posted in All, SQL Azure, SQL Server, SSMS | Comments Off on Upload Shape Files into SQL Azure using OGR2OGR – Explanation of SQL Azure Connection String to be placed within OGR2OGR Command Line

VBA Function to Create Table of Import strings using OGR2OGR targeting a SQL Server

Do you have many shape files you wish to import into a local SQL Server Database so that you can display them in QGIS or serve them on Geoserver? Here’s a short function I wrote that will take a table … Continue reading

Posted in All, MS Access, QGIS General, SQL Azure, SQL Server, VBA Code MS Access | Comments Off on VBA Function to Create Table of Import strings using OGR2OGR targeting a SQL Server

SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

The first thing you need to get sorted when moving to SQL Azure is having the ability to get your information out and safe if needs be. When experimenting with MS Azure and for applications that don’t have sensitive information … Continue reading

Posted in All, MS Access, Productivity, Programming, SQL Azure, SQL MS Access, SQL Server, VBA Code MS Access | Comments Off on SQL Azure to MS Access – Using VBA to Dump Azure Tables into MS Access Tables

MS Access VBA Function (Part 5) – Run SQL Queries from a table

Clearly there is a problem with generating 66,000 queries and ramming each of them into the Query Database Window. Yes you got it, an MS Access database can only hold circa 32,000 objects (32,768 to be exact). I had been … Continue reading

Posted in Address Matching, All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function (Part 5) – Run SQL Queries from a table

MS Access VBA Functions (Part 2) Address Matching – UK Postcode String Finder

UK Postcode extractor Function GetPostCode(Optional AddressText As Variant) As String Dim AddrTextLength As Integer, TempText As String, TempPicture As String Dim PostCodePics(10) As String, PictureItemNum As Integer Dim n As Integer, x As Integer GetPostCode = “” ‘ default response … Continue reading

Posted in Address Matching, All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Functions (Part 2) Address Matching – UK Postcode String Finder

MS Access VBA Functions (Part 1) Address Matching – Add and Drop Fields

Here are a series of Functions that can be used to help in matching addresses between a dataset that is good (eg Assessors Street File) and a dataset that could be improved – eg a Customer Relationship Management System. ADD … Continue reading

Posted in Address Matching, All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Functions (Part 1) Address Matching – Add and Drop Fields

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 Address Matching, 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 … Continue reading

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

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