Data Source Connection Problem

[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

 

[Cindy M -WordMVP-]

Hi =?Utf-8?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

 

[Marty]

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

 

[bus&ong&sun]

"Marty" เขียน:

 

[Cindy M -WordMVP-]

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