Mailmerge Access 2003 frontend with SQLExpress

[jwbrooks99]

I have been battling to automate a word mailmerge using SQLExpress. I cannot figure out what the path should be to the mailmerge datasource. I am completely new to VBA. I come from a legacy procedural environment and will really appreciate a detailed explanation on the topic. I managed to come up with the following code so far.

[FONT=Arial][SIZE=2][FONT=Arial]Dim objword As  Object[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   Dim objdoc As  Object[/FONT][/SIZE][/FONT]
  
 
 [FONT=Arial][SIZE=2][FONT=Arial]   Set objword  =CreateObject("Word.Application")[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   Set objdoc  =objword.Documents.Open(strInputFileName)[/FONT][/SIZE][/FONT]
 
 [FONT=Arial][SIZE=2][FONT=Arial]   Dim strDBPath As  String[/FONT][/SIZE][/FONT]
 [B][B][FONT=Arial][SIZE=2][COLOR=red][COLOR=red][FONT=Arial]   strDBPath =  xxxxxxxxxxxxxxxx[/FONT][/COLOR][/COLOR][/SIZE][/FONT][/B][/B]
 [FONT=Arial][SIZE=2][FONT=Arial]   Dim strTableName As  String[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   strTableName="recordslettersvw"[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   MsgBox strDBPath[/FONT][/SIZE][/FONT]
 
 
 [FONT=Arial][SIZE=2][FONT=Arial]   Withobjdoc.MailMerge[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   'SetMerge Data  Source[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   [COLOR=red][COLOR=red]objdoc[B][B][FONT=Arial][FONT=Arial].MailMerge.OpenDataSourcename:=strDBPath,_[/FONT][/FONT][/B][/B][/COLOR][/COLOR][/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   LinktoSource:=True,_[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   Connection:="TABLE"&  strTableName,  _[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   SQLStatement:="SELECT* FROM"  &  strTableName[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   .Destination=  wdSendToNewDocument[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   .Execute[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   objword.ActiveDocument.PrintOutFalse[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   objword.ActiveDocument.ClosewdDoNotSaveChanges[/FONT][/SIZE][/FONT]
 [FONT=Arial][SIZE=2][FONT=Arial]   EndWith[/FONT][/SIZE][/FONT]

 

[HiTechCoach]

I have found that this example works with any back end:

Super Easy Word Merge
*** you will need to scroll down the page to find it.

 

[jwbrooks99]

Thank you for the example. I have imported the modules and tried it. I get an error with the function GetAppDir(), strDB = CurrentDB.name. The error reads Run time error '91', Object variable or With block variable not set. Any ideas how to rectify? The process is great and I would really like to get it right in the application

 

[HiTechCoach]

Are you using an MDB or ADP?

 

[jwbrooks99]

I am using ADP

 

[LPurvis]

You might struggle a little with Albert's code as it is very MDB (or ACCDB) oriented.
(Your example problem with CurrentDb being just the start - not only does that not return a valid object in ADPs but the recordsets are all DAO ones too).
You could certainly go through his example ADP/ADO adapting it - but you'd need to know how to do that.

If you're doing single row merges - then you can leave actual Mailmerge behind as such with other Word automation examples such as:
http://www.utteraccess.com/forums/showflat.php?Cat=&Board=89&Number=1603409
or
http://support.microsoft.com/default.aspx/kb/285176
(which isn't as helpful as it sounds lol)

But using an ADP, any non-mailmerge solution will inevitably feature ADO coding.

I personally prefer not to have Word accessing the SQL Server directly for the mailmerge (I've no problem with Word code hitting SQL Server in general - it has as much right to as any application, but mailmerge isn't an object we have all that much control over once it's initiated).

 

[jwbrooks99]

Thank you Leigh. As mentioned still new at this and the topic is a bit over my head. Will never the less take a crack at it