Unable to open data source VFP to Word XP

[PaulHerschell]

I am having a problem mailmerging Visual FoxPro 6 with Word XP resulting in a '1429 OLE IDispatch exception code 0 from Microsoft Word: Word was unable to open the data source' error.

The curious thing about this error is that the mailmerge works fine on Word 2000, it also works fine on Word XP systems that have been upgraded from Word 2000 like my own system, however, I have had a number of customers that have upgraded from Word 97 to Word XP or just installed Word XP for the first time who get the error message.

My first thought was a problem with the ODBC drivers. I compared the machines to my own working machine and the Visual FoxPro Tables and Visual FoxPro Database entries were identical, even to the version number, not surprising I suppose as I supply the ODBC drivers with my application (using the built in Microsoft Setup program within VFP 6) in case users do not already have them installed. I removed the drivers on my PC and replicated the error, reinstalled my app and the error cleared, however the same procudure on the customer PCs made no difference - it still crashed with the 'Word was unable to open the data source' error at the .opendatasource line below.

My code looks something like the following in my VFP program

with oword

&& copy cursor to a temporary file on disk for use by Word mailmerge
select * from customers into table mytable
&& close new file file so not open when Word tries to open it later
use
with oword.odocument.mailmerge
.MainDocumentType = wdFormLetters

