Peter Jamieson said:
Peter Jamieson said:
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it will
lead to a solution, but I have not been given a copy of Word 07 yet to try
out the solution
With this post, I was hoping to find someone who has successfully used a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent post on
another ng that a dbf will not work *at all* with Word 07 mailmerge. He had
suggested the data must be written out from dbf to Access, CSV or Excel to do
the merge. When I prompted him further, the person making the post admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
Peter Jamieson said:
Peter Jamieson said:Hi Markus,
Yes, I thought you might have been waylaid
I'll have another look at the Word 2007 + DBF stuff and see if I can
spot any additional difficulties in doing the merge "from an app".
However, I'm definitely not in a position to check specifically that a
dBase Plus app. can be made to work.
Peter Jamieson
http://tips.pjmsn.me.uk
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it will
lead to a solution, but I have not been given a copy of Word 07 yet to try
out the solution
With this post, I was hoping to find someone who has successfully used a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent post on
another ng that a dbf will not work *at all* with Word 07 mailmerge. He had
suggested the data must be written out from dbf to Access, CSV or Excel to do
the merge. When I prompted him further, the person making the post admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
Peter Jamieson said:
Markus said:Hi Peter,
Not really interested in a DB Plus app, as just whether any app can call
out
to Word 07 using a dbf datasource. I got the feeling from the other ng
that
MS no longer supports a dbf datasource. Hard for me to believe, but
anything
is possible.
Be great if you have first hand knowledge that dbf will work with 07.
Thanks again,
Mark
Peter Jamieson said:Hi Markus,
Yes, I thought you might have been waylaid
I'll have another look at the Word 2007 + DBF stuff and see if I can
spot any additional difficulties in doing the merge "from an app".
However, I'm definitely not in a position to check specifically that a
dBase Plus app. can be made to work.
Peter Jamieson
http://tips.pjmsn.me.uk
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it
will
lead to a solution, but I have not been given a copy of Word 07 yet to
try
out the solution
With this post, I was hoping to find someone who has successfully used
a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent
post on
another ng that a dbf will not work *at all* with Word 07 mailmerge.
He had
suggested the data must be written out from dbf to Access, CSV or Excel
to do
the merge. When I prompted him further, the person making the post
admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful
in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of
dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
:
Markus,
Are you the same Markus who posted in the vba.general group back in
April or so?
If so, this is where we were up to - maybe you could say what happened
at your end. And if not, the following may help:
<<
Thanks again, and I'll post back here after I confirm a solution.
Mark
:
I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or
because the
table name is in the wrong format for the provider. The ODBC
dirver
expects something like
Select * from MM.dbf
(this is what Word generates when you try to connect manually
using
ODBC)
but the OLE DB provider expects something more like
Select * FROM [MM]
or
Select * FROM `MM`
However, both the OLE DB provider and ODBC driver that I have
here (as
it happens, it's the ACE provider/driver) accept any of
Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`
FWIW the error I see here is
"Error has occurred: The Microsoft Access database engine could
not
find
the object 'M.DB'. Make sure the object exists and that you spell
its
name and the path name correctly."
i.e. the first and last characters of the name have been
stripped.
I believe that Word will do (a) because you are specifying the
.dbf
pathname in the Name parameter /and not/ specifying a Subtype
parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I
thought
Word
XP/2003 also did that,but perhaps the behaviour was slightly
different,
or perhaps it used the OLE DB provider in those versions too, but
the
unquoted file name worked in those cases. You can probably find
out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in
Word
2003 (I wouldn't try in Word XP - it may still crash if you do
that). If
the string starts with DSN, it's ODBC; if it starts with
Provider,
it's
OLE DB.
Here, I can get Word 2007 to connect using ODBC either by setting
Name
to "", or by setting it to the pathname of the .dbf /and/
specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think
you had
to specify wdMergeSubtypeWord2000 in all cases where Name was
set to
"", but I think an update modified that behaviour.
If it doesn't matter whether you use the ODBC provider or the OLE
DB
driver, you could see if the dBase equivalent of
.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"
works on all the versions of Word you support after Word 2000.
All the above assumes that you can use the dBASE provider/driver
(which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.
FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit
SP1,
Word 2007 SP1) show that it is using the newer ACE driver.
Modifying it
to use the older Jet driver made no difference to the behaviour
of the
OpenDataSource code I tried here.
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Hi Peter,
The code is in Dbase Plus, but it has a similar format to VB.
Hope you
can tell from this what the datasource issue might be. As I
said, this code
has worked perfectly for years thru to Word 2003.
oMerge.OpenDataSource( cDSname_, ; // name
0, ; // Format - wdOpenFormatAuto
false, ; // ConfirmConversions
true, ; // ReadOnly
true, ; // LinkToSource
false, ; // AddToRecentFiles
"", ; // PasswordDocument
"", ; // PasswordTemplate
false, ; // Revert
"", ; // WritePasswordDocument
"", ; // WritePasswordTemplate
"DSN=dBASE Files;DBQ=" + cDSpathOnly_ + ";", ;
cDSsql )
cDSpathOnly_ = path to file e.g., C:\APPTMP
cDSname = name of dbf datasource file e.g., MM.dbf
cDSsql = sql statement e.g., Select * from MM.dbf
Many thanks again,
Mark
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Sorry, I posted this in general group instead of here where I think
it should
go.
Anyone know for sure whether Word 07 can successfully use a dbf file
as a
datasource for a mailmerge?
Many thanks in advance,
Mark
Hi Peter,
Not really interested in a DB Plus app, as just whether any app can call out
to Word 07 using a dbf datasource. I got the feeling from the other ng that
MS no longer supports a dbf datasource. Hard for me to believe, but anything
is possible.
Be great if you have first hand knowledge that dbf will work with 07.
Thanks again,
Mark
Peter Jamieson said:Hi Markus,
Yes, I thought you might have been waylaid
I'll have another look at the Word 2007 + DBF stuff and see if I can
spot any additional difficulties in doing the merge "from an app".
However, I'm definitely not in a position to check specifically that a
dBase Plus app. can be made to work.
Peter Jamieson
http://tips.pjmsn.me.uk
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it will
lead to a solution, but I have not been given a copy of Word 07 yet to try
out the solution
With this post, I was hoping to find someone who has successfully used a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent post on
another ng that a dbf will not work *at all* with Word 07 mailmerge. He had
suggested the data must be written out from dbf to Access, CSV or Excel to do
the merge. When I prompted him further, the person making the post admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
:
Markus,
Are you the same Markus who posted in the vba.general group back in
April or so?
If so, this is where we were up to - maybe you could say what happened
at your end. And if not, the following may help:
<<
Thanks again, and I'll post back here after I confirm a solution.
Mark
:
I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or
because the
table name is in the wrong format for the provider. The ODBC dirver
expects something like
Select * from MM.dbf
(this is what Word generates when you try to connect manually using
ODBC)
but the OLE DB provider expects something more like
Select * FROM [MM]
or
Select * FROM `MM`
However, both the OLE DB provider and ODBC driver that I have here (as
it happens, it's the ACE provider/driver) accept any of
Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`
FWIW the error I see here is
"Error has occurred: The Microsoft Access database engine could not
find
the object 'M.DB'. Make sure the object exists and that you spell its
name and the path name correctly."
i.e. the first and last characters of the name have been stripped.
I believe that Word will do (a) because you are specifying the .dbf
pathname in the Name parameter /and not/ specifying a Subtype
parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I thought
Word
XP/2003 also did that,but perhaps the behaviour was slightly
different,
or perhaps it used the OLE DB provider in those versions too, but the
unquoted file name worked in those cases. You can probably find out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in Word
2003 (I wouldn't try in Word XP - it may still crash if you do
that). If
the string starts with DSN, it's ODBC; if it starts with Provider,
it's
OLE DB.
Here, I can get Word 2007 to connect using ODBC either by setting Name
to "", or by setting it to the pathname of the .dbf /and/
specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think you had
to specify wdMergeSubtypeWord2000 in all cases where Name was set to
"", but I think an update modified that behaviour.
If it doesn't matter whether you use the ODBC provider or the OLE DB
driver, you could see if the dBase equivalent of
.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"
works on all the versions of Word you support after Word 2000.
All the above assumes that you can use the dBASE provider/driver
(which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.
FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit SP1,
Word 2007 SP1) show that it is using the newer ACE driver.
Modifying it
to use the older Jet driver made no difference to the behaviour of the
OpenDataSource code I tried here.
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Hi Peter,
The code is in Dbase Plus, but it has a similar format to VB.
Hope you
can tell from this what the datasource issue might be. As I
said, this code
has worked perfectly for years thru to Word 2003.
oMerge.OpenDataSource( cDSname_, ; // name
0, ; // Format - wdOpenFormatAuto
false, ; // ConfirmConversions
true, ; // ReadOnly
true, ; // LinkToSource
false, ; // AddToRecentFiles
"", ; // PasswordDocument
"", ; // PasswordTemplate
false, ; // Revert
"", ; // WritePasswordDocument
"", ; // WritePasswordTemplate
"DSN=dBASE Files;DBQ=" + cDSpathOnly_ + ";", ;
cDSsql )
cDSpathOnly_ = path to file e.g., C:\APPTMP
cDSname = name of dbf datasource file e.g., MM.dbf
cDSsql = sql statement e.g., Select * from MM.dbf
Many thanks again,
Mark
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Sorry, I posted this in general group instead of here where I think it should
go.
Anyone know for sure whether Word 07 can successfully use a dbf file as a
datasource for a mailmerge?
Many thanks in advance,
Mark
Peter Jamieson said:
I do not know if they "support" it in the other sense of "we will
provide technical support in that scenario".
What I can verify is that Windows Vista Sp1 (32-bit) + Word 2007 SP2
will connect to what Access considers to be dBASE V format test DBFs
manually, programmatically (using Word VBA) and programmatically from
another application (I used Excel VBA). However,
a. whether it will work for you on all your supported Windows/Office
combinations using your DBFs and whether you will encounter other
problems down the line, I cannot predict.
b. Word 2007 connects using OLE DB by default, and there can be
problems connecting via ODBC. Because the OLE DB provider and ODBC
driver may return some data - date, numeric, logical and perhaps even
text - in different formats, you may need to make a decision as to
whether you are going with OLE where it is available or sticking with
ODBC. In my view, ODBC is potentially more problematic in Word 2007 than
in previous versions of Word, and you may find that you cannot get it to
work at all on Vista 64-bit platforms (I cannot tell you right now).
Here are some notes...
a. as Doug says, .dbf is still listed as a format in the list of
formats in Word 2007's Select Data Source dialog box
b. Access 2007 can still export to dBASE III, IV and 5 format (I guess
it can still link to them and import them but have not actually tried)
c. If I create .dbf tables from Access, Word can certainly open them
as data sources. The Word documents save, close and open again OK. Word
uses the ACE (formerly Jet) OLE DB provider by default.
d. From this point on I'm only experimenting with the dBASE5 format)
e. If I start with an open blank .docx and execute the following Word
VBA,
Sub connectole()
With ActiveDocument.MailMerge
..OpenDataSource Name:="c:\a\t5.dbf"
End With
End Sub
then Word opens the data source using the ACE OLE DB provider and engine
type Jet OLEDB:Engine Type=18
f. If I start with an open instance of Word, a closed blank .docx and
execute the following Excel VBA,
Sub opendocxandconnectole()
' (Need to reference the Word object library
)
Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = GetObject(, "Word.Application")
objWord.DisplayAlerts = wdAlertsNone
Set objDoc = objWord.Documents.Open("c:\a\t5olexl.docx")
With objDoc.MailMerge
.MainDocumentType = wdNotAMergeDocument
.OpenDataSource Name:="c:\a\t5.dbf"
End With
Set objDoc = Nothing
Set objWord = Nothing
End Sub
then the data source opens with exactly the same characteristics as above.
You may need to take account of http://support.microsoft.com/kb/825765
in some of these experiments.
g. If I have to open using ODBC, I can use the following Word VBA:
Sub connectodbcwithfilename()
With ActiveDocument.MailMerge
..OpenDataSource _
Name:="c:\a\t5.dbf", _
connection:="DSN=dBASE
Files;DBQ=C:\a;DefaultDir=C:\a;DriverId=533;FIL=dBASE
5.0;MaxBufferSize=2048;PageTimeout=5;", _
sqlstatement:="SELECT * FROM t5.DBF", _
SubType:=wdMergeSubTypeWord2000
End With
End Sub
(you will need to reconstruct the connection parameter in there)
then Word opens the data source fine using the specified connection string.
h. Or I can use the following Excel (starting with an open instance of
Word and a closed .docx)
Sub opendocxandconnectodbc()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = GetObject(, "Word.Application")
objWord.DisplayAlerts = wdAlertsNone
Set objDoc = objWord.Documents.Open("c:\a\t5odbcxl.docx")
With objDoc.MailMerge
.MainDocumentType = wdNotAMergeDocument
.OpenDataSource _
Name:="c:\a\t5.dbf", _
Connection:="DSN=dBASE
Files;DBQ=C:\a;DefaultDir=C:\a;DriverId=533;FIL=dBASE
5.0;MaxBufferSize=2048;PageTimeout=5;", _
sqlstatement:="SELECT * FROM t5.DBF", _
SubType:=wdMergeSubTypeWord2000
End With
End Sub
i. (This may not be of any interest to you, but I record it for anyone
else who may have reached this far and is interested in such stuff,
which may have a bearing on other problms opening Word data sources).
If I try to round-trip all these different mail merge main document
files (i.e. save/close them, then reopen them), what I find is that
typically if I actually make the connection from a .doc, Word will
re-open the data source, but with a .docx or Word 2007 .xml format, it
depends. All the above code should be OK. Curiously enough, if you
connect manually using the old Jet ODBC driver DSN and round-trip, you
should also be OK, whereas if you connect using the new ACE ODBC driver
DSN and round-trip, Word re-opens the mail merge main document but not
the data source. After poking around the XML for these different
scenarios, it looks to me as if
1. the critical difference is that in the cases where Word
successfully reopens the data source, Word has specified the pathname of
the data source using a Word 2007 XML relationship. In the cases where
Word does not reopen the data source, the pathname is not stored at all
- the path itself can be found in the connection string, and the file
name in the SELECT statement, but that does not appear to be enough for
Word when it opens a file in one of its new formats. Also perhaps worth
noting that the pathname is stored as a file URL : "file://c:\a\t5.dbf"
2. If you open programmatically using the ODBC examples above and
provide the filename in the Name parameter, Word stores the pathname.
However in earlier versions of Word, to open a data source using a
machine (user/system) DSN you /had to/ specify an empty name (Name:="").
If you do that in Word 2007 and save as a .docx/.xml, the data source
path name is not saved and in the case of a .dbf, Word will not reopen
the data source.
3. If I connect manually using the Jet ODBC driver, when I examine
the value of Activedocument.Mailmerge.DataSource.Name, it contains the
pathname of the .dbf. If I connect manually using the ACE driver, the
..Name property is empty. Although that is consistent with the above
observations, I have not yet tried to find out why the behaviour is
different when connecting with these two drivers. Connecting via Jet
also inserts the <w:linkToQuery/> element in the XML but connecting via
ACE does not.
Peter Jamieson
http://tips.pjmsn.me.uk
Hi Peter,
Not really interested in a DB Plus app, as just whether any app can call out
to Word 07 using a dbf datasource. I got the feeling from the other ng that
MS no longer supports a dbf datasource. Hard for me to believe, but anything
is possible.
Be great if you have first hand knowledge that dbf will work with 07.
Thanks again,
Mark
Peter Jamieson said:Hi Markus,
Yes, I thought you might have been waylaid
I'll have another look at the Word 2007 + DBF stuff and see if I can
spot any additional difficulties in doing the merge "from an app".
However, I'm definitely not in a position to check specifically that a
dBase Plus app. can be made to work.
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it will
lead to a solution, but I have not been given a copy of Word 07 yet to try
out the solution
With this post, I was hoping to find someone who has successfully used a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent post on
another ng that a dbf will not work *at all* with Word 07 mailmerge. He had
suggested the data must be written out from dbf to Access, CSV or Excel to do
the merge. When I prompted him further, the person making the post admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
:
Markus,
Are you the same Markus who posted in the vba.general group back in
April or so?
If so, this is where we were up to - maybe you could say what happened
at your end. And if not, the following may help:
<<
Thanks again, and I'll post back here after I confirm a solution.
Mark
:
I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or
because the
table name is in the wrong format for the provider. The ODBC dirver
expects something like
Select * from MM.dbf
(this is what Word generates when you try to connect manually using
ODBC)
but the OLE DB provider expects something more like
Select * FROM [MM]
or
Select * FROM `MM`
However, both the OLE DB provider and ODBC driver that I have here (as
it happens, it's the ACE provider/driver) accept any of
Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`
FWIW the error I see here is
"Error has occurred: The Microsoft Access database engine could not
find
the object 'M.DB'. Make sure the object exists and that you spell its
name and the path name correctly."
i.e. the first and last characters of the name have been stripped.
I believe that Word will do (a) because you are specifying the .dbf
pathname in the Name parameter /and not/ specifying a Subtype
parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I thought
Word
XP/2003 also did that,but perhaps the behaviour was slightly
different,
or perhaps it used the OLE DB provider in those versions too, but the
unquoted file name worked in those cases. You can probably find out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in Word
2003 (I wouldn't try in Word XP - it may still crash if you do
that). If
the string starts with DSN, it's ODBC; if it starts with Provider,
it's
OLE DB.
Here, I can get Word 2007 to connect using ODBC either by setting Name
to "", or by setting it to the pathname of the .dbf /and/
specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think you had
to specify wdMergeSubtypeWord2000 in all cases where Name was set to
"", but I think an update modified that behaviour.
If it doesn't matter whether you use the ODBC provider or the OLE DB
driver, you could see if the dBase equivalent of
.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"
works on all the versions of Word you support after Word 2000.
All the above assumes that you can use the dBASE provider/driver
(which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.
FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit SP1,
Word 2007 SP1) show that it is using the newer ACE driver.
Modifying it
to use the older Jet driver made no difference to the behaviour of the
OpenDataSource code I tried here.
Peter Jamieson
Doug Robbins - Word MVP said:The .dbf file type is still listed in the file types that can be selected
when attaching a data source to a mail merge main document.
--
Hope this helps.
Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.
Doug Robbins - Word MVP, originally posted via msnews.microsoft.com
Markus said:Hi Peter,
Not really interested in a DB Plus app, as just whether any app can call
out
to Word 07 using a dbf datasource. I got the feeling from the other ng
that
MS no longer supports a dbf datasource. Hard for me to believe, but
anything
is possible.
Be great if you have first hand knowledge that dbf will work with 07.
Thanks again,
Mark
Peter Jamieson said:Hi Markus,
Yes, I thought you might have been waylaid
I'll have another look at the Word 2007 + DBF stuff and see if I can
spot any additional difficulties in doing the merge "from an app".
However, I'm definitely not in a position to check specifically that a
dBase Plus app. can be made to work.
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Hi Peter,
As a fellow developer you know what it is to be called off on other
projects. Your April post was most informative, and I am hopeful it
will
lead to a solution, but I have not been given a copy of Word 07 yet to
try
out the solution
With this post, I was hoping to find someone who has successfully used
a dbf
file with Word 07 for mailmerge. My concern was prompted by a recent
post on
another ng that a dbf will not work *at all* with Word 07 mailmerge.
He had
suggested the data must be written out from dbf to Access, CSV or Excel
to do
the merge. When I prompted him further, the person making the post
admited
they had only heard this was the case and did not know for sure.
As I read your post again tho, it sounds like you have been successful
in
using a dbf both from within Word and within an app. (Is that correct?)
As long as I know for sure that Word 07 has not eliminated the use of
dbf
altogether, then I guess I just have to be patient till they distribute
copies of Word 07 for testing.
Thanks again for your help,
Mark
:
Markus,
Are you the same Markus who posted in the vba.general group back in
April or so?
If so, this is where we were up to - maybe you could say what happened
at your end. And if not, the following may help:
<<
Thanks again, and I'll post back here after I confirm a solution.
Mark
:
I believe what is happening in this case is that
a. Word is trying to connect using the OLE DB provider
b. it either fails because that provider is not present or
because the
table name is in the wrong format for the provider. The ODBC
dirver
expects something like
Select * from MM.dbf
(this is what Word generates when you try to connect manually
using
ODBC)
but the OLE DB provider expects something more like
Select * FROM [MM]
or
Select * FROM `MM`
However, both the OLE DB provider and ODBC driver that I have
here (as
it happens, it's the ACE provider/driver) accept any of
Select * FROM [MM]
Select * FROM `MM`
Select * FROM [MM.dbf]
Select * FROM `MM.dbf`
FWIW the error I see here is
"Error has occurred: The Microsoft Access database engine could
not
find
the object 'M.DB'. Make sure the object exists and that you spell
its
name and the path name correctly."
i.e. the first and last characters of the name have been
stripped.
I believe that Word will do (a) because you are specifying the
.dbf
pathname in the Name parameter /and not/ specifying a Subtype
parameter
equal to wdMergeSubtypeWord2000 (i.e. 8). As it happens, I
thought
Word
XP/2003 also did that,but perhaps the behaviour was slightly
different,
or perhaps it used the OLE DB provider in those versions too, but
the
unquoted file name worked in those cases. You can probably find
out by
inspecting ActiveDocument.MailMerge.DataSource.connectString in
Word
2003 (I wouldn't try in Word XP - it may still crash if you do
that). If
the string starts with DSN, it's ODBC; if it starts with
Provider,
it's
OLE DB.
Here, I can get Word 2007 to connect using ODBC either by setting
Name
to "", or by setting it to the pathname of the .dbf /and/
specifying the
wdMergeSubtypeWord2000 subtype value. FWIW at one time I think
you had
to specify wdMergeSubtypeWord2000 in all cases where Name was
set to
"", but I think an update modified that behaviour.
If it doesn't matter whether you use the ODBC provider or the OLE
DB
driver, you could see if the dBase equivalent of
.OpenDataSource _
Name:="C:\wbdb\db3\KUNDNDB3.DBF", _
sqlstatement:="SELECT * FROM [KUNDNDB3]"
works on all the versions of Word you support after Word 2000.
All the above assumes that you can use the dBASE provider/driver
(which
is part of Jet/ACE) and that you do not have to use the FoxPro
driver/provider, which is a different thing altogether.
FWIW the properties of the "dBASE Files" DSN here (Vista 32-bit
SP1,
Word 2007 SP1) show that it is using the newer ACE driver.
Modifying it
to use the older Jet driver made no difference to the behaviour
of the
OpenDataSource code I tried here.
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Hi Peter,
The code is in Dbase Plus, but it has a similar format to VB.
Hope you
can tell from this what the datasource issue might be. As I
said, this code
has worked perfectly for years thru to Word 2003.
oMerge.OpenDataSource( cDSname_, ; // name
0, ; // Format - wdOpenFormatAuto
false, ; // ConfirmConversions
true, ; // ReadOnly
true, ; // LinkToSource
false, ; // AddToRecentFiles
"", ; // PasswordDocument
"", ; // PasswordTemplate
false, ; // Revert
"", ; // WritePasswordDocument
"", ; // WritePasswordTemplate
"DSN=dBASE Files;DBQ=" + cDSpathOnly_ + ";", ;
cDSsql )
cDSpathOnly_ = path to file e.g., C:\APPTMP
cDSname = name of dbf datasource file e.g., MM.dbf
cDSsql = sql statement e.g., Select * from MM.dbf
Many thanks again,
Mark
Peter Jamieson
http://tips.pjmsn.me.uk
Markus wrote:
Sorry, I posted this in general group instead of here where I think
it should
go.
Anyone know for sure whether Word 07 can successfully use a dbf file
as a
datasource for a mailmerge?
Many thanks in advance,
Mark