 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Mailmerge and Fax / October 2007Word cannot find data source problem
Hi, I'm having a problem getting Word to remember the link to its data source for some files. It continually asks to locate the data source and if you point it to the file it just continually loops back and asks for the file location again. If you tell it "No" on the SQL check dialog, the file opens and I can go to "Open Data Source" and point to the file that way. The merge document is a one page letter. The data is a simple csv file with only 10 fields. I've tried putting the csv file in the My Data Sources folder and that doesn't fix it. I've tried using the registry setting to skip the SQL check, but that means I end up in the "Locate Data Source" loop with no way of opening the file at all. The closest thing I've come up with that sort of gets me to a work around is that some of the field headings have an underscore in them: First_Name, Last_Name, Address_1, etc. However, if I make a new csv file in notepad and make the names with an _, it works fine. But then I get the second clue. If I click on Tools | Letters and Mailings | Mail Merge to get the Mail Merge side bar, step 3 has some weirdness. On the csv file that does not work, the source that the recipients are currently selected from appears as: [:\foldername\source.cs] in "source.csv" If I make a csv file from scratch and do not use any underscores, that line becomes just: "test.csv" But as soon as I change a field name in the working csv file to contain and underscore it changes to: [:\foldername\test.cs] in "test.csv" It still works, probably because somewhere in the file it remembers that it used to work before I changed the file name, but who knows at this point. Has anyone else run into something similar or know of a fix? Thanks! Well spotted on the incorrect display of the file path name, but unfortunately as far as I know it is a red herring and does not indicate anything other than a fault in the display. Further, whether the Mailmerge task pane displays the "long" data source location details or just the short file name depends on whether the data source is in what Word thinks is the "active" folder" (I think) - i.e. it is likely to change to that folder if you create a test .csv in Word and save it. However, things are complicated by the fact that Word has a number of different mechanisms for opening a .tx or .csv type file and chooses the mechanism depending on the file content (I think). I don't work for Microsoft or have have access to the source code, so I have to guess, but in Word 2002(XP) and 2003, I believe Word will either use Word's built-in text converter to read the file, or OLE DB. If it uses OLE DB, it generates a "connection string" which contains the path name of the folder containing the file. However, Word does not save the whole connection string (max 255 characters I think) and can truncate the pathname, so when you close and re-open the When you close the mail merge main document and re-open it, Word can't find the file.I'm not so sure that happens when Word opens the file using its converter. Anyway, if you try putting the file in a folder with a short pathname I think it will always work. However, there could also be problems if Word does not recognise the character encoding of your .csv file correctly - but let's leave that for now. Peter Jamieson > Hi, > [quoted text clipped - 45 lines] > > Thanks! I'm having a very similar problem. In Word 2003 (on a Windows 2003 network) we cannot save word merge files with a user -assigned data source. One of the following will happen: 1. Our Word Merge templates (the 'letter' part of the file) are copied into a client directory, and staff edits the file to 'link' to the new data source (text file with CSV). Staff saves the document. The next time the staff opens the document Word responds with 'Opening this document will run the following SQL Command' - as we would expect - but then we get a message 'Word cannot find its data source ' - displaying the name of the file we linked in - which we know is there - so staff selects 'Find Data Source' and re-opens the file. WE GET THE SAME MESSAGE - Word cannot find its data source. The only option is to remove the headers. But even after we do that, Word Merge cannot find the data source. We cannot save the Word merge file with the correct data source attached. Here's the strange part. All our word files are saved to the network - the templates and the data source. If I attached a data source that's on my local drive to the word file, there is no problem - the merge file with the new data source saves fine. It's as if the merge file does not want a data source that's on a network drive - or is it the path? > Well spotted on the incorrect display of the file path name, but > unfortunately as far as I know it is a red herring and does not indicate [quoted text clipped - 75 lines] > > > > Thanks! I just did some simple tests here and was able to re-open when both Mail merge Main Document and data source were on the same network drive, i.e. suggesting that the problem does not occur solely because it's a netwrok drive (it worked whether I connected using E DB or the internal text file converter). A few questions: a. are your mail merge document and data source in the same network folder? b. how long is the pathname of the data source? e.g. longer or shorter than the pathname you used when testing on a local drive? - the total length consists of all the charaters in \\computername\sharename\folders`filename.ext c. have any sorts or filters been applied to the data?  SignaturePeter Jamieson http://tips.pjmsn.me.uk > I'm having a very similar problem. > [quoted text clipped - 116 lines] >> > >> > Thanks! 1. The two files are in the same folder on a network drive. 2. the path to the data source is longish: \\server\company\word\mhl clients\h\hollowayjane\mailmerge.txt when I tested on the local drive it was short: c:\cis\mailmerge.txt 3. there are no filters or sorts > I just did some simple tests here and was able to re-open when both Mail > merge Main Document and data source were on the same network drive, i.e. [quoted text clipped - 131 lines] > >> > > >> > Thanks! OK, I checked again with a similar length pathname and it does not appear to be long enough to cause problems here. Which leaves me a bit stuck. If you are able to test the same document and mail merge source on a much shorter network path and it still does not work, I think that would help establish that it is probably something to do with the network setup. I would be looking at the permissions for the /share/ and for the underlying folder - e.g. you may need to be able to read and write to both the share and the folder (even though mailmerge typically only reads the data source). SignaturePeter Jamieson http://tips.pjmsn.me.uk > 1. The two files are in the same folder on a network drive. > 2. the path to the data source is longish: [quoted text clipped - 167 lines] >> >> > >> >> > Thanks! I'll try that. I'll check into the permissions on network drives too. If I have to I'll call Microsoft. Thanks for your help. > OK, I checked again with a similar length pathname and it does not appear to > be long enough to cause problems here. Which leaves me a bit stuck. [quoted text clipped - 177 lines] > >> >> > > >> >> > Thanks! One other thing that may be worth checking is that the document is not connected to a template that also has a dtaa source (maybe the same one) attached. SignaturePeter Jamieson http://tips.pjmsn.me.uk > I'll try that. I'll check into the permissions on network drives too. > If [quoted text clipped - 224 lines] >> >> >> > >> >> >> > Thanks! do you mean connected to a .dot file? How would I find out if it is? > One other thing that may be worth checking is that the document is not > connected to a template that also has a dtaa source (maybe the same one) [quoted text clipped - 228 lines] > >> >> >> > > >> >> >> > Thanks! Wen you have managed to open it, use Tools->Addins and Templates to have a look. SignaturePeter Jamieson http://tips.pjmsn.me.uk > do you mean connected to a .dot file? How would I find out if it is? > [quoted text clipped - 260 lines] >> >> >> >> > >> >> >> >> > Thanks! I am having the same problem as all of you. I receive the error "Word cannot find the source ". My files are on the network and have long descriptive file names. Please let me know if there is any further information on this from your experiences. Thanks. Mike > Wen you have managed to open it, use Tools->Addins and Templates to have a > look. [quoted text clipped - 261 lines] > >> >> >> >> > > >> >> >> >> > Has anyone else run into something similar or know of a fix? Is this also Word 2007? SignaturePeter Jamieson http://tips.pjmsn.me.uk >I am having the same problem as all of you. I receive the error "Word >cannot [quoted text clipped - 314 lines] >> >> >> >> >> > Has anyone else run into something similar or know of a >> >> >> >> >> > fix? Sorry no it is not. Word 2003. > Is this also Word 2007? > [quoted text clipped - 253 lines] > >> >> >> >> >> > that > >> >> >> >> >> > doesn't fix it. As far as I know, the main reasons why Word may have difficulty /re-/opening a data source are a. In Word 2002 there is a problem where the data source is lost if you apply a filter or sort criteria to your data source. This may have been fixed in a later SP, and I believe was fixed in Word 2003. b. the connection information saved by Word when you close a working mail merge main document is truncated in such a way that Word loses essential information about the location of the data source For example, by default Word 2002 and later use OLE DB providers to open as many types of data source as possible, including Excel worksheets, Access databases and plain text files. The OLE DB provider typically divides the location of a data source into a "database" and a "table". So for example, if the data source is... a. ...an Access table, the "database" is the Access .mdb file that contains all the data, and the table is a table or query within that .mdb b. ...a text file, the "database" is the Windows folder that contains the text file, and the .txt file itself is the "table" c. ...an Excel worksheet, the "database" is the Excel workbook, and the "table" is a worksheet, a named range, or perhaps a range specified in R1C1 format. An application such as Word that uses OLE DB to get data usually specifies the database part of the data source's locatoin in a /Connection string/, and specifies the "table" part either simply by naming the table, or specifying a SQL query that names the table. So what goes wrong in Word? Well, Word constructs a connection string containing whatever path name is required to specify the "database", and uses it to open the document. But then when you save the Word document, it truncates the connection string to 255 characters long. If the pathname of the database file or folder is so long that it spans that 255 boundary, Word in effect forgets where the database it. What can you do about it? Well, unfortunately, you cannot shorten the connection string by leaving out unnecessary information. Word always includes certain properties even when they are not strictly necessary. So the only things you can do are a. use another method to make the connection (and every method has its drawbacks - see e.g. http://tips.pjmsn.me.uk/t0003.htm for a discussion of some of the issues surrounding connections to Excel files, for example) b. give your data source a shorter name, or put it in a folder with a shorter pathname, depending on exactly what typ eof data source it is. There can in theory be other problems that would cause this problem, but in most of those cases you would be unlikely to connect at all. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Sorry no it is not. Word 2003. > [quoted text clipped - 299 lines] >> >> >> >> >> >> > that >> >> >> >> >> >> > doesn't fix it. Peter, Can you tell me where the path to the data source is saved when I do the following: . With my merge document open in Word 2003 I click Letters and Mailings on the Tools menu, and then click Mail Merge. In the Select recipients section of the Mail Merge Wizard (Step 3 of 6), I select the Data Source by browsing to the location of the this .txt file on my network drive then click through the next steps and save the document. Also, could you tell me if any data source information is stored in my Windows XP user profile? Thanks! Mike > As far as I know, the main reasons why Word may have difficulty /re-/opening > a data source are [quoted text clipped - 247 lines] > >> >> >> >> >> >> if > >> >> >> >> >> >> you create a test .csv in Word and save it. > Can you tell me where the path to the data source is saved when I do the > following: 1. In the document, either completely or partially, either in clear text if you save as .rtf, .xml or .htm, or in Word's binary format (actually it may be a readable Unicode string - I don't know) if you save as .doc. The string will be saved either in the connection string, or in the SQLStatement, or split across the two, depending on how Word chose to make the connection (it may choose to use OLE DB or its converter, and just possibly ODBC, depending on the file). 2. the pathname may also be saved in various "recently accessed files" type lists, e.g. in the Windows registry, but that's just my guess. In the scenario you describe I would expect any such info to be stored in a DSN, .udl or .odc file > Also, could you tell me if any data source information is stored in my > Windows XP user profile? I'm not sure if the above answers your question. However, when you save a mailmerge main doc. you /may find that data source info. is cached in the .doc. If you save as .htm I think it looks more as if enough info. to identify individaully selected records is stored (more or less). SignaturePeter Jamieson http://tips.pjmsn.me.uk > Peter, > [quoted text clipped - 306 lines] >> >> >> >> >> >> >> if >> >> >> >> >> >> >> you create a test .csv in Word and save it. Peter, Thanks for the reply. This information was helpful. I have previously done, as you mentioned, saving the document as an HTML and then opening it as text. The problem ended up being solved by the following microsoft support doc. http://support.microsoft.com/kb/834699 Thanks for all your help. Mike > > Can you tell me where the path to the data source is saved when I do the > > following: [quoted text clipped - 231 lines] > >> >> >> >> >> >> > a client directory, and staff edits the file to 'link' > >> >> >> >> >> >> > to Good feedback, thanks. SignaturePeter Jamieson http://tips.pjmsn.me.uk > Peter, > [quoted text clipped - 292 lines] >> >> >> >> >> >> >> > 'link' >> >> >> >> >> >> >> > to |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.