[AccessD] Handling strings in VBA SQL scripts?

Hi All

I am searching for a function that determines the need for barcketing and
need for apostrophing/stringing of variable names. For example a tablename
stored in variable strTableName with value "table1" would not need
bracketing, while value "table 1" definitly needs bracketing, e.g. as part
of an SQL string "FROM [" & strTableName & "]".  

To make it a bit more difficult the value could also be "John's Table". This
would cause problems when we need to string the variable in an SQL string,
e.g. "SELECT '" & strTableName & "' AS TableName, " would result in "SELECT
'Peter's table' AS FromTable," which obviously is not correct, it should be
"SELECT 'Peter''s table' AS FromTable,".  It would also cause problems when
we decide to code using something like rst(strTableName).

Up until now I either avoided the problem by forcing users to use
unproblematic names, or developed one of solutions dependent on the data
(hoping that they would not change their naming conventions.

Thanks to Skrol29 (http://www.skrol29.com/) and Juan M. Afán deRibera
(http://www.mvp-access.com/juanmafan/) I am aware of WizHook.IsValidIdent
and WizHook.BracketString for testing valid variable names.  But Wizhook is
an undocumented feature.  I am also aware of tools which "string" a string
for you, e.g. CodeCrafter or the MS VBA String Editor that can do this with
a static string during design time.
But I was wondering if someone has found the time to find a more generic
solution.

The following gives my proto-analysis:

Function OutputString (strInputString as String,
                      Optional intFlag as Integer = 0,
                      Optional strStringChar As String = '"') AS string
' ============================
' Arguments:
' strInputString : the string to be converted
' intFlag        : the type of conversion
'                   0 = standard string conversion converting apostrophes
'                   1 = just bracketing for filename
'                   and others that could be useful
' strStringChar  : the character to be used as start and close of the string

'                  standard "
'============================
' PRELIMINARY ANALYSIS
' If intFlag = 1 Then
'   check if bracketing is needed
'   check for a the appearance of a list of characters in the string (which
characters?)
'   if one appears, at least bracket
' Else
'   if apostrophe appears double it
' Endif
End Function

Further note: Probably the function should return a Boolean indicating
success or failure of the action while the result is transfered to the
caller by other means. How?

Many thanks in advance

Harry Coenen