Converting Access VBA to VB.NET – Global Variables (21st February 2010)

support@ascassociates.biz , Sales Enquiries

Converting Access VBA to VB.NET – Global Variables (21st February 2010)

In this article we look at how to translate Access Global Variables into equivalent variables in a .net web form application, we also show how to construct a support class enabling converted Access VBA code to run in a web form application.

Access applications allow you to define a global variable as shown below :-

Global MyVariable As String

In Access these variables are available in any part of the application. Once set the variable will hold its value (unless the application code crashes) until the user closes the application.

One very important point is that every user gets their own set of global variables, so these variables are user specific.

Access programmers will often use a global variable to make it easy for parts of the application to set and retrieve a piece of data. A second use of global variables is to improve performance, for example when working with recordsets a reference to Currentdb could be held in a global variable as shown below and used throughout the application.

Global db As Database

Sub SetDatabaseReference()
Set db = CurrentDb
End Sub

Global Variables in .net web applications

Historically asp has supported two types of global variables using the Session and Application objects.

A Session variable is very similar to an Access global variable.

Session("Myvariable") = "a value"

The second variable is a global application variable, which is global and shared by all users.

Application("Myvariable") = "a value"

Session variables hold their values until the Session is timed out after a period of inactivity, and Application variables hold their values once set until the web application is re-started.

Public Variables

Using a class module in .net also allows the definition of a Public variable as shown below:-

Imports Microsoft.VisualBasic

Public Module TestClass
Public GlobalVar As String
End Module

These public variables behave in a similar way to Application variables.

Mapping Access VBA Code using Global Variables

If you are converting your VBA code to run in a web application you have two choices for handling global variables.

If you are content to have the Global variable shared by all users, then using a Public variable has the advantage of behaving like an Application variable and means that you do not need to make changes in your converted VBA code.

If you intend the variable to hold user specific preferences (which is probably the most common use) then you need to replace all references in your VBA code with Session("MyVariable") references.

Example

This example shows how to build a support class for the CurrentDB reference in Access VBA code.

The first step is to create a supporting class for a Database object.

Class file called DAO.vb

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Namespace DAO

   Public Class Connection
      Private p_SQLCon As SqlConnection
      Private p_ConnectionString As String
      Sub New()
         ' Default constructor
         p_SQLCon = New SqlConnection
         p_ConnectionString = ConfigurationManager.ConnectionStrings("appConnectionString").ConnectionString
         p_SQLCon.ConnectionString = p_ConnectionString
         p_SQLCon.Open()
      End Sub
      Public ReadOnly Property AssignConnection() As SqlConnection
         Get
            AssignConnection = p_SQLCon
         End Get
      End Property
      Public Sub Open()
         p_SQLCon.Open()
      End Sub
      Public Sub close()
         p_SQLCon.Close()
      End Sub

   End Class

   Public Class Database
      Private p_SQLCon As Connection
      Private p_SQLCmd As SqlCommand
      Public Sub New()
         ' Default constructor
         p_SQLCon = New Connection
      End Sub
      Public Sub Execute(ByVal strSQL As String, Optional ByVal Settings As Int16 = 1)
         p_SQLCmd = New SqlCommand
         p_SQLCmd.CommandText = strSQL
         p_SQLCmd.CommandType = Data.CommandType.Text
         p_SQLCmd.Connection = p_SQLCon.AssignConnection
         p_SQLCmd.ExecuteNonQuery()
       End Sub
      Public Sub Close()
      End Sub

   End Class

End Namespace

Then we create a file for our global variables called GlobalVariables.vb

Imports Microsoft.VisualBasic
Imports DAO

Public Module GlobalVariables
Public CurrentDB As New DAO.Database
End Module

The testing code shows three different methods for performing an update, cmdExecuteSQL_Click shows how to use our support class, cmdUseSessionVariable_Click shows how this could be achived using a session variable, and cmdExecuteCode_Click shows the standard full code method for achieving this.

Below are examples of code to test this technique.

Imports System.Data.SqlClient
Partial Class _Default
   Inherits System.Web.UI.Page

   Protected Sub cmdExecuteSQL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdExecuteSQL.Click
      Dim startTime As DateTime = DateTime.Now
      CurrentDB.Execute("UPDATE Orders SET RequiredDate = '1 january 2010'")
      Response.Write("Code execution using CurrentDb took : " & (DateTime.Now - startTime).ToString)
   End Sub

   Protected Sub cmdExecuteCode_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdExecuteCode.Click
      Dim startTime As DateTime = DateTime.Now

      Dim p_SQLCon As SqlConnection
      Dim p_ConnectionString As String
      p_SQLCon = New SqlConnection
      p_ConnectionString = ConfigurationManager.ConnectionStrings("appConnectionString").ConnectionString
      p_SQLCon.ConnectionString = p_ConnectionString
      p_SQLCon.Open()

      Dim strSQL As String
      strSQL = "UPDATE Orders SET RequiredDate = '1 january 2010'"

      Dim p_SQLCmd As SqlCommand
      p_SQLCmd = New SqlCommand
      p_SQLCmd.CommandText = strSQL
      p_SQLCmd.CommandType = Data.CommandType.Text
      p_SQLCmd.Connection = p_SQLCon
      p_SQLCmd.ExecuteNonQuery()
      Response.Write("Code execution took : " & (DateTime.Now - startTime).ToString)
   End Sub

   Protected Sub cmdUseSessionVariable_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles cmdUseSessionVariable.Click
      Session("Currentdb") = New DAO.Database
      Dim startTime As DateTime = DateTime.Now
      Session("Currentdb").Execute("UPDATE Orders SET RequiredDate = '1 january 2010'")
      Response.Write("Code execution using Session Variable took : " & (DateTime.Now - startTime).ToString)

   End Sub
End Class

Summary

In this article we have illustrated how you can start constructing support classes in .net which will allow you existing VBA code to execute with a minimum amount of change.

Code Download

The sample code described in this article can be downloaded using the following link. (click here)

by Andrew Couch Office Access MVP, Director ASC Associates andy@ascassociates.biz

support@ascassociates.biz , Sales Enquiries

sales@ascassociates.biz