Using Access Objects vs. Pure VBA Code

[Simon_MT]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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.