Microsoft KB Archive/144743

{|
 * width="100%"|

Workaround for 512-Character Limit for SQL String

 * }

Q144743

-

The information in this article applies to:


 * Microsoft Word for Windows, versions 6.0, 6.0a, 6.0c
 * Microsoft Word for Windows, 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.

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

Issue type :

Technology :