Why VBA still makes sense

(In connection with Ribbon) I can't agree with the following:

"If you are just beginning, VBA is a great place to start." I'm missing Ribbon object in VBA that's why I wouldn't recommend VBA as a "great place to start". VBA is the worst place to start with Ribbon.

VBA in access is a great way to manage the ribbon. Vladimir see my article that was posted on this blog a few months ago in which I provide some VBA to manage the ribbon Simplifying Ribbon Modification at Runtime

blogs.msdn.com/.../simplifying-ribbon-modification-at-runtime.aspx In the above I provide an VBA class that allows you with ease to enable, hide buttons or change pictures in the ribbon using VBA. Also, for office (and access) 2010 we can now make any tab in the ribbon active using VBA (this feature was missing in access 2007). So there is new enhancements for the ribbon that are available from VBA.

Glad to hear that. VBA is integral to my Access aps. I'm excited about 2010 and the new features. I was not about 2007.

John's article is less than forthright: 1. Macros in Access are NOT written in VBA. 2. The talk of VBA is condescending and pitched at the power user level with talk of making "little buttons" etc... 3. The segue onto .NET is jarring, with the pitch now aimed at object-oriented coders, selling the virtues of strongly typed objects and security. (This after Access security was dumped in Access 2007!) 4. No mention of the appalling implementation of the Ribbon object with zero native VBA methods for building or customisation, or for handling pop-up menus which still use the CommandBar object.

