This tutorial takes a basic MS Access file and writes an SQL VBA routine from scratch, showing you how to program some SQL. After working through this tutorial you should be able to write an MS Access script to run SQL tasks for you. The article assumes you know how to write SQL queries. If you want to learn that first go here and come back once you have a decent grasp of SQL.
Watch this video to see how to create an SQL routine from scratch or jump to the 4-step guide below. Get the file used in the video here.
MS Access SQL Programming in 4-Steps
- Understand your tables and what you need to achieve
- Click Alt+F11 to open the VBA editor–goto Insert, module
- When the code editor pops up, under ‘Option Compare Database’ type the following on each line:
- Sub vbasql1()
- Dim db As DAO.Database
- Set db = DBEngine(0)(0)
- db.Execute “SELECT var1 into table 2 from table1”
- End Sub
- Feel epic… that is basically it–write a new db.Execute line for each SQL action query you want to perform. Magic!
Why this Technique Rocks
Firstly, for data analysis MS Access is ubiquitous. It is a great middle ground between Excel and advanced analytic platforms or expensive reporting applications. As if Access querying were not good enough in this respect, SQL opens up possibilities tremendously, giving you an ability to record your manipulations succinctly. This technique takes that to the next level allowing you to script a sequence of SQL tasks to do what you want, thus giving you tremendous flexibility to achieve whatever you want with the data. This is very powerful for routine analytics tasks or data modelling processes.
The flexibility is the kicker though… the impact/effort ratio here is very high indeed. Well worth the time investment for what can be achieved.