Where does VBA comes in

[Ron_dK]

I have made a couple of databases and tried to apply the 1st, 2nd and 3rd Norm
as much as possible.
So starting off with the business concept, making tables, queries, relationships, entry forms, overview forms, reports and the like, I always struggled where and when to use VBA. I seldom use any macro's.
Could anyone shed some light on to where VBA would come in.

 

[raskew]

3-1/2 years + 402 posts and you ask this?! Maybe another line of work might be more appropriate.

Bob

 

[Ron_dK]

I asked this as to see if a general concept would be applied by all of you.
Not only for me, but for other ( new) access users, to get an idea
of where , when and how to use and apply VBA in general.

 

[raskew]

Hey BeBop -

This is not working for you!
Suggest you look for another line of endeavor.

Bob

 

[Rich]

3-1/2 years + 402 posts and you ask this?! Maybe another line of work might be more appropriate.

Bob

I've been coming here a lot longer and couldn't answer directly either. Do I need a new career?

 

[Pat Hartman]

I have an answer but I'm not saying that it is the only answer.

I use Access because of all the work it does for me. I happen to understand exactly what that work is because I used to have to code it all myself when I was developing mainframe applications and perhaps that makes me appreciate it more. I could do it now but I've already written my million lines of code and I don't need the practice. My policy is to use SQL for everything that SQL can do. If I can't do it with SQL, I use property settings on forms/reports. Only if Access and SQL offer me no solution will I write VBA. I'm not talking here about a line of code here and there to set properties or calculate a value. I'm talking about the hundereds of lines of codes way too many people feel the need to write to do things that Access already does and does well.

I find that many people who previously used VB, C++, or any other language that doesn't have bound forms/reports start out by making unbound forms/reports because that is what they're used to. In my mind, they should have stuck with their previous environment since they are not taking advantage of the best tool Access has to offer.

So to sum it up, in mind there isn't any choice as to whether or not to write VBA. It always comes down to - write VBA if there isn't any other way to do it.

Macros don't enter into my universe because I actually can't think the way you need to think to do complex things with a macro. Most complex macro processing involves automating forms to process recordsets. For my own purposes, I have occassionally built macros that run a bunch of queries. This is most likely to happen when I'm doing a conversion. The conversion may have to be done numerous times but it is not really part of the application. Once it is done, it is done and all that code is no longer needed. But, I never build macros in a database that will be used by others.

Comming up in the next version of Access, there will be a new emphasis on macros because of the way Microsoft has elected to handle security. But since they still haven't given us error trapping, I will continue to avoid them.

 

[jsanders]

Coming to Access, from a completely different avenue then Pat, I still have the exact philosophy.

My background is running small businesses and now helping people who run small businesses become more efficient.

I never do things the hard way.

If I can use a saved query, or copy a little tiny bit of code from another form. That’s what I do.

I think a lot of people would consider me inept as a database designer but I think more about what users need to do and what is the most intuitive way for them to work instead of cool code.

I am in the process of making a database for a customer that had it done in C++ what a joke, $30,000 worth of unusable code. I’m replacing it with a couple of thousand dollars worth of forms, tables and reports, which actually work.

 

[Ron_dK]

My policy is to use SQL for everything that SQL can do. If I can't do it with SQL, I use property settings on forms/reports. Only if Access and SQL offer me no solution will I write VBA.
.
.
So to sum it up, in mind there isn't any choice as to whether or not to write VBA. It always comes down to - write VBA if there isn't any other way to do it.
.
.
Macros don't enter into my universe because I actually can't think the way you need to think to do complex things with a macro.

Thanks for your worthy response, Pat.
In line with what you're saying, I try to stick to SQL and if needed I add
code to the form, report and the like.
The background of my question comes from a discussion I had with some
guys here, who are experts in VBA coding, but are hardly using any SQL in their Access Dbase design. As I am not an expert in VBA, I tried to avoid
inserting code and use SQL instead.
It seems that my way of working matches the policy you described.

Cheers, Ron

 

[Pat Hartman]

The problem, as I see it, with writing a lot of code is that I have to debug it. Given that most programmers are arrogantly optimistic (I wrote it so if it compiles it must work), myself included, testing is not as thorough as it should be – just look at all the bugs left in commercial software for example.

An example of substituting SQL for VBA is the use of update queries. People familiar with VBA will automatically create a code loop to do the update. I will automatically create an update query. My way will execute faster and take less time to develop but none of the books tell you that.

 

[WindSailor]

The problem, as I see it, with writing a lot of code is that I have to debug it. Given that most programmers are arrogantly optimistic (I wrote it so if it compiles it must work), myself included, testing is not as thorough as it should be – just look at all the bugs left in commercial software for example.

True.
Especially when I find I have code bouncing around modules, forms etc. and filtering the loaded form using the ‘OnCurrent’ event. I believe it is easy to over-complicate an idea with code and blow everything out of the water. It seems there are 1000+ ways to do things, some are more elegant than others and some are just simple and stable, big difference!
I wish the editor would by default carry some error handling similar to Try/Catch/Finally statements, but one could always implement their own.
To get out of bounds here a little, sometimes in .Net working with databases (some engines support visual data binding and some do not) it is easier to set your Relationships/DataBindings etc. in code when designing a project (Newbie, working with program flow, adjusting tables etc.), it helps when things are moving around. You can always change later.
Actually to help with code, I am going back over the ‘Normal Forms’ rules and seeing if changing the database design will help,

 

[ChrisO]

Seems like a good topic.

When we move from environment to environment we take with us our past experience.

