Solved Advice on resources to learn VBA and /or SQL (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Feb 19, 2002
Messages
42,981
The most useful book for me was the Access Cookbook but Getz, Litwin, and Baron. It is a learn by example tool rather than a learn to program tool. No need to read it from beginning to end. Find a problem that interests you in the TOC and learn how to solve it. The book is quite old but don't let that bother you. VBA is still the same as it was when the book was written. What has changed is that new functions have been created and Access itself, has been updated to solve some of the problems the Access Cookbook told you how to solve for yourself.

Another similar book is Access Annoyances by Mitchell & Callahan.

I've never seen a book that starts from ground 0 so you need to poke around with Access and examine things so you are somewhat familiar with objects before any of the books will make any sense to you.

Find the documentation for VBA functions. I use duckduckgo as my search engine rather than google. There are two lists. One alphabetic, which is pretty much useless since you are unlikely to know the name of the function you need and one by category. The list by category is much more useful since if you know you need a "date" function, you will find them all together which makes it easy to determine which one is appropriate.
 

Rene vK

Member
Local time
Today, 14:24
Joined
Mar 3, 2013
Messages
123
Thank you. I noticed that the link refers to VBA and Excel. Am I correct in believing that VBA for Access and VBA for Excel are very similar?
Yes, vba is used in both, syntax is the same. the difference is the use in Access or Excel.
 

Isaac

Lifelong Learner
Local time
Today, 06:24
Joined
Mar 14, 2017
Messages
8,738
Thank you. I noticed that the link refers to VBA and Excel. Am I correct in believing that VBA for Access and VBA for Excel are very similar?
Once you become confident in VBA (any object model - Excel, Access, Powerpoint, Outlook), you then know a lot about how things work. As Rene said the difference is just in usage. I personally started in Excel VBA and then went to Access after some time, but can't really claim any benefit of that method.

Well, I guess there might be one thing. If you learn Excel VBA well, you presumably learn principles of "better" vs. "worse" coding, because in Excel there's the macro recorder, which does a lot of Select, Selection, Activate, Active - which is awful code that should never be used. You learn the alternative, which is to diligently declare and set ALL appropriate variables, and then Act on them, instead of depending on what is Selected or Activated. This "better" principle then well prepares you for realistic coding in Access, where nobody [almost] ever writes code to select things or make them active.

It really just depends on where you see yourself going with it. Access without VBA is like a car with square wheels, so if you're going to be Access developing you might as well work it.

You also start to learn that any host program can control any other Office program using its VBA. I'd just focus on wherever you enjoy most and have the most use cases for it
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:24
Joined
Jul 9, 2003
Messages
16,245
And my blog:-

SQL Statements for Beginners

 

Isaac

Lifelong Learner
Local time
Today, 06:24
Joined
Mar 14, 2017
Messages
8,738
You could also delve into VBA in this (I think) fun/creative way and one guaranteed to lead you to the pursuit of "how-to" on the most random and varied of topics:

Create home-use VBA scripts designed to help automate something, or manage some data, in your home/family
A few things I've tried or done:
  • Making an app to track grocery / shopping / needs / wants list for the family by user, approval process, necessity level, etc
  • Making an app to help take, describe, store, retrieve & display Screenshots (during a family member's online class with a lot of screenshots showing anatomy/physiology pictures that were hard to remember)
  • Making a VBScript--very similar to VBA--to be activated upon a certain screen idle time, meant to save all the user's open documents, take screenshots, display a visual & verbal warning, and then safely log off
  • Budgets/ledgers/financial planning
  • A homegrown system of Note-taking/search/retrieval by subject, date, class, topic for a family member in an online class
But I have seen people do many other things - tracking all kinds of information related to something they or a family member is involved in for work, school, class, volunteer or hobby.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:24
Joined
Sep 12, 2006
Messages
15,614
I can say that I have 4 reference books that I used far in excess of any others.
These go back 20 years or so - the principles are still relevant, but Access might look a bit different now.

So
General Access Developemt.
Access 2000 Development - Alison Balter - Just the best general "What is Access all about" book I found.

VBA
VBA Developer's Handbook - Getz and Gilbert - A great reference.

Access Specifics
Access Cookbook. O'Reilly. - Getz, Litwin and Baron - A load of real world problems solved. Solved so well, that many of the problems discussed have now been built into later versions. (such as anchoring controls against form edges)

DAO
DAO Object Model - O'Reilly. Helen Feddema. Just priceless. A fantastic reference.
 
Last edited:

Umpire

Member
Local time
Today, 06:24
Joined
Mar 24, 2020
Messages
120
Thank you to all that responded.
I was able to watch the videos from home. The work "Net Nanny" blocks them. Working with IT to fix that.
I was able to order a couple of the books. Others are out of print. One book had 1 copy on Amazon for $395. The Kindle version was $20.

Most of the books are 5+ years old. Ancient by computer standards. Still lots of good info though.
Maybe some of the more experienced people on here can get together and write an updated book?
I think I have a good starting point. Heck, just by looking at a preview of one book (Access Cookbook) I was able to solve one thing I wanted to do. That one got ordered.
 

Isaac

Lifelong Learner
Local time
Today, 06:24
Joined
Mar 14, 2017
Messages
8,738
Glad you were able to find some! 99% of Access & VBA hasn't changed over the years, the 'look' has.
 

jdraw

Super Moderator
Staff member
Local time
Today, 09:24
Joined
Jan 23, 2006
Messages
15,364
Agree that things have not changed radically. I found used Access books on Amazon very good condition -quite inexpensive Access 2010 Programmer Reference list $44.99 US , amazon $13.90US. Still a good reference.
Glad you have found material.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 13:24
Joined
Sep 12, 2006
Messages
15,614
$395 is expensive for an occasional use reference book.

I think A97 was a very mature product. Some things that were added after are useful, and it might be a good idea for AWF to add a sticky to record those features. As @Isaac noted, the main body of Access is pretty well unchanged since A97. I doubt DAO has changed, although it may have.

However some add-ons were non-standard and available only in Access - such as multi-valued-fields. I think the description below is correct, as I don't use them - Normally you would have a second table to manage and make available a choice of options and you would then have to write your own code to show a combo box/list box to select from the second table. Access "solved" this by wrapping this whole functionality inside a "multi-value-field". This is all well and good until you need to upsize a database from Access to , say SQL Server, and then find the MVF can't be managed within SQL server.

TempVars is very useful, although I didn't get into the habit of using these either, Normally to use a variable in a query, you need to use a function to read the variable. You need to declare the variable, set it to a value in code, and then write the function to read the value. Tempvars allows you to define a variable and use that variable directly in a query without needing a function. A further added benefit is that the tempvars don't get reset as a result of an unhandled error.
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 02:24
Joined
Apr 1, 2019
Messages
713
Umpire, i was (am) in a similar situation to you in terms of improving your knowledge. From experience, i've found that this forum and it's members have been only too helpful no matter what i've asked. Members of the forum have stuck by me on multiple occasions where 'i've just not got it' & even simple stuff where i've gotten the syntax of a command wrong.

To me it seems that no matter how simple (stupid) i've been i always get directed correctly. I too am self taught (teaching) & have collected a bunch of tools and experience by trying, reading, googling, posting etc. As said by other members, have a go at a project. You'll likely get stuck at some point, thats cool how else do you learn. Your project should have a vision or scope, don't compromise otherwise you're selling yourself short. I've found that i needed to be forced 'out of my comfort zone' regularly to force me to seek a solution.

I started a thread about 12 months ago "tips for newbies" in which i highlighted some of my experiences & forum members contributed. I would have liked that thread to stay alive but never mind. I don't know how to reference it, but you should be able to search for it. It may help
 

HillTJ

To train a dog, first know more than the dog..
Local time
Today, 02:24
Joined
Apr 1, 2019
Messages
713
Colin, Thanks for adding the link.
 

Umpire

Member
Local time
Today, 06:24
Joined
Mar 24, 2020
Messages
120
Umpire, i was (am) in a similar situation to you in terms of improving your knowledge. From experience, i've found that this forum and it's members have been only too helpful no matter what i've asked. Members of the forum have stuck by me on multiple occasions where 'i've just not got it' & even simple stuff where i've gotten the syntax of a command wrong.

To me it seems that no matter how simple (stupid) i've been i always get directed correctly. I too am self taught (teaching) & have collected a bunch of tools and experience by trying, reading, googling, posting etc. As said by other members, have a go at a project. You'll likely get stuck at some point, thats cool how else do you learn. Your project should have a vision or scope, don't compromise otherwise you're selling yourself short. I've found that i needed to be forced 'out of my comfort zone' regularly to force me to seek a solution.

I started a thread about 12 months ago "tips for newbies" in which i highlighted some of my experiences & forum members contributed. I would have liked that thread to stay alive but never mind. I don't know how to reference it, but you should be able to search for it. It may help
I totally get the self taught advise. And using this site. My problem is that I was not understanding what I was looking at while searching for answers. I think with just a little bit of book background, I will be able to A. understand the answers better, and B. Ask better questions. I have been in the situation where I do not even know how to ask some questions.

I will give your Thread a look. I am sure there is a lot of good information there
 

Users who are viewing this thread

Top Bottom