Category Archives: MS Access

MS Access Function CreateSimpleStrings()

A simple function that will loop through and create strings that add a number to a simple string. This string will then be used to create update queries. The same could be done in Excel or any other spreadsheet option … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function CreateSimpleStrings()

MS Access Function – import all CSV files from a directory with the same structure into a single table

This is a really nice function that can be used to place all data from multiple CSVs (with the same structure) into a single table. Here I use the Ordnance Survey’s excellent Code Point data set that gives postcodes in … Continue reading

Posted in Address Matching, All, Geographical Information Systems, GIS, MS Access, Open Source, VBA Code MS Access | Comments Off on MS Access Function – import all CSV files from a directory with the same structure into a single table

MS Access Function – Scan through a directory and write list of files to a table

Pretty much as the title Ensure you have a table called tblFiles with one field called Filename Here I am finding all files in folderspec = “C:\Users\Mark\Documents\CodePoint\Data\CSV” Alter as appropriate Function ShowFolderList() Dim fs, f, f1, fc, s Dim rs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function – Scan through a directory and write list of files to a table

MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

As I have indicated before MS Access makes a brilliant ETL tool. Important in this is being able to connect to different databases. I have set out how to connect to MySQL and SQLAzure before – the following sets out … Continue reading

Posted in All, MS Access, PostGIS, Postgres | Comments Off on MS Access Front End – Linked to PostGreSQL back end – a simple walk through using Access 2003

MS Access Function Collection that can be used to Generate Housing Forecast Figures

Apologies if you are coming here for the first time. This post is a somewhat dense domain specific holding post for some work I did at the weekend to pull together some thoughts. I was thinking that if I had … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access Function Collection that can be used to Generate Housing Forecast Figures

Navigate to Website directly from MS Access using Internet Explorer

One of the major suppliers of planning software to the United Kingdom is a company called Idox Group plc. They produce probably the most popular back office software that runs all aspects of administering planning permission. As such their public … Continue reading

Posted in MS Access, VBA Code MS Access | Comments Off on Navigate to Website directly from MS Access using Internet Explorer

MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

Had to move to MS Access 2010 to do this as no facility for direct to PDF print in MS Access 2003 Where the fields in the QUERY-ListofIDtoPrint includes ID / Field02 / Field03 / Field04 Public Function LoopandPrint() Dim … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access 2010 – Simple Function to loop through a list and Print to file an individual PDF

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

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

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 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 Function (Part 4) – Write queries to a table

This is really great for address matching – take a clean source of information and create a set of update queries looking for strings that will be run on a table with less than clean data. Public Function CreateTableofSQL() Dim … Continue reading

Posted in Address Matching, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function (Part 4) – Write queries to a table

MS Access VBA Function (Part 3) Address Matching – Find X Replace Y

A useful function for replacing characters or strings in a single field. This can be used in advance of address matching to increase the chances of getting matches in fields that have been collected through a UI with little or … Continue reading

Posted in Address Matching, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function (Part 3) Address Matching – Find X Replace Y

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