Recommendations for learning VB/VBA

[turkishgold]

Hi, I've had to do some basic Excel macros to massage data before (and struggled plenty with them), but I want and need to become more comfortable with writing processing modules/macros...particularly in Access. I hope that this can be a springboard into other areas (e.g., .NET, Python) in terms of OOP etc. I've had exposure to all the basic concepts in web mapping courses, and with certain code I can get an idea of what's going on, but when I want to sit down and begin scripting I feel like I'm trying to compose a letter in French. I've been on MSDN...but I'm trying to figure out hwo to go about approaching becoming relatively proficient with VBA/VB.NET, etc. Where does one start? Recommendations? Recommendations for tutorials/books/training materials? General sage advice? I posted this here b/c I'm currently struggling with some basic Access modules I'm trying to write, and basically don't know how to move forward with them. I much appreciate any advice/words of wisdom thanks Tom

[OBP]

Tom, I can't comment on the other applications but for Access I have used

"Beginning Access 2000 VBA"
by Robert Smith & David Sussman
(ISBN 0-7645-4383-0)

You should also look at
Office 2003 Editions: Access VBA Language Reference


here
http://www.microsoft.com/downloads/details.aspx?FamilyID=0447c5a0-5e58-4e69-b90e-c42ec7dbf887&displaylang=en

Or other versions.

[CreganTur]

One of the best books that I've found for a beginner is Programming By Example with VBA, XML, and ASP (http://www.amazon.com/Access-2003-Programming-Example-VBA/dp/1556222238/ref=sr_1_2?ie=UTF8&s=books&qid=1246046990&sr=8-2)

It will take you through the basics, and then go into some very important advanced techniques.

HTH:thumb

[Movian]

i personally learned through necessity. I needed to make something to do something specific. I found examples and figured my way through it. Then used that basic knowledge the next time i had to do something and built on it. Rinse wash repeat....

Now im writing and developing full blown database application systems (With some major help from the people here). So my personall recomendation would be to set yourself a project and to figure it out with online resourse. But these guys know far more than me so i would listen to them first :)

[OBP]

Movian, I agree about having something to work on as a first step, especially if the subject is something that you are interested in is very useful.
I also collect useful code in a Wordpad document with a brief description of what it does as a Central source to find that "elusive" bit of code that you know you have "used/seen/heard of" but can't remember where :).

[turkishgold]

i personally learned through necessity. I needed to make something to do something specific. I found examples and figured my way through it. Then used that basic knowledge the next time i had to do something and built on it. Rinse wash repeat....

Now im writing and developing full blown database application systems (With some major help from the people here). So my personall recomendation would be to set yourself a project and to figure it out with online resourse. But these guys know far more than me so i would listen to them first :)

Movian, I agree about having something to work on as a first step, especially if the subject is something that you are interested in is very useful.
I also collect useful code in a Wordpad document with a brief description of what it does as a Central source to find that "elusive" bit of code that you know you have "used/seen/heard of" but can't remember where :).

Yes, these are both approaches I do try to use now. I do have a basic task I want to accomplish, but it just gets frustrating sometimes as you know it's a relatively simple task, but you can't figure out the exact syntax etc.

One of the things I'm dealing with now is having an Access db full of a whole bunch of tables. I would like to have a module to go through tables based on a certain part of their table name (all the tables with "Annual" as part of the name) and then open them up, and essentially return a distinct SELECT of two columns, then INSERT those data into another results table.

It seems like some sort of Array or loop would make sense...but I'm struggling with how to set that up in the context of Access and also how to get the tables to open based on my criteria (Annual being part of the table name)...then having what I guess I would need a SQL statement that doesn't reference a specific table name but something like an "active table"

thanks for your input
Tom

[OBP]

How many tables?
If there aren't alot of them it would be easier to just create Queries to do what you want.
However if you have a very large number of tables with Annual in the name you can use the Tables Group to lop through them like this

For Each ce In dbs.TableDefs
If ce.Name = "tbl_Temp" Then
For Each f In ce.Fields
count2 = count2 + 1
f.Name = "Field" & count2
If count2 > 6 Then Exit For
Next f
End If
Next ce

[turkishgold]

Well, I actually just dug my hands in yesterday and somehow cobbled together what I wanted to do. Previously when I had worked with macros in VBA in Excel...I was unaware of MSDN references, so was pretty much going blind. But, have been going through some basic tutorials and looking at their examples...and was able to script this in a couple hours which worked just as I had wanted (there were 95 tables, and SELECT UNION statements don't work well with a whole lot of tables...and thought this was a great simple task to learn with):


Option Compare Database
Option Explicit
Public Function GetSiteIDSiteName()
Dim obj As AccessObject
Dim dbs As Object
Set dbs = Application.CurrentData
'turn off warnings so a user isn't prompted over and over
DoCmd.SetWarnings False
'loop through each table in the current application and process it to get the
'SiteIDs and SiteNames, and then INSERT those into TestTable
For Each obj In dbs.AllTables
'determines if the table meets the criteria of having "Annual" in the name based on the AllTables.Name property
If obj.Name Like "*Annual*" Then

DoCmd.RunSQL "INSERT INTO Test_Table SELECT DISTINCT " & obj.Name & ".SiteID, " _
& obj.Name & ".SiteName" & " FROM " & obj.Name & " WHERE " & obj.Name & ".SiteID IS NOT NULL;"

End If
Next obj
'turns the warning back on
DoCmd.SetWarnings True
End Function

Felt REALLY good to finish it and actually have it work with only one post-scripting tweak.

thanks for the input

Tom

[OBP]

That was easy wasn't it. :thumb