Difference between Access VBA & SQL

[state90]

Stupid question but I guess that asking the stupid questions helps us get smarter!

What exactly is the difference between SQL and Access VBA? I guess the next logical question is why does MS Access make it possible to code with either?

If you were advising an Access user on which to use and learn in order to create more robust databases which would you recommend and why? I see experts give feedback and advice asking for the coding in either SQL or VBA and provide answers using one or the other. It seems to me that, using a query as an example, the SQL coding behind a query organizes what I created using the front-end menu driven tools (if that is the right way of describing basic Access usage) and VBA allows enhanced functionality. Are they mutually exclusive tools within Access or can either do what the other does?

 

[plog]

Are they mutually exclusive tools within Access or can either do what the other does?

Yes, they are mutually exclusive. Actually, you could use VBA to achieve what SQL does. However, you couldn't use SQL to achieve what VBA does.

SQL isn't a programming language, just look at what it stands for: Structured Query Language. Its purpose is solely writing queries, you can't change a forms color, you can't make it send email, its for queries and that's it.

If you were advising an Access user on which to use and learn in order to create more robust databases which would you recommend and why?

You are going to have to define 'robust'. My guess is you are talking pretty--you want catchy user interfaces and whiz bang features. That's VBA.

Of course, I prefer function over form, so I would recommend you learn SQL first.

 

[state90]

Again, not sure if I am using the right terminology when I reference the "front-end" menus driven view/functionality of Access versus the behind the scenes SQL but is the front end enough to get thinsg done or does SQL really help queries with their functionality and/or performance?

Thanks for your insight as well.

 

[pbaldy]

Regarding "does SQL really help queries with their functionality", a query is SQL. The design grid you may be familiar with is simply a user friendly interface. Behind the scenes, it creates SQL. You can take any query in design view and click on the View dropdown and select SQL View to see the SQL that was created.

To the original question, they are different tools. SQL (queries) is how we interact with data. Most common are queries forms and reports are based on. They let you draw data out of tables in an organized way, they join tables together, they allow you to apply criteria. There are also append, delete and update queries that manipulate data. Again, in all cases, this is SQL at work.

VBA is a programming language. It lets us provide more complex functionality, control the flow of logic, ect. You can integrate SQL in VBA, as in opening a recordset on an SQL string, but again they are different tools. Within your VBA process you are using SQL to define the set of data you want to work with.

 

[plog]

does SQL really help queries with their functionality and/or performance?

SQL is queries. There's no help about it.

 

[gemma-the-husky]

@state90

regarding SQL and Access queries

I am amazed that people can write sql directly. probably comes from years of experience.

I can do simple stuff directly, but anything tricky, I have to use the query design pane to see what the SQL should look like.


but when you do one these in vba

docmd.openquery "myquery"
currentdb.execute "myquery"
docmd.runsql querydefs("myquery").sql
docmd.runsql sqlstring
docmd.runsql "insert into .... "

you are doing a very similar thing: executing a SQL statement to interrogate/manage your database. The statements themselves have nuanced differences, but that isn't so important as the general priniciple.

vba (or visual studio, or c# etc etc) provides the programming container to manager the underlying SQL statements that interact with the actual data table.

 

[Galaxiom]

SQL isn't a programming language, just look at what it stands for: Structured Query Language.

We should qualify that statement.

Access SQL is not a programming language but TSQL as used in MS SQL Server is a programming language. It can do subroutines, user defined functions, conditional processing, loops and all kinds of stuff normally associated with other programming languages.

AFAIK it can't manipulate Windows objects like VBA does but it definitely still considered a programming language.

 

[Galaxiom]

I am amazed that people can write sql directly. probably comes from years of experience.

I can do simple stuff directly, but anything tricky, I have to use the query design pane to see what the SQL should look like.

That must be hell if you have to work in MS SQL Management Studio. The designer tool there is terrible while the SQL query editor is absolutely brilliant.

Access has a pretty reasonable visual designer with an absolutely hideous SQL View. It is the other way around in MSSQLMS. I guess this tells much about where the respective users do most work.

I think it is the way SQL view in Access scrambles the code that inhibits many Access users from ever learning to write SQL.

Access SQL View can be made to behave if you add a table called Dummy with one field and no records. Append this line to the bottom of your Select queries:

UNION ALL SELECT Null, Null, Null FROM Dummy

Put in as many Nulls as there are fields returned by the query. This addition turns makes no difference to the results but it into a Union query that the Design View can't deal with so it leaves your code and formatting alone. Of course it also become non-updateable but it does let you work on it without it being scrambled.

 

[marlan]

vba... provides the programming container to manager the underlying SQL statements that interact with the actual data table.

That is true when working in VBA module, but modules are objects, or tools, within Access. you can create an entire application using Macros, and not a single line of VBA code (it would not be a very rich application...), and to the contrary: you can create a user defined function, and run it as part of SQL query.
I would say VBA is a vary powerful tool Access provides, and can enrich any application, and manage any section in it.

But in refer to the initial question:

If you were advising an Access user on which to use and learn in order to create more robust databases which would you recommend and why?

Access is first of all a Date-Base, and DBs speak SQL, so if you are an Access user I would advise you to first learn SQL, learn how to create a basic data application, then learn how to enrich it using VBA.
But if you are a software developer, I would advise you first learn the basics of computer programming: Variables, Conditional Execution, Looping, Arrays, Sub Routines ets. That way you will see SQL is a very powerful tool for managing data (Just as Dave has pointed) in a software application.

ATB