MS Access SQL using 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 related datasets to that table, then discard any staging 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. This is an incredibly powerful step forward from using Access queries via the UI or in SQL view because it enables you to string several SQL actions together to achieve a desired effect.

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 workbook (d_sources2.xlsx) 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. This opens the VBA code editor where VBA code can be written.
In the code editor opens up with "Option Compare Database" at the top. Write the below code into the code editor then click F8 a few times to step through the code.
```
Option Compare Database

Sub import_data() 'routine for importing sample tables from the excel file

Dim fnm As String
Dim arr1
Dim i As Integer

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
						
```
The above code does a few things: first it declares a new subroutine with the sub import_data() line, then it then declares some variables for use later in the program (fnm as string, arr1 as an array, and i as integer).

Next the path of the d_sources2.xlsx file is assigned to the fnm1 variable; then an array of 5 strings (that match the tabs in d_sources2 workbook) is assigned to arr1.

Finally, for each tab in the workbook the DoCmd.TransferSpreadsheet method is used to import the object to a table of the same name.
After completing the subroutine, 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 write the following code below the import data subroutine, then place the cursor under the sub sr_mid() line and F8 through the code to execute it.

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

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

Like the last one this subroutine is declared at the top--only this time as as sr_mid. the db variable is declared as a dao.database object then set to the current database engine. After doing this we are ready to write SQL.

the db.Execute method is used to perform an SQL statement. The first statement executes the create table d_master... sql statement, defining an empty table with 4 columns. If you check back in Access after executing (F8) this statement you should now see a new blank table called d_master.

The next two statements perform INSERT INTO operations using SQL, then an UPDATE operation is performed (as commented in the code above). Following this, a DELETE * FROM operation removes records meeting a certain condition. Then the DROP TABLE statement removes 4 extraneious tables. Finally, a GROUP BY query is used with an INSERT INTO operation to create the a_summary table.
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.

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 , after following this article you should be able to program relatively complex processes for getting the data how you want it.