MailMerge from txt file - failing when the file path contains a reserved SQL word

travb81 · #1 10-18-2018, 12:12 AM

Hiya,

I've got some VBA code that pulls some records from a database, throws them into a tab separated .txt file, and then sets up a mailmerge to that text file.
Works fantastically!

Except....
I've just been hunting for a glitch that occurred today. I've worked out that the file path contained the word 'where' in it.
eg "C:\Jobs\Timber where missing\"

Code:

        strPath = ActiveDocument.Path & "\MailMerge.txt"
        ActiveDocument.MailMerge.OpenDataSource strPath, , , True, False
        ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False

Any suggestion on how I can get around this?
I'm guessing this is because WHERE is an SQL reserved word...
If I manually do the mail merge, i get an SQL error. See image below.

I've tried to put ' or " at the start and finish of the strPath variable. No good.

The only work around is to rename the folder.
This isn't ideal - as if i go down this line, there are probably other SQL keywords that will cause problems.

Any suggestions for this unique situation?....
It is kind of a Microsoft Glitch / Bug...

Guessed · #2 10-18-2018, 02:26 PM

Looking at https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue it appears that you have two options to explore.

1. Include a SQLStatement variable to see if Word doesn't try to interpret the Name as a SQL statement instead.
2. Create a .qry file and point at that instead

macropod · #3 10-18-2018, 09:30 PM

Why not simply have the mailmerge main document connect directly to the database and apply whatever filtering you require within the mailmerge main document itself?

travb81 · #4 10-21-2018, 04:17 PM

Quote:

Originally Posted by Guessed

Looking at https://docs.microsoft.com/en-us/off...6)%26rd%3Dtrue it appears that you have two options to explore.

1. Include a SQLStatement variable to see if Word doesn't try to interpret the Name as a SQL statement instead.
2. Create a .qry file and point at that instead

Hi Andrew,
Thanks for your reply.
#1 - I tried parameter

Code:

SQLStatement:=";"

, without success

#2 - Not too sure what you mean, can you offer any further assistance? I'm not trying to query an SQL source, but set a txt / csv type file as the datasource for the mail merge.

travb81 · #5 10-21-2018, 04:26 PM

Quote:

Originally Posted by macropod

Why not simply have the mailmerge main document connect directly to the database and apply whatever filtering you require within the mailmerge main document itself?

Hi Paul,

I did try this initially. The issue I ran into, was the sql statement was very long and complex. Using the OpenDataSource function, the SQLStatement and SQLStatement1 parameters seemed to not be large enough for this query.

Working with Access regularly, it was easy to establish a connection and recordset, and throw the data I need into a file. It doesn't change very often, so this disconnected data works very well.

macropod · #6 10-21-2018, 07:40 PM

Quote:

Originally Posted by travb81

The issue I ran into, was the sql statement was very long and complex. Using the OpenDataSource function, the SQLStatement and SQLStatement1 parameters seemed to not be large enough for this query.

SQLStatement is suitable for query strings up to 255 characters. Where the query string is longer than 255 characters, you'd use SQLStatement1 for the remainder; there is no specified character limit for SQLStatement1.

travb81 · #7 10-26-2018, 03:31 AM

Quote:

Originally Posted by macropod

SQLStatement is suitable for query strings up to 255 characters. Where the query string is longer than 255 characters, you'd use SQLStatement1 for the remainder; there is no specified character limit for SQLStatement1.

Hi Paul,
Are you sure SQLStatement1 accepts a longer string?

Code:

    
strSQL1 = Left(strSQL, 255)
strSQL2 = Right(strSQL, Len(strSQL) - 255)

My statement is 514 characters long, and the .OpenDataSource statement is throwing up runtime error 9105, 'string is longer than 255 characters'.
If I remove a few spaces or one of the WHERE criteria from the statement, it executes ok...

macropod · #8 10-26-2018, 04:00 AM

I have no idea what the character limit for SQLStatement1 might be; all I said is that no limit is specified. Assuming both are limited to 255 characters, you might explore the possibility of optimising the SQL code or even using field coding within the mailmerge main document to achieve the same outcome.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Putting path and file name into all Word documents	Kevin Beach	Word	3	11-26-2016 06:14 PM
Determining file path for linked image in Word 2007	samhdc	Word	7	01-26-2015 04:21 PM
Display Full File Path Name of Document in Title bar MS-Word 2010	Carlos06x	Word	1	10-12-2011 10:39 AM
Programmatically get File Path for currently opened WORD document	franferns	Word	1	11-26-2009 12:36 PM
get file name without Path	Ziggy1	Word VBA	1	09-29-2006 07:55 PM