Using Access Objects vs. Pure VBA Code

[Capo]

I am wondering what you all think about designing front end applications via Access and whether one should use Access objects or simply hard-code everything via VBA in forms.

Essentially, what I'm asking is whether one of the two following routes have a preference, and why. Route 1 would be creating your Access FE application with linked tables, queries, modules, macros, etc... essentially using all the built in object features in access.

Or for route 2, simply creating your forms in access but doing all queries, linked tables, etc. via connection strings. Essentially, making the entire Application VBA based.

Are there advantages to either route? Could you please elaborate on some short-comings for either?

Thanks!

 

[Banana]

1) SQL is faster than VBA.
2) Access objects is already tested & tried and therefore less likely to cause errors or undesired results, or at least bugs are already known and can be worked around.
3) It's much, much less work to simply use the objects and you can get your product to market faster.

VBA exists to allow you to extend *beyond* what the objects are capable of. If you are going to write everything in VBA, you might as well drop Access like a hot potato and do it in .NET environment with SQL server. Access's selling point is rapid application development, and if you can accomplish several things using the built-in objects and fill the gap in with VBA, then you'd have saved considerably more than if you went the .NET/SQL Server route.

You need to know what your specifications and contraints are; Access is not always the best fit for everyone's needs. Only you can decide if your requirements can be served well by Access or by other solution.

HTH.

 

[The_Doc_Man]

Banana is absolutely right. I'll say it a different way but we are in agreement.

Using VBA entirely, you "roll your own" application at the nuts and bolts level.

Using a mix of Access and VBA, you take advantage of the fact that someone else has already written some general code using industry-standard techniques to provide a very capable rapid application development environment.

Let's say that Access did 65% of what you wanted straight up, no craziness.

Which would you rather do in VBA? 100% of your project or just the 35% of your project that Access won't already do for you. (And I'd be AWFULLY surprised if the numbers were as bad as 65/35. More often, unless you have the damnedest problem any of us have ever seen, you might see 90% for Access and 10% for you.)

 

[Capo]

Good points, thank you for sharing.

I'm curious to hear other viewpoints and/or additions, if there are any.

Thanks!

 

[Dennisk]

Another Pro to consider when using all vba is that you can use a disconnected database system. So you only connect when you read/write data. This may well result in more robust dbs when running on a network with many users.

I've just had to fix a currupted backend that had duplicate autonumbers, orphaned records, Referential Integrity removed etc. If this was a disconnected db it may never have got to state it was in this morning.

 

[neileg]

I can't see the point of using 100% VBA since you still need the Access container to run it. Now VB is a different matter since you can compile it and you don't need Access.

 

[Banana]

Another Pro to consider when using all vba is that you can use a disconnected database system. So you only connect when you read/write data. This may well result in more robust dbs when running on a network with many users.

I've just had to fix a currupted backend that had duplicate autonumbers, orphaned records, Referential Integrity removed etc. If this was a disconnected db it may never have got to state it was in this morning.

While it is a benefit, it is something I wouldn't do in VBA.

Writing a program in VBA is much, much more slower and pain in neck than if you wrote a program in other proper language (including VB.NET or maybe at least VB6, although I doubt VB6 is that much better).

VBA is extensible; it is there to allow you to add functionality to objects. Once you get into realm of writing objects on your own, you're in worse state of affairs, as there are several other IDEs out there that will do things more easier, faster, and better than VBA.

It's essentially a package deal; you either use everything in Access to get benefit of using it, or you don't use Access and roll out your own application in <insert your programming language> to get what Access can't do for you.

 

[Brianwarnock]

I seem to remember Pat Hartman replying to a similar post a couple of years ago and saying that you could never right code to match the performance of the built in facilities.

I would also hazard a guess that you would be less likely to fall foul of Microsoft changes as Barry recently did with his Undo.

Brian

 

[Banana]

who's Barry and what did he do with Undo to bring down Microsoft's wrath?

 

[Brianwarnock]

Barry is a regular on the forum and code that worked fine in Access 97 failed in 2003, he posted on the forum and apparently the rules had changed according to more knowledgeable posters, I think it was Rural Guy who explained it but I could be wrong. The important point I was failing to make was that own grown systems are more prone to upgrade problems than going with the supplier not that there was a none code way to tackle the Undo.
Does that ramble make any sense?

Brian

 

[Banana]

Yessir, clear as mud now!

Thanks for the background.

 

[Brianwarnock]

Yessir, clear as mud now!
QUOTE]

I'm noted for my eloquence

Brian

 

[Capo]

If you all don’t mind, I’d like to touch a bit more on the performance issues. Let’s say we have a form that auto-populates several fields by pulling data from an ODBC connection. Now, which route would be more beneficial?

