Workaround for 512-Character Limit for SQL String

• Microsoft Word for Windows, versions 6.0, 6.0a, 6.0c
• Microsoft Word for Windows 95, version 7.0

SYMPTOMS

By design, the maximum number of characters that can be used in an SQL string in a macro is 512. This article describes a how you can work around this limitation.

WORKAROUND

To work around this problem, use the following steps:

1. Place the text for the SQL string in a sequential Microsoft Access file.

2. Call a Microsoft Access macro that reads this file, and create a query definition based on that SQL string.

3. Insert the database using this query definition.

WARNING: ANY USE BY YOU OF THE OR MACRO CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this macro code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

Word Macro

Sub MAIN

End Sub

     On Error GoTo ErrHandler
     KillAccess = 0
     'If there are quotes in the string, convert them to XqX
     ' to be switched back in Access.
     x = InStr(SQLStuff$, Chr$(34))
     While x <> 0
          SQLStuff$ = Left$(SQLStuff$, x - 1) + "XqX" +

          x = InStr(SQLStuff$, Chr$(34))
     Wend
     'Start Access if it isn't running. If Access is already running
     ' you won't need to shut it down when your finished.
     If Not AppIsRunning("Microsoft Access") Then
          Shell "C:\MSOFFICE\ACCESS\MSACCESS.EXE "
          KillAccess = 1
     End If
     'Open the sequential access file and insert the SQL string.
     Open "C:\WORDSQL.TXT" For Output As #1
     Write #1, SQLStuff$, KillAccess
     Close #1
     'Call Access via DDE. Run the Access macro.
     Chan = DDEInitiate("MSACCESS", "System")
     DDEExecute Chan, "[OpenDatabase " + DBtoUse$ + "]"
     On Error Resume Next
     DDEExecute Chan, "[WordQuery]"
     DDEExecute Chan, "[CloseDatabase]"
     DDETerminate Chan
     On Error Goto 0
     'Read the sequential access file to see if Access is done with it.
     Quitting = 0
     While Quitting = 0
          Open "C:\WORDSQL.TXT" For Input As #1
          Read #1, Dun$
          If Dun$ = "You're Done" Then Quitting = - 1
          Close #1
          For I = 1 To 100
          Next I
     Wend
     'Delete the sequential access file
     Kill "C:\WORDSQL.TXT"
     'Insert the database using the query definition via DDE.
     InsertDatabase .Format = 0, .Style = 0, .LinkToSource = 0,
.Connection = "QUERY Wordquery", .SQLStatement =      "SELECT * FROM [
Wordquery ] ", .SQLStatement1 = "",      .PasswordDoc = "", .PasswordDot =
"",      .DataSource = "C:\MSOFFICE\ACCESS\SAMPAPPS\NWIND.MDB",      .From

     InsertSQL = 1
     Goto TheEnd

     MsgBox "Error" + Str$(Err) + " occurred."
     Err = 0

Microsoft Access Module

Function WordQuery () As Integer

Dim FileName As Variant, MyFile As Integer, MyWorkspace As WorkSpace, MyDB As Database, MyQuery As QueryDef, QD As Variant

'Read the SQL string from the sequential access file.

'Convert any ossurances of XqX to quotes.

    WordSQL$ = Left$(WordSQL$, x - 1) + Chr$(34) + Mid$(WordSQL$, x + 3)
    x = InStr(WordSQL$, "XqX")

'Find out if a query definition named WordQuery exists. If one exists,
' delete it.

    If MyDB.QueryDefs(I).Name = "Wordquery" Then
    QD = I
    End If

'Create query definition based on SQL string

'Write information into sequential access file to let Word know
' we're finished.

'If Access wasn't running when Word called it, quit Access.

Access Macro Named "WordQuery"

   Name     Condition     Action        Argument           Value
   ---------------------------------------------------------------------
                          RunCode       FunctionName:      WordQuery()