Solved

Problem "unable to find data source" when merging to Word XP

Posted on 2004-08-17
24
957 Views
Last Modified: 2008-02-07
I have done some searching on this site and know exactly where my problem lies, but am having troubles in applying a fix for it.

My Access database uses VB to merge to a word document all quite nicely under Word 2000 but falls flat with Word XP.  It seems that this problem is caused by the differences between the msword9 and 10 libraries.

I have found my way through to site http://support.microsoft.com/default.aspx?scid=kb;EN-US;279462 which shows that you must add a line  "SubType:= wdMergeSubTypeWord2000" but when I do this, I get a message a VB compile error (variable not defined) on my pc that only has Word2000.  I suppose that the simple solution is just to upgrade to XP but I do not want to do this in case I cascade a whole host of other problems.

The code that I have is

Function MergePGRschWebApp(theApp As Long)

 Dim oApp As Object
 Set oApp = CreateObject("Word.Application")
 oApp.Visible = True
 oApp.application.documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
 ' Make Word visible.
  oApp.application.Visible = True
 ' Set the mail merge data source
   oApp.application.ActiveDocument.MailMerge.OpenDataSource _
   Name:="access database", _
     Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
    SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, _
    SubType:=wdMergeSubTypeWord2000
 '   ' Execute the mail merge.
     oApp.application.ActiveDocument.MailMerge.Execute
 '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
 '      Set oApp = Nothing ' Dispose of word object
End Function
 

Any help is greatly appreciated
0
Comment
Question by:jainesteer
  • 11
  • 8
  • 3
  • +1
24 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11818766
Try something like this to determine first if you are running Word 2000, then based on that run your command:

Function MergePGRschWebApp(theApp As Long)

     Dim oApp As Object
     Dim strKey As String
     Dim bKeyExists As Boolean

     Set oApp = CreateObject("Word.Application")
     oApp.Visible = True
     oApp.application.documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
     ' Make Word visible.
     oApp.application.Visible = True
     ' Set the mail merge data source
      oApp.application.ActiveDocument.MailMerge.OpenDataSource _
      Name:="access database", _
         Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
         SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, _

      strKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Word"

      bKeyExists = RegKeyExists(strKey)

      If Not bKeyExists Then
         SubType:=wdMergeSubTypeWord2000
      End If

     '   ' Execute the mail merge.
         oApp.application.ActiveDocument.MailMerge.Execute
     '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
     '      Set oApp = Nothing ' Dispose of word object
End Function

See here for more details:
http://www.experts-exchange.com/Databases/MS_Access/Q_20835218.html?query=VBA+Determine+Office+version&topics=39
0
 

Author Comment

by:jainesteer
ID: 11826680
This is a good start but I still have a problem.

When I include the vb code into the module,the line
 SubType:=wdMergeSubTypeWord2000

automatically turns red.

Also I had to get rid of the ",_ "at the end of the sqlstatement and add the function

Function RegKeyExists(sRegKey)
  Set oShell = CreateObject("WScript.Shell")
  RegKeyExists = True
  sRegKey = Trim(sRegKey)
  If Not right(sRegKey, 1) = "\" Then
    sRegKey = sRegKey & "\"
  End If
  On Error Resume Next
  RegReadReturn = oShell.RegRead(sRegKey)
  If Err Then
    If LCase(left(Err.description, 7)) = "invalid" Then
      'key not found...
      RegKeyExists = False
    End If
    Err.Clear
  End If
  On Error GoTo 0
End Function


Now when I run the code, I get a compile error (syntax error)

Any thoughts on how to go on from here
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11828190
OK. Sorry, I blew it and did not read you code fully so when I added the if statement it screwed up your connection statement. I think you will need to create your connection statement in a string variable and then pass it on to the Open Datasource command. Try this:

