Writing Solid VBA

[iheartvba]

Hi Everyone,

I Currently have two problems:
Problem 1: My Access DB has gotten to a stage where everything works, but there are many things which don't need to be there, and despite my best efforts not to, In all the exitement of coding I have written a fair bit of code which I do not understand. So my question is, are there books (etc) that I can refer to for learning to write code, where when reading the code, I or any one else who understands VBA can comprehend what is going on very easily.

Problem 2: My Access DB is running slowly I have many reports which take ages to load due to their complex underlying Queries. The slowness could be because my backend is only Access and not SQL server (etc). But I suspect that there would be a way to write better (more efficient) SQL Strings which will run faster. I currently use the query builder to build my queries. But I would be able to write them into the VBA if that makes it more efficient. I know this is a very general question, but any guidance would be appreciated even if is a good book in the subject.


Much Thanks in advance.

[PianoMan64]

To answer your 2 questions,

Question 1. The easiest way that I've found to write code, is to Comment, comment, comment. Everything that you write, put at the top of the routine what it does, and what the general purpose is for that Function, Subroutine, or Property. Show how it is used. As for lines within the code. Comment what it means and what it is doing. This will make is easy for you and also for anyone else that may come after you to begin coding where you left off.

As for any resources for learning how to code VBA, I would recommend any microsoft publishing book on VBA or any book that deals with the level of VBA programming that you understand. The other type of book that you may want to look into is a MS Access Programming Book. These are loaded with VBA code that is specificly for MS Access Only. If this is the area that you want to concentrate your efforts, that is always a good choice.

Question 2:

Since your queries can sometimes be slow due to either design, or lack of structure (i.e. no indexes on key fields that are linked between tables) this will slow the query down 10 to 1000 times depending on the number of records involved. I would recommend, that if you're planning on expanding the data in the database tables, that it may be time to Upzise to either MS SQL Express (FREE) or Oracle 10g Express (Free) or if you want to spend a little bit of money MS SQL Developer ($49.95+shipping) from microsoft. The only limit is 4Gig database size. If you're wanting something larger than that, then you're going to have to upgrade, and I don't know if that is what your database is going to need or not. Just giving you some options.

If you do want to see if your queries that you've written are good queries, you're more than welcome to post them on here and ask if anyone of the experts can take a look at them.

I hope all this helps some,

If you have any more questions, feel free to post again.

Thanks,

Joe P.

[iheartvba]

To answer your 2 questions,

Question 1. The easiest way that I've found to write code, is to Comment, comment, comment. Everything that you write, put at the top of the routine what it does, and what the general purpose is for that Function, Subroutine, or Property. Show how it is used. As for lines within the code. Comment what it means and what it is doing. This will make is easy for you and also for anyone else that may come after you to begin coding where you left off.

As for any resources for learning how to code VBA, I would recommend any microsoft publishing book on VBA or any book that deals with the level of VBA programming that you understand. The other type of book that you may want to look into is a MS Access Programming Book. These are loaded with VBA code that is specificly for MS Access Only. If this is the area that you want to concentrate your efforts, that is always a good choice.

Question 2:

Since your queries can sometimes be slow due to either design, or lack of structure (i.e. no indexes on key fields that are linked between tables) this will slow the query down 10 to 1000 times depending on the number of records involved. I would recommend, that if you're planning on expanding the data in the database tables, that it may be time to Upzise to either MS SQL Express (FREE) or Oracle 10g Express (Free) or if you want to spend a little bit of money MS SQL Developer ($49.95+shipping) from microsoft. The only limit is 4Gig database size. If you're wanting something larger than that, then you're going to have to upgrade, and I don't know if that is what your database is going to need or not. Just giving you some options.

If you do want to see if your queries that you've written are good queries, you're more than welcome to post them on here and ask if anyone of the experts can take a look at them.

I hope all this helps some,

If you have any more questions, feel free to post again.

Thanks,

Joe P.

Thanks alot Joe, I have only a slight idea of the purpose of indexes as well as how to use them effectively I will do some more research on them.

[youmike]

I endorse what JoeP says about comments and would add some other thoughts, but recognise that your question is a huge one.

1. Get to know a naming convention and use it rigidly for all the objects in your code. The Leszynski convention would be my recommendation. There are plenty of online references to it.

2. Get into the habit of indenting your code when you use Do Loops, If Clauses and the like. This makes it so much easier to follow the logic when fiixing is needed.

3. When you analyze what you need your code to do, try to break the task into self contained sub tasks which can be called as needed, with the necessdary parameters being passed at the start and end. Put the code in clearly named modules, which can be called from objects in the various forms, reports and so on .My own preference is to take this modular approach to the point that all necessary SQL is declared as a string constant embedded in the module that holds the procedure that needs it.

4. Personally, I favour an approach which uses multiple front ends linked to a common back end, with each front end concentrating on a clearly defined need, and I tend to separate needs to summarise and report from the need to process information. This is another big topic.

5. Stay away from macros!

[Stewart Ross]

Hi. Just to add to what JoeP and YouMike have already advised, ensure that you define the relationships between all tables at the design stage (i.e. setting appropriate primary keys and then setting explicit 1-many relationships between tables). Indexes for primary keys are created automatically, and where relationships are already defined this helps the Access database engine with optimising query performance.

In queries, the use of IIF statements, DLookups and so on can have a drastic effect on performance. Eliminate these in large queries if at all possible (IIFs can always be replaced by custom-written VBA functions, which run around twice as fast). DLookup, DSUM and similar domain functions are excellent solutions where one-off lookups are needed, but because these functions effectively open and close a recordset every time they are called they can really slow down a larger query.

-Stewart

[youmike]

What Stewart added is arguably the most important point. In any DB application, the difference between good and poor performance is very often relationship definition. As an application designer, you have to train yourself to think quite differently about interaction between the data elements in the system you are designing. You'll find lots of help on the internet. Personally, I have gone back and back to the original IBM report by Dr E F Cobb and I have notes written by Paul Litwin which I've found useful over the years. Microsoft also have papers in their knowledge base and the early versions of Access (Version 2, Access 95 & 97) had very useful Manuals which are still relevant.

Finally, recognise that you never stop learning. I've been at it since IBM sold the first PCs and have over the years taught others, both formally in classes and informally on the job. I still treasure forums like this because they are such a great source of knowledge.

I could make available a list of books I've used over the years, if it would be of any help.

[LosLobo]

What Stewart added is arguably the most important point. In any DB application, the difference between good and poor performance is very often relationship definition. As an application designer, you have to train yourself to think quite differently about interaction between the data elements in the system you are designing. You'll find lots of help on the internet. Personally, I have gone back and back to the original IBM report by Dr E F Cobb and I have notes written by Paul Litwin which I've found useful over the years. Microsoft also have papers in their knowledge base and the early versions of Access (Version 2, Access 95 & 97) had very useful Manuals which are still relevant.

Finally, recognise that you never stop learning. I've been at it since IBM sold the first PCs and have over the years taught others, both formally in classes and informally on the job. I still treasure forums like this because they are such a great source of knowledge.

I could make available a list of books I've used over the years, if it would be of any help.

I would be interested in seeing any list of books you, Stewart or anyone else would like to recommend. Though this isn't my thread, I too am always looking to learn more and there is always something new I didn't know before. :-)