If we come from the world of SQL then that is the way we perceive the new world.
If we come from the world of code then that is the way we perceive it.
However, it takes time to fully utilize the facilities of that new world.

A question then arises; why not try to learn the new world whatever it may be, and, what do we use in the interim while learning?

We need to get the job done and not sit around twiddling our thumbs doing nothing else other than learning.

To learn which is the better method, SQL or VBA, will take time.

Claims, largely unjustified because they have not been tested and submitted for scrutiny, that SQL is better than or worse than VBA can be ignored until they are justified. What we usually get is a lot of ‘gum bashing’ but seldom do we get the proof, and that goes both ways SQL<>VBA.

So the theory seems to be; we come to Access and therefore we should learn SQL.
Correct, but if we come from nowhere would it not be better to learn both?

One day we might have to go to a non-SQL world.

Regards,
Chris.

 

[Pat Hartman]

I think you missed a couple of points in my post, Chris.
1. I come from a code background as I have said on many occasions. I learned to write code before SQL was invented (and also before I could vote or drink) so I think I can speak from both perspectives.
2. SQL, while certainly useful, cannot do everything whereas code can so I'm not sure what you mean by "If we come from the world of SQL then that is the way we perceive the new world. If we come from the world of code then that is the way we perceive it." You may know of a world (development environment) where only SQL exists, but I don't.
3. "Claims, largely unjustified because they have not been tested and submitted for scrutiny," That statement simply offends me. Just because I didn't create a formal proof and submit it for your approval doesn't mean that I am not making this statement based on empirical evidence.

 

[jsanders]

I think you missed a couple of points in my post, Chris.
1. I come from a code background as I have said on many occasions. I learned to write code before SQL was invented (and also before I could vote or drink) so I think I can speak from both perspectives.
2. SQL, while certainly useful, cannot do everything whereas code can so I'm not sure what you mean by "If we come from the world of SQL then that is the way we perceive the new world. If we come from the world of code then that is the way we perceive it." You may know of a world (development environment) where only SQL exists, but I don't.
3. "Claims, largely unjustified because they have not been tested and submitted for scrutiny," That statement simply offends me. Just because I didn't create a formal proof and submit it for your approval doesn't mean that I am not making this statement based on empirical evidence.

You go girl.

 

[ChrisO]

G’day Pat.

Well I wasn’t replying to you but simply adding some general comment to the thread about the need to learn both SQL and VBA.

I too came from a coding background, about the mid ‘70’s, and did not hit SQL till about ten years ago. The reason for that was I had no need for SQL until I started with databases.

There are other people on the web who will freely admit they come from an SQL background and are not all that proficient with code (VBA). Nothing wrong with that either because they probably had little need for it.

I do not know of a development environment where only SQL exists, but I am often surprised how much of a database can be created without code. For example, the Northwind database has very little code for what it does. So people that can do that sort of database, without much need for code, will probably not become proficient with code. Others, such as myself, lean heavily on code and will probably not become proficient with SQL. (These days I almost force myself to try to use SQL but often don’t succeed.)

But VBA and SQL are both languages and like all languages if we don’t use them they will be forgotten or at least diminished.

I very much doubt if you should be in any way offended by my dislike for unjustified claims. The type of claims to which I refer are those that state this is faster/better than that. These claims are a dime a dozen on the web but seldom are they justified. I once had a reply that stated that testing for True was faster than testing for –1. The strange thing was that no mention was even given if the tests were done in VBA or SQL…just that it was faster???

You might recall a thread in which we did some timing tests on eight ways to update controls on a form. You and a very few others contributed to those tests but those that make unjustified claims did not contribute. It seems that for some people it is simply enough to read something somewhere and continue to just repeat it time and time again.

So to recap…
My post was a general comment to this thread and not to anything or anyone in particular.
I think it’s best to try and learn both SQL and VBA, only then can we make a reasonable choice.
Claims should be justified, at least the test conditions under which the claim is made.

And above all else Pat, please do not be offended, you have no reason for that.

Kind regards,
Chris.

 

[Pat Hartman]

Here's one of my recomendations that got quantified.
http://www.access-programmers.co.uk/forums/showthread.php?t=101171

 

[Rank Am]

Here's my two penneth worth

I am in broad agreement with Pat and don't see the point of writing large amounts of VBA code to do something that can be done automatically by access. Most of my use of VBA is for manipulating and adding functionality to the user interface. As a side note one thing that I usually don't allow access to do however is handle the connection and data transfer between the tables and the user app. This I handle 99% of the time with server side SQL stored procedures and OLEDB - ADO. I coulod use VBA class modules and collection classes but don't see the point as all you are doing is adding another wrapper on top of the SQL.

The main reason I don't let Jet handle the connection and data trasnfer is mainly down to the flexibility that SQL and ADO gives in controling:
record locking and managing write conflicts,
reducing strain on network resources by strictly enforcing the amount of data provided from the server and forcing the server (through SQL) to process any calc fileds,
ease of obtaining data from different data providers (e.g. Access, Oracle, MySQL, SAP etc) ease of shifting from a file - server to a client server environment.

In general I use VBA as an addition to the in built functions in access to manipulate the user interface and handle such things as automating word, excel and office web components, I also use it to test for installed components, testing Network status and error handling, custom Login procedures (where Win Login not suitable) usually through class modules. As a general rule I do not use it for any data handling.
One other thing that I forgot to mention was that VBA class modules are superb for and that I use a lot is application of custom methods on stored data. For example If you have a series of complex mathematical analysis fuctions to process data Class modules on the client are the way to go. These are a nightmare with SQL. Whether you break 3NF and store this data or process on the fly however is yet another contraversial topic.