1) Having the table linked in via Access, building a query in access and then referencing that query in the form

Or

2) Create a connection string in VBA, query the table via a SQL script in VBA and then populate the results

Basically, what I’m getting at is will it be more beneficial to run your SQL queries in Access, or via VBA?

 

[DJkarl]

If you all don’t mind, I’d like to touch a bit more on the performance issues. Let’s say we have a form that auto-populates several fields by pulling data from an ODBC connection. Now, which route would be more beneficial?

1) Having the table linked in via Access, building a query in access and then referencing that query in the form

Or

2) Create a connection string in VBA, query the table via a SQL script in VBA and then populate the results

Basically, what I’m getting at is will it be more beneficial to run your SQL queries in Access, or via VBA?

I can only speak about my own experience in this area but I would say that linked tables and stored queries seem to run slightly faster than using connection strings and pure VBA.

I have used and still use both methods depending on what I'm developing. If I am developing an automated process that does not need a user interface I will use whatever method gives me the best combination of reliability and performance. If however I'm developing a front-end for a user then I'm far more concerned with security than performance, I will usually opt for the VBA method because it prevents adventurous users from messing with linked tables or stored queries.

 

[Banana]

Few points to consider.

It is difficult to answer your question, because it depends on what is more important to you.

If you want something that just works, then ODBC is more than adequate for the job. IINM, Access doesn't exactly support disconnected connection, (but I was told that it could be done in ADO... Anyone?). Therefore, connection strings would be the answer.

However, I don't think overhead will change that much. With ODBC, you're using a driver to communicate between two servers (Access's Jet engine and the server backend). With connection client, you're using a front-end client, if only a CLI client, for the server to pass along the string. I haven't done any benchmarking but can't imagine performance would be that drastically different between those two methods. Hence my assertion that it depends more on what you need out of ODBC/Connection string.

 

[Len Boorman]

You can write code and debug it to your heart’s content. I'll be sipping pina coladas by the pool while you are burning the midnight oil.....

That swings the discussion for me

Len

 

[Simon_MT]

Personally, One of the advantages of Access has been its interface particularly in designing Queries. SQL Queries should not be underestimated, they are extremely powerful and will take you right into the world of web based applications, if required.

I personally don't see the need to re-invent the wheel when it comes to Forms and Reports although you do grow out of the wizards. I would also write all your scripts in a Module outside the Form or Report, rather than using Macros or Sub Routines. There is better error handling, collaboration and debugging facilities with VB.

The only problem with linked tables is the performance with FE and BE deployments, this can be resolved with an "always open" Form bound to a Query or Table at the BE.

Simon

 

[The_Doc_Man]

I'll be stronger than Simon on the form/report wizards. REGARDLESS of the fact that they are as dumb as a box of rocks, the aforementioned wizards can be used to give you a VERY good starting point for a basic form. Then you customize from there.

There is only one reason to not use the wizards and that is when you are trying to do something for which Access wasn't really designed.

 

[The_Doc_Man]

I'm going to address another theme: Efficiency.

Access is written in whatever it is written in, probably mostly C++ but heck, that's just a guess. BUT ... it is compiled to machine code level. Very efficient for most things. VERY efficient for any kind of math and for many object property operations because they involve register/indexing off the base of the object's data structure - which hardware supports directly.

If you roll your own equivalent using VB then you can again compile it down to machine code level. Again, register/offset indexing to get properties is a simple thing.

If you use VBA to do everything, remember that VBA is semi-compiled to pseudo-code after which the pseudo-code is interpreted. Right there is a HUGE performance hit. The property pick-up now has to go through all sorts of validation and verification so that you pick up something that is properly defined.

I once wrote an assembler simulator for a different machine's assembler. I got an A+ for the simulator, which had an implementation ratio of between 25 and 50 instructions of my host machine for every simulated instruction. I've seen some fairly fast pseudo-code operations that do a lot of efficient things but that ratio is still up there in the 25+ real instructions to each pseudo-instruction. The overhead to protect the environment is just incredible - and not to be ignored when implementing systems.

So if you wanted to roll your own with VBA, limit that to functions Access does not itself support. OR... if you are good at writing DLL files, you could write your own DLL in VB and then make an external reference to it. With this word of warning - rolling your own DLL works only if you turn down your system security level or if you can digitally sign your DLL file. And even if THAT all works, you have to temper the urge to write in VB because there is overhead in an external call, too.

Generally, when talking efficiency, you just can't beat Access pre-coded queries and other features.

 

[Brianwarnock]

Doc I posted this way back on 17/9 post#8

I seem to remember Pat Hartman replying to a similar post a couple of years ago and saying that you could never write code to match the performance of the built in facilities.
Brian

To paraphrase another poster, "If Pat said it it swings the argument for me"

Brian