wizards/source/access2base/Database.xba

   1 <?xml version="1.0" encoding="UTF-8"?>
   2 <!DOCTYPE script:module PUBLIC "-//OpenOffice.org//DTD OfficeDocument 1.0//EN" "module.dtd">
   3 <script:module xmlns:script="http://openoffice.org/2000/script" script:name="Database" script:language="StarBasic">REM =======================================================================================================================
   4 REM ===                                 The Access2Base library is a part of the LibreOffice project.                                                                   ===
   5 REM ===                                 Full documentation is available on http://www.access2base.com                                                                   ===
   6 REM =======================================================================================================================
   7
   8 Option Compatible
   9 Option ClassModule
  10
  11 Option Explicit
  12
  13 REM -----------------------------------------------------------------------------------------------------------------------
  14 REM --- CLASS ROOT FIELDS                                                                                                                                                                                       ---
  15 REM -----------------------------------------------------------------------------------------------------------------------
  16
  17 Private _Type                                   As String                               &apos;  Must be DATABASE
  18 Private _This                                   As Object                               &apos;  Workaround for absence of This builtin function
  19 Private _DbConnect                              As Integer                              &apos;  DBCONNECTxxx constants
  20 Private Title                                   As String
  21 Private Document                                As Object                               &apos;  com.sun.star.comp.dba.ODatabaseDocument or SwXTextDocument or ScModelObj
  22 Private Connection                              As Object                               &apos;  com.sun.star.sdbc.drivers.OConnectionWrapper or com.sun.star.sdbc.XConnection
  23 Private URL                                             As String
  24 Private _ReadOnly                               As Boolean
  25 Private MetaData                                As Object                               &apos;  interface XDatabaseMetaData
  26 Private Form                                    As Object                               &apos;  com.sun.star.form.XForm
  27 Private FormName                                As String
  28 Private RecordsetMax                    As Integer
  29 Private RecordsetsColl                  As Object                               &apos;  Collection of active recordsets
  30
  31 REM -----------------------------------------------------------------------------------------------------------------------
  32 REM --- CONSTRUCTORS / DESTRUCTORS                                                                                                                                                                      ---
  33 REM -----------------------------------------------------------------------------------------------------------------------
  34 Private Sub Class_Initialize()
  35         _Type = OBJDATABASE
  36         Set _This = Nothing
  37         _DbConnect = 0
  38         Title = &quot;&quot;
  39         Set Document = Nothing
  40         Set Connection = Nothing
  41         URL = &quot;&quot;
  42         _ReadOnly = False
  43         Set MetaData = Nothing
  44         Set Form = Nothing
  45         FormName = &quot;&quot;
  46         RecordsetMax = 0
  47         Set RecordsetsColl = New Collection
  48 End Sub         &apos;  Constructor
  49
  50 REM -----------------------------------------------------------------------------------------------------------------------
  51 Private Sub Class_Terminate()
  52         On Local Error Resume Next
  53         Call CloseAllRecordsets()
  54         If _DbConnect &lt;&gt; DBCONNECTANY Then
  55                 If Not IsNull(Connection) Then
  56                         Connection.close()
  57                         Connection.dispose()
  58                         Set Connection = Nothing
  59                 End If
  60         Else
  61                 mClose()
  62         End If
  63         Call Class_Initialize()
  64 End Sub         &apos;  Destructor
  65
  66 REM -----------------------------------------------------------------------------------------------------------------------
  67 Public Sub Dispose()
  68         Call Class_Terminate()
  69 End Sub         &apos;  Explicit destructor
  70
  71
  72
  73 REM -----------------------------------------------------------------------------------------------------------------------
  74 REM --- CLASS GET/LET/SET PROPERTIES                                                                                                                                                            ---
  75 REM -----------------------------------------------------------------------------------------------------------------------
  76
  77 Property Get ObjectType() As String
  78         ObjectType = _PropertyGet(&quot;ObjectType&quot;)
  79 End Property            &apos;  ObjectType (get)
  80
  81 REM -----------------------------------------------------------------------------------------------------------------------
  82 REM --- CLASS METHODS                                                                                                                                                                                           ---
  83 REM -----------------------------------------------------------------------------------------------------------------------
  84
  85 REM -----------------------------------------------------------------------------------------------------------------------
  86 Public Function mClose() As Variant
  87 &apos;  Close the database
  88
  89 If _ErrorHandler() Then On Local Error Goto Error_Function
  90 Const cstThisSub = &quot;Database.Close&quot;
  91         Utils._SetCalledSub(cstThisSub)
  92         mClose = False
  93         If _DbConnect &lt;&gt; DBCONNECTANY Then Goto Error_NotApplicable
  94
  95         Connection.close()
  96         Connection.dispose()
  97         Set Connection = Nothing
  98         mClose = True
  99
 100 Exit_Function:
 101         Utils._ResetCalledSub(cstThisSub)
 102         Exit Function
 103 Error_NotApplicable:
 104         TraceError(TRACEFATAL, ERRMETHOD, Utils._CalledSub(), 0, 1, cstThisSub)
 105         Goto Exit_Function
 106 Error_Function:
 107         TraceError(TRACEABORT, Err, Utils._CalledSub(), Erl)
 108         GoTo Exit_Function
 109 End Function            &apos;  (m)Close
 110
 111 REM -----------------------------------------------------------------------------------------------------------------------
 112 Public Sub CloseAllRecordsets()
 113 &apos;  Clean all recordsets for housekeeping
 114
 115 Dim sRecordsets() As String, i As Integer, oRecordset As Object
 116         On Local Error Goto Exit_Sub
 117
 118         If IsNull(RecordsetsColl) Then Exit Sub
 119         If RecordsetsColl.Count &lt; 1 Then Exit Sub
 120         For i = 1 To RecordsetsColl.Count
 121                 Set oRecordset = RecordsetsColl.Item(i)
 122                 oRecordset.mClose(False)                &apos;  Do not remove entry in collection
 123         Next i
 124         Set RecordsetsColl = New Collection
 125         RecordsetMax = 0
 126
 127 Exit_Sub:
 128         Exit Sub
 129 End Sub                         &apos;  CloseAllRecordsets      V0.9.5
 130
 131 REM -----------------------------------------------------------------------------------------------------------------------
 132 Public Function CreateQueryDef(ByVal Optional pvQueryName As Variant _
 133                                                                 , ByVal Optional pvSql As Variant _
 134                                                                 , ByVal Optional pvOption As Variant _
 135                                                                 ) As Object
 136 &apos;Return a (new) QueryDef object based on SQL statement
 137 Const cstThisSub = &quot;Database.CreateQueryDef&quot;
 138         Utils._SetCalledSub(cstThisSub)
 139
 140 Const cstNull = -1
 141 Dim oQuery As Object, oQueries As Object, i As Integer, sQueryName As String
 142
 143         If _ErrorHandler() Then On Local Error Goto Error_Function
 144
 145         Set CreateQueryDef = Nothing
 146         If _DbConnect &lt;&gt; DBCONNECTBASE Then Goto Error_NotApplicable
 147         If IsMissing(pvQueryName) Then Call _TraceArguments()
 148         If IsMissing(pvSql) Then Call _TraceArguments()
 149         If IsMissing(pvOption) Then pvOption = cstNull
 150
 151         If Not Utils._CheckArgument(pvQueryName, 1, vbString) Then Goto Exit_Function
 152         If pvQueryName = &quot;&quot; Then Call _TraceArguments()
 153         If Not Utils._CheckArgument(pvSql, 2, vbString) Then Goto Exit_Function
 154         If pvSql = &quot;&quot; Then Call _TraceArguments()
 155         If Not Utils._CheckArgument(pvOption, 3, Utils._AddNumeric(), Array(cstNull, dbSQLPassThrough)) Then Goto Exit_Function
 156
 157         If _ReadOnly Then Goto Error_NoUpdate
 158
 159         Set oQuery = CreateUnoService(&quot;com.sun.star.sdb.QueryDefinition&quot;)
 160         oQuery.rename(pvQueryName)
 161         oQuery.Command = _ReplaceSquareBrackets(pvSql)
 162         oQuery.EscapeProcessing = Not ( pvOption = dbSQLPassThrough )
 163
 164         Set oQueries = Document.DataSource.getQueryDefinitions()
 165         With oQueries
 166                 For i = 0 To .getCount() - 1
 167                         sQueryName = .getByIndex(i).Name
 168                         If UCase(sQueryName) = UCase(pvQueryName) Then
 169                                 TraceError(TRACEWARNING, ERRQUERYDEFDELETED, Utils._CalledSub(), 0, False, sQueryName)
 170                                 .removeByName(sQueryName)
 171                                 Exit For
 172                         End If
 173                 Next i
 174                 .insertByName(pvQueryName, oQuery)
 175         End With
 176         Set CreateQueryDef = QueryDefs(pvQueryName)
 177
 178 Exit_Function:
 179         Utils._ResetCalledSub(cstThisSub)
 180         Exit Function
 181 Error_NotApplicable:
 182         TraceError(TRACEFATAL, ERRMETHOD, Utils._CalledSub(), 0, 1, cstThisSub)
 183         Goto Exit_Function
 184 Error_NoUpdate:
 185         TraceError(TRACEFATAL, ERRNOTUPDATABLE, Utils._CalledSub(), 0)
 186         Goto Exit_Function
 187 Error_Function:
 188         TraceError(TRACEABORT, Err, cstThisSub, Erl)
 189         GoTo Exit_Function
 190 End Function    &apos;  CreateQueryDef  V1.1.0
 191
 192 REM -----------------------------------------------------------------------------------------------------------------------
 193 Public Function CreateTableDef(ByVal Optional pvTableName As Variant) As Object
 194 &apos;Return a (new/empty) TableDef object
 195 Const cstThisSub = &quot;Database.CreateTableDef&quot;
 196         Utils._SetCalledSub(cstThisSub)
 197
 198 Dim oTable As Object, oTables As Object, sTables() As String
 199 Dim i As Integer, sTableName As String, oNewTable As Object
 200
 201         If _ErrorHandler() Then On Local Error Goto Error_Function
 202
 203         Set CreateTableDef = Nothing
 204         If _DbConnect &lt;&gt; DBCONNECTBASE Then Goto Error_NotApplicable
 205         If IsMissing(pvTableName) Then Call _TraceArguments()
 206
 207         If Not Utils._CheckArgument(pvTableName, 1, vbString) Then Goto Exit_Function
 208         If pvTableName = &quot;&quot; Then Call _TraceArguments()
 209
 210         If _ReadOnly Then Goto Error_NoUpdate
 211
 212         Set oTables = Connection.getTables
 213         With oTables
 214                 sTables = .ElementNames()
 215                 &apos;  Check existence of object and find its exact (case-sensitive) name
 216                 For i = 0 To UBound(sTables)
 217                         If UCase(pvTableName) = UCase(sTables(i)) Then
 218                                 sTableName = sTables(i)
 219                                 TraceError(TRACEWARNING, ERRTABLEDEFDELETED, Utils._CalledSub(), 0, False, sTableName)
 220                                 .dropByName(sTableName)
 221                                 Exit For
 222                         End If
 223                 Next i
 224                 Set oNewTable = New DataDef
 225                 oNewTable._Type = OBJTABLEDEF
 226                 oNewTable._Name = pvTableName
 227                 Set oNewTable._ParentDatabase = _This
 228                 Set oNewTable.TableDescriptor = .createDataDescriptor()
 229                 oNewTable.TableDescriptor.Name = pvTableName
 230         End With
 231
 232         Set CreateTabledef = oNewTable
 233
 234 Exit_Function:
 235         Utils._ResetCalledSub(cstThisSub)
 236         Exit Function
 237 Error_NotApplicable:
 238         TraceError(TRACEFATAL, ERRMETHOD, Utils._CalledSub(), 0, 1, cstThisSub)
 239         Goto Exit_Function
 240 Error_NoUpdate:
 241         TraceError(TRACEFATAL, ERRNOTUPDATABLE, Utils._CalledSub(), 0)
 242         Goto Exit_Function
 243 Error_Function:
 244         TraceError(TRACEABORT, Err, cstThisSub, Erl)
 245         GoTo Exit_Function
 246 End Function    &apos;  CreateTableDef  V1.1.0
 247
 248 REM -----------------------------------------------------------------------------------------------------------------------
 249 Public Function DAvg( _
 250                                         ByVal Optional psExpr As String _
 251                                         , ByVal Optional psDomain As String _
 252                                         , ByVal Optional pvCriteria As Variant _
 253                                         ) As Variant
 254 &apos;  Return average of scope
 255 Const cstThisSub = &quot;Database.DAvg&quot;
 256         Utils._SetCalledSub(cstThisSub)
 257         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 258         DAvg = _DFunction(&quot;AVG&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 259         Utils._ResetCalledSub(cstThisSub)
 260 End Function    &apos;  DAvg
 261
 262 REM -----------------------------------------------------------------------------------------------------------------------
 263 Public Function DCount( _
 264                                         ByVal Optional psExpr As String _
 265                                         , ByVal Optional psDomain As String _
 266                                         , ByVal Optional pvCriteria As Variant _
 267                                         ) As Variant
 268 &apos;  Return # of occurrences of scope
 269 Const cstThisSub = &quot;Database.DCount&quot;
 270         Utils._SetCalledSub(cstThisSub)
 271         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 272         DCount = _DFunction(&quot;COUNT&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 273         Utils._ResetCalledSub(cstThisSub)
 274 End Function    &apos;  DCount
 275
 276 REM -----------------------------------------------------------------------------------------------------------------------
 277 Public Function DLookup( _
 278                                         ByVal Optional psExpr As String _
 279                                         , ByVal Optional psDomain As String _
 280                                         , ByVal Optional pvCriteria As Variant _
 281                                         , ByVal Optional pvOrderClause As Variant _
 282                                         ) As Variant
 283
 284 &apos;  Return a value within a table
 285     &apos;Arguments: psExpr:                    an SQL expression
 286     &apos;                      psDomain:               a table- or queryname
 287     &apos;                      pvCriteria:             an optional WHERE clause
 288     &apos;                      pcOrderClause:  an optional order clause incl. &quot;DESC&quot; if relevant
 289     &apos;Return:    Value of the psExpr if found, else Null.
 290     &apos;Author:    inspired from Allen Browne. http://allenbrowne.com/ser-42.html
 291     &apos;Examples:
 292     &apos;           1. To find the last value, include DESC in the OrderClause, e.g.:
 293     &apos;               DLookup(&quot;[Surname] &amp; [FirstName]&quot;, &quot;tblClient&quot;, , &quot;ClientID DESC&quot;)
 294     &apos;           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
 295     &apos;               DLookup(&quot;ClientID&quot;, &quot;tblClient&quot;, &quot;Surname Is Not Null&quot; , &quot;Surname&quot;)
 296
 297 Const cstThisSub = &quot;Database.DLookup&quot;
 298         Utils._SetCalledSub(cstThisSub)
 299         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 300         DLookup = _DFunction(&quot;&quot;, psExpr, psDomain _
 301                                         , Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria) _
 302                                         , Iif(IsMissing(pvOrderClause), &quot;&quot;, pvOrderClause) _
 303                                         )
 304         Utils._ResetCalledSub(cstThisSub)
 305 End Function    &apos;  DLookup
 306
 307 REM -----------------------------------------------------------------------------------------------------------------------
 308 Public Function DMax( _
 309                                         ByVal Optional psExpr As String _
 310                                         , ByVal Optional psDomain As String _
 311                                         , ByVal Optional pvCriteria As Variant _
 312                                         ) As Variant
 313 &apos;  Return maximum of scope
 314 Const cstThisSub = &quot;Database.DMax&quot;
 315         Utils._SetCalledSub(cstThisSub)
 316         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 317         DMax = _DFunction(&quot;MAX&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 318         Utils._ResetCalledSub(cstThisSub)
 319 End Function    &apos;  DMax
 320
 321 REM -----------------------------------------------------------------------------------------------------------------------
 322 Public Function DMin( _
 323                                         ByVal Optional psExpr As String _
 324                                         , ByVal Optional psDomain As String _
 325                                         , ByVal Optional pvCriteria As Variant _
 326                                         ) As Variant
 327 &apos;  Return minimum of scope
 328 Const cstThisSub = &quot;Database.DMin&quot;
 329         Utils._SetCalledSub(cstThisSub)
 330         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 331         DMin = _DFunction(&quot;MIN&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 332         Utils._ResetCalledSub(cstThisSub)
 333 End Function    &apos;  DMin
 334
 335 REM -----------------------------------------------------------------------------------------------------------------------
 336 Public Function DStDev( _
 337                                         ByVal Optional psExpr As String _
 338                                         , ByVal Optional psDomain As String _
 339                                         , ByVal Optional pvCriteria As Variant _
 340                                         ) As Variant
 341 &apos;  Return standard deviation of scope
 342 Const cstThisSub = &quot;Database.DStDev&quot;
 343         Utils._SetCalledSub(cstThisSub)
 344         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 345         DStDev = _DFunction(&quot;STDDEV_SAMP&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)      &apos;  STDDEV not STDEV !
 346         Utils._ResetCalledSub(cstThisSub)
 347 End Function    &apos;  DStDev
 348
 349 REM -----------------------------------------------------------------------------------------------------------------------
 350 Public Function DStDevP( _
 351                                         ByVal Optional psExpr As String _
 352                                         , ByVal Optional psDomain As String _
 353                                         , ByVal Optional pvCriteria As Variant _
 354                                         ) As Variant
 355 &apos;  Return standard deviation of scope
 356 Const cstThisSub = &quot;Database.DStDevP&quot;
 357         Utils._SetCalledSub(cstThisSub)
 358         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 359         DStDevP = _DFunction(&quot;STDDEV_POP&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)      &apos;  STDDEV not STDEV !
 360         Utils._ResetCalledSub(cstThisSub)
 361 End Function    &apos;  DStDevP
 362
 363 REM -----------------------------------------------------------------------------------------------------------------------
 364 Public Function DSum( _
 365                                         ByVal Optional psExpr As String _
 366                                         , ByVal Optional psDomain As String _
 367                                         , ByVal Optional pvCriteria As Variant _
 368                                         ) As Variant
 369 &apos;  Return sum of scope
 370 Const cstThisSub = &quot;Database.DSum&quot;
 371         Utils._SetCalledSub(cstThisSub)
 372         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 373         DSum = _DFunction(&quot;SUM&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 374         Utils._ResetCalledSub(cstThisSub)
 375 End Function    &apos;  DSum
 376
 377 REM -----------------------------------------------------------------------------------------------------------------------
 378 Public Function DVar( _
 379                                         ByVal Optional psExpr As String _
 380                                         , ByVal Optional psDomain As String _
 381                                         , ByVal Optional pvCriteria As Variant _
 382                                         ) As Variant
 383 &apos;  Return variance of scope
 384 Const cstThisSub = &quot;Database.DVar&quot;
 385         Utils._SetCalledSub(cstThisSub)
 386         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 387         DVar = _DFunction(&quot;VAR_SAMP&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 388         Utils._ResetCalledSub(cstThisSub)
 389 End Function    &apos;  DVar
 390
 391 REM -----------------------------------------------------------------------------------------------------------------------
 392 Public Function DVarP( _
 393                                         ByVal Optional psExpr As String _
 394                                         , ByVal Optional psDomain As String _
 395                                         , ByVal Optional pvCriteria As Variant _
 396                                         ) As Variant
 397 &apos;  Return variance of scope
 398 Const cstThisSub = &quot;Database.DVarP&quot;
 399         Utils._SetCalledSub(cstThisSub)
 400         If IsMissing(psExpr) Or IsMissing(psDomain) Then Call _TraceArguments()
 401         DVarP = _DFunction(&quot;VAR_POP&quot;, psExpr, psDomain, Iif(IsMissing(pvCriteria), &quot;&quot;, pvCriteria), &quot;&quot;)
 402         Utils._ResetCalledSub(cstThisSub)
 403 End Function    &apos;  DVarP
 404
 405 REM -----------------------------------------------------------------------------------------------------------------------
 406 Public Function getProperty(Optional ByVal pvProperty As Variant) As Variant
 407 &apos;  Return property value of psProperty property name
 408
 409         Utils._SetCalledSub(&quot;Database.getProperty&quot;)
 410         If IsMissing(pvProperty) Then Call _TraceArguments()
 411         getProperty = _PropertyGet(pvProperty)
 412         Utils._ResetCalledSub(&quot;Database.getProperty&quot;)
 413
 414 End Function            &apos;  getProperty
 415
 416 REM -----------------------------------------------------------------------------------------------------------------------
 417 Public Function hasProperty(ByVal Optional pvProperty As Variant) As Boolean
 418 &apos;  Return True if object has a valid property called pvProperty (case-insensitive comparison !)
 419
 420         If IsMissing(pvProperty) Then hasProperty = PropertiesGet._hasProperty(_Type, _PropertiesList()) Else hasProperty = PropertiesGet._hasProperty(_Type, _PropertiesList(), pvProperty)
 421         Exit Function
 422
 423 End Function    &apos;  hasProperty
 424
 425 REM -----------------------------------------------------------------------------------------------------------------------
 426 Public Function OpenRecordset(ByVal Optional pvSource As Variant _
 427                                                                 , ByVal Optional pvType As Variant _
 428                                                                 , ByVal Optional pvOptions As Variant _
 429                                                                 , ByVal Optional pvLockEdit As Variant _
 430                                                                 ) As Object
 431 &apos;Return a Recordset object based on Source (= SQL, table or query name)
 432
 433 Const cstThisSub = &quot;Database.OpenRecordset&quot;
 434         Utils._SetCalledSub(cstThisSub)
 435 Const cstNull = -1
 436
 437 Dim lCommandType As Long, sCommand As String, oObject As Object
 438 Dim sSource As String, i As Integer, iCount As Integer
 439 Dim sObjects() As String, bFound As Boolean, oTables As Object, oQueries As Object
 440
 441         If _ErrorHandler() Then On Local Error Goto Error_Function
 442         Set oObject = Nothing
 443         If IsMissing(pvSource) Then Call _TraceArguments()
 444         If pvSource = &quot;&quot; Then Call _TraceArguments()
 445         If IsMissing(pvType) Then
 446                 pvType = cstNull
 447         Else
 448                 If Not Utils._CheckArgument(pvType, 1, Utils._AddNumeric(), dbOpenForwardOnly) Then Goto Exit_Function
 449         End If
 450         If IsMissing(pvOptions) Then
 451                 pvOptions = cstNull
 452         Else
 453                 If Not Utils._CheckArgument(pvOptions, 2, Utils._AddNumeric(), dbSQLPassThrough) Then Goto Exit_Function
 454         End If
 455         If IsMissing(pvLockEdit) Then
 456                 pvLockEdit = cstNull
 457         Else
 458                 If Not Utils._CheckArgument(pvLockEdit, 3, Utils._AddNumeric(), dbReadOnly) Then Goto Exit_Function
 459         End If
 460
 461         sSource = Split(UCase(Trim(pvSource)), &quot; &quot;)(0)
 462         Select Case True
 463                 Case sSource = &quot;SELECT&quot;
 464                         lCommandType = com.sun.star.sdb.CommandType.COMMAND
 465                         sCommand = _ReplaceSquareBrackets(pvSource)
 466                 Case Else
 467                         sSource = UCase(Trim(pvSource))
 468                         REM Explore tables
 469                         Set oTables = Connection.getTables
 470                         sObjects = oTables.ElementNames()
 471                         bFound = False
 472                         For i = 0 To UBound(sObjects)
 473                                 If sSource = UCase(sObjects(i)) Then
 474                                         sCommand = sObjects(i)
 475                                         bFound = True
 476                                         Exit For
 477                                 End If
 478                         Next i
 479                         If bFound Then
 480                                 lCommandType = com.sun.star.sdb.CommandType.TABLE
 481                         Else
 482                                 REM Explore queries
 483                                 Set oQueries = Connection.getQueries
 484                                 sObjects = oQueries.ElementNames()
 485                                 For i = 0 To UBound(sObjects)
 486                                         If sSource = UCase(sObjects(i)) Then
 487                                                 sCommand = sObjects(i)
 488                                                 bFound = True
 489                                                 Exit For
 490                                         End If
 491                                 Next i
 492                                 If Not bFound Then Goto Trace_NotFound
 493                                 lCommandType = com.sun.star.sdb.CommandType.QUERY
 494                         End If
 495         End Select
 496
 497         Set oObject = New Recordset
 498         With oObject
 499                 ._CommandType = lCommandType
 500                 ._Command = sCommand
 501                 ._ParentName = Title
 502                 ._ParentType = _Type
 503                 ._ForwardOnly = ( pvType = dbOpenForwardOnly )
 504                 ._PassThrough = ( pvOptions = dbSQLPassThrough )
 505                 ._ReadOnly = ( (pvLockEdit = dbReadOnly) Or _ReadOnly )
 506                 Set ._ParentDatabase = _This
 507                 Call ._Initialize()
 508                 RecordsetMax = RecordsetMax + 1
 509                 ._Name = Format(RecordsetMax, &quot;0000000&quot;)
 510                 RecordsetsColl.Add(oObject, UCase(._Name))
 511         End With
 512
 513         If Not ( oObject._BOF And oObject._EOF ) Then oObject.MoveFirst()               &apos;  Do nothing if resultset empty
 514
 515 Exit_Function:
 516         Set OpenRecordset = oObject
 517         Set oObject = Nothing
 518         Utils._ResetCalledSub(cstThisSub)
 519         Exit Function
 520 Error_Function:
 521         TraceError(TRACEABORT, Err, cstThisSub, Erl)
 522         GoTo Exit_Function
 523 Trace_NotFound:
 524         TraceError(TRACEFATAL, ERROBJECTNOTFOUND, Utils._CalledSub(), 0, , Array(_GetLabel(&quot;TABLE&quot;) &amp; &quot;/&quot; &amp; _GetLabel(&quot;QUERY&quot;), pvSource))
 525         Goto Exit_Function
 526 End Function    &apos;  OpenRecordset   V1.1.0
 527
 528 REM -----------------------------------------------------------------------------------------------------------------------
 529 Public Function OpenSQL(Optional ByVal pvSQL As Variant _
 530                                                 , Optional ByVal pvOption As Variant _
 531                                                 ) As Boolean
 532 &apos;  Return True if the execution of the SQL statement was successful
 533 &apos;  SQL must contain a SELECT query
 534 &apos;  pvOption can force pass through mode
 535
 536         If _ErrorHandler() Then On Local Error Goto Error_Function
 537
 538 Const cstThisSub = &quot;Database.OpenSQL&quot;
 539         Utils._SetCalledSub(cstThisSub)
 540
 541         OpenSQL = False
 542         If IsMissing(pvSQL) Then Call _TraceArguments()
 543         If Not Utils._CheckArgument(pvSQL, 1, vbString) Then Goto Exit_Function
 544 Const cstNull = -1
 545         If IsMissing(pvOption) Then
 546                 pvOption = cstNull
 547         Else
 548                 If Not Utils._CheckArgument(pvOption, 2, Utils._AddNumeric(), Array(dbSQLPassThrough, cstNull)) Then Goto Exit_Function
 549         End If
 550         If _DbConnect &lt;&gt; DBCONNECTBASE And _DbConnect &lt;&gt; DBCONNECTFORM Then Goto Error_NotApplicable
 551
 552 Dim oURL As New com.sun.star.util.URL, oDispatch As Object
 553 Dim vArgs(8) as New com.sun.star.beans.PropertyValue
 554
 555         oURL.Complete = &quot;.component:DB/DataSourceBrowser&quot;
 556         oDispatch = StarDesktop.queryDispatch(oURL, &quot;_Blank&quot;, 8)
 557
 558         vArgs(0).Name = &quot;ActiveConnection&quot;            :       vArgs(0).Value = Connection
 559         vArgs(1).Name = &quot;CommandType&quot;                 :       vArgs(1).Value = com.sun.star.sdb.CommandType.COMMAND
 560         vArgs(2).Name = &quot;Command&quot;                             :       vArgs(2).Value = _ReplaceSquareBrackets(pvSQL)
 561         vArgs(3).Name = &quot;ShowMenu&quot;                            :       vArgs(3).Value = True
 562         vArgs(4).Name = &quot;ShowTreeView&quot;                        :       vArgs(4).Value = False
 563         vArgs(5).Name = &quot;ShowTreeViewButton&quot;  :       vArgs(5).Value = False
 564         vArgs(6).Name = &quot;Filter&quot;                              :       vArgs(6).Value = &quot;&quot;
 565         vArgs(7).Name = &quot;ApplyFilter&quot;                 :       vArgs(7).Value = False
 566         vArgs(8).Name = &quot;EscapeProcessing&quot;            :       vArgs(8).Value = CBool(Not ( pvOption = dbSQLPassThrough ))
 567
 568         oDispatch.dispatch(oURL, vArgs)
 569         OpenSQL = True
 570
 571 Exit_Function:
 572         Exit Function
 573 Error_Function:
 574         TraceError(TRACEABORT, Err, &quot;OpenSQL&quot;, Erl)
 575         GoTo Exit_Function
 576 SQL_Error:
 577         TraceError(TRACEFATAL, ERRSQLSTATEMENT, Utils._CalledSub(), 0, , pvSQL)
 578         Goto Exit_Function
 579 Error_NotApplicable:
 580         TraceError(TRACEFATAL, ERRMETHOD, Utils._CalledSub(), 0, 1, cstThisSub)
 581         Goto Exit_Function
 582 End Function            &apos;  OpenSQL         V1.1.0
 583
 584 REM -----------------------------------------------------------------------------------------------------------------------
 585 Public Function Properties(ByVal Optional pvIndex As Variant) As Variant
 586 &apos;  Return
 587 &apos;          a Collection object if pvIndex absent
 588 &apos;          a Property object otherwise
 589
 590         Utils._SetCalledSub(&quot;Database.Properties&quot;)
 591 Dim vProperty As Variant, vPropertiesList() As Variant, sObject As String
 592         vPropertiesList = _PropertiesList()
 593         sObject = Utils._PCase(_Type)
 594         If IsMissing(pvIndex) Then
 595                 vProperty = PropertiesGet._Properties(sObject, &quot;&quot;, vPropertiesList)
 596         Else
 597                 vProperty = PropertiesGet._Properties(sObject, &quot;&quot;, vPropertiesList, pvIndex)
 598                 vProperty._Value = _PropertyGet(vPropertiesList(pvIndex))
 599         End If
 600         Set vProperty._ParentDatabase = _This
 601
 602 Exit_Function:
 603         Set Properties = vProperty
 604         Utils._ResetCalledSub(&quot;Database.Properties&quot;)
 605         Exit Function
 606 End Function    &apos;  Properties
 607
 608 REM -----------------------------------------------------------------------------------------------------------------------
 609 Public Function QueryDefs(ByVal Optional pvIndex As Variant, ByVal Optional pbCheck As Boolean) As Object
 610 &apos;  Collect all Queries in the database
 611 &apos;  pbCheck unpublished
 612
 613         If _ErrorHandler() Then On Local Error Goto Error_Function
 614         Utils._SetCalledSub(&quot;Database.QueryDefs&quot;)
 615         If IsMissing(pbCheck) Then pbCheck = False
 616
 617 Dim sObjects() As String, sObjectName As String, oObject As Object
 618 Dim i As Integer, bFound As Boolean, oQueries As Object
 619         Set oObject = Nothing
 620         If Not IsMissing(pvIndex) Then
 621                 If Not Utils._CheckArgument(pvIndex, 1, Utils._AddNumeric(vbString)) Then Goto Exit_Function
 622         End If
 623
 624         Set oQueries = Connection.getQueries
 625         sObjects = oQueries.ElementNames()
 626         Select Case True
 627                 Case IsMissing(pvIndex)
 628                         Set oObject = New Collect
 629                         oObject._CollType = COLLQUERYDEFS
 630                         oObject._ParentType = OBJDATABASE
 631                         oObject._ParentName = &quot;&quot;
 632                         Set oObject._ParentDatabase = _This
 633                         oObject._Count = UBound(sObjects) + 1
 634                         Goto Exit_Function
 635                 Case VarType(pvIndex) = vbString
 636                         bFound = False
 637                 &apos;  Check existence of object and find its exact (case-sensitive) name
 638                         For i = 0 To UBound(sObjects)
 639                                 If UCase(pvIndex) = UCase(sObjects(i)) Then
 640                                         sObjectName = sObjects(i)
 641                                         bFound = True
 642                                         Exit For
 643                                 End If
 644                         Next i
 645                         If Not bFound Then Goto Trace_NotFound
 646                 Case Else               &apos;  pvIndex is numeric
 647                         If pvIndex &lt; 0 Or pvIndex &gt; UBound(sObjects) Then Goto Trace_IndexError
 648                         sObjectName = sObjects(pvIndex)
 649         End Select
 650
 651         Set oObject = New DataDef
 652         oObject._Type = OBJQUERYDEF
 653         oObject._Name = sObjectName
 654         Set oObject._ParentDatabase = _This
 655         oObject._readOnly = _ReadOnly
 656         Set oObject.Query = oQueries.getByName(sObjectName)
 657
 658 Exit_Function:
 659         Set QueryDefs = oObject
 660         Set oObject = Nothing
 661         Utils._ResetCalledSub(&quot;Database.QueryDefs&quot;)
 662         Exit Function
 663 Error_Function:
 664         TraceError(TRACEABORT, Err, &quot;Database.QueryDefs&quot;, Erl)
 665         GoTo Exit_Function
 666 Trace_NotFound:
 667         If Not pbCheck Then TraceError(TRACEFATAL, ERROBJECTNOTFOUND, Utils._CalledSub(), 0, , Array(_GetLabel(&quot;QUERY&quot;), pvIndex))
 668         Goto Exit_Function
 669 Trace_IndexError:
 670         TraceError(TRACEFATAL, ERRCOLLECTION, Utils._CalledSub(), 0)
 671         Goto Exit_Function
 672 End Function            &apos;  QueryDefs       V1.1.0
 673
 674 REM -----------------------------------------------------------------------------------------------------------------------
 675 Public Function Recordsets(ByVal Optional pvIndex As Variant) As Object
 676 &apos;  Collect all active recordsets
 677
 678         If _ErrorHandler() Then On Local Error Goto Error_Function
 679         Utils._SetCalledSub(&quot;Database.Recordsets&quot;)
 680
 681         Set Recordsets = Nothing
 682         If Not IsMissing(pvIndex) Then
 683                 If Not Utils._CheckArgument(pvIndex, 1, Utils._AddNumeric(vbString)) Then Goto Exit_Function
 684         End If
 685
 686 Dim sObjects() As String, sObjectName As String, oObject As Object
 687 Dim i As Integer, bFound As Boolean, oTables As Object
 688
 689         Select Case True
 690                 Case IsMissing(pvIndex)
 691                         Set oObject = New Collect
 692                         oObject._CollType = COLLRECORDSETS
 693                         oObject._ParentType = OBJDATABASE
 694                         oObject._ParentName = &quot;&quot;
 695                         Set oObject._ParentDatabase = _This
 696                         oObject._Count = RecordsetsColl.Count
 697                 Case VarType(pvIndex) = vbString
 698                         bFound = _hasRecordset(pvIndex)
 699                         If Not bFound Then Goto Trace_NotFound
 700                         Set oObject = RecordsetsColl.Item(pvIndex)
 701                 Case Else               &apos;  pvIndex is numeric
 702                         If pvIndex &lt; 0 Or pvIndex &gt;= RecordsetsColl.Count Then Goto Trace_IndexError
 703                         Set oObject = RecordsetsColl.Item(pvIndex + 1)          &apos;  Collection members are numbered 1 ... Count
 704         End Select
 705
 706 Exit_Function:
 707         Set Recordsets = oObject
 708         Set oObject = Nothing
 709         Utils._ResetCalledSub(&quot;Database.Recordsets&quot;)
 710         Exit Function
 711 Error_Function:
 712         TraceError(TRACEABORT, Err, &quot;Database.Recordsets&quot;, Erl)
 713         GoTo Exit_Function
 714 Trace_NotFound:
 715         TraceError(TRACEFATAL, ERROBJECTNOTFOUND, Utils._CalledSub(), 0, , Array(_GetLabel(&quot;RECORDSET&quot;), pvIndex))
 716         Goto Exit_Function
 717 Trace_IndexError:
 718         TraceError(TRACEFATAL, ERRCOLLECTION, Utils._CalledSub(), 0)
 719         Goto Exit_Function
 720 End Function            &apos;  Recordsets      V0.9.5
 721
 722 REM -----------------------------------------------------------------------------------------------------------------------
 723 Public Function RunSQL(Optional ByVal pvSQL As Variant _
 724                                                 , Optional ByVal pvOption As Variant _
 725                                                 ) As Boolean
 726 &apos;  Return True if the execution of the SQL statement was successful
 727 &apos;  SQL must contain an ACTION query
 728
 729         If _ErrorHandler() Then On Local Error Goto Error_Function
 730
 731         Utils._SetCalledSub(&quot;RunSQL&quot;)
 732
 733         RunSQL = False
 734         If IsMissing(pvSQL) Then Call _TraceArguments()
 735         If Not Utils._CheckArgument(pvSQL, 1, vbString) Then Goto Exit_Function
 736 Const cstNull = -1
 737         If IsMissing(pvOption) Then
 738                 pvOption = cstNull
 739         Else
 740                 If Not Utils._CheckArgument(pvOption, 2, Utils._AddNumeric(), Array(cstNull, dbSQLPassThrough)) Then Goto Exit_Function
 741         End If
 742
 743 Dim oStatement As Object, vResult As Variant
 744         Set oStatement = Connection.createStatement()
 745         oStatement.EscapeProcessing = Not ( pvOption = dbSQLPassThrough )
 746         On Local Error Goto SQL_Error
 747         vResult = oStatement.executeUpdate(_ReplaceSquareBrackets(pvSQL))
 748         On Local Error Goto Error_Function
 749         RunSQL = True
 750
 751 Exit_Function:
 752         Exit Function
 753 Error_Function:
 754         TraceError(TRACEABORT, Err, &quot;RunSQL&quot;, Erl)
 755         GoTo Exit_Function
 756 SQL_Error:
 757         TraceError(TRACEFATAL, ERRSQLSTATEMENT, Utils._CalledSub(), 0, , pvSQL)
 758         Goto Exit_Function
 759 End Function            &apos;  RunSQL          V1.1.0
 760
 761 REM -----------------------------------------------------------------------------------------------------------------------
 762 Public Function TableDefs(ByVal Optional pvIndex As Variant, ByVal Optional pbCheck As Boolean) As Object
 763 &apos;  Collect all tables in the database
 764 &apos;  pbCheck unpublished
 765
 766         If _ErrorHandler() Then On Local Error Goto Error_Function
 767         Utils._SetCalledSub(&quot;Database.TableDefs&quot;)
 768         If IsMissing(pbCheck) Then pbCheck = False
 769
 770 Dim sObjects() As String, sObjectName As String, oObject As Object
 771 Dim i As Integer, bFound As Boolean, oTables As Object
 772         Set oObject = Nothing
 773         If Not IsMissing(pvIndex) Then
 774                 If Not Utils._CheckArgument(pvIndex, 1, Utils._AddNumeric(vbString)) Then Goto Exit_Function
 775         End If
 776
 777         Set oTables = Connection.getTables
 778         sObjects = oTables.ElementNames()
 779         Select Case True
 780                 Case IsMissing(pvIndex)
 781                         Set oObject = New Collect
 782                         oObject._CollType = COLLTABLEDEFS
 783                         oObject._ParentType = OBJDATABASE
 784                         oObject._ParentName = &quot;&quot;
 785                         Set oObject._ParentDatabase = _This
 786                         oObject._Count = UBound(sObjects) + 1
 787                         Goto Exit_Function
 788                 Case VarType(pvIndex) = vbString
 789                         bFound = False
 790                 &apos;  Check existence of object and find its exact (case-sensitive) name
 791                         For i = 0 To UBound(sObjects)
 792                                 If UCase(pvIndex) = UCase(sObjects(i)) Then
 793                                         sObjectName = sObjects(i)
 794                                         bFound = True
 795                                         Exit For
 796                                 End If
 797                         Next i
 798                         If Not bFound Then Goto Trace_NotFound
 799                 Case Else               &apos;  pvIndex is numeric
 800                         If pvIndex &lt; 0 Or pvIndex &gt; UBound(sObjects) Then Goto Trace_IndexError
 801                         sObjectName = sObjects(pvIndex)
 802         End Select
 803
 804         Set oObject = New DataDef
 805         oObject._Type = OBJTABLEDEF
 806         oObject._Name = sObjectName
 807         Set oObject._ParentDatabase = _This
 808         oObject._ReadOnly = _ReadOnly
 809         Set oObject.Table = oTables.getByName(sObjectName)
 810
 811 Exit_Function:
 812         Set TableDefs = oObject
 813         Set oObject = Nothing
 814         Utils._ResetCalledSub(&quot;Database.TableDefs&quot;)
 815         Exit Function
 816 Error_Function:
 817         TraceError(TRACEABORT, Err, &quot;Database.TableDefs&quot;, Erl)
 818         GoTo Exit_Function
 819 Trace_NotFound:
 820         If Not pbCheck Then TraceError(TRACEFATAL, ERROBJECTNOTFOUND, Utils._CalledSub(), 0, , Array(_GetLabel(&quot;TABLE&quot;), pvIndex))
 821         Goto Exit_Function
 822 Trace_IndexError:
 823         TraceError(TRACEFATAL, ERRCOLLECTION, Utils._CalledSub(), 0)
 824         Goto Exit_Function
 825 End Function            &apos;  TableDefs       V1.1.0
 826
 827 REM -----------------------------------------------------------------------------------------------------------------------
 828 REM --- PRIVATE FUNCTIONS                                                                                                                                                                                       ---
 829 REM -----------------------------------------------------------------------------------------------------------------------
 830
 831 REM -----------------------------------------------------------------------------------------------------------------------
 832 Private Function _DFunction(ByVal psFunction As String _
 833                                                         , ByVal psExpr As String _
 834                                                         , ByVal psDomain As String _
 835                                                         , ByVal pvCriteria As Variant _
 836                                                         , ByVal Optional pvOrderClause As Variant _
 837                                                         ) As Variant
 838     &apos;Arguments: psFunction         an optional aggregate function
 839     &apos;                      psExpr:                 an SQL expression [might contain an aggregate function]
 840     &apos;                      psDomain:               a table- or queryname
 841     &apos;                      pvCriteria:             an optional WHERE clause
 842     &apos;                      pcOrderClause:  an optional order clause incl. &quot;DESC&quot; if relevant
 843
 844 If _ErrorHandler() Then On Local Error GoTo Error_Function
 845
 846 Dim oResult As Object                   &apos;To retrieve the value to find.
 847 Dim vResult As Variant                  &apos;Return value for function.
 848 Dim sSql As String                              &apos;SQL statement.
 849 Dim oStatement As Object                &apos;For CreateStatement method
 850 Dim sExpr As String                             &apos;For inclusion of aggregate function
 851 Dim sTempField As String                &apos;Random temporary field in SQL expression
 852
 853     vResult = Null
 854
 855         If psFunction = &quot;&quot; Then sExpr = &quot;TOP 1 &quot; &amp; psExpr Else sExpr = UCase(psFunction) &amp; &quot;(&quot; &amp; psExpr &amp; &quot;)&quot;
 856
 857         Randomize 2^14-1
 858         sTempField = &quot;TEMP&quot; &amp; Right(&quot;00000&quot; &amp; Int(100000 * Rnd), 5)
 859     sSql = &quot;SELECT &quot; &amp; sExpr &amp; &quot; AS [&quot; &amp; sTempField &amp; &quot;] FROM &quot; &amp; psDomain
 860     If pvCriteria &lt;&gt; &quot;&quot; Then
 861         sSql = sSql &amp; &quot; WHERE &quot; &amp; pvCriteria
 862     End If
 863     If pvOrderClause &lt;&gt; &quot;&quot; Then
 864         sSql = sSql &amp; &quot; ORDER BY &quot; &amp; pvOrderClause
 865     End If
 866
 867     &apos;Lookup the value.
 868     Set oStatement = Connection.createStatement()
 869         With oStatement
 870                 .ResultSetType = com.sun.star.sdbc.ResultSetType.FORWARD_ONLY
 871                 .ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.READ_ONLY
 872                 .EscapeProcessing = False
 873             sSql = _ReplaceSquareBrackets(sSql)         &apos;Substitute [] by quote string
 874                 Set oResult = .executeQuery(sSql)
 875             If Not IsNull(oResult) And Not IsEmpty(oResult) Then
 876                                 If Not oResult.next() Then Goto Exit_Function
 877                     vResult = Utils._getResultSetColumnValue(oResult, 1)
 878         End If
 879         End With
 880
 881 Exit_Function:
 882     &apos;Assign the returned value.
 883     _DFunction = vResult
 884     Set oResult = Nothing
 885     Set oStatement = Nothing
 886     Exit Function
 887 Error_Function:
 888     TraceError(TRACEABORT, ERRDFUNCTION, _A2B_.CalledSub, 0, , sSQL)
 889     Goto Exit_Function
 890 End Function            &apos;  DFunction               V1.1.0
 891
 892 REM -----------------------------------------------------------------------------------------------------------------------
 893 Public Function _hasRecordset(ByVal psName As String) As Boolean
 894 &apos;  Return True if psName if in the collection of Recordsets
 895
 896 Dim oRecordset As Object
 897         If _ErrorHandler() Then On Local Error Goto Error_Function
 898         Set oRecordset = RecordsetsColl.Item(psName)
 899         _hasRecordset = True
 900
 901 Exit_Function:
 902         Exit Function
 903 Error_Function:         &apos;  Item by key aborted
 904         _hasRecordset = False
 905         GoTo Exit_Function
 906 End Function    &apos;  _hasRecordset   V0.9.5
 907
 908 REM -----------------------------------------------------------------------------------------------------------------------
 909 Private Function _PropertiesList() As Variant
 910
 911         _PropertiesList = Array(&quot;ObjectType&quot;)
 912
 913 End Function    &apos;  _PropertiesList
 914
 915 REM -----------------------------------------------------------------------------------------------------------------------
 916 Private Function _PropertyGet(ByVal psProperty As String) As Variant
 917 &apos;  Return property value of the psProperty property name
 918
 919         If _ErrorHandler() Then On Local Error Goto Error_Function
 920         Utils._SetCalledSub(&quot;Database.get&quot; &amp; psProperty)
 921 Dim vEMPTY As Variant
 922         _PropertyGet = vEMPTY
 923
 924         Select Case UCase(psProperty)
 925                 Case UCase(&quot;ObjectType&quot;)
 926                         _PropertyGet = _Type
 927                 Case Else
 928                         Goto Trace_Error
 929         End Select
 930
 931 Exit_Function:
 932         Utils._ResetCalledSub(&quot;Database.get&quot; &amp; psProperty)
 933         Exit Function
 934 Trace_Error:
 935         TraceError(TRACEFATAL, ERRPROPERTY, Utils._CalledSub(), 0, , psProperty)
 936         _PropertyGet = vEMPTY
 937         Goto Exit_Function
 938 Error_Function:
 939         TraceError(TRACEABORT, Err, &quot;Database._PropertyGet&quot;, Erl)
 940         _PropertyGet = vEMPTY
 941         GoTo Exit_Function
 942 End Function            &apos;  _PropertyGet
 943
 944 REM -----------------------------------------------------------------------------------------------------------------------
 945 Public Function _ReplaceSquareBrackets(ByVal psSql As String) As String
 946 &apos; Returns psSql after substitution of [] by quote character
 947 &apos; [] square brackets in (single) quoted strings not affected
 948
 949 Dim sQuote As String            &apos;RDBMS specific quote character
 950 Dim vSubStrings() As Variant, i As Integer
 951 Const cstSingleQuote = &quot;&apos;&quot;
 952
 953         sQuote = MetaData.IdentifierQuoteString
 954         If sQuote = &quot; &quot; Then          &apos;  IdentifierQuoteString returns a space &quot; &quot; if identifier quoting is not supported.
 955                 _ReplaceSquareBrackets = Trim(psSql)
 956                 Exit Function
 957         End If
 958         vSubStrings() = Split(psSql, cstSingleQuote)
 959         For i = 0 To UBound(vSubStrings)
 960                 If (i Mod 2) = 0 Then           &apos;  Only even substrings are parsed for square brackets
 961                         vSubStrings(i) = Join(Split(vSubStrings(i), &quot;[&quot;), sQuote)
 962                         vSubStrings(i) = Join(Split(vSubStrings(i), &quot;]&quot;), sQuote)
 963                 End If
 964         Next i
 965
 966         _ReplaceSquareBrackets = Trim(Join(vSubStrings, cstSingleQuote))
 967
 968 End Function    &apos;  ReplaceSquareBrackets           V1.1.0
 969 </script:module>