Query vs VBA

[Knildon]

Hello to all,
I have a program that downloads info from the internet, imports it into Access, massages the info and then builds tables that are used in a different program.
This program has one(1) form and about a half dozen queries.
I recently moved the SQL for the queries to the Visual Basic page behind the form and deleted the queries.
My question:
Are there any advantages/disadvantages to what I did? If so, please let me know what they are.
My thought was that the whole program is made up of one form and the VB behind it. In the future I can easily incorporate it into my other program.
Thanks in advance for any and all thoughts.

Don

 

[Pat Hartman]

Using saved querydefs is more efficient. When a querydef is created and saved, Access creates an execution plan and saves it with the querydef. It then uses that plan whenever the query is executed. SQL strings in VBA need to be "compiled" every time they run. It doesn't take a lot of time but it takes some so it really depends on how many queries get run this way. Aside from that it is a personal decision and people on either side feel strongly about their position. I prefer querydefs. I find them easier to change and debug. They are also reusable.

 

[Galaxiom]

While it is more efficient because of the saved execution plan the difference in actual user experience is negligible for an Access query being executed by JET/ACE on the local machine.

Moreover, refreshing the execution plan can be a benefit as the number of records increases so the saved query is not automatically superior.

However the execution plan at the database server level can be very important. The Stored Procedure versus raw SQL on a database server really can matter because the server can be heavily loaded with large numbers of connections. A few milliseconds here and there in a server over hundreds of connections does add up.

One great advantage of VBA is the ability to protect it from tampering by password protecting the code.

None-the-less when faced with two alternatives it rarely makes sense to choose the slower option.

Alternatively many developers prefer the the VBA because it doesn't "clutter" the part that most users don't see anyway.

However the VBA approach is a step on the way to other languages.

It is good to be fluent in both ways but certainly don't loose sleep on which way you should use on a particular project.

 

[Pat Hartman]

When queries use ODBC tables, Jet/ACE can't create an execution plan because it isn't actually going to run the query. It just "passes through" the SQL string it prepared and saved to the server. The server has to create an execution plan on the fly. That is why stored procedures are slightly faster. They are the equivalant of saved querydefs. Their execution plans have been calculated and saved.

Unless you execute SQL strings hundreds of times, you are unlikely to actually see much of a measurable speed difference. The big problem is that this binding process causes severe database bloat in earlier Access versions. A2010 seems to have solved this problem so it doesn't appear to be a problem any longer.

 

[Knildon]

Thanks to both of you for your thoughts and a bit of education for me.
The program I described will only be run about once every 2 weeks so speed is not a requirement. The program that that one will be incorporated into will run possibly on a daily basis and has about 8 queries that I am not going to convert. These are stand alone programs with only one user on his/her computer.
I don't know anything about the behind the scenes operation of Access and it's probably best that way or I would probably really mess things up. A little knowledge can be dangerous!!!!
Thanks again for your responce.
Don
You actually can teach an old dog new tricks!!!!!