Catalog Merge v Copy and Paste

[ScriptHam]

Hi

I need to get 2 sets of about 50 records with about 8 fields from Access into Word, one following the other and as landscape in an otherwise portrait document. I will need to do this once every month. Some but not much of the data may need review and adjustment. So that the Word doc may read a little different from the Access data.

I think that the options are:

Catalog merge (Which appears to be Publisher only and then is appended to the end of the document only, we don't have publisher)
Cut and paste from a query into Word
Cut and Paste from a report into Word
Cut and paste from query to Sheet, adjust, then to Word
Cut and paste from report to Sheet, adjust, then to Word.I have a few questions which I hope you might help me with.

Are there any others please that are commonly used?
Am I right about the catalog merge?
What offers the best scope for office automation do you think?

I look forward to hearing from you.
Many thanks.
ScriptHam

[TonyJollans]

To be perfectly honest for two small recordsets a month, which also need manual reviewing it isn't worth spending very much time on automation.

That said, a catalog merge should work in Word (what version are you using?) but it might be easier to write a bit of code which reads the record sets directly (via ADO) and writes (or rewrites) the data into a document.

How is the (otherwise portrait) document created? Is it new every month? Is it already automated or would you be automating any of it along with the database stuff? Could its template include an empty landcsape section in the middle ready to receive the Access data? Could you post a sample at all (document and database)?

[ScriptHam]

Hi Tony

Thanks for your reply.

Word is version 2003 (11.5604.5606)
Access is version 2003 (11.5614.5606)

The document was created several years ago. In general it evolves very slowly except for the merge data which changes at a rate of about 10%/month. So there are 2 pages wordprocessed then 8 pages of database data then 3 pages wordprocessed. Currently there is no automation and the only automation planned is this catalog merge.

I am sure that an empty landscape section could be created. After all I could add the new data after last months and then delete last months; thinking aloud.

I could post the WP doc. Regarding the database that is tricky, the data comes from several databases with tables linked and queried. What I could do is make a table from the query. Would that help? I guess attachments such as this have to be zipped up.

Regarding code. I have taken DAO written by others and modified it to my own requirements on a couple of occasions. I have a copy of "DAO Object Model" by O'Reilly. I have never written ADO. This brings me to another point.

Last night I saw this http://www.uk-open-learning.com/CartV3/Details.asp?ProductID=352. Now you are in the Professional Office Developers Association. Do you have reservations about distance learning packages which deal with ADO and DAO and Access development? Have any members found any to be very good or very bad?

Again Many thanks for your response.
ScriptHam

[TonyJollans]

Hi ScriptHam,

I am most terribly sorry. I took your question about training to PODA and then forgot to come back here.

Personally I know nothing of this training although I'm inclined to think it expensive. I got some good feedback on the author but none on the course itself. PODA are currently looking into training courses so there may be more information in the future.

As to your problem .. I am attaching a simple example. I did it in Office 2000 so you may need to change the references to use it in 2003. Obviously you'll need to replace the query with your own. If you have any further questions on it I will try to answer more quickly.

[TonyJollans]

I should say that for a relatively straightforward task like this you can use DAO instead of ADO if you want and are more comfortable with it. I don't do much with Access these days but I had got into the habit of using ADO because it always seemed unclear just how much longer DAO would be supported.

[ScriptHam]

Hi Tony

Thanks for the code, the examples you sent run nicely in Word 2003. Thanks very much. Don't worry about the delay at all, apart from anything else it is the holiday season.

I should be able to integrate this into my system without problem.
Thanks again.
Scriptham.