Month: August 2017

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 connections is not always straight forward and as ever involves configuration – something which often evades all but the most accurate of intelligent guesswork. This is set out for MS Access 2003 but I would expect this to work on all versions of MS Access.

Use the architect version of the MySQL driver that relates to the version of MS Access that you are using. In this case MS Access 03 is 2003 so I used this.

MySQL ODBC drivers at August 2017

Install as per normal driver.

Then open up ODBC Data Source Administrator – I have two options here 32 bit and 64 bit – its not clear if there is a difference but I have been choosing the 32 bit version – Navigate to file DSN and then hit Add…

You will be presented with a Create New Data Source window – navigate down to the MySQL and here choose unicode – ANSI and Unicode are two character encodings that were historically in wide use. Ansi is very old and is used by older operating systems like Windows 98. Unicode is newer which has a vast character set and is used by Office 2003 and upwards. UTF-8 is even newer.

Select then hit the next button.

You will be presented with a Create New Data Source dialog in which you can hit the Browse button and create the name of your file dsn. Here I have already created three – 2 are SQL Azure dsns and the third is a link to my inspirunner database. A hosted account.

You should then be shown the MySQL Connector / ODBC

You absolutely must know the name of your server – the port it is listening on and the user password – once these are filled in selecting the downard arrow should allow you to select the database.

Hit test and you should see success and then hit OK.

Now simply go into MS Access 2003 as per usual right click in the tables window and select the file dsn from the location you stored it in – you should be given all the tables from which to select

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 and we needed to transfer all the information across to a new system which initially was being run in parallel. Over several months myself and a colleague built up 500 queries that pulled out all of this information and placed it in a staging access database that was then loaded into a new Oracle backend. The queries didn’t just export the data they did a significant amount of transformation as the source and target databases had different structures. By being able to script the queries we had created we could take the information out of the source database at short notice. Generally in 2 hours. As we progressed further through the project we would get into the habit of running script of queries periodically as we saw fit.

Generally we did this by creating user defined functions that scripted the queries we wished to run.
This is an example of the format that we used for these functions.
DoCmd.SetWarnings False is important as without it you have to stay at your computer to hit annoying OK buttons 

Public Function RunQueries()

DoCmd.SetWarnings False
DoCmd.OpenQuery ("Query01")
DoCmd.OpenQuery ("Query02")
DoCmd.SetWarnings True
MsgBox "Finished"

End Function

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 functions
2.How to run them
3.How to string multiple functions together (or script them)

Here is a simple user defined function that we will use for demonstration purposes.

Public Function DisplayCompleteTime()
Dim X As Date
X = Now()
MsgBox "Finished Function at " & X
End Function

1.Where to put Functions?
There is more than one place to place functions. They can be placed in forms, in modules or in class modules. Here I describe how to place them in a module in a MS Access 2003 database called ScriptDatabase the menu path of how you get to the module section varies from Access version to access version but they are very very similar.

Open the Database window

Click on Module and then Click on New a new modal pop up screen should appear

Take your Function and place it in the module
I like to type Option Explicit at the start of every function.

Using the menus save the module – I saved my module prior to taking the snapshot of the screen – if you haven’t done this it will automatically ask you to save the module on exit.

2. Running a Function
OK so you have a function in a module which is saved but you want to run it.
Go back into the module with your function and navigate the menus View / Immediate Window.

How the immediate window displays is a bit unpredictable sometimes it comes up as a modal sometimes it is placed within the module screen and squeezes other sections out either way is should be a blank area with flashing cursor at the beginning.

Now to run your function or functions within the Immediate Window type a question mark followed by the function you wish to run in our case DisplayCompletionTime then simply press return

?DisplayCompleteTime

on return you should get something like this

Congratulations you can now run any function from the immediate window#

3. How to run multiple Functions consecutively

Do same as step 1 but this time type in the following

Public Function MultipleLinkedFunctions()

Call DisplayCompleteTime
Call DisplayCompleteTime
Call DisplayCompleteTime

End Function

as per 2 open the immediate window and then type in ?MultipleLinkedFunctions and press return.

Hitting return will display the complete time this is run three times because you are using a function that asks the function to display three times.

Congratulations you have just run a script 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;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;

and the full OGR2OGR to import Command Line Instruction for a shape file called Monuments.shp would be..

ogr2ogr -overwrite -f MSSQLSpatial "MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;" "C:\Monuments.shp"

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 called T0001OpenStreetMapLayers with fields PKID/Name/Directory/Type/Flag – and produce OGR strings that can then be used to load them into a local SQL Server / SQL Express or SQL Azure

For this to be useful you will need
A version of QGIS
A local SQL Server copy (in this case SQL Server Express)
A database within your copy called OpenStreetMap
All shape files in the same directory
You will also need to figure out how to get all those shape files into the table T0001OpenStreetMapLayers table
A starting database with 2 tables
T0001OpenStreetMapLayers with populated fields PKID/Name/Directory/Type/Flag
T0002OGRStrings blank table with fields PKID/CommandLine – This is where all the Command Line Strings will be stored

Public Function CreateTableOGR2OGRString()

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim db As DAO.Database
Dim O2O As String
Dim LCounter As Integer
Dim strQuote As String
Set db = CurrentDb
strQuote = Chr$(34)
</code>

<code>LCounter = 1
While LCounter < 3000</code>
<code>LCounter = LCounter < 3000</code>

<code>Set rs1 = CurrentDb.OpenRecordset("SELECT T0001OpenStreetMapLayers.PKID, T0001OpenStreetMapLayers.Name, T0001OpenStreetMapLayers.Directory, T0001OpenStreetMapLayers.Type, T0001OpenStreetMapLayers.Flag FROM T0001OpenStreetMapLayers WHERE (((T0001OpenStreetMapLayers.Type)=1) AND ((T0001OpenStreetMapLayers.Flag)=0 Or (T0001OpenStreetMapLayers.Flag) Is Null));")
O2O = "ogr2ogr -append -f MSSQLSpatial " & strQuote & "<strong><mark>MSSQL:server=DESKTOP-JECT7QO\SQLEXPRESS;database=OpenStreetMap;trusted_connection=yes</strong></mark>" & strQuote & " " & strQuote & rs1!Directory & rs1!Name & ".shp" & strQuote & ""</code>


<code>rs1.Edit
rs1!Flag = 1
rs1.Update
rs1.MoveNext
rs1.Close

Set rs2 = CurrentDb.OpenRecordset("T0002OGRStrings")
With rs2
.AddNew
rs2!CommandLine = O2O
rs2.Update
rs2.Close
End With
Wend
End Function

For SQL Azure target databases replace the yellow connection string with something resembling;

MSSQL:Server=tcp:azureinstance1.database.windows.net;Database=TouristDB1;
Uid=tom@azureinstance1.database.windows.net;Pwd=Edinburgh;

There are multiple methods of finding the name of your SQL Instance – Ignoring the fact that you won’t be able to connect to it if you don’t know it – Within SSMS you can right click on the instance and look to properties but the name itself is usually in the instance path of SSMS as well.