Category Archives: VBA Code MS Access

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 – Code Snippets for Navigating using Internet Explorer

Waiting for a web page to load While ie.Busy DoEvents Wend Selecting differing radio buttons Dim ieRadio As Object Set ieRadio = ie.Document.all ieRadio.Item(“datetype”)(1).Checked = True A function that can be used to delay action before an action in code … Continue reading

Posted in All, Data Mining, VBA Code MS Access | Comments Off on MS Access – Code Snippets for Navigating using Internet Explorer

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

MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

Going forward there are more and more systems that have somewhat un-formated text or memo fields. It can be useful to tag fields. Here’s a collection of 2 functions with a script to pull them together designed to create a … Continue reading

Posted in All, Database Design, VBA Code MS Access | Comments Off on MS Access – VBA Functions – Create Category Tag Junction Table by comparing a text field against a table of categories

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

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

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)

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

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