Still in the search of ways to improve our databases reliability and
performance, I was wondering why there is kind of two languages appearing
in
an MsAccess file.
In fact, there is a 3rd way to work in ms-access, and that is the use of
macros.
So, you got a macro tab, a query (sql) tab, and then you got the module tab
(VBA code).
Does it means that programmers have the choice to use one or the other
language?
Yes, in SOME cases you can use one or the other. However, often, it is not a
either/or decision.
The problem is that the sql in ms-access is not a procedural language. By
procedural, we mean can you sit down, and have the language execute a SERIES
of commands. The sql in ms-access cannot do this.
So, if you needed to execute 5, or 15 sql statements one after another, you
would need to use the programming part, and that code could execute those 15
sql statements (you can use VB, or macros to do this).
When you build a query, you get ONE statement, and that is ALL you get.
Without question, that one sql statement can be VERY powerful, and do quite
amazing things. However, we are still talking about ONE statement here. In
fact, those sql statements are so powerful, that often, one statement can
replace MANY lines of code.
Lets take a very simple example:
update tblCustomers set City = 'New York' where city = 'N.Y'"
The above is ONE sql statement. It goes through the table called
tblCustomers, and looks for data in a field called city that is = to "N. Y."
When it finds that value, it replaces it with the text 'New York'
So sure, that one sql statement can be defined as a procedure that looks for
a city, and changes it. However, at the end of the day, it is only ONE
statement.
We could also write the above in a code module. The code would be as follows
dim rst as dao.recordset
set rst = currentdb.OpenReocrdSet("tblCustomers")
do while rst.EOF = false
if rst!City = "N.Y." then
rst.Edit
rst!City = "New York"
rst.UpDate
end if
rst.movenext
loop
rst.close
set rst = nothing
So, both of the examples do the same thing. However, using code is often
better, since then you can have the program code make DECISIONS. You can't
really have sql make decisions in the same sense of vba code. For
example,what happens if we wanted to ask the user if they want to do the
above, but we wanted to make sure the person REALLY REALLY wants to do this?
Well, in code we can do things based on the users input.
if msgbox("Do you really want to update N.Y to New York?",vbyesno) = vbyes
then
if msgbox("are you sure?",vbYesNo) = vbyes then
currentdb.Execute "update tblCustomers set City = 'New York' where
city = 'N.Y."
end if
end if
In the above, note how we first prompt the user, and then actually execute
some sql. Note also how our code can execute sql. So, using code, or sql is
not a either/or concept, but a beautiful blend of both technologies.
Further, the real beauty of code is that you can perform tasks,and those
tasks you perform are based on decisions that the program code and logic can
operate on.
If a application was just a form to enter data, then we would never need
software developers. However, I have a tour reservation system. When you
book 4 people to a hotel room, it also checks if there is room on the bus.
And, if there is more then one bus on the tour, if the first bus is full,
then it puts the people on the first bus that has 4 spaces free. All of
sudden now the computer is doing data processing, and making decisions on
the data at hand. It is this processing and decision making ability that
makes computers so exciting and so useful.
You don't have that decision making ability from sql alone. And, you don't
get decision making ability form a form that enters data. Automaton, and
automating of tasks that can make decisions is why people find computers so
incredible. You can build a screen that lets you enter payroll hours.
However, that program code part that does the payroll calculations is what
really saves the human labor here. So, program code = human labor savings!
If yes, which one would be the most efficient? If we planify to
eventually migrate our DB on SQL, I guess it would be a better choice to
use
this language?
The above is interesting question. One of the interesting things about sql
server is that it DOES HAVE a procedural language. That language is called
t-sql. It is not very good language, but it IS a procedural language. Sql
server cannot make reports. Sql server cannot build you forms. So, when you
build your application, you still have to build the user interface part. You
can use VB, c++, or ms-access to build this interface part. However, some of
the program logic can now be placed on the server side, and NOT have to be
written in c++, or VB, or our cases VBA in ms-access. This what we call a
stored procedure in sql server. So, often, when you convert a application
from JET (the data engine for ms-access) to sql server, you will continue to
use ms-access as the user interface, but you CAN in fact move some of the
code and program logic to the server now. Often, this is good idea, since
then all your VB programs, c++ programs, ms-access programs, and even your
web sever can all use the SAME stored procedures in the sql server side
(this would be an example were all programs were using the SAME sql server
data).
Of course, the problem is that t-sql does not let you build a interface, and
frankly, as a programming linage, it is REALLY REALLY horrible!! I have been
LONG complaining how bad of language t-sql is. However, finally, the future
has arrived, and the new version of sql server now lets you throw up vb.net
code, and it will run it. I don't have the time to tell you how nice this
feature is..this annoucnt for the new verison of sql server is welcome news.