Function MergePGRschWebApp(theApp As Long)

     Dim oApp As Object
     Dim strKey As String, strConn AsString
     Dim bKeyExists As Boolean

     Set oApp = CreateObject("Word.Application")
     oApp.Visible = True
     oApp.application.documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
     ' Make Word visible.
     oApp.application.Visible = True

     'Check the registry for the presence of Word2000
     strKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Word"
     bKeyExists = RegKeyExists(strKey)

     'First create a string variable for your connection
     strConn = "Name:='access database', Connection:='DSN=webapps;DBQ=access database;FIL=RedISAM',"
     strConn = strConn & "SQLStatement:='Select * from Q_WEB_PGRsch_Applications where id = " & theApp

     'Now if application is not running in Word2000 add the SubType
      If Not bKeyExists Then
         strConn = strConn & ", SubType:=wdMergeSubTypeWord2000"
      End If

     ' Set the mail merge data source using the connection string built above
      oApp.application.ActiveDocument.MailMerge.OpenDataSource  strConn

     '   ' Execute the mail merge.
         oApp.application.ActiveDocument.MailMerge.Execute
     '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
     '      Set oApp = Nothing ' Dispose of word object
End Function
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:jainesteer
ID: 11836927
This code now opens the template but comes up with a box "Confirm data source" whereby I have to enter the ODBC information

After I scroll down and select the webapps one, I am prompted to select the database, then the table (or query in this case), and then it open ALL of the results.

Somehow I don't think that the connection statement is being called correctly.

Any further thoughts

(ps thanks so far)
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11838450
My first thought would be that it is not getting the string properly. could be a minor syntax issue. I'll have to test it.

For now you could try changing this:
      oApp.application.ActiveDocument.MailMerge.OpenDataSource  strConn

To this:
      oApp.application.ActiveDocument.MailMerge.OpenDataSource  & strConn

I'll let you know when I've tested it.

God Bless!
0
 

Author Comment

by:jainesteer
ID: 11838486
That comes straight back with a compile error so no luck there
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11838611
OK, I see one thing that may be the cause of that. A small syntax error in the if statement that I gave you, which left out a closing quote on the SQL statement. Try changing the if statement to this and see if it still gives you the error:

      If Not bKeyExists Then
         strConn = strConn & "', SubType:=wdMergeSubTypeWord2000"
      End If

(Note the single quote between the double quote and the comma)
0
 

Author Comment

by:jainesteer
ID: 11838651
No it still goes through the same set of questions

I am just about to go so I won't be back on this again until tomorrow

Thanks
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11838654
Sorry stupid mistake on my part... what I posted above will only close the SQL statement if you are not in Word2000. We need to add the closing quoter in the seccond line that is building strConn and leave the if statement as it was like this:

     strConn = strConn & "SQLStatement:='Select * from Q_WEB_PGRsch_Applications where id = " & theApp & "'"

     'Now if application is not running in Word2000 add the SubType
      If Not bKeyExists Then
         strConn = strConn & ", SubType:=wdMergeSubTypeWord2000"
      End If
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11838660
OK. I'll test it out more today (my day is just beginning) and hopefully have a working solution for you when you get back. Have a good night!
0
 

Author Comment

by:jainesteer
ID: 11838683
No that didn't make any difference
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11846569
Good morning jainsteer!
    Hope you had a good sleep! Hopefully I have a working solution for you this time. It was actually from a suggestion from a friend that I found this and it makes a lot of sense. It's almost back to using your original code, so I'll appologise for sending "around Robin Hood's barn" with my first suggestions. We'll still use the part to check for the registry key, but instead of trying to make a single strin work for either version we'll run the connection string in the if statement so that it runs the correct statement for the version of Word found.
