> ## Content Index
> Fetch the complete content index at: https://nolongerset.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# VBA to T-SQL via TDD: Step 2
- URL: https://nolongerset.com/vba-to-tsql-via-tdd-step-2/
- Published: 2021-09-09T02:51:09.000Z
- Updated: 2026-05-08T13:02:31.000Z
- Description: Step 2. Build a "test table population query" in Microsoft Access
- Author: Mike Wolfe
- Tags: VBA to T-SQL via TDD, #Import 2026-05-20 02:59

*This is Part 2 of [my series](https://nolongerset.com/vba-to-tsql-via-tdd/) 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](https://nolongerset.com/trust-but-verify/) (which uses bankers rounding) as a SQL Server scalar function.*

---

After [identifying test data](https://nolongerset.com/vba-to-tsql-via-tdd-step-1/) 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**](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15). 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](https://nolongerset.com/vba-to-tsql-via-tdd-step-1/#sample-data-for-the-round-function) I showed in Step 1):

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-31.png)

## Sample Query

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

```sql
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](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function):

1. Number
2. NumDigitsAfterDecimal

![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/image-32.png)

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.

---

### Referenced articles

[How to Convert a VBA Function to a SQL Server Scalar FunctionMS Access Developers Can Remove All the Risk From This Complex Process With These 3 Words![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/caterpillars-2869783_1920.jpg)](https://nolongerset.com/vba-to-tsql-via-tdd/)

[Trust But VerifyWhat Ronald Reagan’s Words Can Teach Us About the Sample Code We Find on the Internet![](https://nolongerset.com/favicon.png)No Longer SetMike Wolfe![](https://storage.ghost.io/c/eb/d7/ebd732c1-5f03-4f07-b386-5d08557e15c9/content/images/2021/09/president-67550_1920.jpg)](https://nolongerset.com/trust-but-verify/)

### External references

[bcp Utility - SQL ServerThe bulk copy program (bcp) utility bulk copies data between an instance of SQL Server and a data file in a user-specified format.Microsoft Docsmarkingmyname![](https://docs.microsoft.com/en-us/media/logos/logo-ms-social.png)](https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15)

[Round function (Visual Basic for Applications)Microsoft Docso365devx](https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/round-function)