Monthly Archives: April 2017

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