Most of DBAs that work with database servers will never use a local application such as MS Access to manage data, but once in a while they get to convert an existing application based on MS Access to use SQL Server. Unfortunately, most SQL Server DBAs do not have basic knowledge of VBA. Moreover, the object-oriented nature of VBA language is inherently different from the set-based nature of T-SQL. This article introduces the basics of converting VBA commands or logic to their T-SQL equivalent keywords and functions.

* If you have any needs which are not already covered in this article, then please feel free to ask in the comments to this article. If you have any idea of a solution which is not yet covered in the article please feel free to edit and add to this Wiki. Remember, this is a community shared article, which anyone can edit.

Before we start, you have to remember - Translating a logic from one technology to another is not always a good idea, as each technology has fundamental differences. For example, using the "Do Until" loop for each record in VBA can be implemented in SQL Server using a "While" loop and/or using cursor looping. But most of the time this row-by-row (sometimes called RBAR, pronounced ReeBAR) parsing of data set will be very bad idea, as SQL Server works with data-sets and can implement most loop operations without any explicit looping logic.

* This article's target audience are DBAs, and therefore it will not include in-depth explanations of the Transact-SQL equivalent solutions we're looking for, or the implementation of action on SQL Server. We will just point to the way we can implement the same VBA action using Transact-SQL language.

Converting from MS Access [VBA] to SQL Server [T-SQL]


VBA basically uses one type of method (function), those functions can return any type of element that we want, including a user defined type, a collection of elements, or a single element. 

T-SQL basically uses three main functions types: (1)Scalar Functions, (2)Table-Valued Functions, and (3)System Functions.

There are sub type of those functions. For example "Scalar Functions" can be (1.1) CLR function or (1.2) TSQL Function, "Table-Valued Functions" can be (2.1) CLR function, (2.2) inline Table-Valued Function or (2.3) multi-statement Table-Valued function. 

First step in converting a VBA function into T-SQL function is to understand the different T-SQL types, and to choose the right one. This link can help you to do so:

The next link brings us some simple examples on creating functions in T-SQL:

Next step is choosing the right type in T-SQL. In order to select the correct type of function, you need to answer what type will the function return? Compare the answer to the T-SQL different function's behavior. For example will it be a single element which fit an SQL Server element (like an integer, String)? If so, then we can probably use a Scalar Function. If not then maybe it returns a collection of elements, and therefore might fit to Table-Valued Functions.

On Error GoTo

SQL Server does not have anything like VBA "On Error GoTo" construct, but it is has something like "On Error Resume Next" statement + GoTo statement. Together it give you the ability to implement the same action as in VBA code.

* There is a GoTo statement in T-sql just like in VBA, but it is highly recommended NOT to use it! You can implement GoTo like this example:

GOTO Branch_Two --Jumps to the second branch & skips Branch_One.
    SELECT 'Select Branch One.'
    SELECT 'Select Branch Two.'
    SELECT 'Select Branch Three.'

* Handling Errors can be done in several ways. For example using the Variable @@ERROR, And / Or using try/catch block, as shown in these scripts:

    --Do Some action here
    COMMIT TRAN      
    PRINT 'Error occurred!'
    IF XACT_STATE() <> 0


create table QQ (id int)
DECLARE @MyError int
    insert QQ values(1/0)
    -- Divide by zero error encountered.
    SELECT @MyError = @@ERROR
    IF @MyError != 0 GOTO HANDLE_ERROR
    -- do some work if all is OK
    COMMIT TRAN -- No Errors, so we can commit our work
Select @MyError

* Notice that the original error message return, but the code proceeded to capture and return the error value, and move to the HANDLE_ERROR block.  This allows client applications to capture the message and return it to their caller.

* @@ERROR is reset after each and every SQL statement!  Therefore, you must capture it immediately after each SQL statement that might produce an error. In the above example we insert the value to a variable in order to use it later on.

Do Until

A "do until" loop can be implement using a WHILE loop in T-SQL, as shown here:

WHILE (@QQ < 10)
    SET @QQ = @QQ + 1


