Use of VisualFoxpro 8 data source.

[Jim Hawkins]

A couple of years ago I had great trouble grtting Word97 to mailmerge from
Foxpro 2.0 table data sources.
Now I am having similar trouble getting Word2003 to mailmerge from a VFP8
table.
Whether I select "All data sources" or "Dbase files" (no mention of
FoxPro!) I get "Word was unable to open the data source"
I know there is nothing wrong with my table.
It works fine with Word2000 (that's how I checked the table is OK), but my
client uses Word2003
What hoops do I have to jump through to get Word2003 to work properly?
They're both standard MS products for God's sake !

Jim Hawkins

 

[Peter Jamieson]

As far as I know, you either have to connect to FoxPro using ODBC or OLEDB.

To do the former, you need to go into the ODBC Administrator (look in the
Control Panel) and create a DSN for FoxPro. Unfortunately, when you select
the FoxPro driver, you will probably see a message that you have to upgrade
it and will be directed to http://msdn.microsoft.com/vfoxpro/downloads/ .
From there, follow the link to Product Updates. Once you have created a DSN,
you will need to check Word Tools|Options|General|Confirm conversions at
open, then go through the data source selection process, select the dbf (or
the dbc), and select the appropriate DSN when offered (if you don't see it
in the list, check the "Show All" box. If that doesn't work , you may need
to go into MS Query, which is now an option in the Tools menu in the top
right corner of the Select Data Source dialog.

To use OLEDB, you will need to get and install the provider (same URL as
above), then use the New Source button in the Word Select Data Source
dialog, pick "Other/Advanced", pick the FoxPro driver, and enter any
necessary configuration information.

I've only done FoxPro connections as experiments and can't really remember
whether it was easy or difficult, but vaguely remember the OLEDB approach
being harder.

Peter Jamieson

 

[Jim Hawkins]

As far as I know, you either have to connect to FoxPro using ODBC or
OLEDB.

To do the former, you need to go into the ODBC Administrator (look in the
Control Panel) and create a DSN for FoxPro. Unfortunately, when you select
the FoxPro driver, you will probably see a message that you have to
upgrade it and will be directed to
http://msdn.microsoft.com/vfoxpro/downloads/ . From there, follow the link
to Product Updates. Once you have created a DSN, you will need to check
Word Tools|Options|General|Confirm conversions at open, then go through
the data source selection process, select the dbf (or the dbc), and select
the appropriate DSN when offered (if you don't see it in the list, check
the "Show All" box. If that doesn't work , you may need to go into MS
Query, which is now an option in the Tools menu in the top right corner of
the Select Data Source dialog.

To use OLEDB, you will need to get and install the provider (same URL as
above), then use the New Source button in the Word Select Data Source
dialog, pick "Other/Advanced", pick the FoxPro driver, and enter any
necessary configuration information.

I've only done FoxPro connections as experiments and can't really remember
whether it was easy or difficult, but vaguely remember the OLEDB approach
being harder.

Peter Jamieson

Thanks for your help, Peter.

I spent this morning trying to set it up - in vain.
The VFPODBC.DLL driver is present in ..\system32 and correct.
The ODBC Administrator shows :-
An existing driver for VFP tables on the 'User DSN' tab.
An existing driver for VFP tables on the 'System DSN' tab.
No entry in the 'File DSN' tab. So I assume it wants one.
So I click 'Add', select the VFP driver from the offerred list, and click
'Next'
I then get a prompt "Type the name of the file data source you want to save
this connection to."
No idea what it wants here, so I try entering 'c:\temp\sevenday.dbf' (my
VFP merge table).
I also tried entering "c:\temp\sevenday.dsn' in case it was wanting a file
ending in .dsn
But in either case I got "A connection could not be made using the file data
source parametrers entered."
Any idea what it wants here ?

Thanks in avance,
Jim Hawkins


..

 

[Peter Jamieson]

First of all, you have to take a slightly different approach when connecting
to some types of ODBC data sources. I had forgotten that it was necessary in
this case.

You will either need to make the connection via MS Query (it's on the Tools
menu in the Select Data Source dialog box) or using VBA. MS Query is an
Office/Word SETUP option so you may not have it on your system but you can
install it from the SETUP disks if you want. However, you only need MS Query
to set up the connection, not to use it after that. So you can set it up in
VBA instead, using something like the following, which assumes you have a
User or System DSN called "Visual FoxPro Tables" and that your .dbf is in
c:\mytables:

Sub OpenFoxProTable()

With ActiveDocument.MailMerge
' remove the old connection
.MainDocumentType = wdNotAMergeDocument
.MainDocumentType = wdDirectory
.OpenDataSource _
Name:="", _
connection:="DSN=vVisual FoxPro
Tables;SourceDB=c:\mytables;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;",
_
sqlstatement:="SELECT * FROM sevenday", _
subtype:=wdMergeSubTypeWord2000

End Sub

Turning now to your text,

An existing driver for VFP tables on the 'User DSN' tab.
An existing driver for VFP tables on the 'System DSN' tab.

The first thing to notice is that the things listed under the DSN tabs are
not really "drivers" - they are "Data Source Names", which let you specify a
driver and provide configuration details. So there can be many DSNs for a
single driver. But the key thing is to ensure that the DSN is set up
correctly. If you already had a VFP DSN, I would either delete it and start
again, or leave the old one and create a new one. It could be a file DSN,
but that doesn't help you avoid using MS Query or VBA and since it adds a
complication I would avoid it. In essence, a User DSN is available to the
current user, and a System DSN is available to any user on the system.

As far as I know the only bit of configuration you need to do is to specify
that the Database Type is a Free Table Directory (unless you are using a
..dbc, in which case, use the other option. You don't need to specify a
folder. If you do, you should not have to specify a "SourceDB" in the
connection string unless it is different from the one in the DSN.

I hope that helps you get a bit further.

Peter Jamieson