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

[Umpire]

i am looking for resources on how to learn to write code for Access.
While looking on the web it appears that I can either get books on Access or on SQL.

Should I be learning VBA or SQL? I do not anticipate using it much beyond this current project.

I already have a functioning database but I have reached the limits of what the built in wizards etc. can do for me. To do more I need to write code. Which I am new to.

What resource would you recommend I use to move forward? I am looking for something more specific than YouTube.
Preferably something in book form so I can refer back to it as needed, but a web based series not out of the equation.

I tried some of the video tutorials listed on this site but they all come up as broken links.

Thank you

 

[Isaac]

I would recommend learn the same way most people do - by taking a case-by-case needs basis, an actual use case, a project - and find out how to do each thing you need to get done. That's probably one of the most common ways of learning!

Don't worry, I personally wouldn't recommend learning technical things by video. Written words - and what they mean - is the foundational cornerstone of learning computer languages! Being successful inside that framework - the written word, the read word, following precise [i.e., written] instructions - is necessary communication skill to succeed and understand IMO.

 

[Minty]

I'm self-taught I suspect like 90% of the people on here, and like @Isaac my main learning has been through the necessity of achieving a specific task.

Programming with VBA is required to make Access a much more powerful tool, it adds a level of control and flexibility to your database that is impossible with macro's alone.

Learning SQL will help with more complex queries and understanding how your data is retrieved and manipulated.
SQL is a backbone of the data extraction used to drive your forms etc. The query designer does do a good job for most cases, however the more complex your data the more likely you are to probably need to create a query "off-piste" outside the scope of the query designer.

TLR - VBA first and pick up the SQL as you go.

 

[Umpire]

I would recommend learn the same way most people do - by taking a case-by-case needs basis, an actual use case, a project - and find out how to do each thing you need to get done. That's probably one of the most common ways of learning!

Don't worry, I personally wouldn't recommend learning technical things by video. Written words - and what they mean - is the foundational cornerstone of learning computer languages! Being successful inside that framework - the written word, the read word, following precise [i.e., written] instructions - is necessary communication skill to succeed and understand IMO.

And where or how do you recommend I learn each thing as i need to do it? Asking this site every time I want to do something is not only time consuming for me and those that answer, it also doesn't really teach what is being done or why. Usually questions are answered with code to do whatever was needed. And that's great. But in the long run all that teaches is how to con others into writing code for you.

To me, this site should be used for those rare situations where someone is truly stuck and just needs a nudge in the right direction.

 

[Umpire]

I'm self-taught I suspect like 90% of the people on here, and like @Isaac my main learning has been through the necessity of achieving a specific task.

Programming with VBA is required to make Access a much more powerful tool, it adds a level of control and flexibility to your database that is impossible with macro's alone.

Learning SQL will help with more complex queries and understanding how your data is retrieved and manipulated.
SQL is a backbone of the data extraction used to drive your forms etc. The query designer does do a good job for most cases, however the more complex your data the more likely you are to probably need to create a query "off-piste" outside the scope of the query designer.

TLR - VBA first and pick up the SQL as you go.

You input on VBA vs SQL is noted. Thanks.
Even being self taught, you had to start someplace. How did you go about learning VBA? Where did you start? Did you know other programing first?

 

[Isaac]

And where or how do you recommend I learn each thing as i need to do it? Asking this site every time I want to do something is not only time consuming for me and those that answer, it also doesn't really teach what is being done or why. Usually questions are answered with code to do whatever was needed. And that's great. But in the long run all that teaches is how to con others into writing code for you.

To me, this site should be used for those rare situations where someone is truly stuck and just needs a nudge in the right direction.

I am sorry to hear that you do not feel you have learned much from your AWF interactions.

It is true that some members are in the habit of offering to DO "everything" for a poster - i.e., Tell me your project and I'll design all the tables for you, tell me what you want done and I'll write all the code for you. However, it's not everyone. A lot of AWF posters will try to point you in specific directions, and keep pointing, at intervals, while you also try (work) on your end, which generally is a more sensible approach. Since 1) most of us don't have time to do everyone else's work for them, and 2) it's a lot more benefit to the original poster to learn by doing rather than just be handed a product.

