VBA to T-SQL via TDD: Step 2

Step 2. Build a "test table population query" in Microsoft Access

Mike Wolfe

Sep 8, 2021 • 3 min read

This is Part 2 of my series on converting a VBA function to a SQL Server scalar function using test driven development. As a proof of concept, I will be re-implementing the VBA Round function (which uses bankers rounding) as a SQL Server scalar function.

After identifying test data in Microsoft Access, the next step is to make it as easy as possible to move that data into SQL Server so that we can run our tests.

There are a few different approaches you can take. If you have a large amount of test data (thousands of records or more), you probably want to use the SQL Server bulk copy utility. If you have fewer records, I prefer a simpler approach: writing a SELECT query in Access that returns an INSERT INTO...VALUES statement for each row of test data.

Query Results

I think the concept is easiest to explain if we work backwards from the finished result.

Here is the output from the sample query (I'm using using the sample data I showed in Step 1):

Sample Query

Here is the SQL statement used to build the output shown above:

SELECT "INSERT INTO RoundTestData " & 
 "  (NumberToRound, NumDigitsAfterDecimal, ExpectedValue) " & 
 "VALUES (" 
 & [NumberToRound] & ", "
 & [NumDigitsAfterDecimal] & ","
 & Round(NumberToRound, NumDigitsAfterDecimal) & ")" AS TSQL
FROM RoundTestData;

Query Explanation

The query has a single field: the generated T-SQL that we will be executing against our SQL Server test database.

The first n - 1 fields are the inputs to whatever function in VBA we are trying to migrate to SQL Server. In this case, those are the two inputs to the VBA Round function:

Number
NumDigitsAfterDecimal

The nth field is the actual output of the function (i.e., the expected value).

The way we generate the expected value is to call the function as part of our Access query. We use the values from the first n - 1 fields to pass as inputs to the function. That function call can be seen in the second-to-last line in the sample SQL statement above: Round(NumberToRound, NumDigitsAfterDecimal).

Finally, note the double-quotes and ampersands in the Access SQL. Those are required because we are building a single string field (the INSERT INTO statement). We will copy and paste the results of this query into SQL Server Management Studio and execute them as part of Step 4.