VBA - why??? 14

[neillovell]

I'm reading some books on VBA and all I keep thinking is "why?"

For example I read that you can search records using vba, filter the records using vba, write sql clauses using vba, but WHY would I want to do this when I can use the navigational bars, the property pages, etc. etc. to do exactly the same things and much quicker and easier?

Am I missing something here?

 

[hjgoldstein]

Hi, Neill.

I've been wondering the same for a while now, but was too scared to ask. I have a database that someone else here has developed almost exclusively in VBA, and it seems to be so that anyone without at least three or four years of VBA experience can't get anything out of it.

I'll be interested to the other guys' response to your question.

 

[SgtJarrow]

Me![HangingOnEndgeOfBlackHoleThisWillBecome].Hands = 2

I wasn't going to get into this discussion at first, but I found some free time.....

I am going to start by saying, that there is a time and place for almost every object in Access. Queries can be useful, if the query is static and does not change. Macros have their place, again if their is not much of a change in the macro. Navigation buttons make sense if you want to walk through the records one by one.

But let's imagine a query that is flexible.....for example, you have a national sales database. You want to be able to display in a report all the sales you have made in fifeteen states. So you build yourself a query. Now, you need to be able to display a report of thirty other states....another query is needed,or you have to modify the existing one.....Using one list box, a SQL statement, and a for each statment, you can have a flexible report, that will display only the selected states, from 1 to 50, in the list box.
-------------------------------------------
Now let's imagine a large database (thousands of records). You have a request to be able to jump ten or maybe fifty records ahead of current with a navigation button. Nav buttons like:

-100 -50 -10 -1 +1 +10 +50 +100

Creating your own buttons through VBA, and using the .Move property of a recordset, you can easily accomplish this.
------------------------------------------
Next let's take comments.......can you place comments in a query??? On a property sheet??? No! So what happens when in two years (average time on job in the IT world), you leave the company and someone else takes over your database??? Or worse, you get hired to fill the job of the guy who just left??? With the all query method, you are in a world of hurt to take over and figure what is going on. A properly developed database suing VBA code for most of its function will include a large amount of comment lines....and the comment lines should be in plain english (or appropriate language) that any joe schmoe off the street can read it and know what the next lines of code will do. I write my comments as if my boss, a non-Access programmer, needs to be able to understand them.
-----------------------------------------------
Size is an issue of warrant. Every object, like a query and a macro, may not seem like much individually, but when you have hundreds to view through and maintain, they take up screen space and MBs too.....
-------------------------------------------------
Finally, security.......

First, the fewer static objects in the database, the easier user-level security through an .mdw file is to maintain....you have fewer object to assign permissions (or forget) to. And if you create a .mde file......all you "code" is completely locked out....users (and potential competitors) have absolutely no access to the procedures of your database.....I can open a .mde file that is all queries and such, and figure out exactly what is going on.....if there is mostly VBA modules and functions....I ain't getting into em....
-------------------------------------------------
These are just a few examples.......I gonna grip the the side of the blackhole again and watch from the rim.....

Me![HangingOnEndgeOfBlackHoleThisWillBecome].Hands = 2 Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[neillovell]

Thanks for taking the time to explain your thoughts. There are sound reasons I can see from what you have said, but it does not make me want to convert my database to VBA. Speaking of which, how could I change say my SQL query from SQL to VBA code?

It appears to be useful for long term, large projects, or if you need to do something like the navigational buttons skipping several records at once like you said, but it appears that the need for VBA is to do with MS Access' limitations.

 

[SgtJarrow]

Again, like I said there is a time and place for each approach......If you have the SQL statement.....in your VBA code, you can use:

DoCmd.RunSQL("Insert SQL Here&quot

Just take a look at the DoCmd.RunSQL help and let me know if you have any questions....

Some other considerations......VBA is useful in automation with Word, Excel, Powerpoint and Outlook....Using VBA, you can open a word, file, edit it, save it as a new name, etc. You can create an entire Powerpoint presentation from memo fields in your database, all with custom formats, graphics, sounds, etc. You can create e-mails with attachments, create journal entries, and even retrieve e-mail data, such as body text and insert it directly into your Access database.

Imagine you want to import a .csv file that has 22 fields. (I just completed this project....) Field 13 has 10 different values. For each value, you have to grab a certain field for import....for example if field 13 = abcdefg then import field 14, else if field 13 = hijklmno then import field 15.....This would suck if you have to use queries and a macro to make it work.....Using VBA, you create a recordset of the import, and a recordset of the table to put the data in.....and you go through it line by line....using a select case staement for field 13 you know which field to import......in just one statement.

I would never stress converting your entire database over to VBA. if that were the case, perhaps you should be looking at true VB instead??? What you have to do is decide what works for you....but also leads to the best managability.....There is nothing worse than a database you haven't touched in a year (and you have since developed ten other) suddenly going goofy, and you have no comments and stuff to work with.....

As to the comment it is useful in large/long term databases....here's a bit of advice.....(from my military career)......Train As You Work. If you figure out a good system, use it always.....you may be doing small databases now, but what happens when they (the illustrious they) ask you to develop a larger, more robust database? You will tend to want to use that which is familiar. Start learning the good habits now..... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[neillovell]

Ah thanks.

But how do I get to the VBA coding bit? Do I do Tools-> Macro -> Visual Basic Editor or is there another way?

 

[SgtJarrow]

That ultimately depends on what you are trying to do......If it is a universal (global) function, then in the database window, simply click on modules and then click new. If it is part of a form or report, there is abutton on the tool bar to pull up the code window. To convert a macro to vba, go to the macros tab, highlight the macro, in the menu bar, select tools -> macros -> convert macro to vba. This will create a Module (in the modules tab) of the macro.

I would ultimately suggest you invest in some serious Access resources. They may be about $30 - $50 USD each in the store (you can find them much cheaper online at sites such as eBay and Amazon) but they are SO worth it.....And besides, if this is for a job, you can usually get your employer to buy or reemburse you for the purcahses....after all, it only benefits them.....

Some good suggestions (I have the 2000 version of these):

Access insert 97, 2000, or XP Developer's Handbook published by Sybex
Access insert 97(?), 2000, or XP(?) VBA Handbook published by Sybex
Visual Basic Langauge Developer's Handbook published by Sybex

If you want ISDNs for these, please let me know.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[simmsey]

I've been trying to resist getting dragged in but.. To reinforce what has already been said.. here goes.

You shouldnt think of VB and Access Form methods as one or the other. They are complementory. The best thing is to find a useful balance using capabilities and advantages of each. VB is an integral part of Access not a prop for inadequecies in Access as was suggested. VB provides a much more powerfull set of capabilities which just couldnt be handled with the standard form functions.

No one would suggest rewritting databases to just use Access VB, there wouldnt be enough benefit unless you tried to drop Access completely and use VB6 or similar (but that is completely different to using VB within Access)

Most people fairly quickly find things they would like to do in thier database that stretches the standard form, report, query and macro functions. And that drives them to investigate VB events and modules.

If you want examples how about:

Automating a complex set of data import queries.
Have code that formats a field in a bizare way.
Have a custom validation procedure to check data against your special criteria before entry.
Have forms that can switch automatically which tables they get thier data from depending on user input.
Automate queries to use date picker controls etc.

Really the list is endless.

Besides the jobs that you can only really do through VB there are many jobs which could be done either way. E.g. a macro called by a control on a form VS VB in an event.
Or a query VS a query in VB code.
As previously stated you'll quickly find that VB offers a usefull ability to simplify the dependencies in your database to help management.
You can get to the point that it's very hard to track which files are interdependent in the database.
i.e. this form uses this query and this macro.
VB can allow you to make forms that are not dependent on any other files. (obviously much easier to manage)

Best place to start understanding VB's usefulness in Access is to get a good Access book.

 

[patkung]

Robert, I have found your comments most helpful. I have a question about your comment, "can create e-mails with attachments". I have been able to send e-mails from my code but Outlook always comes up for each e-mail sent and needs to verify that I want the e-mail sent. Is there a good way around this? I know it is a virus prevention thing, and probably want it on most of the time, except when I am trying to send e-mails from my database. Any good suggestions? Thanks in advance.

 

[mikey69]

I think one thing that needs emphasising is that manipulating your data to add records can be done without VBA, but the process will be infinitely less robust. Use VBA and you can trap errors, validating your users' input is much easier.

I'd also like to reinforce what simmsey says about a good book. Helen Feddema's book is one such. Another is Access Desktop Developers Handbook by Litwin, Getz and Gunderloy.

Microsoft's own book Building applications with Microsoft Access alos has useful background about why VBA.

Finally, if you perservere, you'll find that jhaving VBA as one of a range of tools speeds up the process of getting applications up and running. I should also add that I personally don't include macros in my toolkit. I decided right at the start of my working with Access to go straight to VBA and I've never regretted it - quite the oposite, in fact.

 

[SgtJarrow]

Patkung,

You wrote:

. I have a question about your comment, "can create e-mails with attachments". I have been able to send e-mails from my code but Outlook always comes up for each e-mail sent and needs to verify that I want the e-mail sent. Is there a good way around this? I know it is a virus prevention thing, and probably want it on most of the time, except when I am trying to send e-mails from my database. Any good suggestions? Thanks in advance.

It would probbely be best to start a new thread for this question....It is on the same topic yes, but thid is a discussion on viability of VBA....

So start a new thread. In it include your original question as poste above. Also, include a sample of the code line you are using to send the e-mails. I already have my suspicions and I'll be grlad to help you out.... Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[DataChick]

Most everything has already been said but I wanted to make one more point....

In response to the comment about the developer who designed practically the whole database in VBA:

Keep in mind programming is an art. Each person develops their own style as they gain experience. I have had a lot of training in languages such and C+ and Visual Basic and find that I tend to write out a lot more in VBA as opposed to doing the same things using the Expression Builder or a Macro. On the flip side, I have not had a lot of training in SQL (and don't really care for it) so I tend to use the Query building tools in Access instead of writing them out in SQL.

The person who preceded you probably had been trained in VB first (or a similar language) and Access second, so they leaned toward the developing style that was more comfortable to them.

One of the greatest things about Access is it's flexibility. You have the ability to use the tools provided to accomplish the same things you could code by hand in a much shorter time (and without having to endure the training of SQL and Visual Basic)...or you can code it all longhand, using comments to create a step by step of what your database is trying to accomplish.

Just my thoughts...great discussion. "The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[LittleSmudge]

Neil

You said
But how do I get to the VBA coding bit? Do I do Tools-> Macro -> Visual Basic Editor or is there another way?

I think you are searching too deep

All of the EVENT PROCEDURES code that we've been discussing over recent weeks IS - VBA

thread68-355567
thread701-367344
thread702-362968
etc .. ..

All of the If .. Then stuff
All of the Dim rst As ADODB.Recordset stuff
All of the For .. Next stuff

Its all VBA.

If you're not already into MACROs I would NOT invest AND time in learning anything about macros. They are an old legacy approach that is best left to history.


To 'get into it' you need to decide when and where you want the code to run - then place that bit of code inside the appropriate Event Procedure.
To get to the Event Procedure go to a form ( or Report ) in design view and have the properties dialog box open.
Select the object that you want the event procedure for and in the properties dialog box you'll see a list of the relevant ones.

Double Click in the which space beside the name an the words ""[Event Procedure]"" will appear in the space.
Then click on the little box with three dots in it and you are into the VBA code window.


'ope-that-'elps


G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.

Please remember to give helpful posts the stars they deserve!
This makes the post more visible to others in need! :-D

 

[hjgoldstein]

I've read all of this with interest, and now I understand a little more about the value of VBA. I've ordered the Developer's Handbooks and when these arrive, I'll no doubt be able to understand more.

In response to Datachick's post, my big problem with the code in the other guy's code is the complete lack of comments. I'll just have to work through it and hope for the best.

Thanks for all the feedback to Neil's initial post.

 

[neillovell]

Coming from a C++ background VBA is a curious beast - it's like Access lets you do so much, but to do more you need VBA.

Hard to get one's head round.

 

[cheerio]

Returning to the original question, there are two extreme ways in which Access can be used.

First, a very competent Access user may wish to create a database just for his/her use. He/she will be comfortable with using the different Access components - queries/reports/macros/VBA and will use as appropriate. For example, if I want a list of my contacts in Canada I can create a query for the purpose.

Secondly, a developer may wish to create a database for non-technical people to use. We assume these people will not create their own queries and reports. They will usually work using forms. There will be validation to avoid capturing data that is clearly wrong. When things do go wrong they will get error messages in terms that they understand rather than messages that require a knowledge of Access. They should be protected from warning messages that need not concern them.

The developer will use VBA because it provides many of these features - error handling, validation, custom messages etc. As mentioned by others it is far easier to document and has greater security.

One does need a period of time to get the relevant level of VBA skills. It also takes time to create an application that is used by others as you need to understand their needs and what they may do wrong. It is not a criticism of Access that these things are not easy because it is never easy to create systems for others to use.

It is a big step to go from developing a database for your own use to creating one for other non-technical users. Anyone planning that step will need VBA in order to create robust reliable applications that allow users to see and work in a way that is comfortable with them.

Ken

 

[lameid]

A thread referenced this thread as an answer... While I do not disagree with anything thus far, I feel compelled to put my two cents in.

A reason not to do everything in VBA is performance. Query's that are saved are compiled and execute faster than literal sql statements that have to be compiled to run. Similarly a query even if it is compiled and run in VBA is faster than a recordset that does the same thing. So why use recordsets? Because sometimes queries can't do what you need. It is also good to know that if you have to use recordsets, it is best to use a query or sql to limit the records you are going to process first, if possible (use criteria in a query to process all records for Ohio instead of using findnext or worse still using movenext and testing the fields).

And to reiterate, a lot of using VBA and not using VBA is personal preferance. I'd also add that if you can read VBA and you can read macros, it is easier to read the VBA instead of macro's... Well there is one counterexample I have. I have a macro that runs a series of action queries. I find that it is easier to cursor down and read the query names in the macro than it is to read them in VBA. But again that is personal preference.

On the otherhand there is an elimant of truth that VBA is there to supplement the other objects. The other objects have their place but some things can only be done in VBA but a lot can be done either way.

 

[lameid]

Wow, "elimant" obviously should be "element"... Who taught my fingers phoenetics? I'll let my lesser mistake(s) slide.

 

[CJTyo]

Robert's first point is the most excellent reason to use VBA: reusable code. VBA code can be written to handle receiving various names/variables based on different inputs, making it much nicer than having to 'hard code' information into queries over and over again.

Having recently taken over management of a DB designed by a novice, let me tell you... the first thing I've done in the redesign process (after proper table design & relationship structure) is to start teaching myself VBA from the ground up using the Access 2000 VBA Handbook. And it's helping me a LOT in figuring out what I can do with my new, from scratch, redesigned DB.

C *~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[DataChick]

The point about reusable code is a good one.

Modules can only be created using VBA and that is where you write your code for things you are going to reference over and over. The nice thing about creating the module is that you can then use them in your macros so once you start with VBA it doesn't mean you have to be committed to it for the duration of your database development.

I personally only use Macros when I am just running a series of queries because it is a lot easier than typing out all the query names in VBA but I prefer VBA because there are many more options then with the Expression Builder and Macros.

But, I prefer Access with VBA over VB because the option to "cheat" and use the building tools available in Access is very enticing. I definately think the proper combination of VB and the Access tools helps a Programmer develop an Access application that is powerful, efficient and easy for an end user to understand. "The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein