Workaround for 512-Character Limit for SQL String

ID: Q144743

The information in this article applies to:

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:

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

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

Insert the database using this query definition.

The following WordBasic macro and Microsoft Access module demonstrate this technique.

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
MySQL$ = "SELECT DISTINCTROW Categories.[Category Name], Products.[Product
Name], Products.[Units In Stock], Products.[Units On Order],
Suppliers.[Company Name], Suppliers.Phone, Suppliers.Fax,
Suppliers.[Contact Name] FROM Categories INNER JOIN (Suppliers INNER JOIN
Products ON Suppliers.[Supplier ID] = Products.[Supplier ID]) ON
Categories.[Category ID] = Products.[Category ID] WHERE ((Not
Suppliers.[Company Name]=" + Chr$(34) + "Zaanse Snoepfabriek" + Chr$(34) +
") AND (Not Suppliers.Fax=" + Chr$(34) + "(1) 03.83.0.62" + Chr$(34) + ")
AND (Not Suppliers.[Contact Name]=" + Chr$(34) + "Giovanni Giudici" +
Chr$(34) + ")) ORDER BY Suppliers.Fax, Suppliers.[Contact Name];"
X = InsertSQL("c:\msoffice\access\sampapps\Nwind.MDB", MySQL$)
End Sub

Function InsertSQL(DBtoUse$, SQLStuff$)
     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" +
Mid$(SQLStuff$, X + 1)
          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
= "", .To = "", .IncludeFields = 1
     InsertSQL = 1
     Goto TheEnd
ErrHandler:
     MsgBox "Error" + Str$(Err) + " occurred."
     Err = 0
TheEnd:
End Function' InsertSQL()

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.
FileName = "C:\WORDSQL.TXT"
MyFile = FreeFile
Open FileName For Input As MyFile  ' Open file.
Input #MyFile, WordSQL$, KillAccess
KillAccess = Val(KillAccess)
Close MyFile

'Convert any ossurances of XqX to quotes.
x = InStr(WordSQL$, "XqX")
While x <> 0
    WordSQL$ = Left$(WordSQL$, x - 1) + Chr$(34) + Mid$(WordSQL$, x + 3)
    x = InStr(WordSQL$, "XqX")
Wend
'Find out if a query definition named WordQuery exists. If one exists,
' delete it.
Set MyWorkspace = DBEngine.Workspaces(0)
Set MyDB = MyWorkspace.Databases(0)
QD = -1
For I = 0 To MyDB.QueryDefs.Count - 1
    If MyDB.QueryDefs(I).Name = "Wordquery" Then
    QD = I
    End If
Next I
If QD <> -1 Then
MyDB.QueryDefs.Delete "Wordquery"
End If
'Create query definition based on SQL string
Set MyQuery = MyDB.CreateQueryDef("Wordquery", WordSQL$)
'Write information into sequential access file to let Word know
' we're finished.
Open FileName For Output As MyFile  ' Open file.
Write #MyFile, "You're Done"
Close MyFile
'If Access wasn't running when Word called it, quit Access.
If KillAccess = 1 Then
Application.Quit
End If
WordQuery = -1
End Function

Access Macro Named "WordQuery"


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

Additional query words: 6.0

Keywords : kbinterop
Version : WINDOWS:6.0,6.0a,6.0c,7.0
Platform : WINDOWS
Issue type :