Mail Merge - Data Source

[Mile-O]

I've been trying to set up a Mail Merge between Access 97 and Word 97.

I've followed the wizard through and it would appear that the Word template is primed and ready to accept data.

I've used the information in Article Q159328 to try and automate the passing of a query to the template but have come across the problem that:

Runtime Error '5922'

Word was unable to open the data source.

Does anyone have any ideas?

 

[ptaylor-west]

I had a similar problem in XP and was advised of the following:


The error occurs because you don't choose the connection type as ODBC when you create the merge in Word. When you select the database to merge to there is a small checkbox under the Open Button with the text 'Select Method'. Click this when you re-establish the merge, this should present you with a number of connection methods, choose ODBC and proceed.


Hope this helps.

 

[Mile-O]

Nice idea, hadn't noticed that but...going the ODBC method only presents me with tables - I need a query to merge.

 

[ptaylor-west]

Aha,

Sorry I should have given you more info... the rest of the dialogue I had was as follows:

You need to make the query type a 'Make-Table Query'. This creates and overwrites the table it creates each time you use it. So by using 'On Click' on the command button (or label) to run the query and leaving the hyperlink in (using ODBC in the Word doc) it works a treat.

The only thing I get using this is that is creates 2 warning messages... "you are going to overwrite the *query" etc., but I will work out how to stop these appearing.

To eliminate the error message try wrapping it all up in
code:


Docmd.SetWarnings False
'your code
Docmd.SetWarnings True (always reset to warn)


Put this just after the first line of code.



Alternatively delete the table at the start of the procedure

code:

Docmd.DeleteObject acTable "Whatever"


Another suggestion is to use an append query instead of a make table because using a make table query can cause database bloat because of the deletion and creation of tables leaving residual stuff (to use a technical phrase) behind on each run. The append query I suggest would not do this because you are merely changing the records in an already existing table.

You need to enter the following code so that existing records are deleted, use an on open command

You could use:

Dim strSQL as string

strSQL = "DELETE * FROM YourMergeTable"

Docmd.RunSQL strSQL

Alternatively build a stored query and run that using Docmd.OpenQuery.

Both of these will require that you turn warnings off if you don't want to know how many records are to be deleted.


Hope this was not overkill for you

 

[jon98548]

ptaylor, compacting at close would keep it from bloating I believe.

 

[Mile-O]

The old Make Table was my intial thought after seeing only tables - I'll investiagte further.

Thanks for that.

Hope this was not overkill for you

Na, like reading a favourite book for the nth time!

 

[Mile-O]

Well, I had it simply working through DDE and have it working through ODBC but I want to automate it so that I can click a button and have all the results printed off without the user ever knowing Word had been opened just like the link in my original post.

 

[Mile-O]

Well, I finally got the damn thing working to a degree using the DDE method.

Only problem is that it seems to open another instance of the database but I see there's an article about this on KNowledge Base.

Thanks for your input,

Mile

 

[mission2java_78]

Well, I finally got the damn thing working to a degree using the DDE method.

Only problem is that it seems to open another instance of the database but I see there's an article about this on KNowledge Base.

Thanks for your input,

Mile

Why dont you populate the word document using bookmarks...and using the record data to fill those bookmarks? That is what I do...all through vb \ vba without even looking at word =)

Jon

 

[pcEars]

Mile AND mission2java,

I'd love to see your final results, as I've put a similar task on the back burner for quite awhile now. Any chance of posting what you've got that works?

Thanks