I think if you continue here at AWF, you are bound to experience more learning, as a lot of answer-ers will include explanation or links to the Microsoft documentation that will give more color to the situation.

I admire your attitude, and wish every one learned it. I learned mostly by doing, and being helped. Of course, you also must study any code you're given to fully understand it - let that be a rule you give yourself that must be met prior to using code.

 

[Minty]

You input on VBA vs SQL is noted. Thanks.
Even being self taught, you had to start someplace. How did you go about learning VBA? Where did you start? Did you know other programing first?

I had done some programming, but donkey's years ago, machine code, Basic, a bit of Cobol.
I think if you have a logical mind it is easier. If you can break down a complex process into basic small steps.

I started by just trying to do some real basics.

Making a bespoke menu with some buttons on it to open forms.
Some cascading combo boxes.

Beginning to understand normalised data structures, then building some Master/Child Sub Forms
Adding some controls that would open a folder based on data in a field.
Then creating the folder if it didn't exist.

You can start by converting the macros to VBA.
The code that is produced isn't very verbose but does give you a start.

Learning about data types, and getting and using a naming convention will help you write readable maintainable code.

 

[Minty]

I'd agree with @Isaac I generally guide rather than solve. You'll learn a lot more and so will the poster.

If it makes you feel any better about using the net to learn, I spent over an hour trying to google how to set Powerpoint Data Labels from a separate data range in VBA from Access last night and failed. The Excel examples I found didn't work, and PowerPoint VBA information on the web is sketchy at best. I've done some relatively complex PPT automation and had to learn all of it by trial and error.

Nothing I could find or look up helped, and I like to think I'm a pretty good Google hound.
I ended up with a work-around.

 

[Umpire]

I am sorry to hear that you do not feel you have learned much from your AWF interactions.

It is true that some members are in the habit of offering to DO "everything" for a poster - i.e., Tell me your project and I'll design all the tables for you, tell me what you want done and I'll write all the code for you. However, it's not everyone. A lot of AWF posters will try to point you in specific directions, and keep pointing, at intervals, while you also try (work) on your end, which generally is a more sensible approach. Since 1) most of us don't have time to do everyone else's work for them, and 2) it's a lot more benefit to the original poster to learn by doing rather than just be handed a product.

I think if you continue here at AWF, you are bound to experience more learning, as a lot of answer-ers will include explanation or links to the Microsoft documentation that will give more color to the situation.

I admire your attitude, and wish every one learned it. I learned mostly by doing, and being helped. Of course, you also must study any code you're given to fully understand it - let that be a rule you give yourself that must be met prior to using code.

maybe you misunderstand my level of knowledge. I see posts where some say they want to do X. I think 'Great, I also want to do that." I go to the thread and when they either post the code they started with, or someone posts a solution, I have no clue what I am looking at. It might as well be in another language. Which I guess it really is.

I am looking for resources to teach me the language of VBA. Because right now it might as well be ancient Latin. I can recognize some individual words, but I have no clue as to the context or meaning.

 

[Isaac]

+5 for what Minty said about readable maintainable code! If I was asked to choose just ONE thing - just one! - that had helped me more than any single other thing over my time in the technical world, I think it might be the discipline of making easy to read code. It helps me in 3 ways:

1) Myself, when I'm 2 pages of code 'down' from the Declarations area, and wondering what r means vs. rngFound or cte vs. cteRegionSalesTotals
2) Myself, when it's 2 weeks later, and I can't remember what something means unless there are comments and very descriptive variable names
3) Someone else, because remember you are never the last leg of the employment chain. Someone will always come after you, or beside you.

I've learned not to employ this technique only with "super important things", but also things that seem trivial, because half the time trivial things grow and evolve.

I see many coders approaching variable names (and SQL table aliases, and CTE names, and subquery names, etc. etc. ) as if they were only an opportunity to shorten the text. X, Y, var, S, etc.
I take a very different approach. To me they aren't really a chance to save myself from typing. They are a chance to descriptively name the variable in a way that is virtually impossible to misunderstand, and makes understanding the code a lot easier. In fact, my variable names are quite frequently long! Once you're 2 pages down from the top "introduction" comments, it's quite valuable to see these descriptive names.

 