Working with RecordSets

Working with recordsets, record by record is a very common action in VBA, but is not recommend for most needs in T-SQL. T-SQL handles records as 'one record set element' better. SQL executes exponentially faster than a VBA recordset. It is also far more flexible, especially when working with nested sets of data.

With that said, let's see how we do implement it when it is necessary.

Working with tables, VBA code uses the element type Recordset and assigns our table records to it. For our example we will use the name "RecSet".

There are five main types of Recordset objects, which defines how we handle the records.

  • Table-type [dbOpenTable]
  • Dynaset-type [dbOpenDynaset]
  • Snapshot-type [dbOpenSnapshot]
  • Forward-only-type [dbOpenForwardOnly]
  • Dynamic-type [dbOpenDynamic]

The command will look like: 

Dim MyRecordset As Recordset
Set MyRecordset = RecSet.OpenRecordset (<Table_Name>,< Recordset_Type>)

* The Recordset implicitly uses cursors to navigate through the Recordset, But using VBA we control it a bit different and the cursors are distinct from the cursors used in SQL Server.

The implementation of the same behavior in T-SQL is done using a cursor element. 

SQL Server use four main cursor types:

  • Static cursors
  • Dynamic cursors
  • Forward-only cursors
  • Keyset-driven cursors

A basic use of T-SQL cursor will look like:

create table QQ (id int)
insert QQ values(1),(2),(4)
    SELECT id from QQ
    OPEN MyCursor
        WHILE @@FETCH_STATUS = 0
               SELECT @ID
               FETCH NEXT FROM MyCursor INTO @ID
    CLOSE MyCursor 


VBA command to move to the next record will be implemented in T-SQL cursor using "FETCH NEXT" command.


VBA EOF is a marker for the End Of File, or in our case the last record in the Recordset object. There is no direct option to check if this is the last record using a cursor, but there is a way to implement it by, (1) move to the next record, and (2) check the status of the cursor. The status will be equal to zero if the FETCH statement was successful. That means that we did not get to the end and we got another record to work with. But if we try to fetch the next record and there are no more records to fetch, then the @@FETCH_STATUS  will be -1.


VBA BOF is a marker for the Beginning Of file or in our case the first record in Recordset object. There is no direct option to check if this is the first record using cursor, but we can use the same logic as checking if this is the last record. We can use "FETCH PRIOR" command to get previous record and then check the status of the cursor.

Do Until RecSet.EOF

VBA loop until we get the last record will convert into WHILE @@FETCH_STATUS = 0

For Each

Using VBA we can loop a collection of an unknown number of elements using the "for each" command. There is no equivalent for this type of loop in T-SQL but it can be implemented using a simple "For loop" or a "While Loop" and the COUNT function in order to get the number of elements.


The VBA build-in IIF function returns one of two values, depending on whether the Boolean expression evaluates to true or false. In SQL Server 2012 we have the same feature (function) which we can implement directly, but previous versions do not have an equivalent function. In this case we can implement the same behavior in several ways using IF/ELSE, or using a CASE expression, or even using ISNULL, as can be seen in the next examples:

SELECT IIF(2 > 1, 'YES','NO') -- SQL SERVER 2012 Use the same function as VBA
-- Implement IIF using ISNULL
-- Implement IIF using CASE
    WHEN 2 > 1 THEN 'YES'
    ELSE 'NO'
-- Implement IIF using IF/ELSE
IF 2>1
    SET @MyResult = 'YES'
ELSE SET @MyResult = 'NO'
SELECT @MyResult


Nested IIF statement

DECLARE @A INT = 2, @B INT = 1, @C INT = 3
/************** Nested IIf statement: Example 1 */
            @A > @B,
            IIF(@C > @B,'A>B & C>B', 'A > B, but C not > B'),
            'NOTHING FIT'
-- Implement using nested IF statement
IF @A > @B
    IF @C > @B SET @MyResult = 'A>B & C>B'
    ELSE  SET @MyResult = 'A > B, but C not > B'
