Mail Merge from Access 2002 worked, but not any more

[Winston Abernathy]

Some quick background: Office 2002. WinXP. MS Access
Frontend composed of Forms, reports, queries and a few
tables. Lots of VBA. Separate back end DB composed of
only tables. Mail merge to MSWord worked fine up until
recently. Major change to system is that I created new
backend db and exported all tables to the new backend.
Mail merge stopped working. (not sure of the cause and
effect relationship, but I took the old back end (which
works with Mail merge) and created a new backend (by
removing all tables, compacting and then re-exported
tables to the new backend db - problem repeats so there
is probably a relationship)... Backend has no vba,
however I made all the vba references the same in the
new backend db. Also tried exporting to brand new db.
Same problem. Access Error message as follows:
"Error 4065: The method or property is not available
becuase the current mail merge document needs a data
source."

Just a few more quick comments... because I exported all
the tables to a new db, this isn't a simple question of a
missing table. Also as there is no vba in the backend
db (and I checked the references anyway) that is not a
problem. I built the backend db about 18 months ago so
if I added something to the db specifically for mail
merges (like a reference) I just can't recall what it
might be. Again, the frontend code works fine with the
older backend, but something is different in the new
backend.
A few final comments. When I try to re-link the
word .dot, I am unable to see the data source from Access
in the non-working db, but can in the working db
(obviously as it works)

Anyone got any ideas?

thanks again
Winston Abernathy

 

[Peter Jamieson]

The error message you are getting is saying that the Word document whose
mailmerge object you are trying to .Execute does not have a data source
attached. But are you attaching the data source in code using the
OpenDataSource method (and if so, could you post the code here?) or are you
setting it up manually in Word (i.e. so no OpenDataSource should be needed)?

Also, which connection method are you using to connect to your data?

 

[Guest]

I am using the Jet connection. I eventually got it to
work by copying the db, deleting all but the mail merge
table and then exporting all other tables to the new db.
Not sure why it works, but when I started from a fresh db
it just wouldn't work. Perhaps the Word to Access
interface uses some sort of table numbering control
rather than table name. This may be an oversimplified
perception. Here is the code (by the way, it had the
same problem when I did it manually in Word):

With gobjWord
' Make Word visible
.Visible = True
' Open the word document
.Documents.Open "Z:\TheBackEndDB\MailMergeDocuments\"
& strMailMergeLetterName
' Give the document time to open
DoEvents
' Use the MailMerge method to perform a mail merge
With gobjWord.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With
' Send the result to the print preview window
.ActiveDocument.PrintPreview 'Preview
' Make Word visible
.Visible = True
End With
Really appreciate your insight.

Winston

-----Original Message-----
The error message you are getting is saying that the Word document whose
mailmerge object you are trying to .Execute does not have a data source
attached. But are you attaching the data source in code using the
OpenDataSource method (and if so, could you post the code here?) or are you
setting it up manually in Word (i.e. so no

OpenDataSource should be needed)?

 

[Peter Jamieson]

First, from your description I'd suspect that you secured the tables in some
way when you first did this and that the security information is being
carried across if you copy but not if you create tables from scratch.

As for specific points...

I am using the Jet connection.

OK, not sure what you mean by this - did it involve creating a .odc file? I
was thinking of "DDE", "ODBC", "OLEDB" connection types.

Perhaps the Word to Access
interface uses some sort of table numbering control
rather than table name.

Nothing I've ever seen would lead me to this hypothesis - as far as I know
it just uses a combination of the following:
a. the database (.mdb) file name
b. a connection string, whose contents vary depending on the type of
connection
c. an SQL query

(b) and (c) may be embedded in Word or specified inside a .odc.

You may be able to see what Word is using for (b) and (c) are by printing
the values of

gobjWord.ActiveDocument.MailMerge.DataSource.ConnectString

and

gobjWord.ActiveDocument.MailMerge.DataSource.QueryString


The code you provide doesn't have an OpenDataSource call in it so the
database connection must have been specified beforehand in Word. FWIW as a
matter of sound programming practice I would probably use something more
like

Set objDoc = .Documents.Open("Z:\TheBackEndDB\MailMergeDocuments\" &
strMailMergeLetterName)

then

With objDoc.MailMerge

etc.

 

[Winston Abernathy]

Thanks... I will have to do a bit of work to check out
your suggestions. They sound quite probable.
Thanks again.
Winston Abernathy

-----Original Message-----
First, from your description I'd suspect that you secured the tables in some
way when you first did this and that the security information is being
carried across if you copy but not if you create tables from scratch.

As for specific points...


OK, not sure what you mean by this - did it involve creating a .odc file? I
was thinking of "DDE", "ODBC", "OLEDB" connection types.


Nothing I've ever seen would lead me to this hypothesis - as far as I know
it just uses a combination of the following:
a. the database (.mdb) file name
b. a connection string, whose contents vary depending on the type of
connection
c. an SQL query

(b) and (c) may be embedded in Word or specified inside a .odc.

You may be able to see what Word is using for (b) and (c) are by printing
the values of

gobjWord.ActiveDocument.MailMerge.DataSource.ConnectStrin g

and

gobjWord.ActiveDocument.MailMerge.DataSource.QueryString


The code you provide doesn't have an OpenDataSource call in it so the
database connection must have been specified beforehand in Word. FWIW as a
matter of sound programming practice I would probably use something more
like

Set objDoc = .Documents.Open

("Z:\TheBackEndDB\MailMergeDocuments\" &