Home |
Search |
Today's Posts |
|
#1
|
|||
|
|||
Data Source Connection Problem
Hi-
About 2 years ago I wrote a mailmerge? macro? in Word97 which opens forms, asks questions then merges everything into a document. It worked fine until we changed servers. I changed both instances of the connection in the macro from the old server name to the new. In ODBC, I added the new SQL server connection and deleted the old one. Now, when I try to run the template, Word gives an Open Database Connectivity Error and says "Data source name not found and no default driver specified." What have I not done? When I search the entire project I do not find the old server name. Please help. Thanks, Marty |
#2
|
|||
|
|||
Hi ?B?TWFydHk=?=,
Difficult to say; you don't give us a lot to go on. Are you still using Word 97, or have you upgraded to another (which) version? Did you try recording the new connection in a macro? Please copy/paste the macro code you've tested in your reply. About 2 years ago I wrote a mailmerge? macro? in Word97 which opens forms, asks questions then merges everything into a document. It worked fine until we changed servers. I changed both instances of the connection in the macro from the old server name to the new. In ODBC, I added the new SQL server connection and deleted the old one. Now, when I try to run the template, Word gives an Open Database Connectivity Error and says "Data source name not found and no default driver specified." What have I not done? When I search the entire project I do not find the old server name. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#3
|
|||
|
|||
Hi Cindy-
I am still using Word97. There are 2 connections. The first is: WordBasic.MailMergeMainDocumentType 0 WordBasic.MailMergeOpenDataSource Name:="", ConfirmConversions:=0, ReadOnly:=0, LinkToSource:=0, AddToMru:=0, PasswordDoc:="", PasswordDot:="", Revert:=0, WritePasswordDoc:="", WritePasswordDot:="", Connection:="DSN=NCR-SQL;UID=sa;PWD=;APP=Microsoft Query;WSID=SHIVAINDRA;LANGUAGE=us_english;DATABASE =lp_patents", SQLStatement:="execute splp_lppci " & Chr(34) & OrderLetterNumber & Chr(34), SQLStatement1:="" WordBasic.MailMerge CheckErrors:=1, Destination:=0, MergeRecords:=0, From:="", to:="", Suppression:=0, MailMerge:=1, MailSubject:="", MailAsAttachment:=0, MailAddress:="" The second is: lpmcn.Open "DSN=NCR-SQL;uid=sa;pwd=;database=LP_Patents" Set cmd.ActiveConnection = lpmcn cmd.CommandText = "SELECT lpm_order_letter_no, lpm_applicant_code, lpm_country_code, lpm_agent_code, lpm_prefix_no, lpm_case_no, lpm_suffix_no, lpm_filing_date, lpm_all_countries_flag, lpm_crp, lpm_kind_of_protection, lpm_international_appl_no, lpm_auxiliary_client_designation, lpm_drawings, lpm_small_entity, lpm_parent_date, lpm_parent_order_letter_no, lpm_int_appl_date, lpm_convention, lpm_international_appl_no FROM LPMain WHERE lpm_order_letter_no = '" & OrderLetterNumber & "'" lpmrs.CursorLocation = adUseClient lpmrs.Open cmd, , , adLockUnspecified Not sure why I have 2 connections, but someone told me to do it this way. Instead of "NCR-SQL" it used to have the name of the old server. On my local PC, I have ODBC connections to both the old and new servers. The template works fine. When I take out the connection to the old server, Word I get a Data Source Not Found Error. I put a breakpoint in my AutoOpen module of the macro. It never gets there. The error pops up first. When I first created the template, I seem to remember going into MailMerge Helper and going through all 3 steps: Creating Main Document, Data Source and Merge the two. Somehow I feel that this is what is "remembering" the old server name. I think that I need to redefine the Data Source here, but I do not want to undo all the coding that I have. I sure hope that you can help me. Thanks, Marty "Cindy M -WordMVP-" wrote: Hi ?B?TWFydHk=?=, Difficult to say; you don't give us a lot to go on. Are you still using Word 97, or have you upgraded to another (which) version? Did you try recording the new connection in a macro? Please copy/paste the macro code you've tested in your reply. About 2 years ago I wrote a mailmerge? macro? in Word97 which opens forms, asks questions then merges everything into a document. It worked fine until we changed servers. I changed both instances of the connection in the macro from the old server name to the new. In ODBC, I added the new SQL server connection and deleted the old one. Now, when I try to run the template, Word gives an Open Database Connectivity Error and says "Data source name not found and no default driver specified." What have I not done? When I search the entire project I do not find the old server name. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#4
|
|||
|
|||
Hi Marty,
This is going to be very difficult to trouble-shoot long distance, I think. there person who told you to use both connections is no longer available, I take it? The second connection you show has nothing to do with the mailmerge, at least, not directly. This looks like an ADO connection. Perhaps something is being added to the document outside the actual mail merge interface? The first set of code you show was certainly converted from Word 6.0/95 and not created directly in Word 97. I can tell that from the WordBasic at the beginnin go of each line. Word will ALWAYS store the last mail merge connection in the document, and try to connect to that when it opens the document. If the path cannot be found, then you'll get an error. What you might try is to go into the Mail merge helper and, in the first step, choose "Revert to normal Word document". This should disconnect the data source. Now save this (using Save As, so you don't lose your original), close it and try opening it again. Does that behave more as you'd expect? I am still using Word97. There are 2 connections. The first is: WordBasic.MailMergeMainDocumentType 0 WordBasic.MailMergeOpenDataSource Name:="", ConfirmConversions:=0, ReadOnly:=0, LinkToSource:=0, AddToMru:=0, PasswordDoc:="", PasswordDot:="", Revert:=0, WritePasswordDoc:="", WritePasswordDot:="", Connection:="DSN=NCR-SQL;UID=sa;PWD=;APP=Microsoft Query;WSID=SHIVAINDRA;LANGUAGE=us_english;DATABASE =lp_patents", SQLStatement:="execute splp_lppci " & Chr(34) & OrderLetterNumber & Chr(34), SQLStatement1:="" WordBasic.MailMerge CheckErrors:=1, Destination:=0, MergeRecords:=0, From:="", to:="", Suppression:=0, MailMerge:=1, MailSubject:="", MailAsAttachment:=0, MailAddress:="" The second is: lpmcn.Open "DSN=NCR-SQL;uid=sa;pwd=;database=LP_Patents" Set cmd.ActiveConnection = lpmcn cmd.CommandText = "SELECT lpm_order_letter_no, lpm_applicant_code, lpm_country_code, lpm_agent_code, lpm_prefix_no, lpm_case_no, lpm_suffix_no, lpm_filing_date, lpm_all_countries_flag, lpm_crp, lpm_kind_of_protection, lpm_international_appl_no, lpm_auxiliary_client_designation, lpm_drawings, lpm_small_entity, lpm_parent_date, lpm_parent_order_letter_no, lpm_int_appl_date, lpm_convention, lpm_international_appl_no FROM LPMain WHERE lpm_order_letter_no = '" & OrderLetterNumber & "'" lpmrs.CursorLocation = adUseClient lpmrs.Open cmd, , , adLockUnspecified Not sure why I have 2 connections, but someone told me to do it this way. Instead of "NCR-SQL" it used to have the name of the old server. On my local PC, I have ODBC connections to both the old and new servers. The template works fine. When I take out the connection to the old server, Word I get a Data Source Not Found Error. I put a breakpoint in my AutoOpen module of the macro. It never gets there. The error pops up first. When I first created the template, I seem to remember going into MailMerge Helper and going through all 3 steps: Creating Main Document, Data Source and Merge the two. Somehow I feel that this is what is "remembering" the old server name. I think that I need to redefine the Data Source here, but I do not want to undo all the coding that I have. Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004) http://www.word.mvps.org This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-) |
#5
|
|||
|
|||
"Marty" เขียน: Hi- About 2 years ago I wrote a mailmerge? macro? in Word97 which opens forms, asks questions then merges everything into a document. It worked fine until we changed servers. I changed both instances of the connection in the macro from the old server name to the new. In ODBC, I added the new SQL server connection and deleted the old one. Now, when I try to run the template, Word gives an Open Database Connectivity Error and says "Data source name not found and no default driver specified." What have I not done? When I search the entire project I do not find the old server name. Please help. Thanks, Marty |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specific Email Merge w/ Specific Attachements | Mailmerge | |||
Mail merge data source problem | Mailmerge | |||
Cannot Find Its Data Source | Mailmerge | |||
merging Word 2000 data source and word 2003 envelopes | Mailmerge | |||
Merge Data Source path | Mailmerge |