[Isaac]

maybe you misunderstand my level of knowledge. I see posts where some say they want to do X. I think 'Great, I also want to do that." I go to the thread and when they either post the code they started with, or someone posts a solution, I have no clue what I am looking at. It might as well be in another language. Which I guess it really is.

I am looking for resources to teach me the language of VBA. Because right now it might as well be ancient Latin. I can recognize some individual words, but I have no clue as to the context or meaning.

For what it's worth, there would be nothing wrong with asking someone to explain what each piece of code meant, if you so chose.

 

[NauticalGent]

I concur with both Isaac and Minty....

However, if you feel like jumping into the deep end of the pool, this book has taught me much:

Amazon.com: Microsoft Access 2010 VBA Programming Inside Out: 9780735659872: Couch, Andrew: Books

Amazon.com: Microsoft Access 2010 VBA Programming Inside Out: 9780735659872: Couch, Andrew: Books

www.amazon.com

As far as videos go, if you want to crawl before you walk:

Best of luck and remember you can always post a question here

 

[Umpire]

For what it's worth, there would be nothing wrong with asking someone to explain what each piece of code meant, if you so chose.

True but it is a very slow method.

 

[Rene vK]

Hi Umpire,

I bought a good ms Access programming book and started work from that.
online you can start learning vba from this link

 

[Minty]

@Umpire If you want a piece of code explained even at "I've totally no idea" level just ask.

As long as it's not war and peace length I (and I'm sure a load of others here) would be happy to oblige.
If you have any programming experience It might be good to know what, so we could maybe use some suitable comparisons of terms.

EDIT: This looks like a quite good basic introduction

https://www.accessallinone.com/download/vba_uploads/Access%20VBA%20Fundamentals%20-%20Level%201.pdf

 

[Umpire]

I concur with both Isaac and Minty....

However, if you feel like jumping into the deep end of the pool, this book has taught me much:

Amazon.com: Microsoft Access 2010 VBA Programming Inside Out: 9780735659872: Couch, Andrew: Books

Amazon.com: Microsoft Access 2010 VBA Programming Inside Out: 9780735659872: Couch, Andrew: Books

www.amazon.com

As far as videos go, if you want to crawl before you walk:

Best of luck and remember you can always post a question here

Thank you. I will look into that book.

As for the video I get this message when I try to play it: The webpage at https://www.youtube . com/embed/kogGwRIHH6o?wmode=opaque&start=0 might be temporarily down or it may have moved permanently to a new web address. (Added extra spaces to prevent the link from populating.)

I get a similar message for every video on this site. Is this a site problem or a me problem?

 

[Umpire]

Hi Umpire,

I bought a good ms Access programming book and started work from that.
online you can start learning vba from this link

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?

 

[Minty]

As for the video I get this message when I try to play it: The webpage at https://www.youtube . com/embed/kogGwRIHH6o?wmode=opaque&start=0 might be temporarily down or it may have moved permanently to a new web address. (Added extra spaces to prevent the link from populating.)

I get a similar message for every video on this site. Is this a site problem or a me problem?

It plays okay in my browser Chrome?
I don't often view embedded video's, to be honest.
This is the direct link

 

[NauticalGent]

Thank you. I will look into that book.

As for the video I get this message when I try to play it: The webpage at https://www.youtube . com/embed/kogGwRIHH6o?wmode=opaque&start=0 might be temporarily down or it may have moved permanently to a new web address. (Added extra spaces to prevent the link from populating.)

I get a similar message for every video on this site. Is this a site problem or a me problem?

Strange...it opens up perfectly for me.

 

[Umpire]

@Umpire If you want a piece of code explained even at "I've totally no idea" level just ask.

As long as it's not war and peace length I (and I'm sure a load of others here) would be happy to oblige.
If you have any programming experience It might be good to know what, so we could maybe use some suitable comparisons of terms.

EDIT: This looks like a quite good basic introduction

https://www.accessallinone.com/download/vba_uploads/Access%20VBA%20Fundamentals%20-%20Level%201.pdf

Thank you for the link. It is printing out as I type this.