Category Archives: VBA Code MS Access

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

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

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

MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

So in my previous post I had looked at generating single SQL Nested IF statements using MS Access but had highlighted that Access will error out indicating the SQL is too complex should there be more than 13 nested IIFs … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Generate Multiple Nested IIF SQL statements into table with parameter to set Number of Nestings

Generate Nested IIF SQL using VBA code

I came across a situation at work where we were needing to alter a large number of values in particular fields from one value to another. Here is a function I put together to assist in this. After some thought … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Generate Nested IIF SQL using VBA code

Manipulating MS Word Documents from MS Access 2003

The following code generates separate word documents for each parent record in a table called T001ParentRecords and places the children records relating to the parent record in a word document. It then goes on to format that word document before … Continue reading

Posted in All, VBA Code MS Access | Comments Off on Manipulating MS Word Documents from MS Access 2003

Links to VBA Functions

Allen Browne Functions Index Technet list of functions

Posted in All, MS Access, VBA Code MS Access | Comments Off on Links to VBA Functions

What’s the difference between Sub Routines and Functions

I was curious Sub Routines and Functions appear to perform almost the same thing what is the difference and what are their relative advantages? Functions return a value that is stored whereas subs don’t. The main difference is not only … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on What’s the difference between Sub Routines and Functions

Typical While Loop VBA

Function TypicalWhileLoop() ‘This performs the same as next loop but uses the while loop Dim LCounter As Integer LCounter = 1 While LCounter < 10 MsgBox (LCounter) LCounter = LCounter + 1 Wend End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical While Loop VBA

Typical For Next Loop

Function TypicalForNextExample() ‘This performs the same as the while loop but uses for next Dim i As Integer For i = 1 To 9 MsgBox (i) Next i End Function

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical For Next Loop

Typical DAO.Recordset VBA for looping through and altering

Function TypicalDAOrecordset() ‘Make sure the name of the recordset is unambiguous ‘Good practice to reference the actual library Dim rs As DAO.Recordset Dim db As DAO.Database Set db = CurrentDb Set rs = db.OpenRecordset(“SELECT * FROM T001Main where T001Main.ValueNumber = … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on Typical DAO.Recordset VBA for looping through and altering

MS Access VBA Function – Count Numbers of Records in Tables and list.

Not quite finished yet but place here for later correction. Public Function CountAllTablesRows() Dim rs As New ADODB.Recordset Dim rsRC As New ADODB.Recordset Dim strTbName As String Dim lngRowCount As Long Dim tbl As TableDef CurrentProject.Connection.Execute “Delete from TABLE_INFO” rs.Open … Continue reading

Posted in All, MS Access, VBA Code MS Access | Comments Off on MS Access VBA Function – Count Numbers of Records in Tables and list.