Solved

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

Posted on 2009-03-30
12
328 Views
Last Modified: 2012-05-06
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
Comment
Question by:CSE-User
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017579
Hi CSE-User,

The Data Source has been saved in the Word documnet


Gary
0
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017590
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
 

Author Comment

by:CSE-User
ID: 24017661
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017672
So you"ll need to manually reconnect the Word Docs
0
 

Author Comment

by:CSE-User
ID: 24017679
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
 

Author Comment

by:CSE-User
ID: 24017722
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017729
Sorry I've lost you
0
 

Author Comment

by:CSE-User
ID: 24017788
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017828
You might find life easier if you output your query to excel & link the Word to Excel
0
 

Author Comment

by:CSE-User
ID: 24017857
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
 
LVL 46

Expert Comment

by:tbsgadi
ID: 24017884
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
 

Accepted Solution

by:
CSE-User earned 0 total points
ID: 24018910
I'm removing the mail merge source from the documents and using VBA to populate the information.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question