Scripting MS Access SQL VBA
The process I am going to show here uses some excel datasets to create an indexed table with primary key fields, add data from other sources to the table, then discard any unnecessary tables at the end of the process. Almost all the steps are done using a sequence of SQL queries: here is how to automate that process using a few lines of VBA.
Before we start, I only assume you have working copies of Access and Excel on your machine.
- Create a new Access database and open it
- Download the sample data spreadsheet entitled d_sources2.xlsx HERE and save it in the same folder in which you created the new Access database in step 1
- Go back to your Access database, press Alt+F11 to pull up the VBA Project Explorer window; then, on the standard toolbar, click on Insert–>Module
- In the code editor opens up with “Option Compare Database” at the top. Copy and paste the code from item 1 near the end of this article into the code editor (below where it says “Option Compare Database”)
- Either import the data from each tab of the spreadsheet to Access tables manually or click on some text below the Sub import_data() line in the code editor so the cursor is located in that subroutine then press F5 to run that subroutine which basically imports each tab from the spreadsheet into a table with the name of the tab’s range (instead of F5 you can click F8 repeatedly to see the code executing line by line)
- Check back in the MS Access window to make sure the tables have been added properly (table names are: d_trkdr, d_thh, d_trkd, l_e6s, and d_bsm)
- Now that the sample data is imported you can see how to manipulate the database with a few basic lines of VBA and SQL statements. Go back to the code editor and click on an area below the Sub sr_mid() line so that the cursor is located within the text of the sr_mid subroutine
- Now click F8 to start stepping through the code. After clicking F8 twice check your screen matches Item 2 below
- Each time you click F8 you are executing a command. To understand what the code is doing, take a look at the comments in Item 2 below.
- After you have finished executing the subroutine (i.e. all the way down to “End Sub” line) check back in the Access window to see that the source tables have been removed (the “drop” SQL statement in the code does this) and that tables called d_master and a_summary have been created containing refined data originally sourced from the excel datasets
When setting this up for your own project, essentially once you have set up a subroutine and declared and set the database variable, all you need to do is use db.execute to perform any SQL statements you need.
Item 1: MS Access SQL VBA Module Code
Option Compare Database Sub sr_mid() 'routine to generate returns mi dataset (tr_mid) 'inputs: existing datasets (d_trkdr, d_thh, d_trkd, l_e6s, d_bsm) 'outputs: tr_mid Dim db As DAO.Database 'declare db variable Dim fnm As String 'declare file name variable Set db = DBEngine(0)(0) 'set db equal to the current database (i.e. the database in which this code resides) db.Execute "create table d_master(msnbg text(15), rmdate date, mpn double, msnsrc text(3), constraint ix_msnbg unique(msnbg), constraint ix_mpd unique(mpn, rmdate))" 'create empty table (tr_mid) to contain source data 'write all available msn data to d_master in the section below db.Execute "insert into d_master(msnbg, mpn, rmdate, msnsrc) select msn, mpn, rmdate, 'tkb' from d_trkdr" 'inserts movement data from the dataset 'd_trkdr' db.Execute "insert into d_master(msnbg, mpn, rmdate, msnsrc) select a.msn, b.mpn, b.date, 'tkh' from d_thh a inner join d_trkd b on a.mpn=b.mpn and a.orderno=b.orderno where b.[job status]='complete' and mid(b.[service order description],3,1) in('x','r')" 'insert movement data from valid d_thh data db.Execute "update d_master a inner join l_e6s b on a.msnbg=b.msn1 set a.msnbg=b.msn2 where b.msn2 like 'l*'" 'change msnbg to msn2 if it is an 'l' number db.Execute "delete a.* from d_master a left join d_bsm b on a.msnbg=b.msn_bsm where b.msn_bsm is null" 'delete any invalid data db.Execute "drop table d_thh, d_trkd, l_e6s, d_bsm" 'discard any tables no longer required db.Execute "select dateserial(year(rmdate),month(rmdate),1) as jmonth, count(*) as volume into a_summary from d_master group by dateserial(year(rmdate),month(rmdate),1) " 'create summary table by product type End Sub Sub import_data() 'routine for importing sample tables from the excel file Dim db As DAO.Database Dim fnm As String Dim arr1 Dim i As Integer Set db = DBEngine(0)(0) fnm1 = CurrentProject.Path & "d_sources2.xlsx" 'setting excel filename arr1 = Array("d_trkdr", "d_thh", "d_trkd", "l_e6s", "d_bsm") For i = 0 To 4 Debug.Print arr1(i) DoCmd.TransferSpreadsheet acImport, 9, arr1(i), fnm1, True, arr1(i) Next i End Sub
Item 2: Access Module Code Annotations
Where to apply MS Access SQL VBA
If you work with MS Access a lot, programming it to do multiple tasks is useful. For example if you are running month-end financial calculations it can streamline the process. Perhaps you are tapping SAP order data to generate a weekly sales performance scorecard. Any process that involves hauling out large datasets from somewhere then performing a series of manipulations on the data can benefit from scripting a sequence of SQL queries in Access. As long as you can write basic SQL (click here for an article introducing that topic), after following this article you should be able to program relatively complex processes for getting the data how you want it.
Thoughts, comments, suggestions, ideas–please feel free to comment below.