>Macros in Access are NOT written in VBA. Yes, it good to point this out. It been a simple observation and fact for many years that access developers quickly learn that macros mean something different in access. So, sure, it is good of you to point out that Access macros are differnt (and in 2010, even more so with the new engine level table triggers!). >Ribbon object with zero native VBA methods I don’t understand the above problem? I been supporting and answering questions in the newsgroups for about 10+ years now, and in fact there’s not really any native VBA support for DAO reocrdsets, and yet we use DAO all the time in our code! We do this by having a reference set to the DAO object model. The same goes for the ribbon, and it easy to work with the ribbon using VBA. At the end of the day you can still use VBA to manipulate the command bar objects >or for handling pop-up menus There is new features for creating right click context menus on forms in 2007 without having to use the command or object at all. See this article: office.microsoft.com/.../HA102825091033.aspx So, you can still use VBA to create and manipulate those context menus, we also now have the addition of a new macro feature that allows the creation context menus also. (it not like this issue was not dealt with and it not like somehow VBA can't use the commandbars object..you are still free to do this) >after Access security was dumped in Access 2007 That is a completely wrong and a totally false statement. User level security is completely supported if you use mdb files. On the other hand if it’s important for you to point out the difference between little details like macros being different for longtime access developer’s here, then I would expect you to give the same courtesy to everyone else here when you accidentally leave out BIG FACTS like you can use user level security in access 2007 with mdb files.

So Albert, are you saying that you can, in Access 2007, create a custom right click menu option, say for an object in the database window (navigation pane), that can execute arbitrary code? I could do this in MS Access 97-2003 to do things like copy the name of a table by right-clicking a table in the database window and then selecting my new context menu option. The new option would call code to copy the name of the selected object to the clipboard. I also had other really handy stuff as well, like being able to generate a SELECT SQL or CREATE TABLE statement from the currently selected table. I REALLY hope I have missed something, but I'm blowed if I could figure out how to do it in 2007.

Albert,

I think the point Tony was making is that Access security was removed for the new file format, i.e. .accdb files. Old .mdb format is not being developed like the new .accdb format. I have to say though that I think Access 2010 is brilliant. It took a long while to convince me that Access 2007 was good (apart from the lack of security), but on trying out Access 2010 it is clear that a huge effort has gone into it and the web stuff is really exciting (still no Access security for .accdb files run in non-web mode though). I'm actually posting this comment from inside Access 2010 via a web browser control whose control source is linked to a field in one of my tables. How about putting a link to your video on this blog to demo a web database? Alan

CyrusB: I bet Albert is not able to do it!

Vladimir my Friend: MS ACCESS 2007 is the worst release ever..hows that? The scenario: I have installed the A2k7-SP2 runtime to my client machine with pre-installed MSOffice 2k3, so far its fine.But when some of the apprentice installed MSOFFICE 2k7 it started to go crazy by running the long-setup wizard which means again..CONFLICT!..do you have a remedy on this situation my friend aside from using SAGEKEY? Thinstall is unusable on this situation (A2k7-only)..very sad! Thanks

ERwin

>So Albert, are you saying that you can, in Access 2007, create a custom right click menu option, say for an object in the database window (navigation pane), that can execute arbitrary code? I was talking about context menus that you build for your custom applications (for forms and reports). Since the old menu bar customizing is gone, then to build right click menus for your applications you can continue to use the commandbars object + VBA (that is what many people used). However, you can NOW ALSO now the NEW macro approach to create that right click custom context menu. In your case, you are talking about the built in menus and those to my knowledge those menus NEVER EVER did travel with your applications you deploy to your customers. So, you talking about a very different issue. I not tried to modify the built-in context menus with the commbars object. So, to be clear here I was talking about the right click context menu(s) that you specify in the “other” tab of a form (or report). This is generally how custom context menus are created for customer applications. When you use macros (or VBA, or in the past the menu customize feature), your result was a short cut menu that you would specify in the “other” tab of the form in design mode. So, we were talking about context menus you develop for your customer applications. Changing the built in ones as you are suggesting NEVER EVER did travel with the application. So the issue was in light of developing applications that you deploy to customers. I would strongly recommend that you don't mess with the customers built-in menus as they will change EVERY application on that customers desktop and that will get you in heaps of trouble real fast.

Albert: "...you can continue to use the commandbars object + VBA..."

Is it possible with Access 2007 Runtime without MS Office 2007 on a target machine? I think, it's a "mission impossible". To me, creating menus via macros seems too much complicated, though I have not tried it, yet.

Erwin: "MS ACCESS 2007 is the worst release ever":

True Deployment & cross-version incompatibilities (bugs):

MS is not willing to create a professional developers' tool for deployment. It's always been a problem in Access. You can find more deployment issues & bugs in my "bug database": www.alis.cz/relax/download/access/Access2007_bugs.rar

See the following IDs: 11, 68, 70, 71, 82, 83, 85, 88, 90 (!!!). More than a year ago we talked to people from MS CZ. They told us that one of the reasons not to develop a professional deployment tool was that they can't "cut down" other companies, like Sagekey. IMHO, it's not true. How could then MS develop it's own anti-virus SW, firewall, and many others?

Vladimir: "How could then MS develop it's own anti-virus SW, firewall, and many others?" That didn't make sense... I suppose one could write firewall and anti virus using VBA but... that's reinventing the wheels. Normally when we talk about deployment, we talk about a package, of which Access may be a part of. Sometime company doesn't give a hoot about security because they will have the network manage the security. Other companies may just want to use terminal server and demand VPN. All of those software will work just fine with Access and can be had off the shelf. WRT the original topic: Having had programmed in C#, I have to agree that VBA is so *much* convenient and straightforward. A task in VBA that takes few lines, or even better yet, zero lines because it's a property setting for an Access object, would requires about 10 objects and many lines to accomplish the same task! Yes, C# and other languages has more power and flexibility but it's not always needed, and we must not forget that Access can consume COM components, which also mean it can consume .NET components using COM interop or ActiveX (issues not withstanding) so we can get a 90% solution out of the package, and put on 10% when we actually need it. Even using a hybrid solution would still be faster & cheaper than if we had to do it everything in Visual Studio. That said, I do really, really hope MS will consider updating VBA just a bit. It's been long since it had any new constructs. For one example, error handling are, IMHO, long in tooth, and I would be so glad to have a Try/Catch which provides for more intuitive & flexible error handling as well enforcing code clean up at all time. Another example would be to provide convenient keywords for incrementing & returning values (e.g. i++ & return i for example) or handling complex logic in a Select Case (break, return or fall through). I think those little improvements will go a long way should MS have no plan to eventually migrate to VSTO for the entire Office.

>Albert: "...you can continue to use the commandbars object + VBA..." >Is it possible with Access 2007 Runtime without MS Office 2007 on a target machine? I think, it's a "mission impossible". Oh, such a lightweight you are! Sure you can use VBA in the 2007 runtime to make a right click menu. Try this code in a forms on-load event… Dim cb As CommandBar Dim mybut As CommandBarButton Dim strMenu As String strMenu = "MyShortMenuTest" On Error Resume Next Set cb = CommandBars.Add(strMenu, msoBarPopup, False) If Err.Number = 0 Then Set mybut = cb.Controls.Add(1) With mybut .Caption = "test new1" .OnAction = "=Msgbox('hello')" End With End If Me.ShortcutMenuBar = strMenu Was the above so hard for you? Note that I used =MsgBox(), but you could replace that with the name of a VBA function (note that the VBA function you specify here can even be a code function in the CURRENT form’s code module! ). So, right click menus to call VBA are totally supported in the 2007 runtime, and using VBA to create a menubar/command bar on the fly is also supported. >To me, creating menus via macros seems too much complicated, though I have not tried it, yet. That is Bad answer! So you can’t spend a few minutes to use a new feature, it is likely too difficult (macors are too hard for you!). Yet your standing here asking for new features? How you do think this makes you look as a developer? Lets step things up here a notch. Look: We all here love access and some of you are making us developers look like clowns before the access team and community. I think we need to put forth a really good professional front to the access people here else they not going to take us very serious. I will support ANY developer here who wants to make a unified front and make a good impression so that the access team will really listen to our requests. I want YOU on my side when we band together (or gang up) to suggest and ask the access Team to give us needed features we want for our lovable access. Lets make sure what we present here makes a great impression on the access team. I don’t mind some people here not knowing about as many features in access as I do (I am a true expert on this product and I here to share my knowledge with you). I do mind when that lack of knowledge is used to make unfair criticisms. This makes all of us here look bad. Lets make sure what we present here makes a great impression on the access team. And you can bet I will join anyone in complaints about something in access, but lets make sure we have some corresponding professionaisi here else we not be taken serous.

Banana: I'm affraid you didn't get the point. :-/