VB Forum / Database Access / August 2003

You've got the right idea Jason.

This type approach works a treat for us in an application suite that
supports multiple RDBMS platforms.

We have a .ini file that provides us with the connection string, a
'platform' indicator and the path to the xml file.

The sql strings for all platforms are stored in one xml file that is
implemented as an ADODB.Recordset streamed to xml.

The application creates an ADODB.Recordset, opens it from the xml file and
filters it based on the platform indicator.

Each record in the recordset has the platform identifier, a record
identifier and the sql string.

A Public function 'GetSQL' is used to return the required sql string which
then can be executed, assigned to the connamdtext property of an
ADODB.Command object or whatever.

The required identifier is passed to the function along with a ParamArray
containing replaceable parameters thus:

   sql = GetSQL(12345,"fred",3)

Once the function has located the sql string it works through the ParamArray
replacing tokens with the supplied values. We represent the tokens as '%R%'
thus:

   select aaa,bbb,ccc from zzz where ddd='%R%' and eee=%R%

The sql string returned will be:

   select aaa,bbb,ccc from zzz where ddd='fred' and eee=3

The xml file can be freely edited as long as one remembers about special xml
characters such as < and > etc.

Once you have converted to using stored procedures, the same mechanism can
still be used except that the sql strings will define stored procedure calls
instead.

One thing I find usefule is to include the sql string in the source as a
comment line associated with the GetSQL call so it is easy to see what one
is doing.

You will find that, using this approach, the size of the executable will be
significantly reduced becaue the literal sql strings are, of course, not
compiled into the executable.

Clear as mud?