Forms and modules in VBA to be converted to C#

Posted on 2014-08-27
Last Modified: 2014-09-01

I'm starting to convert a number of "utilitarian apps" from Access/VBA to C#, and some of the equivalents escape me sofar.

By "utiltarian" I mean Access apps that run unattended, process data in various databases (mostly in one SQL Server DB), and possibly go through various phases of processing. So I have typically constructed them around one main form which when loaded calls a number of routines, some located in the form itself, and many located in modules, of which there are 2 categories: standard, generic modules which are used by many of the apps, and specific modules which do some specific processing only in one of the apps.

Many of my modules log what they are doing in a table in SQL Server, and they also add the same or similar kind of text log entries in one listbox on the main form, which always bear the same name, lbProgress, in all apps and which allows me when developing and testing to see what the code is doing and what problems it encounters. So basically I have this:
- main form calls subroutine A in module X by passing any needed arguments, but always Me as argument
- subroutine A (which has the argument "frm as Form" to map the Me argument in the sub call in the main form) starts working and, when it wants to "publish" the fact that it has reached some step in the processing, it calls a generic logging routine, passing it its own "frm" parameter and the text to log as arguments
- the generic logging routine uses these arguments to add the logging text to the main form's list box lbProgress by referring to it as frm.lbProgress

So in pseudo code, it is something like (simplified):

Private Sub Form_Load()  (of frmMain form)
    Call A(Me)
End Sub

in specific module of app:
public Sub A(frm as Form)
    ' Want to log text "Step 1 completed"
    call LogProgress(frm, "Step 1 completed")
End Sub

In generic module modAppLogging:
Public Sub LogProgress(frm as Form, sLogText as String)
    Dim Temp as Variant
    Temp = SysCmd(acSysCmdSetStatus, sLogText )
    frm.lbProgress.AddItem sLogtext
End Sub

I thought I would therefore start to rewrite such a generic module, which is basically a collection of public sub's doing some processing which may or may not access data in tables in a SQL Server DB, and to make a solution with one form as project and the generic module code in a sealed class. However, I'm stumbling on many small obstacles:
- the main form passes its reference "Me" in the sub call to A, because it might be used by A and by the LogProgress generic routine. What's the equivalent to "Me" in C# ? "this" ?
- is there an equivalent in C# for the SysCmd call, which just shows the log text in the status bar of the form ?
- the LogProgress routine uses in VBA the 2 arguments shown above: a reference to the main form, and a string. I can't fathom what type this "frm" variable should be in the sealed class A and in the sealed class where LogProgress is, i.e what's the type in:
public static void LogProgress( ??Type?? frm, string sLogText)

