Can I execute an Access query each time a Word doc is opened?

[Jeff K]

I am trying to insert a 'quote of the day' concept into a Word document that
will change each time the document is opened.

The 'quote of the day' is retrieved from an Access Database and inserted
into the document header.

Right now, I need to manually re-execute the query in order to get a new
quote in each time I open the document.

Is there a way I can set this up to execute the Access query each time the
document is opened?

-JK

 

[Jay Freedman]

I am trying to insert a 'quote of the day' concept into a Word
document that will change each time the document is opened.

The 'quote of the day' is retrieved from an Access Database and
inserted into the document header.

Right now, I need to manually re-execute the query in order to get a
new quote in each time I open the document.

Is there a way I can set this up to execute the Access query each
time the document is opened?

-JK

See http://word.mvps.org/FAQs/InterDev/GetDataFromDB.htm.

Be sure to put this code only in the template used to create this particular
type of document. You don't want it to run when *every* document is opened.

Also, the sample code in the article doesn't contain any error-trapping. For
a template that you're going to allow anyone else to use, you have to be
prepared for the likely situations that (a) the database won't open, (b) the
database opens but it doesn't contain the table named in the code, or (c)
the field names don't exist in the table, as well as any less likely
problems. See the VBA help for the On Error statement.

 

[Jeff K]

Outstanding! I knew I couldn't be attempting to do something that's never
been done before.

Thanks for your help, Jay!

-JK

 

[Daniel McCarty]

Is there a way I can set this up to execute the Access query each time the
document is opened?

In addition to Jay's answer, you can put the code in Document_Open(),
which is one of the pre-defined functions that will run whenever the
document is opened. If you save the document as a template, creating
new documents based on the template will trigger the procedure and put
your quote of the day in the new document.

-Dan.

 

[Jay Freedman]

In addition to Jay's answer, you can put the code in Document_Open(),
which is one of the pre-defined functions that will run whenever the
document is opened. If you save the document as a template, creating
new documents based on the template will trigger the procedure and put
your quote of the day in the new document.

-Dan.

A slight adjustment: Document_Open() runs each time you open a
document that's already been saved at least once. The procedure
Document_New() runs when you create a new document based on the
template containing the code.

You probably want to put the code that reads the quote from Access
into a third procedure, which you call from both Document_New() and
Document_Open() so it runs in both situations.

See http://www.word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm for
more info on all this.