********************************
Function MergePGRschWebApp(theApp As Long)

     Dim oApp As Object
     Dim strKey As String, strConn As String
     Dim bKeyExists As Boolean

     Set oApp = CreateObject("Word.Application")
     oApp.Visible = True
     oApp.Application.Documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
     ' Make Word visible.
     oApp.Application.Visible = True

     'Check the registry for the presence of Word2000
     strKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Word"
     bKeyExists = RegKeyExists(strKey)

     'Open the data connection and if application is not running in Word2000 add the SubType
     If bKeyExists Then
          oApp.Application.ActiveDocument.MailMerge.OpenDataSource _
               Name:="access database", Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
               SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp
     Else
          oApp.Application.ActiveDocument.MailMerge.OpenDataSource _
               Name:="access database", Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
               SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, SubType:=wdMergeSubTypeWord2000
     End If

     '   ' Execute the mail merge.
         oApp.Application.ActiveDocument.MailMerge.Execute
     '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
     '      Set oApp = Nothing ' Dispose of word object
End Function
********************************

Let me know how this works (Lord willing it wil be right).

God bless!

Sam
0
 

Author Comment

by:jainesteer
ID: 11847939
Sorry to say but it's not

When I ran the code as you provided it, I got the original VB compile error (variable not defined)

So I changed the line in the else section from
       SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, SubType:=wdMergeSubTypeWord2000
to
   SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp & "SubType:=wdMergeSubTypeWord2000"

