String is longer than 255 characters

[charle525]

I had created a mail merge using Access and Word that was working fine and then, it seems, all of the suddent I am getting an error "String is longer than 255 characters" at run-time while executing the code below. Clearly the code is longer than 255 characters, but there was no error previously. What could I have changed that caused this? I'm certain the merge worked with a command larger than 255 characters, but now it doesn't.

objWord.MailMerge.OpenDataSource Name:= _
CurrentProject.FullName, LinkToSource:=True, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & CurrentProject.FullName & ";Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB" _
, SQLStatement:="SELECT * FROM [qryStudentMinor] WHERE tblMinor.AdmitDate >= #" & Me!LetterDate & "# AND tblMinor.MinorType= 'General Education'", SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

 

[mph1]

.....but there was no error previously.....

It this has worked before and you made no changes, ie, adding something (not data), then it sounds like something became corrupt.

Try compacting and repair first. If this doesn't help, make a new database and import everyting.

Why does something all of a sudden become corrupt ....... come up with the answer and you could become wealthy.

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[EstuardoSierra]

Charle525:

What about if you break the whole command in short strings? i mean eliminate all the defausl parameters, and split the lines in variables. something like this:

spath = Provider=Microsoft.Jet.OLEDB.4.0;;Data Source=" & CurrentProject.FullName & ";Jet OLEDB:Engine Type=5;Jet OLEDB"

I had cuted all the defaults parameters. Why are u using them?
they are not necessary. you are working with Jet as far as i know it just need four parameter and two of them (User and password) if are not explicitly typed, they are assumed with their default values.

I'm not saying it will work that way, because i can't see your query. But if the err is connection violation, because the lenght of the string it should fix it.

 

[charle525]

The problem is clearly like to be with this long connection string, below. However, when I cut it down as suggested, I get a Data Link Properties dialog box from Word forcing me the verify my database and table/query to use. The only time I've been able to keep this dialog from appearing was by including this long connection string, and of course now that is giving me the String length error. Any clues?

"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" & CurrentProject.FullName & ";Mode=Read;Extended Properties=""""; Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB"

 

[charle525]

Ok, well I solved the problem for this string, but I still have issues with other strings over 255 chars. I use the
SQLStatement argument to further filter the query that the mail merge is based on. Now, for other mail merges I have SQL statements that are over 255 characters. Is there anything I can do to get around this character limit?

 

[EstuardoSierra]

charle525:
Just a question. Why don't you base your marge in saved queries? ussualy the support up to 255 fields (or may be more i'd never went that far)
it will save you the hard typing and you can create a ODBC connection to link the query with your client (MS Word?)

good luck

Estuardo

 

[charle525]

My biggest problem has been with this "Data Link Properties" dialog that often opens when the Word document opens. It wants me to confirm my data source. So, everytime I get the merge to work without seeing this dialog, albeit with long SQL strings hard coded and extra arguments that should just default, I tend to leave it alone. Any little changes seems to mess up the connection the next time I try the merge. I can't figure out why I keep getting this dialog in Word.

Estuardo, you mentioned creating an ODBC connection to link the query and Word. Do you mean actually going to Data Sources and creating it?