Access 2007 SP1 Mail Merge Error

[boblarson]

I have a db from work that used to work fine for mail merge. It would open up word and the data would be in the Word Mail Merge wizard for the users to select their mail merge fields.

Unfortunately, we had to upgrade the clients to 2007 SP1 from the base 2007. It fixed some things but it appears that it broke this.

I looked up the error (9105 String longer than 255 characters) on the MS website and it has been a problem before, but they supposedly had hot fixes, or Service Pack updates. Now it appears to be a problem in 2007 as well. Like I said, it used to work prior to the SP update.

Now I need to figure out a workaround as the clients need this ASAP.

The code that has been there is:

Private Sub Report_Open(Cancel As Integer)
    If Len(Me.OpenArgs) Then
        DoCmd.SetWarnings False
            DoCmd.RunSQL "SELECT qry__Mailing_" & Me.OpenArgs & ".* INTO tbl_MailMerge" & Me.OpenArgs & " FROM qry__Mailing_" & Me.OpenArgs
        DoCmd.SetWarnings True
        Dim wd As New Word.Application
        wd.Visible = True
        Dim doc As Word.Document
        Set doc = wd.Documents.Add
        doc.MailMerge.MainDocumentType = wdFormLetters
           
        doc.MailMerge.OpenDataSource Name:=CurrentProject.FullName, _
            ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
            AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
            WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
            Format:=wdOpenFormatAuto, Connection:= _
            "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & CurrentProject.FullName & ";Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database L" _
            , SQLStatement:="SELECT * FROM `tbl_MailMerge" & Me.OpenArgs & "`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess
            If doc.MailMerge.WizardState = 0 Then doc.MailMerge.ShowWizard 1, True, True, False, True, True, True
            wd.Dialogs(wdDialogMailMergeRecipients).Show
    End If
    Cancel = True
End Sub

It fails on the doc.MailMerge.OpenDataSource part as it is 377 characters long and apparently can't be over 255. I've tried everything I know to do, including trying:

        strSQL = "SELECT * FROM `tbl_MailMerge" & Me.OpenArgs
        strDataSource = "Name:=CurrentProject.FullName,LinkToSource:=True,Connection:='Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & CurrentProject.FullName & "';SQLStatement:=" & strSQL & "'"

or even:

doc.MailMerge.OpenDataSource , , , , True, , , , , , , CurrentProject.Connection, strSQL, , , wdMergeSubTypeAccess

but it still reports over 255 characters.

I'm not doing well. So, maybe anyone have ideas?

 

[Moniker]

For anyone else looking, it this he's referring to:

Connection:= _
            [B][COLOR="Red"]"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & CurrentProject.FullName & ";Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database L" _
            , SQLStatement:="SELECT * FROM `tbl_MailMerge" & Me.OpenArgs & "`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess[/COLOR][/B]

The part in red bold is too long as connection strings are limited to 255 characters.

For Bob, what is the full path/name of the DB? Is that in a sub-sub-sub-sub-folder or something, adding too much to it? It's all that's jumping at me at the moment. Also, since I'm not too familiar with how you're doing that, do you need to set all those options? It looks like you've tried dropping the unneeded ones already, which is why I'm thinking the CurrentProject.FullName is going to be the culprit. If not, then I'll look in the morning when I'm more awake and caffeinated.

 

[boblarson]

For anyone else looking, it this he's referring to:

Connection:= _
            [B][COLOR="Red"]"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & CurrentProject.FullName & ";Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLEDB:Database L" _
            , SQLStatement:="SELECT * FROM `tbl_MailMerge" & Me.OpenArgs & "`", SQLStatement1:="", _
            SubType:=wdMergeSubTypeAccess[/COLOR][/B]

The part in red bold is too long as connection strings are limited to 255 characters.

For Bob, what is the full path/name of the DB? Is that in a sub-sub-sub-sub-folder or something, adding too much to it?

I've tried it with it being on
D:\2008-02-13_2nd\XXX.accdb
as well and it still does it.

The weird thing is that I just ran a test and the unmodified file works on the client machine, but if I bring it over to mine (but I have to email it to myself as the support client they are using doesn't have a file transfer feature) it breaks. It didn't do that prior to the SP1 update.

 

[Moniker]

So, in essence, the weirdest part right now is having to email it to yourself? Can you try copying it to a USB Key? Also, have you attempted to completely reset the document variable (Set doc = Nothing) and then redefine it so that we know there are not hidden characters in there?

I know these are longshots, but you've either discovered yet another weirdo bug (and you're right, MS "is aware of the problem", which is very useful), or you may have to temporarily change the way you do this for the client that will work with and without the SP installed. If it helps, the Access 2003 SP3 caused all sorts of issues, but there was a hotfix that followed shortly after that corrected a lot of the newly introduced bugs.

 

[boblarson]

So, in essence, the weirdest part right now is having to email it to yourself? Can you try copying it to a USB Key?

It's a remote client so I don't have physical access to the computers.

Also, the strange part is that it fails on two of my computers, but I came in this morning and it worked on my work desktop machine. I'm going to go nuts with these things!

 

[AccessHappy]

Hi. I'm too late to help the original poster, but I can add something to the conversation for the next person who Googles the problem and arrives here:

That error also occurs if the SQLStatement parameter for the .MailMerge.OpenDataSource method is longer than 255 characters. I first came across the problem a while back and had to shorten my Connection string first of all. That worked all the time I was just passing "Select * from [tablename]" as the SQLStatement , but recently I tried to pass a complex querystring >255 chars long and started getting errors again. The OpenDataSource method does allow for a second chunk of SQL via its SQLStatement1 parameter, but that still only takes the max length to 510 chars.

My sample code snippet below:
'**Calling routine passes strDocName and queryString parameters**
Set objApp = CreateObject("Word.Application")
With objApp
.Visible = True 'Make it visible - NB images in document header are not loaded properly if app is invisible
Set objMaster = .Documents.Add(strDocName) 'Open new document based on specified template
'Set Merge Data Source (use Word VBA to get value for mailmerge.datasource.querystring and .connectstring)
'NB: Connectstring from MSWord is too long (>255), so remove parameters that use default values.
'Use the Query defined in the arguments as the datasource
strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" & CurrentDb.Name & ";Mode=Read"
strQuery = QueryString

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
ReadOnly:=True, LinktoSource:=False, _
Connection:=strConnect, SQLStatement:=strQuery
End With

Summary: I can confirm that the merge fails when the connection string is <255 but the SQLStatement (querystring) is >255. I can confirm it then works again once the SQL statement is shortened to <255. Defining aliases for table names can help to shorten the SQL statement. Edit: There is apparently also a bug in word that means for some OLEDB sources, the combined length of SQLStatement and SQLStatement1 cannot exceed 255 characters. I rewrote my code to split the querystring into two parts but it still fails if the entire statement exceeds 255 chars (according to MSDN it _should_ accept up to 510 chars).