[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I want to Word 2003 merge an Access table record from VB6

Posted on 2009-05-01
13
Medium Priority
?
366 Views
Last Modified: 2012-05-06
When I tried this, I'm getting 5922 on my:
.ActiveDocument.MailMerge.OpenDataSource  statement
0
Comment
Question by:GregKi
  • 8
  • 5
13 Comments
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24282428
Remove the period at the beginning of the line....?

ActiveDocument.MailMerge.OpenDataSource Name:= _

Open in new window

0
 

Author Comment

by:GregKi
ID: 24282586
I posted the entire code of my VB sub.  I have already tried with the eqt.mdb in a different location; no path in this now because it's in the same folder as the app.
  Dim objWord As Word.Application
  Dim objDoc As Word.Document
  Set objWord = New Word.Application
  Dim sDocNamePath   ' name of merge source document, with path
  
  sDocNamePath = "c:\clients\ehlers\data\qwi.doc"
  
  '-- open the merge source
  '-- set the data source
  '-- merge
  
  
    objWord.Documents.Open (sDocNamePath)
 
    objWord.ActiveDocument.MailMerge.Destination = wdSendToNewDocument
    objWord.ActiveDocument.MailMerge.OpenDataSource Name:="eqt.mdb", SQLStatement:="select * from ClientQuestionnaire where ID=134"
    objWord.ActiveDocument.MailMerge.Execute Pause:=False
    
    objWord.Visible = True

Open in new window

0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24282712
It apparently has to do with the ODBC drivers.

See this: http://support.microsoft.com/default.aspx?scid=kb;en-us;179973
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:GregKi
ID: 24282837
This is my first question ever submitted.  How do I respond to an Expert Comment?  Will responding here make sense in the thread?

Anyway, MS support #179973 refers to text merges; I am merging from Access file.

0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24282868
Must be the wrong KB....

When you use Microsoft Word to create a mail merge document from Direct Mail Manager, you may receive one of the following error messages:

Error (5922) - Word was unable to open the data source. This error occurred while trying to set the data source.
-or-
Another application has locked access to the data file (Commonly caused by a 2nd instance of Word). Please close the other application. Then click the "View Sample Data" button on the DirectNET toolbar to attempt to attach to the data file.
NOTE: This problem may occur when you use either the Simple Form Letter, the Letter Wizard, the Flyer Wizard, or the Postcard Wizard in Direct Mail Manager.
Back to the top
CAUSE
Microsoft Word is attempting to use the ODBC (Open Database Connectivity) Text...
Microsoft Word is attempting to use the ODBC (Open Database Connectivity) Text driver to process the mergedata.txt file that Direct Mail Manager created when it imported your address list. For more information about why this happens, see the "More Information" section of this article.
Back to the top
WORKAROUND
To work around this problem, use any of the following methods. Method 1: Remov...
To work around this problem, use any of the following methods.
Back to the top
Method 1: Remove the ODBC Text Driver
This method allows Word to use its own text converter instead of the ODBC Text Driver to read the data file. Use the following steps to remove the ODBC Text driver:

   1. Quit Microsoft Direct Mail Manager, Microsoft Word, and all Windows applications.
   2. Click the Windows Start menu, point to Settings, and click Control Panel.
   3. Double-click the 32bit ODBC icon.
   4. On the User DSN tab, select "Text Files Microsoft Text Driver (*.txt, *.csv)" and then click Remove.

      NOTE: When you are prompted "Are you sure you want to remove the Text Files data source," click Yes.
   5. Click OK.
   6. Restart Direct Mail Manager and complete your mail merge.

Back to the top
Method 2: Perform the Merge from Microsoft Word
You can perform the merge process manually from Microsoft Word and choose the type of data connectivity you want to use. That is, you can manually choose to connect to the data file using the Text Converter rather the Text ODBC driver and bypass some of the limitations of the Text ODBC driver. To do this, follow these steps:

   1. Click OK to close the error message listed in the Symptoms section of this article.
   2. In Word, click Mail Merge on the Tools menu.
   3. Attach to the Mergedata.txt file that Microsoft Direct Mail Manager created as your data source using the text converter.
         1. In the Mail Merge Helper, click "Get Data and Open Data Source."
         2. Navigate to the Microsoft Direct Mail Manager folder. By default, this is (C:\Program Files\Microsoft Office\Office\Direct Mail Manager).
         3. From the Files of type list, select Text Files.
         4. Click to select the Select Method check box.
         5. Click the Mergedata file and click Open.
         6. In the Confirm Data Source dialog box, click Text Files (*.txt) and click OK.
   4. Continue with the merge, adding merge fields as needed. Perform the merge, by either merging directly to the printer, or merging to a new document and then printing the new document.

Back to the top
Method 3: Submit the Mailing to a Mailing Service
Rather than printing the mailing out yourself, you can submit the mailing to a mailing service and have them process the mailing for you. To do this, follow these steps:

   1. Step 3 of the Microsoft Direct Mail Manager wizard gives you the following options:
      Quick and Simple - First Class Mail
      Standard Mail
      Easy - Use a Mailing Service
      Do not print the mailing now.
   2. Rather then printing the mailing yourself, you choose the third option "Easy - Use a Mailing Service" and have a Mailing Service perform the task for you.

Back to the top
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed a...
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article.
Back to the top
MORE INFORMATION
Microsoft Direct Mail Manager prints the mailing by connecting to Microsoft Wor...
Microsoft Direct Mail Manager prints the mailing by connecting to Microsoft Word using OLE Automation and handing the task off to Microsoft Word for processing and printing. Microsoft Word will then connect to the data source text file generated by Microsoft Direct Mail Manager and process the request.

When using a text file as a data source, Microsoft Word can either connect through a Text ODBC driver or a text converter. For performance reasons, Microsoft Word will always attempt to use the Text ODBC driver to connect to the data source, if the Text ODBC driver is installed. If the driver is not installed, Word will use the text converter.

The Text ODBC driver that ships with Microsoft Office can process only certain types of information. If the text file contains tab-delimited text, comma and quote delimited text, or extended characters, you will receive one of the error message listed in the Symptoms section of this article.
Back to the top
REFERENCES
For more information about removing the Text ODBC driver, please see the follow...
For more information about removing the Text ODBC driver, please see the following article in the Microsoft Knowledge Base:
137385  (http://support.microsoft.com/kb/137385/EN-US/ ) WD97: Error Using Text Data Source with ODBC Driver
For more information about using the mail merge feature in Word, start Microsoft Word, click the Office Assistant, type "mail merge," click Search, and then click one of the topics.

NOTE: If the Assistant is hidden, click the Office Assistant button on the Standard toolbar. If Word Help is not installed on your computer, please see the following article in the Microsoft Knowledge Base:
120802  (http://support.microsoft.com/kb/120802/EN-US/ ) Office: How to Add/Remove a Single Office Program or Component
For more information about troubleshooting Microsoft Direct Mail Manager problems, click the Index tab in Microsoft Direct Mail Manager Help, type "troubleshooting", click to select troubleshooting and then click Display.

Additional informational is available through the Microsoft Direct Mail Manager Web site. You can access this Web site by clicking the "Learn More" button on the Welcome screen of Microsoft Direct Mail Manager.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24282876
I'm not sure what you mean "how do you respond to an expert comment?"
0
 

Author Comment

by:GregKi
ID: 24282924
Ok, I'll just respond in line.

You just displayed the same KB I read.  It applies to merges from text files.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24282943
Ok - then I guess I'm out of ideas.... sorry if I was confused... but I thought the drivers pertained to all types of connections.
0
 

Accepted Solution

by:
GregKi earned 0 total points
ID: 24283200
I figured this out.  I opened Word and recorded a macro while I was opening the data source.  I copied the macro code into my VB6 program, and it worked.

How do we close this with no points?
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24283260
It will be taken care of.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24283268
So what was the difference in the code?
0
 

Author Comment

by:GregKi
ID: 24283346
The code generated by the macro included defaults for all the OpenDataSource parameters.  I was able to ignore those. The key was using backwards single quotes around my table name, as shown below:

SQLStatement:="select * from `ClientQuestionnaire` where ID=134"

I saw that somewhere today, but didn't believe it, since there were no spaces in my table name.  The macro generated it that way, and it worked.
0
 
LVL 37

Expert Comment

by:Joanne M. Orzech
ID: 24317826
Thanks for posting that... that's a new one... backward single quotes....
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question