Iceberg Code

Thank you visiting ICB - Iceberg Code Blog
This is the place, where I'd like to share ideas and some useful code (VB, Excel, T-SQL, JavaScript, ...)
Table of Content

Maybe I'm a little bit too enthusiastic about this module but I use it so often that I consider it the most useful ...

The main idea is to store all the queries action in a table and run them from it.
With a table that contain all kind of queries, you just need to sort and flag them as runable.

Here the TR (track record) of the "SQL_List" table:
SQL_ID (counter)
SP_NAME (the name of the Stored Procedure)
Run_it (a flag to run or not them)
OrderSQL (the order sorting)
What_is_doing (description for portability)
SQL (The SQL of the action query)

What is an Action Query?
The one that does not just show records and does something like update, delete, append, create, ...

What is a SP (Stored Procedure)?
When you start using SQL-server you can really understand it. In short is a list of action, a program to get the result you want

Back to the SQL_List, here an example of how to query it:
SELECT SQL FROM SQL_List WHERE SP_NAME="Test" AND Run_it=1 ORDER BY OrderSQL;

To better use it you should use a Macro, that contain just one Action:
RunCode [ExecListOfQueries ( "SELECT SQL FROM SQL_List WHERE SP_NAME='Test' AND Run_it=1 ORDER BY OrderSQL")]

And here the core of the entire trick you need a module with the code:
ModuleName: UsefullAccessCode
The code is very simple:

Function ExecListOfQueries(MySQLList) ' stop warning DoCmd.SetWarnings False Dim dbs As DAO.Database, rst As DAO.Recordset, TempSQL Set dbs = CurrentDb ' The list of queries Set rst = dbs.OpenRecordset(MySQLList) ' if there are some records loop the result If rst.EOF = False And rst.BOF = False Then      rst.MoveFirst      Do While rst.EOF = False          ' loop to Execute all the queries          ' replace double quote with single one to avoid error          TempSQL = Replace(Replace(rst.Fields(0).Value, """", "'"), vbCrLf, " ")          'Debug.Print TempSQL          DoCmd.RunSQL TempSQL      rst.MoveNext      Loop End If rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing End Function

In this example Run_SQL_List.mdb you get all you need to test the code

The thing I most appreciate in this trick is the portability and flexibility.
... You can store all the queries in a single DB and use linked table to the "SQL_List" table, so it's easier to reuse queries from a project to another
... Or in ASP project you can change queries without changing the code.
... Most of all, the queries stay in a single place!

Download it Now!

Enjoy it!