Re: Data Source Lost When Merge From Access to Word
- From: "Peter Jamieson" <pjj@xxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 25 Oct 2007 15:31:02 +0100
In Word 97/2000, DDE was the default connection method and ODBC was the alternative as far as Access/Jet sources were concerned.
In Word 2003, OLE DB is the default, but unfortunately when you have selected your database, Word does not display the Access tables that are linked to ODBC data sources (which is what you would have even if your Access database is Access 2000 format or later). It isn't that OLE DB cannot "see" the tables: it can, but Word seems to ignore them.
So what can you do? Well, assuming there are no additional problems related to the fact that it's an Access 97 format database rather than (say) Access 2000 format, you can
a. check Word Tools|Options|General|Confirm conversions at open, go through the connection process again, and select either a DDE or ODBC connection from the additional dialog box that's displayed. If you chose ODBC, you will need to check that Word has selected the correct .mdb (it's impossible to see if the pathname is long), click Options... then select all the boxes. You should see the list of linked tables. Or
b. create one query in your Access database for each linked table you want to use, with SELECT * FROM [thattablename], and use that as the data source (which I think you are probably already doing). Or
c. connect using Word VBA and the OpenDataSource method. All you really need in this case is
Sub ConnectToAccess()
ActiveDocument.MailMerge.OpenDataSource _
Name:="the full path name of your .mdb", _
SQLStatement:= "SELECT * FROM [theAccesstablename]"
End Sub
or
d. create a .odc file for each linked table and use those when you select your data source. (in Word's "Select Data Source" dialog box
- click New Source
- Other/Advanced
- select the Microsoft Jet 4.0 OLE DB Provider. Click "Next"
- enter the full path name of the .mdb. Click Yes (test the connection if you want)
- you should then see a list of tables. Select the one you want, then name and save the .odc file
- Word then prompts for a data source - select the .odc you just created, and select the OLE DB Databases connection method if word prompts you for that.
or
e. connect directly to your SQL database (typically you also have to create a .odc for that cf. point (d) above, but using the appropriate provider instead of the Microsoft Jet one.
The different ways of connecting have their advantages and disadvatages so find out whether there are any importnat things you cannot do (e.g. you might not be able to sort/filter or edit data source records in Word, and so on).
--
Peter Jamieson
http://tips.pjmsn.me.uk
"Sharon L." <Sharon L.@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:BF338091-2A52-4588-BE50-BC3A38868AFA@xxxxxxxxxxxxxxxx
I am also using an Access 97 database as the data source for a Word mail
merge. The data originally comes from SQL tables using ODBC links. This
currently works fine when I use Word 2000 but when I use Word 2003 I get
"Word was unable to open the data source" when I try to connect to a
database query. It does seem to work if I create an Access temp table from my
queries. I have hundreds of different queries in different Access 97
databases linked to Word (2000) documents. I don't see any option to select a
DDE link instead of ODBC.
.
- Prev by Date: Re: Black blobs in my letter
- Next by Date: Re: make a merge field go to next merge field if prior one is blank?
- Previous by thread: Re: Black blobs in my letter
- Next by thread: Re: make a merge field go to next merge field if prior one is blank?
- Index(es):
Relevant Pages
|
