Word Merge Using Access Database -2013

[hlewton]

Is there an easy way to change the database associated with a mail merge when using Word and Access 2013. I found a way by moving the associated database to a different folder then directing the Word merge to the database query I want to use but if there is a better or easier way to change the association I'd appreciate knowing it.

Thanks.

[HansV]

You can set the data source using VBA if you wish, e.g.

Code: Select all

    Dim strDatabase As String
    Dim strSQL As String
    strDatabase = "C:\Access\MyDatabase.accdb"
    strSQL = "SELECT * FROM [tblEmployees]"
    ActiveDocument.MailMerge.OpenDataSource _
        Name:=strDatabase, _
        LinkToSource:=True, _
        Connection:="Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;" & _
        "Data Source=" & strDatabase & ";", _
        SQLStatement:=strSQL, _
        SubType:=wdMergeSubTypeAccess

You only need to change the values of strDatabase and/or strSQL.

[hlewton]

Thanks Hans. I am not that familiar with VBA so I think I'll just move the databases and redirect the merge that way so I don't mess anything up.

[HansV]

I don't think there is an easier way of doing it interactively.

[BenCasey]

I would normally have a routine to export the selected tables/query as a xlsx, overwriting the existing one and always have the mailmerge directed to that folder/file.

Just make sure that the export routine always exports to a local drive in case other users want to run the same function/export at the same time. If you export to a networked drive then everybody will be stamping on the same exported xlsx at the same time.

But there are so many ways to do this.

[hlewton]

I would normally have a routine to export the selected tables/query as a xlsx, overwriting the existing one and always have the mailmerge directed to that folder/file.

Just make sure that the export routine always exports to a local drive in case other users want to run the same function/export at the same time. If you export to a networked drive then everybody will be stamping on the same exported xlsx at the same time.

But there are so many ways to do this.

Just a little explanation. These files and merges I have, I created quite a few years ago I believe with Office 95 and Windows 95 but it may have been Office 97 and Windows 98. In any case it was 15 or 16 years ago. Back then even when I put the files on a different computer all my merges worked, I believe because all the computers had the same "My Documents" folder. Now each computer seems to have a slightly different path to the folder even though they still are "My Documents" at least on my machines they are. But since they now include other identifying characteristics in the path to end up in "My Documents" my merges do not find the database source because the path is no longer identical from computer to computer. I use Access database queries as the data source form my merges and usually when I put the files on a new computer I direct the merges to the correct database. This time I directed them to, although a good database query, it was not the database I wanted to use. So after moving the incorrect database to another folder where the Word merge could not find them I redirected the merge to the correct Access database and query I wanted to use. Actually that was pretty easy and only took about 4 or 5 minutes to make the changes to 5 merges.

I really do not have any network drives but I try to keep identical files on a couple machines. The merge files all end up with the same information in them but I must remember to direct them to the correct file the first time I use them on a new machine. After the first time opening them, they work fine.

Thanks for your input and I hope what I have said makes sense.

[HansV]

You could create a folder named Access directly on the C: drive of each computer instead of in My Documents. The path C:\Access\ would be the same on all computers, so the merge would work without having to change the data source.

[hlewton]

You could create a folder named Access directly on the C: drive of each computer instead of in My Documents. The path C:\Access\ would be the same on all computers, so the merge would work without having to change the data source.

That is a great idea. I think I may do that when I have the time to make the changes to 3 computers and probably 20 or more merges.

[hlewton]

Following Hans’s suggestion I did a lot of editing to my Access and merge files today to get them all in one folder and to allow my merges to work no matter what machine I may have to put them on in the future. In doing this I moved every database I could find to that Access folder and then opened them to make sure all was well. There were still some from Access 97 and they would not open to be converted to the newer versions of Access. I got the error seen in the attachment. I am not sure what I should have done with this error and that is what I am asking.

However, I believe I did find a way around it. I fired up an old computer still running Access 2003 and converted those databases to 2002 and 2003 Access files. I was then able to open them in Access 2010 and 2013 without getting that error dialog box.

Still I am curious had I not had any machines running Office 2003 what should I have done to get those couple Access 97 files to run under the newer versions of Access?

AccessError.jpg

[HansV]

Access 2013 can't convert Access 97 databases; Access 2010 and earlier versions are able to do that. So you do need an older version to convert Access 97 databases to a later format.

[hlewton]

Thanks Hans. The attachment and the computer I was using trying to convert that Access 97 file was Access 2010. I guess it may have converted it but it was not in a usable form for me as far as the macros contained in it being able to function. The only thing I saw that I could open manually were the tables and queries. I just tried it again and upon reading the first dialog box more carefully it did say to use an older version of Access to open it to make changes if I didn't convert it. See the attachment. I think I did convert it because it saved a file with the same name but with an "accbb" file extension. There was an "Enable Macros" button but pressing it did nothing. Should converted that file have worked sine I was using Access 2010 or could I have possible done something wrong in trying to convert it? I really didn't see any choices I could have changed except possibly the file's name.

AccessFirstError.jpg

[HansV]

Access 2010 can convert Access 97 databases to Access 2000 or 2002/2003 format (.mdb), but as you have found, converting to the newer Access 2007/2010 (.accdb) format only converts tables and queries.
You can do the following:
- Start Access 2010.
- Select File > Options.
- Select Access 2000 or Access 2002/2003 as default file format for new databases.

S0848.png

- Click OK.
- Open an Access 97 database, click Yes to the question whether you want to convert it, then save it as a .mdb database.

[hlewton]

Thanks for that information. I guess the way I did it by opening Access 2003 and converting it that way to Access 2002 & 2003 will be fine since it seems to work including the macros. I may never need to do it again but it is nice to know that with Access 2010 I still have that option.