Word Automation

[Guest]

Hi

I'm looking to autmate MS Word 2007 from Access 2003 using VBA.

In particular I need to open a document and set the Mail Merge connection.

I'm using:

.....
wdDoc.mailmerge.opendatasource Name:="path to Access database", _
SQLStatement:="SELECT etc. etc."

This works absolutely fine unless the SQL statement is greater than 255
characters. Any SQL statement involving just a couple of Inner Joins is
invariably a lot bigger than 255 characters. Access 2000 used to allow 2 SQL
strings to increase the available size, or a 'connection' parameter to link
to a query in the databse, but these don't seem to be available from Acc2003
onwards.

Anyone have any ideas on how I can use a >255 character SQL statement?

Cheers.

BW

 

[Stuart McCall]

Hi

I'm looking to autmate MS Word 2007 from Access 2003 using VBA.

In particular I need to open a document and set the Mail Merge connection.

I'm using:

....
wdDoc.mailmerge.opendatasource Name:="path to Access database", _
SQLStatement:="SELECT etc. etc."

This works absolutely fine unless the SQL statement is greater than 255
characters. Any SQL statement involving just a couple of Inner Joins is
invariably a lot bigger than 255 characters. Access 2000 used to allow 2
SQL
strings to increase the available size, or a 'connection' parameter to
link
to a query in the databse, but these don't seem to be available from
Acc2003
onwards.

Anyone have any ideas on how I can use a >255 character SQL statement?

Cheers.

BW

You could try making a stored query (say MyQuery) from your SQL, then
selecting from that:

Select MyQuery.* From MyQuery;

Send that through to Word instead.

 

[Guest]

Thanks Stuart, that works perfectly other than ...

.... the first letter of the query gets left behind. In your example I get
an error message telling me the system can't find the yQuery query. If I
change the query name to yQuery it still can't find it. However if I create
two identical queries called MyQuery and yQuery it all works well - bizarre!
Obviously this is an Access/Word glitch which I workaround but have you come
across this before?

Cheers.

BW

 

[Stuart McCall]

Thanks Stuart, that works perfectly other than ...

... the first letter of the query gets left behind. In your example I get
an error message telling me the system can't find the yQuery query. If I
change the query name to yQuery it still can't find it. However if I
create
two identical queries called MyQuery and yQuery it all works well -
bizarre!
Obviously this is an Access/Word glitch which I workaround but have you
come
across this before?

Cheers.

BW

No, I've not seen this behaviour, but another (rather kludgey) workaround
would be to add a character to the beginning of the query name, which can be
"thrown away" :

xMyQuery

No idea if this'll work, but perhaps worth a try. If you end up running with
this it would be a good idea to insert a comment as to why it's being done,
for obvious reasons.