Run-Time '9105' String is longer than 255 characters.

[kjspear]

I have a question about an error I received using VBA in MS Word XP . The error message I receive is;

Run-Time
'9105'
String is longer than 255 characters

Here is my code;

' pccmember3 Macro
' Macro recorded 6/22/2005 by meg17-05
'
ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb; _
"Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" _
, SQLStatement:="SELECT * FROM `registrationPCCMEMBERS`", SQLStatement1:= _
"", SubType:=wdMergeSubTypeAccess
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
-------------------------------------------
Of course the underscores are new lines which was automatically put in
------------------------------------------------
Here is where VBA highlights the error;

ActiveDocument.MailMerge.OpenDataSource Name:= _
"F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb", ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" _
, SQLStatement:="SELECT * FROM `registrationPCCMEMBERS`", SQLStatement1:= _
"", SubType:=wdMergeSubTypeAccess

I belive it's a simple syntax error somewhere. Does anyone notice where the problem is?

Hopefully, the information above is clear.

Thanks,
KJ

 

[Trevil]

There appears to be a limit of 256 characters that you can pass. I copied your code exactly and got same error. Then started deleting optional parameters until it worked.
I deleted:
ReadOnly:=False,
WritePasswordTemplate:="",
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
Password="""";

etc....


"Hmmm, it worked when I tested it....

 

[earthandfire]

I'm not sure what difference this would make but you could try changing:

"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb; _
"Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" _

to

"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=F:\bsn-test\Test of AccessCustomerDatabaseXA.mdb;" & _
"Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Typ" _

 

[TonyJollans]

Hi kjspear,

I believe the Connection String can be a maximum of 255 characters - and yours is 260. Could you rename your database to be a little shorter? (or put it in the root directory so the path name is shorter?)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[kjspear]

Hello,

Yes, I've shortend the string by eliminating some code as per Trevil and the others. It appears to be working fine now.

Thanks Everyone.