Faced with writing an application that uses Access data, forms and reports, you’ll need to decide what development environment you prefer. There is a case for using VBA from within Office, but there are good reasons to move away from VBA to one of the languages in Visual Studio .NET.
The main reason to move away from VBA is the fact that Microsoft is likely to drop it. It may still be contained in the next release of Office, but the chances of it surviving in the longer term are slim. There were few enhancements to VBA in Office 2003, and that you can’t buy Office 2003 in a Developer’s Edition.
So how does Microsoft expect developers to create Office apps in the future? The clue lies in the fact that Microsoft Office 2003 Editions include an environment for building applications called Microsoft Visual Studio Tools for Office, which is based on the .NET Framework. Visual Studio 2005 improves on this with Microsoft Visual Studio 2005 Tools for the Microsoft Office System.
By using Visual Studio 2005 Tools for Office (or the earlier version), you can work within Visual Studio 2005, and take advantage of its improved security model and facilities for deployment and version control. There are also more general advantages such as the ability to use features such as ADO.NET.
There’s no denying that this is a massive change. It’s not a case of minor rewrites; the languages are so different that you will pretty much be rewriting any applications from scratch, and the programming methodologies are very different. If an application doesn’t need updating, my advice would be to leave well alone for as long as possible.
There is a petition under way trying to persuade Microsoft to continue support for VB6 and VBA, but it’s probably safest to assume that it won’t have any effect. This means that your choice is to stay with the final version of Office that includes VBA, or to choose another development environment. If you’ve decided that you’d prefer to work in Visual Studio, the next question you need to ask yourself is which language you should go for. A non-Office developer would probably suggest that C# is the obvious choice – it’s a clean language that doesn’t carry the same historical baggage as VB.NET. However, I’d suggest that there are still good reasons why VB.NET is the better choice.
Like the rest of the Office apps, Access has a large and complex object model. The features in VBA were designed to make it relatively easy to use that model, and classic VB was OK as well – pragmatic rather than pure. The languages in Visual Studio .NET are much more refined. Referencing objects in the Office model from VB.NET is hard enough, but when you try to do it from C#, you’re mixing two different worldviews, and linking the two can be tortuous.
For example, take passing parameters. VBA’s default is to pass by reference, whereas both VB.NET and C#’s default is to pass by value, and C# hasn’t a way around this. You’re going to have to change your code. Just be grateful you’re not programming using the Microsoft Word object model, as that’s really problematic!
Optional parameters is the next place where you’ll need to make changes. Access has plenty of methods that have tens of optional parameters, so you can be very precise about the way your report appears (or whatever), but it also lets you get away with just specifying the basics and letting Access sort out the defaults on the rest. VBA and Visual Basic .NET support optional parameters on method calls, C# doesn’t.
If you’re going to use C#, you’ll need to make use of the Type.Missing value to let the Primary Interop Assembly (PIA) know that you want to accept default values for the optional parameters. (For those of you who are old-VB or VBA developers, the PIA is what Visual Studio calls the Access Object Library).
Let’s take OpenReport as a typical example. It has six parameters:
DoCmd.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)So, whereas in VB.NET you could say:
' Visual Basic .NET ' wdApp refers to the Word Application ' object. ThisApplication.DoCmd.OpenReport( _ “VSJSales”)In C# you’d need to have:
// C# object filename = @”VSJSales”; object missing = Type.Missing; wdApp.Documents.Open(ref filename, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing);If you think this looks bad, some of the other elements of Office are even worse. Excel’s Range object expects thirty parameters, for example!
Late binding
Many Access developers use late binding to get around the problem of not having control over which versions of other applications will be installed on their users’ PCs. For example, if you’re writing an application that uses Microsoft Word for creating letters, some users might be using Word 2000, some Word XP, and some Word 2003. If you use early binding, your Access code specifies at the start that you are creating an object of a particular type.So in VBA you’d refer to the Word application something like this:
Dim wdApp As Word.Application Set wdApp = New Word.ApplicationLate binding allows you to declare a variable as an Object or a Variant data type. The variable is initialised by calling the GetObject or CreateObject function, and specifying the application’s programmatic identifier. For example, in the following code fragment, an Object variable is declared and then set to an instance of Word by using the CreateObject function:
Dim objApp As Object Set objApp = CreateObject( _ “Word.Application”)This is slower, and offers less visibility of the objects within Word, but it will work no matter which version of Word the user is working with. You can refer to any function within Word that will be the same within all the object libraries, and Access will work out which library it should use.
Another place where you will undoubtedly encounter late binding is any applications that were written using VB Script, as it’s not possible to set a reference to an object library from a script file.
In Visual Studio .NET, late binding is possible in VB.NET by a setting called the Option Strict directive. VB.NET’s default is to have Option Strict Off, which then allows late binding. C# (and VB.NET with Option Strict On) can’t make use of late binding.
The other main area that may cause you headaches is the use of properties that can accept parameters in Access. There’s a lot to say about query and report parameters and how they’re handled, and we’ll return to that topic another time. Suffice to say that while VB.NET lets you specify property references that include parameters, to do the same in C# you need to make use of the accessor get/set methods that are included for each property that accepts parameters.
Throughout VBA, the language has been designed to make life easy for the programmer. It’s not the most elegant of languages, or the purest, but it is pragmatic and it works. Move to a ‘better’ language and you’ll discover things you can no longer do, or that are a lot more difficult to do.
If you’re trying to decide which language would be better, remember that Visual Basic .NET is more tolerant of the way you probably want to interact with the Office objects. If you decide to use C#, you’ll have to work a bit harder. Whichever language you choose, you’re going to have to change the way you work. Make sure you’re careful about the way you deal with optional parameters, properties that can accept parameters, and the use of late binding, and you’ll create a working application. It will just take rather longer and involve more cursing!
Kay Ewbank, editor of Server Management magazine, is a highly experienced database analyst who has followed the development of database technology from dbase through to today’s SQL servers.
Comments