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)

Top of Page

Top of Page