Using Access Objects vs. Pure VBA Code (1 Viewer)

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:18
Joined
Feb 28, 2001
Messages
27,001
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

Retired
Local time
Today, 22:18
Joined
Jun 2, 2003
Messages
12,701
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
 

Simon_MT

Registered User.
Local time
Today, 22:18
Joined
Feb 26, 2007
Messages
2,177
Docman,

Since, you raised this, I have found that there are a lot enviromental issues that have a profound effect on the performance of a database and my question compared these issues is VBA really that bad? Here are some of the issues I have encountered:

1) A FE / BE implementation without a Form at the Front End bound to the Back End.
2) Running applications over a network or too many applications open.
3) Tracking cookies
4) Huge jpeg images

VBA once saved my bacon as the volume of combiboxes and generally complexity doing multi-currency / multi-company sales invoicing was resolved by VBA. The last point I find VBA more logical and flexible whilst the compile function with VBA irons out many of the typo and syntax errors.

I only use VBA for "improper scripts" as the Forms and Reports are all Access without Sub Routines as this considered more efficient.

Simon
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 17:18
Joined
Feb 28, 2001
Messages
27,001
VBA has its place and I use the bloody Hell out of it. But the original question had to do with using VBA in place of Access functions. It's not a black & white issue, of course. Rarely do we find those. But the more you can do with an Access query or predefined function, the better off you are. THEN when you can't find what you want anywhere else, VBA is your answer.

Simon, I would be a flamin' idiot if I told you to never use VBA. Just make thoughtful decisions about when to use and to not use VBA. As mentioned somewhere else in the thread, VBA is the scaffolding by which you erect something that just cannot be done within the confines of elementary Access. It is the extension to SQL that lets you get from point A to point B without having to go around the block to get there.
 

Simon_MT

Registered User.
Local time
Today, 22:18
Joined
Feb 26, 2007
Messages
2,177
Doc Man,

Forgive me, I did mean to sound impertinent but the reason I raised this issue is that there are numerous newcomers to the site asking quite fundamental questions coupled with a general misconception about the performance of Access.

The problem we all have is obtaining efficiency and performance from Access and this does not come out of the box. I am a get fan of Queries, Forms and Reports and I'd never consider substituting these with VBA. Yet, VBA has a very important place within Access as well. I found that the heavy use of combiboxes with embedded SQL statements eventually created a situation where a show stopper occurred to the application. This is where VBA came to the rescue.

All the Forms or Reports based Events and Functions are VBA Module based and to me this is extremely efficient and it works. Not one of the Forms or Reports has a Module.

Simon
 

Rabbie

Super Moderator
Local time
Today, 22:18
Joined
Jul 10, 2007
Messages
5,906
All the Forms or Reports based Events and Functions are VBA Module based and to me this is extremely efficient and it works. Not one of the Forms or Reports has a Module.

Simon

I am a little puzzled as to what you mean here. How can you use VBA in a Form without a module. I always thought that as soon as I wrote one event handler for a form then it created a form module.

To return to the main discussion there is always a place for VBA but in any tests I have done it is always quicker to use a query for bulk updates. It is a simple case of horses for courses and you need to keep an open mind on what is the best solution for a particular instance.
 

RoyVidar

Registered User.
Local time
Today, 23:18
Joined
Sep 25, 2000
Messages
805
Rabbie,
=FunctionName() in the event property, no event procedure.

At one point of time, that was discussed and suggested as method to speed up forms etc, but one doesn't often hear about it anymore.
 

Banana

split with a cherry atop.
Local time
Today, 15:18
Joined
Sep 1, 2005
Messages
6,318
But would the code be easy to maintain?

While I would definitely want to use a public module to hold functions/subs that are used in several forms, I am not sure if I would do so for something that's pretty much specific to a form, which is commonly the case when we're talking about data validation?

How would that be maintained?
 

ajetrumpet

Banned
Local time
Today, 17:18
Joined
Jun 22, 2007
Messages
5,638
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
Capo,

From a simple mind, choose the best (easier / less work) option from the following...

1) A person takes a roll of film into Wal-Mart for processing. To get the results, the film passes through...*The customer, *Clerk, *Processor, *Printer

2) A person uploads digital pictures to a computer at a Wal-Mart clerk's workstation. To get the results, the information passes through...*The Workstation, *Printer.

Which is faster??
 

pdx_man

Just trying to help
Local time
Today, 15:18
Joined
Jan 23, 2001
Messages
1,347
Which is faster??
Hmmm ... faster or better ... if the clerk is smokin' hot, I'd probably opt to take my roll in.

My point is, just because it is faster the one time you run it, doesn't necessarily mean it is overall the best solution. My case in point would be utilizing those d@mn Domain Aggregate functions (Dlookup, Dsum ...) against SQL linked tables. It creates a separate connection for each row creating a new batch to send to SQL Server. It locks the SQL table and fills your logs. If your database is totally contained within Access, use the internal functions whenever you can, but if performing the functions listed earlier, utilize VBA to create only one connection and utilize it to get your data.

If anyone is wanting to check it out and have SQL Profiler, run it with all of the TSQL events being traced. Take notice of the SPids changing while your Access query runs. A brand new connection is made for each row.

In this case, I'd go check to see if the clerk was a hottie.
 

Simon_MT

Registered User.
Local time
Today, 22:18
Joined
Feb 26, 2007
Messages
2,177
I agree with the avoidance of Domain aggregates, a last resort measure and I never use them in a Query. I would also add that Combiboxes with SQL statements in the record source don't appear to be efficient either and in my experience using Functions seems to be a much better option.

Going back to VBA I would do all my VBA as Functions is more manageable, your code is all in one place. Its easy to reference [Call Function FunctionName] and its totally searchable.

For someone unfamiliar with VBA and has written Macros there is a tool Convert to Macros to Visual Basic and whilst Macros may not ne able to handle If..then..else etc. VBA certainly can. Anyone can master VBA and its well worth the effort.

Simon
 

ajetrumpet

Banned
Local time
Today, 17:18
Joined
Jun 22, 2007
Messages
5,638
Hmmm ... faster or better ... if the clerk is smokin' hot, I'd probably opt to take my roll in.
Good thing we're talking about a clerk here, 'cuz I've rarely met a machine that gets me excited...
My point is, just because it is faster the one time you run it, doesn't necessarily mean it is overall the best solution. My case in point would be utilizing those d@mn Domain Aggregate functions (Dlookup, Dsum ...) against SQL linked tables.
And see, that is the like saying "It's NOT OK to take Train A's shortcut route to save time getting to your destination, because you might encounter Train B traveling the same route".

There IS a univeral rule regarding labor...."Less Labor per task = more efficiency per task". I know it's not perfect for applying software to your own needs, but the majority of the time, it works!!
 

Rabbie

Super Moderator
Local time
Today, 22:18
Joined
Jul 10, 2007
Messages
5,906
There IS a univeral rule regarding labor...."Less Labor per task = more efficiency per task". I know it's not perfect for applying software to your own needs, but the majority of the time, it works!!
You need to take into account ease of maintenance as well as runtime speed. It would all depend on how often things might change(Often more often than you think) and how great the improvements in efficiency. As has been said several times in this thread you need to use judgement in making these decisions.
 

pdx_man

Just trying to help
Local time
Today, 15:18
Joined
Jan 23, 2001
Messages
1,347
aj, that is my point exactly. What many Access programmers don't think about is the TOTAL cost of implementation. They run a complex query that returns their results in 15 seconds and say, "Hey, that was fast. Great, we'll go with it." But they don't realize that they have locked several tables and caused dozens of other processes to be delayed. They have bloated the SQL logs out so much that they become unusable to the DBA.

Your statement was trying to equate saving time means less labor. This is not always true. Sometimes it takes a lot of labor or it affects a lot of entities to have something occur the quickest. Just because the A train get's there faster does not mean it is the best route. Is it worth taking the shortcut route if you must then send out a crew of 25 people to inspect the track?
 

KenHigg

Registered User
Local time
Today, 18:18
Joined
Jun 9, 2004
Messages
13,327
If a primary requirement is scalabilty, a good rule of thumb as far as tables are concerned when it comes to user interface is to get in, do what you need to do, and get out.

:)
ken
 

ajetrumpet

Banned
Local time
Today, 17:18
Joined
Jun 22, 2007
Messages
5,638
aj, that is my point exactly. What many Access programmers don't think about is the TOTAL cost of implementation. They run a complex query that returns their results in 15 seconds and say, "Hey, that was fast. Great, we'll go with it." But they don't realize that they have locked several tables and caused dozens of other processes to be delayed. They have bloated the SQL logs out so much that they become unusable to the DBA.
And I'm sure you, as an administrator, would not like that very much! :)
Your statement was trying to equate saving time means less labor.
Yes, in a general sense, but hey man, I have known very FEW people in my lifetime that have been able to (or rather had the capacity to) think their actions through completely before jumping in.
Sometimes it takes a lot of labor or it affects a lot of entities to have something occur the quickest.
I will tell that to the next businessman I meet who thinks a job like yours consists of sitting around all day and eating cookies... ;)
Just because the A train get's there faster does not mean it is the best route. Is it worth taking the shortcut route if you must then send out a crew of 25 people to inspect the track?
It depends if those 25 people are girls, and if they are hotties or not... :) :)
 

pdx_man

Just trying to help
Local time
Today, 15:18
Joined
Jan 23, 2001
Messages
1,347
It depends if those 25 people are girls, and if they are hotties or not...
I'm with you there, brother!!! I like the way you think.
 

Users who are viewing this thread

Top Bottom