(by the way, I really don't know what I am doing!!)

This then gave me the error - data source not found

Then I changed it to

          SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp & ", SubType:=wdMergeSubTypeWord2000"

which also gave me the data source error.

All is still chugging along nicely under the earlier version of Word.

Any further thoughts??
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11849701
    I'm a little confused. When it gave you the VB compile error, what line is highlighted if you click debug? From what you said "All is still chugging along nicely under the earlier version of Word" I'm assuming that this code works under Word2000 but not WordXP/2003 is that correct? From what I can tell the connection string for when it does not find Word2000 is identicle to the connection string that you originaly posted except for the layout. Did that string actually work before? You could try re-setting the layout to see if that makes a difference (though I don't see why it would, but you never know).

oApp.application.ActiveDocument.MailMerge.OpenDataSource _
   Name:="access database", _
     Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
    SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, _
    SubType:=wdMergeSubTypeWord2000
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11854442
Jainesteer,
   Can you post a copy of your PGRschWebApp.dot file so I can test this completely?
ThanX!
0
 

Author Comment

by:jainesteer
ID: 11861579
I will sort this out tomorrow. At present it is still the weekend.

Thanks

Jaine
0
 

Author Comment

by:jainesteer
ID: 11888310
Changing the format to that in your last post gave me a compile error - variable not defined...on both XP and 2000

For the life of me, I cannot get the system to merge under XP now at all (previously I could but I have no idea now how this was done)


As it stands at the moment, my code is

Function MergePGRschWebApp(theApp As Long)

     Dim oApp As Object
     Dim strKey As String, strConn As String
     Dim bKeyExists As Boolean

     Set oApp = CreateObject("Word.Application")
     oApp.Visible = True
     oApp.application.documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
     ' Make Word visible.
     oApp.application.Visible = True

     'Check the registry for the presence of Word2000
     strKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\9.0\Word"
     bKeyExists = RegKeyExists(strKey)

     'Open the data connection and if application is not running in Word2000 add the SubType
     If bKeyExists Then
          oApp.application.ActiveDocument.MailMerge.OpenDataSource _
               Name:="i:\corpisc.mdb", Connection:="DSN=webapps;DBQ=i:\corpisc.mdb;FIL=RedISAM", _
               SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp
     Else
     oApp.application.ActiveDocument.MailMerge.OpenDataSource _
   Name:="i:\corpisc.mdb", _
  Connection:="DSN=webapps;DBQ=i:\corpisc.mdb;FIL=RedISAM", _
             SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp & ", SubType:=wdMergeSubTypeWord2000"
    '' this line gives an error so I changed it to the one above          ' SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, SubType:=wdMergeSubTypeWord2000
     End If

     '   ' Execute the mail merge.
         oApp.application.ActiveDocument.MailMerge.Execute
     '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
     '      Set oApp = Nothing ' Dispose of word object
End Function
Function RegKeyExists(sRegKey)
 Dim oShell As Object
 Dim RegReadReturn As Boolean
 
  Set oShell = CreateObject("WScript.Shell")
  RegKeyExists = True
 
  sRegKey = Trim(sRegKey)
  If Not right(sRegKey, 1) = "\" Then
    sRegKey = sRegKey & "\"
  End If
  On Error Resume Next
  RegReadReturn = oShell.RegRead(sRegKey)
  If Err Then
    If LCase(left(Err.description, 7)) = "invalid" Then
      'key not found...
      RegKeyExists = False
    End If
    Err.Clear
  End If
  On Error GoTo 0
End Function

This works under 2000 but not XP (I am starting to despise XP for doing this to me)

As for the template, there is no VB behind it and here is the strange bit.  If I open the template under 2000, it just opens.  Under the increasingly annoying XP however, I am prompted with a message that by opening it some sql will be run and then goes on to list the fields in the q_web_pgrsch_applications query that is used in the merge process.  I cannot post this document to this forum but I could email it to you if you want.

Thanks

PS Sorry it took an extra day to get back to this.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11889506
ThanX for the input Jaine. I will consult a friend of mine who has done some similar work and see if he can write the XP connection string. I'll get back to you soon.

Sam.
0
 

Expert Comment

by:MikeJordan
ID: 11984837
Hi! Based on your original question, I think that the problem is that Word 2000 does not support the "SubType" declaration. As such, wdMergeSubTypeWord2000 is not a valid variable and thus your error. Since wdMergeSubTypeWord2000 is just a constant with a numerical value of 8 why don't you try changing the "SubType" part of the connection string to say SubType:=8. You shouldn't get a "variable not defined" error now.

If this connection string doesn't work under Word 2000 (and it's possible that the "SubType" entry will screw things up), let me know and we can try to build the string contingent on the version as Will proposes above.

-- Mike
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11984873
After setting the version checker we got it to work in Word2000 but have not been able to get it working in WordXP.
0
 

Expert Comment

by:MikeJordan
ID: 11984876
Like this:

Function MergePGRschWebApp(theApp As Long)

 Dim oApp As Object
 Set oApp = CreateObject("Word.Application")
 oApp.Visible = True
 oApp.application.documents.Open ("I:\MergeDocs\PGRschWebApp.dot")
 ' Make Word visible.
  oApp.application.Visible = True
 ' Set the mail merge data source
   oApp.application.ActiveDocument.MailMerge.OpenDataSource _
   Name:="access database", _
     Connection:="DSN=webapps;DBQ=access database;FIL=RedISAM", _
    SQLStatement:="Select * from Q_WEB_PGRsch_Applications where id = " & theApp, _
    SubType:=8
 '   ' Execute the mail merge.
     oApp.application.ActiveDocument.MailMerge.Execute
 '      oApp.Application.Quit (0) 'wdDoNotSaveChanges
 '      Set oApp = Nothing ' Dispose of word object
End Function
0
 

Expert Comment

by:MikeJordan
ID: 11986176
Sorry Will, didn't realize you got the Word 2000 part working :).

Janine, something which has worked for me for Word XP (not Word 2000) and Word 2003, is to drop the Connection part. Try the following replacing your current WordXP string:

oApp.application.ActiveDocument.MailMerge.OpenDataSource _
  Name:="i:\corpisc.mdb", _
  SQLStatement:="Select * from [Q_WEB_PGRsch_Applications] where id =1"

Note that I've replaced the "theApp" variable with 1. Try replacing the 1 with a valid "theApp" value so that that's not causing a problem with your string compiling. Afterwards, you can put the variable back in.

Hope that's some help! -- Mike
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12808116
PAQed with points refunded (250)

modulo
Community Support Moderator
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access report groups with sums 5 29
Cannot Link Master/Child Fields in a Navigation data sheet Sub Form 1 31
Access Update Query 1 20
MS Access from Delphi 31 32
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

803 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