ELSE SET @MyResult = 'NO'
SELECT @MyResult
/************** Nested IIf statement: Example 2 */
-- SQL SERVER 2012 & VBA
            @A < @B,
            'A < B',
            IIF (@A > @B, 'A > B','A = B')
-- Implement using CASE
    WHEN @A < @B THEN 'A < B'
    WHEN @A > @B THEN 'A > B'
    ELSE 'A = B'
-- Implement using COALESCE


Data type's bounders

When writing code we sometimes need to bound our values, in order that SQL Server or our application (Access) will recognize they start and end. The format conventions of the boundaries which are used by the VBA language  are slightly different from those of the Transact-SQL language.


Using VBA, text must be enclosed in either single quotes e.g. 'Our String' or double quotes e.g. "Our String". The default implementation in T-SQL is using single quotes, depending on the settings of QUOTED_IDENTIFIER. 

SET QUOTED_IDENTIFIER ON -- This is the default for SQL Server
select "fgsdgfg gsfg df gdf"
-- Using SQL Server will raise exception!! "Invalid column name 'fgsdgfg gsfg df gdf'". But this work fine on ACCESS by default.
select "fgsdgfg gsfg df gdf"
-- Work OK



Using VBA, a Date should be enclosed in hash marks (#) also called pound or number signs, for example: #09/27/1950#, while implementing this to T-SQL can be done in several ways, as shown in the next examples:

-- as long as we use a date format which fit SQL Server Date's format, we can use those TSQL examples
declare @D datetime
select @D = 2014-01-16 -- value not enclosed work OK
select @D
select @D = '2014-01-16' -- value enclosed with single quotes work OK
select @D
select @D = "2014-01-16" -- value enclosed with double quotes, will work only if SET QUOTED_IDENTIFIER OFF
select @D
select @D = #2014-01-16# -- value enclosed with # will not work
select @D


Rnd Function (ACCESS Random Function)

MS ACCESS and SQL Server both have a built-in random function, but these functions behave differently. Both functions can accept 4 types of input: (1) NONE, (2) negative numbers, (3) positive numbers, (4) Zero. The input type defines the behavior of the function.

Using ACCESS we have three basic options: (A) positive numbers, and none input, behave the same. The function return next random number in the sequence. (B) negative numbers input, the function return the same number every time, using number as the seed. (C) Zero as input, the function return the most recently generated number.

Using SQL Server (T-SQL) we have two basic options: (A) using a number as input (negative, positive, or zero) the function return the same random number every time, using number as the seed. (B) using no input, return next random number in the sequence.
select Rand(-1) as 'First_1',Rand(-1) as 'Second_1'
-- return the same number every time, using number as the seed. Same as ACCESS
SELECT Rand() AS ['First_1'], Rand() AS ['Second_1'];
-- return the next random number in the sequence. Same as in ACCESS
SELECT Rand(0) AS ['First_1'], Rand(0) AS ['Second_1'];
-- return the same number every time, using number as the seed. Not as ACCESS!
select Rand(1) as 'First_1',Rand(1) as 'Second_1'
-- return the same number every time, using number as the seed. Not as ACCESS!


LAST (Access Function)

The LAST function is only supported in MS Access. We can implement the same idea of LAST(column_name), by using "ORDER BY column_name DESC" as shown in this example:

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;


First (Access Function)

The FIRST() function in access returns the first value of the selected column. In SQL Server you use "top 1" with "order by" hint, same as implementing the LAST() function, but without the "desc" in the Order by hint.

General Formatting issues

>> In Access, you can use sub-query in brackets, and there is no need to give it a name. For example "select from (select x from Table)". In SQL Server you have to use a name for each virtual SET of data:

SELECT * from (
    -- subquery
    select Column_Name from Table_Name
) as T


SQL Server User-Defined Functions

Create SQL Server User-defined Functions (samples)


WHILE (Transact-SQL)

VBA Recordset Object

Recordsets for Beginners" is actually a full tutorial not just for beginners! Highly recommended!

Cursor Types (Database Engine)

SQL Server Cursor Tutorials

Forum's Question

See Also