Forms and modules in VBA to be converted to C#

Posted on 2014-08-27
Medium Priority
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
  • 3
  • 2
  • 2
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 600 total points
ID: 40287529
.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 75

Expert Comment

by:käµfm³d 👽
ID: 40287612
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 85
ID: 40287705
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).

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Author Comment

ID: 40287888
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 75

Expert Comment

by:käµfm³d 👽
ID: 40287943
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.

Author Comment

ID: 40287992
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 75

Accepted Solution

käµfm³d   👽 earned 1400 total points
ID: 40288153
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

850 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