Category Archives: All

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

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

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

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

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

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

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

Compact Database automatically using this MS Access Function and VB Script

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

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