How to stop source prompt in mail merge from access

[mvb]

When starting a mail merge from access to word 2003, word prompts for
location of the data source every time even though the merge has been saved.
This behaviour is new with word 2003 and did not occur with word 2000.
Microsoft acknowledged the problem and provided an update (885832 & 887979)
but this didn't have any effect. Any suggestions???
Thanks.

 

[Peter Jamieson]

If nothing else works, you can try:
a. changing the method Word uses to connect to Access (check Word
Tools|Options|General|Confirm conversions at open, and go through the
connection process again). In Word 2003, the default method is OLEDB, but
you should be able to choose DDE (the old method) or ODBC.
b. setting up an appropriate .odc or .udl file and opening that instead -
you may need that if your database is secured via workgroup security. If (a)
doesn't work and you need to use (b), I'll try to explain more.

Peter Jamieson

 

[mvb]

No luck with a. I'm afraid. The merge does work when the pop-up asking for
data source location is cancelled. The link is in DDE and it goes ahead ok -
it's just a nuisance having to cancel the pop-up each time the merge is run.
What does method b. involve?

Mvb

 

[Peter Jamieson]

What does the pop-up say?

Peter Jamieson

No luck with a. I'm afraid. The merge does work when the pop-up asking
for
data source location is cancelled. The link is in DDE and it goes ahead
ok -
it's just a nuisance having to cancel the pop-up each time the merge is
run.
What does method b. involve?

Mvb

 

[mvb]

The mail merge is being called from the Access database via a Visual Basic
function. The message that comes up is:
Data Link Properties
Connection
Specify the following to connect to Access Data
1. Select or enter a database name
(default to open db)
2. Enter information to log onto the database
User Name: Admin

When Cancel is selected a second page pops up called
Login
Data Source MS Access Database

When Cancel is selected the mail merge proceeds using DDE.

Any ideas?

 

[Peter Jamieson]

Are you calling OpenDataSource in your Access code? If not, it would
probably be a good idea to do so, but you will need to remove any existing
connection from the Word mail merge main document because Word will try to
open the existing connection before any OpenDataSource method call can
execute (whether it is in Access or Word). Removing the existing connection
will lose any sort/filter options but all the field codes etc. remain
intact.

If so, you need to use something along the following lines:

Dim sConnaection As String
Dim sName As String
Dim sSQL As String

sName:="the full path name of your Access database"

sConnection="TABLE tablename"
' or
' sConnection = "QUERY queryname"

' If you need to sort or filter the data, use ORDER BY
' and/or WHERE clauses in here
sSQL = "SELECT * FROM [table or query name]"

oWordDocumentObject.OpenDataSource _
Name:=sName, _
Connection::=sConnection, _
SQLStatement:=sSQL, _
Subtype:=wdMergeSubtypeWord2000

NB if you don't add a reference to the Word object library in the Access VBA
editor, you will need to use

Subtype:=8

Peter Jamieson

 

[mvb]

Peter ;
Thanks for your help!

Due to holidays the issue wasn't reviwed until this week.

Adding "Subtype:=wdMergeSubtypeWord2000" worked in suppressing the linking
dialog box.

However...I am now receiving the message: "Opening this document will run
the following SQL command: Select from querynnn. Data from your database
will be placed in the document. Do you want to continue?" on some, but not
all, of the mail merge routines.

Any further suggestions?

Thanks again

Marc

Are you calling OpenDataSource in your Access code? If not, it would
probably be a good idea to do so, but you will need to remove any existing
connection from the Word mail merge main document because Word will try to
open the existing connection before any OpenDataSource method call can
execute (whether it is in Access or Word). Removing the existing connection
will lose any sort/filter options but all the field codes etc. remain
intact.

If so, you need to use something along the following lines:

Dim sConnaection As String
Dim sName As String
Dim sSQL As String

sName:="the full path name of your Access database"

sConnection="TABLE tablename"
' or
' sConnection = "QUERY queryname"

' If you need to sort or filter the data, use ORDER BY
' and/or WHERE clauses in here
sSQL = "SELECT * FROM [table or query name]"

oWordDocumentObject.OpenDataSource _
Name:=sName, _
Connection::=sConnection, _
SQLStatement:=sSQL, _
Subtype:=wdMergeSubtypeWord2000

NB if you don't add a reference to the Word object library in the Access VBA
editor, you will need to use

Subtype:=8

Peter Jamieson

The mail merge is being called from the Access database via a Visual Basic
function. The message that comes up is:
Data Link Properties
Connection
Specify the following to connect to Access Data
1. Select or enter a database name
(default to open db)
2. Enter information to log onto the database
User Name: Admin

When Cancel is selected a second page pops up called
Login
Data Source MS Access Database

When Cancel is selected the mail merge proceeds using DDE.

Any ideas?