VBA or SQL first?

[graveyard]

hi people, i am a frequent user of ACCESS but mainly work with the objects to build queries etc, hence pretty weak in advanced stuffs that require SQL and VBA.

I want to go deeper and acquire more knowledge in these areas and i know the best is to pick up both ..but can anyone advise me which , SQL or VBA, should i learn first in a way it will allow me to appreciate, understand and "transit" more effectively into the next language?

Thanks - personally i find SQL easier to understand at my level than VBA

 

[Galaxiom]

VBA and SQL do very different tasks. SQL is a database language. VBA is a scripting language.

You have no doubt seen some SQL in the SQL View of queries you have built in the designer. However this is only some of the SQL that can be used in queries and doesn't even hint at the SQL that creates, deletes and modifies the design of tables.

The advanced use of SQL is virtually impossible without using some form of scripting to generate dynamic queries. As you learn to use VBA you will encounter this advanced SQL.

VBA is an enormous field. You are not going to sit down and read a book and go "ah now I know all about VBA" at the end of a week. Not even after a year.

Learning VBA is a process of pieces of detatched knowledge being infused (while the learner remains somewhat confused) until the loose ends begin to meet. Then, just when you think you are becoming reasonably well versed, another whole unexpected part opens up. This process repeats again and again.

Don't be daunted. Get started on something simple but make it a real project because nothing motivates like a real need.

 

[graveyard]

VBA and SQL do very different tasks. SQL is a database language. VBA is a scripting language.

You have no doubt seen some SQL in the SQL View of queries you have built in the designer. However this is only some of the SQL that can be used in queries and doesn't even hint at the SQL that creates, deletes and modifies the design of tables.

The advanced use of SQL is virtually impossible without using some form of scripting to generate dynamic queries. As you learn to use VBA you will encounter this advanced SQL.

VBA is an enormous field. You are not going to sit down and read a book and go "ah now I know all about VBA" at the end of a week. Not even after a year.

Learning VBA is a process of pieces of detatched knowledge being infused (while the learner remains somewhat confused) until the loose ends begin to meet. Then, just when you think you are becoming reasonably well versed, another whole unexpected part opens up. This process repeats again and again.

Don't be daunted. Get started on something simple but make it a real project because nothing motivates like a real need.

dear GalaxiomAtHome, thank you for your valuable insight and well-said advice. Glad to know i will be addressing a real need in my work. I am in project management and there is a urgent need to automate reports and data generation to free up time otherwise spent on getting the data together to focus more on the crunching and analysis part.

I was just wondering how far i needed to go as i can accomplish quite a bit with access without knowing sql,vba and i am not in IT line and do not foresee myself moving towardss that direction, so i was just weighing the costs of getting myself trained in sql/vba vs the benefit that i will be getting in view that i will not in IT line (though i see myself dealing with numbers and data for the rest of my career life).

since i am building database, i guess i would need both sql and vba to address both the need to get the right customised data and to automate the system to get that right data.

for now, i guess i will start off with sql and as i move towards advanced sql, i will be ready to take on vba, as you have mentioned.

Many thanks again - appreciate your info!

 

[Galaxiom]

From what you have described I think I would probably go for getting started to a foundation level in VBA.

Knowing how to work with variables and control the flow of the processing so that you can write a simple procedure or function is incredibly useful. Remember custom functions in Excel are exactly the same language.

You really should avoid thinking of yourself as "not in the IT line". Information Technology is integral to data and data is part of working life.

 

[gemma-the-husky]

of the two, VBA is more important, I guess.

If you have played with Access, you have probably designed queries in the interactive design window. You are actually using SQL in this, and Access actually uses a SQL representation of the visual query - which you can see by one of the alternative design modes.

You can do a hell of a lot without having to use any SQL directly.


However, when you come to write applications, you will find that you very quickly reach a point where you need some programming to achieve stuff beyond a relatively trivial point, and for this you need VBA.

one reason is that we seek to manage much of the power of Access by actually restricting what the user can do, to give a more controlled interface. For this, you generally need VBA code.

 

[The_Doc_Man]

My esteemed colleagues have given you good advice. However, I would be remiss if I didn't offer a cautionary explanation, to give you the other side of the picture. Before I do so, I must clarify this: The earlier advice is not wrong. I just think that a more rounded view can help you here.