.OpenDataSource(;
"mytable",;
,;
,;
,;
,;
,;
"",;
"",;
,;
"",;
"",;
"DSN=Visual FoxProTables;UID=; PWD=; SourceDB=c:\mydir"+;
"SourceType=DBF; Exclusive=No; BackgroundFetch=Yes; Collate=MACHINE"+; "Null=Yes; Deleted=Yes;",;
"SELECT * FROM mytable",;
"&quot
endwith

I suspect some incompatible default option in Word XP that sets itself differently when you upgrade from Word 2000 rather than earlier versions or for fresh installs, however, I am really stuck on this. Obviously I could export to different file formats and try that, however, that seems a shame when the code above has been working fine in all Word 2000 and most Word XP installations for a long time now.

Any help on this would be really appreciated. Thanks

Paul

 

[Mike Gagnon]

&& copy cursor to a temporary file on disk for use by Word mailmerge
select * from customers into table mytable
&& close new file file so not open when Word tries to open it later
use
with oword.odocument.mailmerge
.MainDocumentType = wdFormLetters

with oword.odocument.mailmerge

Not sure about the above,
oword=CREATEOBJECT("word.application&quot
oDocument=oword.Documents.Add()
oDocument.MailMerge.MainDocumentType= 0 && wdFormLetters
oDocument.MailMerge.OpenDataSource("myTable&quot

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[PaulHerschell]

Mike,

Thanks for the feedback. I only showed you a snippet of the code. I omitted the following lines of code which open the Word connection, copy the merge template file to a new file so that the original remains unchanged and then opens the new document. My wdformletters is set to 0.

oword.createobject("word.application&quot
copy file formdoc.doc to newdoc.doc
oword.opendocument("newdoc.doc&quot

As far as I can see the rest is similar to your own code so should not really make any difference, although, you have only used one parameter to mailmerge i.e. "mytable", is that allowed? Are you proposing that Word might default the rest and perhaps work a little better than my passing it the other parameters or am I reading too much into that?

Thanks

Paul

 

[Mike Gagnon]

Paul

I have done a few "mail merge" in Word and I found it to be very cumbersome and resource intensive. The way I found to be lees intensive was to first create a template (The document the will be used as a basis of the merge--That is normally done in Word ), then, do like your code but instead of using the merge function in word, (in VFP from here) I open up the template, copy it to the clipboard, open my table where the information is (ie: emplyoyee name, adress etc..)create a new document in word, insert the employee information at the top, paste my template from the clipboard and add a pagebreak, and skip a record and so on. That way I end up with one Word document whit everything I need place in the appropiate places.

although, you have only used one parameter to mailmerge i.e. "mytable", is that allowed?

Sorry, I don;t want to mislead you, but I found that in Word if I only need the first parameter, I can skip the rest, but this might not be your case.

But this seems quite extreneous :

"DSN=Visual FoxProTables;UID=; PWD=; SourceDB=c:\mydir"+;
"SourceType=DBF; Exclusive=No; BackgroundFetch=Yes; Collate=MACHINE"+; "Null=Yes; Deleted=Yes;",;
"SELECT * FROM mytable",;
"&quot
endwith

If you need sample code of the method I'm describing, let me know I'll post it.

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[PaulHerschell]

Mike,

Thanks for that. I must say it was an approach I nearly took when I started this merging a couple of years back, however, I stayed with it and with the help of a very good book on Office Automation I got it to work and implemented it throughout my application. It works very well indeed and I would not say really drains resources even when merging hundreds of records, although if my app required tens of thousands of records I might have to think again. Because it has been working very well for a long time on both Word 2000 and Word XP I am reluctant to change it at present, I simply want to identify what is causing this silly niggle in the odd Word XP installation. It must be something very simple but I don't know what it is. I will hold up a little longer in the hope that someone else has experienced it and if not I might have to bite the bullet and take a different approach.

I tried your opendatasource with a single parameter and Word offered me a list of ODBC items to choose from. It did not give me a full list and did not mention the User DSN I installed with my setup disk, it only used built in drivers that had the (*.dbf) at the end of their names in the ODBC database sources drivers list. Not sure if this is relevant. I created a new data source on my PC based on one of these and successfully chose it from the Word list offered to me - worked fine on my PC but still failed at the troublesome users end though!

Thanks again

Paul

 

[Mike Gagnon]

What line is causing the error? Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Mike Gagnon]

Paul

If I look at the parameters to OpenDataSource and count the ones required by Word XP, you seem to be using more then the ones listed:
OpenDataSource(Name, Format, ConfirmConversions, ReadOnly, LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate, Revert, WritePasswordDocument, WritePasswordTemplate, Connection, SQLStatement, SQLStatement1, OpenExclusive) Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[PaulHerschell]

Mike,

The line causing the problem is opendatasource. Sorry if I got a parameter out, I have not posted the exact code I use I have simplified it to make things clearer. I have obviously made a slight mistake there. The code has been in use unmodified for a couple of years now and is working here fine on Word 2000 and Word XP systems therefore I would not have thought a parameter in the wrong position would cause the problem on just the occasional Word XP installation but I will check again. Thanks

Paul

 

[Mike Gagnon]

This might help:

http://msdn.microsoft.com/library/en-us/vbawd10/html/womthOpenDataSource.asp

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[PaulHerschell]

I count one less on mine than the standard one. It looks like I have not defaulted the 15th parameter, however, as I don't need it it should be fine.

Paul

 

[Mike Gagnon]

PaulHerschell

So? Has your problem been fixed? Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[PaulHerschell]

Mike,

Thanks for getting back again. The FAQ simply lists the syntax I have been working from in the object view in Visual Basic Editor off the Word macros menu. Nothing new jumps out at this time. I have added the 15th parameter at my end and set it to 0 (false) and it works just the same as it did without it here - ie works absolutely fine. I have sent a test program to the customer to see if it makes any difference there, however, I am not optimistic, the parameter is for 'exclusive'. The way I have developed this the user will be the only one using the files as copies are made of the original in a network environment and all temp files used have unique names generated, they are not simply mytable.dbf etc. Also exclusive is pass in the connection string to the VFP driver and set to off there also. Will keep you posted of the results but don't hold your breath! Thanks again

Paul

 

[eyebiz]

Paul

I had a similar problem opening VFP6 data with Word2000 on a new WinXP machine.
I have also had the problem opening VFP6 data with WordXP on a Win2000 machine under a new roaming profile.

I came across:

http://www.visualpractice.com/xp_merge.shtml

http://www.visualpractice.com/kba_wordoleerrors.shtml

Running wdvfpdsn.exe solved my problem. Could this help to your customer?

Leon

 

[PaulHerschell]

Leon,

Thanks for the feedback. Things looked very promising at first. I downloaded the files and installed them on my PC and everything worked fine, but my PC was working fine in the first place anyway. I have just come off the phone from my client who has tried installing them and he got the following message when he tried to install the ODBC MSI download

"You must have MDAC 2.6 or higher installed to run this setup"

He was therefore unable to install the MSI file, he did, however install the Word DSN enabler, however, it made no difference.

Would I be correct in thinking that the MSI install is the same as the Visual FoxPro 6.1 ODBC drivers that get installed by my application anyway? I have built my install using VFP6's Setup wizard and checked the box to 'include ODBC driver'?

Any more help would be appreciated. Thanks again.

Paul

 

[turboman]

I too had many problems along these lines. In the end I changed my system to copy the data to an excel file and then mailmerge to that file from Word. All now works well.

 

[PaulHerschell]

I too am getting closer to a similar resolution. I have looked at downloading various versions of MDAC from the Microsoft site but the end user's PC has unfortunately gone in the repair shop for other reasons so I do not know if any of it works yet. Even if it does, it seems impractical for new potential customers to have to go through all of this when evaluating my software, they will just move on to something else and not stand the frustrations of existing users. It seems like a toss up between using an Excel file through ODBC verses using automation to do find and replace from a table inside the application on the Word document bypassing Words own mailmerge facilities. Have you or anyone else any comments on advantages / disadvantages of either for single record merges, 100 record merges and 1000 record merges in terms of speed particularly. I would have thought the ODBC mailmerge facility quickest as that is what it is optimised for but until I start recoding not sure which is best and will Excel ODBC hit similar problems to the FoxPro ODBC mailmerge access in the future?

Any comments most welcome. Thanks

Paul