VBA string too long

[pnormington]

I'm writing a macro in Word2000 to extract text from an Access2000
database. The following code gives an error "String longer than 255
characters". Any way around?

Selection.Range.InsertDatabase Connection:="DSN=MS Access
Database;DBQ=C:\JPN\exopubdb_be.mdb;", _
SQLStatement:="SELECT Countrys.Country, Countrys.Visa FROM `C:\JPN
\exopubdb_be`.Holidays Holidays INNER JOIN (Countrys INNER JOIN
HolidayCountrys ON Countrys.Country = HolidayCountrys.Country) ON
Holidays.HolidayID = HolidayCountrys.HolidayID WHERE
(Holidays.HolidayID)=('" & x & "')";

 

[John W. Vinson]

I'm writing a macro in Word2000 to extract text from an Access2000
database. The following code gives an error "String longer than 255
characters". Any way around?

I suspect it's an error in your quotemarks.

SQLStatement:="SELECT Countrys.Country, Countrys.Visa FROM `C:\JPN
\exopubdb_be`.Holidays Holidays INNER JOIN (Countrys INNER JOIN
HolidayCountrys ON Countrys.Country = HolidayCountrys.Country) ON
Holidays.HolidayID = HolidayCountrys.HolidayID WHERE
(Holidays.HolidayID)=('" & x & "')";

For one thing you have the funky backquotes around the database name, and no
extension on the filename. I'm not sure what your x variable contains - is
HolidayID a Text field, and does x contain a valid value for it? Why are you
referencing an external database file rather than just linking to the backend?

 

[pnormington]

I suspect it's an error in your quotemarks.

 SQLStatement:="SELECT Countrys.Country, Countrys.Visa FROM `C:\JPN
\exopubdb_be`.Holidays Holidays INNER JOIN (Countrys INNER JOIN
HolidayCountrys ON Countrys.Country = HolidayCountrys.Country) ON
Holidays.HolidayID = HolidayCountrys.HolidayID WHERE
(Holidays.HolidayID)=('" & x & "')";

For one thing you have the funky backquotes around the database name, andno
extension on the filename. I'm not sure what your x variable contains - is
HolidayID a Text field, and does x contain a valid value for it? Why are you
referencing an external database file rather than just linking to the backend?

Thanks - working now. Much of this code was inherited (including the
spelling of countries) and I never looked into it in detail.