Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

How do I batch upgrade word mail merge documents to read Access 2007 accdb file?

I'm trying to batch upgrade some word mail merge documents (writtern in word 95) to Word 2003 format (using word 2007) and accessing a upgraded access database which is now in the ACCDB format.

I tried the following combinations of code. Most either report "Cannot find data source" or "Unable to find Q:\Company\Database.mdb".

The problem is I've trippled checked the code and at no point am I giving it the file database.mdb


Set myDoc = Documents.Open(PathToUse & myFile, False, False, False)
        With myDoc.MailMerge
 
sourcename = "Q:\Company\Database\Company.accdb"
 
'conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcename & ";Mode=Read;Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Part"
 'conn = "DSN=MS Access Database;DBQ=" & sourcename & ";DefaultDir=Q:\Company\Database;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;"
'conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sourcename & ";Persist Security Info=False;"
conn = "DSN=MS Access Database;DBQ=" & sourcename & ";FIL=RedISAM;"
 
.OpenDataSource sourcename, , , False, , , , , , , , conn, .DataSource.TableName
.OpenDataSource Name:=sourcename, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
           PasswordDocument:="", PasswordTemplate:="", _
           WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
           Connection:=conn, SQLStatement:=sourcetable, SQLStatement1:="", _
           SubType:= _
           wdMergeSubTypeAccess
 
end with

Open in new window

0
CSE-User
Asked:
CSE-User
  • 6
  • 6
1 Solution
 
tbsgadiCommented:
Hi CSE-User,

The Data Source has been saved in the Word documnet


Gary
0
 
tbsgadiCommented:
CSE-User,

If you create Q:\Company\Database.mdb you might be able to stop some of the errors, but you"ll need to open the docs manually.

Gary
0
 
CSE-UserAuthor Commented:
Gary,

If I create the Database.mdb it connects to that instead, which lists zero tables / queries. And I can't link the queries required into the mdb files as they are all part of the accdb file.

Dom
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tbsgadiCommented:
So you"ll need to manually reconnect the Word Docs
0
 
CSE-UserAuthor Commented:
There's 55 documents, which I didn't write originally and I need to do the upgrade process quickly.

Also how do you manually update the mail merge?
0
 
CSE-UserAuthor Commented:
I've just worked out how to update the source of the mail merge document however the query I need to link to for the first document is missing from the list when I manually try to update the source
0
 
tbsgadiCommented:
Sorry I've lost you
0
 
CSE-UserAuthor Commented:
Ok sorry,

I've opened the word document and it's report it cannot find the data source.
I've selected the new data source which is the ACCDB database.

It's listed all the different tables and some queries in that database, however it has NOT listed the one query that I needed to relink that document. Why is that do you know?

The query in question looks at an open form to determine what data to fetch.
0
 
tbsgadiCommented:
You might find life easier if you output your query to excel & link the Word to Excel
0
 
CSE-UserAuthor Commented:
The required query is still missing if I try to link an Excel 2007 file to the database.

I've read somewhere that it's because it's not connecting via DDE, but DDE only seems to want to connect to mdb / mde databases and not Access 2007 format databases.
0
 
tbsgadiCommented:
Have a look at the following
http://office.microsoft.com/en-us/excel/HP102432671033.aspx
There shouldn't be any problem with 2007 format
0
 
CSE-UserAuthor Commented:
I'm removing the mail merge source from the documents and using VBA to populate the information.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now