VBA as a language, how "deep" can we go? (2 Viewers)

GBalcom

Much to learn!
Local time
Today, 14:50
Joined
Jun 7, 2012
Messages
459
"Professional" Developers always seem to throw dirt on VBA and say it's not going to be around, etc. After learning what its capable of, I simply don't see the need to move elsewhere. It's too easy to accomplish most things in MS Access. There are great add in tools help the VBE become a better tool to work in, such as FMS Tools, RubberDuck, Ivercy, etc. (Very very happy with all 3)

I want to celebrate for a moment what a great tool this really is. Most of us probably don't have the time/money/energy to develop what we do in "Real" programming languages. They can keep that "prestige", we're on the ground getting things done, while they are still trying to figure it out. Rant over....

Like many of you, the efforts put towards programming in VBA have been rewarded tenfold through easier more productive environments. After "pushing my own envelope", I've been able to do many things I never thought VBA was capable of, such as an HTTP post to a Webhook, so that Zapier can do things in the cloud with my data, and automatically email reports, etc.

I'm interested in hearing how others have done things with VBA they never thought possible. For instance, my next challenge would be tying our data into UPS for custom shipping arrangments. Has anyone done this with VBA somehow?
 

Minty

AWF VIP
Local time
Today, 21:50
Joined
Jul 26, 2013
Messages
10,355
If you have UPS Worldship (Their stand-alone shipping tool) it's an access database!

Fill you boots :)
 

GBalcom

Much to learn!
Local time
Today, 14:50
Joined
Jun 7, 2012
Messages
459
Woah! Thanks Minty, I had no idea. I'll have to get my hands on that.
 

Minty

AWF VIP
Local time
Today, 21:50
Joined
Jul 26, 2013
Messages
10,355
It's a simple set up - BE on a always on PC or server, client FE app on as many machines as you need it.

I haven't played with it properly for ages, but I don't think it's changed much. Pretty certain the linking into your data was a simple field mapping exercise.
 

Lightwave

Ad astra
Local time
Today, 21:50
Joined
Sep 27, 2004
Messages
1,521
IMHO - the only reason to move away from an ms access application is to take advantage of the web and even then you can get round that with cisco / terminal services.

I can't see why I will stop using some of the applications I have already built if they continue to work - it would take me too long to rewrite all the reports and get really nice forms.

And given the whole point of digital is that it is immortal that could be decades into the future.

Most likely I move back end to SQL Azure and have both old access front end and new web UI for those projects that are too vital.

Eventually someone will make UI development as easy for the web as it is for MS Access but no one seems to have really grasped that just yet. Most forms on the web at the moment are extremely simple, there are few good implementations of master details forms for example.

Recordsets and loops linked to tables are truly brilliantly implemented in MS Access and the report writer is superb. I note SQL Server Management Studio took out database designer then got so many complaints that they put it back in.... Also the ability to link MS Access to any backend is superb. Also its ubiquity means there is more freely available code and expertise available on the web compared to most of the other platforms put together and that code tends to be better documented and tested.

I remember being told by a manager in a meeting that MS Access was a security nightmare. 5 minutes later we were told that there had been a major breach of an externally available professionally built web application. I just thought to myself. So our internal MS Access system has an attack surface of about 6 users within a secure building on a network not connected to the web and those users you have personally vetted and could not really steal anything without being noticed compare that against the web application which has an attack surface of the entire world open to the entire world and you are focusing on the danger that the Access database presents to the organisation??? Logic failure. :banghead:
 
Last edited:

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 08:50
Joined
Jan 20, 2009
Messages
12,849
Also the ability to link MS Access to any backend is superb.

That is more about ODBC. Basically anyone who wants their database platform to be widely relevant needs to include an ODBC driver for it.
 

Lightwave

Ad astra
Local time
Today, 21:50
Joined
Sep 27, 2004
Messages
1,521
And those that aren't simple often behave very strangely and run like syrup on a cold day.


Even the ones that are simple run like syrup with most taking at least 2 seconds to resolve.

Although that might just be web stuff and not form specific.

My googlemail account takes about 4 seconds to resolve.
Pages on this forum resolve quite well for me still takes 2 seconds.
My wordpress site resolves often in 3 or 4 seconds

That's applications based on technology with millions of pounds of development time and money and the best hardware money can buy.

Most of my MS Access forms resolve in under 1/2 a second. It makes for a much smoother experience.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:50
Joined
Sep 12, 2006
Messages
15,614
I use access to write some things that are not really database related, because I don't need to get to grips with another language. Even VB is different enough to imply a learning curve.

yes, it would be nice to be able to build an exe file, but its not the end of the world. For some things, I would like to have easy to use pointer syntax. That's the main issue really.
 

Lightwave

Ad astra
Local time
Today, 21:50
Joined
Sep 27, 2004
Messages
1,521
I'm interested in hearing how others have done things with VBA they never thought possible. For instance, my next challenge would be tying our data into UPS for custom shipping arrangments. Has anyone done this with VBA somehow?

Transfer of the entire local authority building control and development control records for local authorities in the UK between Oracle backends - the biggest provider in the UK uses MS Access as a significant tool within their Export Transportation and Load projects. They also recommend MS Access for bespoke report production against their Oracle production servers for their client authorities.

You can write SQL and store it in tables and then run that using an extremely simple function.

https://rounduptheusualsuspects.org/?p=1476

For ETL that really really rocks. Absolutely rocks for things like address matching or anything where you have a golden data source which you want to compare.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:50
Joined
Feb 28, 2001
Messages
27,001
If we are talking about tools that folks didn't think I could write...

One of my side assignments was to help with a problem we had with IIS logs. We needed to do some analysis of "hot spots" and "hot users" and at the time, no one knew of any tool that would do that. (This WAS some time ago...)

As it turns out, I had some experience with finite automata theory and text parsing through a state-oriented Turing machine diagram. So I built something in Access and VBA that would use the OPEN and INPUT LINE verbs and my parser that churned its way through syntax and semantics pretty reasonably. I was able to produce charts showing biggest users and most popular pages in bar-graph format. I was able to derive timing statistics, too. Some of that is available anyway, but I was able to do path analysis because the page path is part of the IIS log. Using INSTR, I could tell if the previous page for user X was at the beginning of the new page for user X, or vice-versa. I could follow their movements and develop some hints that allowed the web designers to provide short-cuts to the most popular paths from the earlier pages rather than forcing a lengthy drill-down.

Before anyone asks, that code is the property of the U.S. Navy because of the technical language of the Copyright Act of 1975 (and as amended). I didn't keep a copy of the analyzer. At most I have a couple of useful subroutines for text analysis.
 

Users who are viewing this thread

Top Bottom