Thanks for helping me with these probably trivial problems.
Question by:bthouin
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    .NET languages are a far cry from Access VBA. I don't use C#, and instead spend most of my time in VB.NET when I work in that environment, but the concepts are the same.

    If you're running "service" type apps - i.e. apps that run unattended, with no user interaction - then in general there's no need to have any Form or UI conventions (except for setup and maintenance, perhaps). When the service app is running, there's just no need to show forms, or fill forms with data, etc etc, as we're accustomed to doing in Access, and doing so can run into unintended errors and such (or can allow users to interact with your service app, which is a bad thing in most cases). Instead you'd typically create Class modules that would interact with the database, and in those Class modules you'd include your various Subs and Functions.

    As far as SysCmd - there is no equivalent in .NET. The SysCmd functions in Access basically just fired off internal System Commands. If you want to fill a Label in .NET, you just set the .Text property of that label directly:

    lblInfo.Text = "Running Code A"

    If you need to refer to a specific Form in your .NET application, you'd generally create that form using a Global variable, and then refer to that Global variable:

    Dim frmMain As fMain

    Now in the startup code for your project:

    frmMain = New fMain

    From there, you could refer to frmMain anywhere in the project:

    frmMain.lblInfo.Text = "Something Here"

    If you MUST have a form, then an even better solution would be to move all that code to a Class module, and then have that class fire off Events as needed to inform the interface of the progress, and your interface could react accordingly. You could then instantiate the Class in the Form's module using the WithEvents method, and all of the Events of that Class would be available for use with the Form. For example, if I have this event in my Class:

    Event AlertUI

    Then my Form's module would be able to fire off code anytime my Class module "fired" that event. So essentially, after moving your code into a Class module, you'd just call that Event as needed to alert the UI that something is going on, and the UI would then handle that event as needed - it could write to a Textbox, Label, StatusLabel, etc etc.

    Note the above is VB.NET syntax, although C# would be similar.

    In short, much of what you're trying to do attempts to force C# to act more "Access-like", and that'll get you in trouble quickly. I made the conversion from an Access-only programmer to .NET several years back, and I went through a period much like this - trying to force .NET to act like Access. It was difficult (to say the least) and in the end .NET won that battle :). I'd strongly suggest that you learn the C# (or VB.NET) language and adapt your code to that environment, rather than trying to cram VBA-centric code and processes into the wrong container. The .NET languages are much, much different from VBA, and from the Access data-centric paradigm.
    LVL 74

    Expert Comment

    by:käµfm³d 👽
    By "utiltarian" I mean Access apps that run unattended, process data in various databases (mostly in one SQL Server DB), and possibly go through various phases of processing.
    This sounds like it should be a console application, not a Forms application.

    What's the equivalent to "Me" in C# ? "this" ?

    If you need to refer to a specific Form in your .NET application, you'd generally create that form using a Global variable, and then refer to that Global variable:
    I disagree with this. Creating globals is the old way of thinking. The preferred way is to pass around abstractions of your form by way of interfaces and abstract classes. But I won't subject you to that at this point in your learning  = )
    LVL 84
    kaufmed has much more experience in this, so I'd certainly defer to his judgement in this matter. My goal with the post was to try and pave something of a path from VBA to .NET (if such a thing exists, that is).
    LVL 1

    Author Comment

    Hi Scott

    Yes, I know that such apps do not need a UI, but because they do sometimes complicated stuff, it's much nicer to follow what's happening in one single app on a form listbox being filled by the executing code than fishing with SQL queries in a huge table with logging entries from 10 different apps.

    And yes I know I'm (unnecessarily) trying to mimic what happens in my Access apps, but I thought that would teach me at the same time a number of things which I'd need anyway later on, because I will have to convert also typical data manipulation apps used by people. And users would anyway not be able to (mis)use my service-type of apps, because they will run on servers on which they' have no access. All they'll ever see is the interactive apps they have today, just in another guise (Win Forms or web forms with ASP.Net).

    I HAVE to use C#, I would have preferred VB 10 times over, but that was not for me to decide.

    OK, thanks for your valuable inputs, I'll see how much and how fast I can "bend" my  Access habits into C#-conform habits :-)
    LVL 74

    Expert Comment

    by:käµfm³d 👽
    All they'll ever see is the interactive apps they have today, just in another guise (Win Forms or web forms with ASP.Net).
    You'll want to take an n-tiered approach to your development then. With such an approach, you will be able to easily swap out layers as you see fit. For example, if you made 3 tiers--a UI layer, a service (validation) layer, and a data layer--you could easily swap out the UI layer between WinForms and WebForms (or something else)--the only thing changing is the technology that is used to display the data.
    LVL 1

    Author Comment

    Hi kaufmed

    Yep, that sounds good, and I'll do it in the end. My problem is that I have very little time to do the conversion (about a year to convert 30,000 lines of VBA code + forms, queries and a few reports) spread over 20+ Access apps, so maybe I'll do it 2-tier first to gain basic experience, and then improve afterwards when I get to the maintenance mode period. I know, it's nearly double work, but I hardly have any choice and too little know how to risk grand plans and perfect structures. The whole thing has been kicked off by guys making lots of politics, so there are loads of aspects which I have to consider which hamper a healthy development. And being a lone-ranger contractor, I cannot impose my views against IT departments with thousands of employees.
    LVL 74

    Accepted Solution

    Understood. Honestly, making it 3-tier shouldn't be too complicated, and I would focus on what the code as a whole does, not what each line does.. You basically have:

    One project for UI
    One project for service layer
    One layer for data access

    You would have data transfer objects (DTOs--i.e. classes) that carry data between layers. Your UI layer will be dumb:  get some inputs; display some outputs. Your UI layer puts the inputs into a DTO that goes off to the service layer. The service layer runs its validations against the inputs to ensure that they are up to snuff. If they are not, return an error; otherwise, forward the DTO on to the data layer to execute the query/queries.

    Ultimately, its your project, so do it the way that makes sense for you. Just giving my opinion  = )

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now