Deciding between SQL vs. VBA can sometimes be a difficult problem. Here are some pro and con issues.

SQL code is compiled. Stored queries are pre-analyzed. Dynamic queries still take advantage of the compiled nature of the SQL facility even though the analysis takes a bit longer. Therefore, if you CAN write what you want in SQL, it might be faster than if you wrote a loop in VBA to do the same thing. SQL, when it works and is meaningful, is more direct. SQL will probably be very easy to "up-convert" if you ever change to a split front-end + back-end situation where the back-end is not Access. SQL can be easily designed using the query grid and then switching from design view to SQL view.

VBA code is interpretive, but it is capable of doing something that SQL cannot do - remember context from one record to the next. VBA is slower than the equivalent SQL statement - when there is an equivalent SQL statement. VBA can do context-sensitive testing based on values in a given record. VBA can offer incredible flexibility. But a speed-demon, it is not.

To be honest, I just retro-fitted a VBA sequence with SQL because the back-end server was suffering terrible network congestion. VBA can make such situations very aggravating. I would not say that it wasn't doing the job - but if the network connection to a shared back-end gets slowed down, you have a really good chance of network drop-outs causing databases to become corrupted.

To my way of thinking, you gain a lot of insight when doing VBA & recordset operations. However, it comes with a price. Don't get me wrong. I will use VBA where I feel it is appropriate. However, you are trying to become a particular type of technician. It would be in your best interests to assure that BOTH tools are in your toolkit. Which one first? Whichever one is more comfortable and consistent with what you need to get done.

 

[graveyard]

My esteemed colleagues have given you good advice. However, I would be remiss if I didn't offer a cautionary explanation, to give you the other side of the picture. Before I do so, I must clarify this: The earlier advice is not wrong. I just think that a more rounded view can help you here.

Deciding between SQL vs. VBA can sometimes be a difficult problem. Here are some pro and con issues.

SQL code is compiled. Stored queries are pre-analyzed. Dynamic queries still take advantage of the compiled nature of the SQL facility even though the analysis takes a bit longer. Therefore, if you CAN write what you want in SQL, it might be faster than if you wrote a loop in VBA to do the same thing. SQL, when it works and is meaningful, is more direct. SQL will probably be very easy to "up-convert" if you ever change to a split front-end + back-end situation where the back-end is not Access. SQL can be easily designed using the query grid and then switching from design view to SQL view.

VBA code is interpretive, but it is capable of doing something that SQL cannot do - remember context from one record to the next. VBA is slower than the equivalent SQL statement - when there is an equivalent SQL statement. VBA can do context-sensitive testing based on values in a given record. VBA can offer incredible flexibility. But a speed-demon, it is not.

To be honest, I just retro-fitted a VBA sequence with SQL because the back-end server was suffering terrible network congestion. VBA can make such situations very aggravating. I would not say that it wasn't doing the job - but if the network connection to a shared back-end gets slowed down, you have a really good chance of network drop-outs causing databases to become corrupted.

To my way of thinking, you gain a lot of insight when doing VBA & recordset operations. However, it comes with a price. Don't get me wrong. I will use VBA where I feel it is appropriate. However, you are trying to become a particular type of technician. It would be in your best interests to assure that BOTH tools are in your toolkit. Which one first? Whichever one is more comfortable and consistent with what you need to get done.

hi GalaxiomAtHome - yes, you are right. But as time is limited to me (as it is for everyone else!), i want to get the most out of the stuff i learnt as obviously learning vba/sql gonna eat up quite a bit of time and its not really my core job scope to pick up these. i am more towards project management and collecting/analyzing data is just a part but time consuming of the entire project flow.

hi gemma-the-husky - ya learning vba is inevitable eventually as i really have to automate the reports so i guess i will try to pick up both ASAP, with focus on vba of course!

hi The_Doc_Man - thanks too for your awesome detaileed advice. yes, actually learning either one is equally important and time sensitive ..though these are not my core job (my project team does not include a data analyst or IT guy so i have to do it on my own )

but i kinda sort it out. first is to get all the requirements built into the access database so i gonna start with SQL. Automation can come after i can get what i want from the database - thats where i need to pick up vba. i just hv to convince my boss to do the reports the manual way or if theres an immediate need to automate, pass to IT helpdesk to write scripts ..

thanks people for the advice!!! you guys are great!