I suspect you're going to have to use some VBA.
The problem I had earlier was that I could make an ODBC connection, but
then Word would not re-open it (it didn't even issue the usual "SQL"
prompt) when you saved/closed/re-opened your mail merge main document.
So I couldn't be sure what connection string Word was actually using.
However, the reason why Word will not re-open is typically because it
expects the data source to have a "file name", and when you open using a
machine ODBC DSN (i.e. user or system DSN), there is no file name. it is
possible that a similar problem is preventing Word from seeing your data
source correctly. This used to be OK in previous versions of Word but no
longer works in Word 2007 - I think it's actually an accidental change
rather than a deliberate attempt to scupper ODBC connections.
So I tried using a file DSN. Unfortunately, Word doesn't really like
them either so I don't think you can open them purely using the standard
Word user interface.
However, you do seem to be able to open using a file DSN using Word VBA
and an OpenDataSource statement. And in fact the "file DSN" doesn't even
need to be a real .dsn file. It can be a completely empty file that you
create in Notepad. You can provide completely different connection
information in the connection parameter. But you have to know what that
connection information should be.
For example, here's the VBA I was using with an empty excel.dsn
Sub ConnectViaEmptyFileDSN()
With ActiveDocument.MailMerge
' the following statement disconnects
' any existing source
.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:="C:\Users\username\Documents\My Data Sources\excel.dsn", _
connection:="DSN=Excel
Files;DBQ=C:\mywbs\test.xls;DriverId=1046;MaxBuffe rSize=2048;PageTimeout=5;",
_
sqlstatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeWord2000
End With
End Sub
In the above,
a. the Name parameter is set to the pathname of the (empty) .dsn
b. the connection string references a /user/ DSN (not a file DSN)
called "Excel Files". You presumably have a DSN that you use to connect
to Sage, and you would use that name instead. The rest of the connection
string contains values that would either typically be stored in the User
DSN anyway, such as MaxBufferSize, or values that are unique to this
connection - in this case
DBQ specifies the path name of the .xls file.
c. In your case, I don't know what would need to go in the
sqlstatement parameter, but it would probably be a simple "SELECT * FROM
[something]"
Alternatively, if you have a File DSN for your data source, you should
be able to do:
Sub ConnectViaCompleteFileDSN()
With ActiveDocument.MailMerge
' the following statement disconnects
' any existing source
.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:="C:\Users\username\Documents\My Data Sources\excel.dsn", _
connection:="FILEDSN=C:\Users\username\Documents\M y Data
Sources\excel.dsn", _
sqlstatement:="SELECT * FROM `Sheet1$`", _
SubType:=wdMergeSubTypeWord2000
End With
End Sub
In other words, the same file is referenced by the Name parameter and
the FILEDSN keyword.
There are other "mix 'n' match" possibilities I think.
Not very nice but once you have set up your data source, Word /may/
reconnect to it when it re-opens the mail merge main document, and that
may be worth having.
If it works at all for /your/ data source, that is, and unfortunately as
I don't have Sage here I can't experiment.
Peter Jamieson
http://tips.pjmsn.me.uk
JWhitehead wrote:
1. The data source is w/in sage (our finance program).
2. Word 2007 w/ SP2
3. Windows Vista, 32-bit
If I pull the data into either Excel or Access, and then create the mail
merge in word using that as the data source it works no troubles. Just can't
seem to get it to work with the ODBC.
"Peter Jamieson" wrote:
1. What is the data source?
2. Do you have Word 2007 SP2, or an earlier version of Word 2007?
3. Are you using Vista-64 bit, or perhaps another 64-bit version of Windows?
As far as point (2) is concerned, currently I only have Word 2007 Sp2 to
experiment with, and when I attempted an ODBC connection with Excel,
although the connection appeared to work fine, subsequent investigation
suggests that although it is an ODBC connection, Word is not going to
tell us what the connection string is.
Peter Jamieson
http://tips.pjmsn.me.uk
JWhitehead wrote:
I am trying to do a mail merge in MS Word 2007 from an ODBC source. I am able
to find the option to do so. I go through this, but when I get the end, it
tells me there is errors with the merge, and no data is showing from the
merge.
If I import the data from ODBC to Access, for example, it shows all 450
records correctly. I can then do the mail merge in Word from there. The only
thing is, if the data that I've imported into Access changes in the original
table(s), other than re-importing, how would I get it to show the updated
data?
I guess what I'm asking here...
Is there a way to do a mail merge in MS Word 2007 to an ODBC source, and
actually be able to view the records?