InsertDatabase in worked WordXP but not in Word 2003

[Virginia Kirkendall]

Hi folks:

The following snippet worked from a WordXP template function but
doesn't work as expected in 2003.
######################################
sConn = "DSN=dsnPPTS;SERVER={Server-04};DATABASE=BACK;TRUSTED_CONNECTION=YES;"

With rQuote
.Collapse direction:=wdCollapseEnd
.InsertDatabase _
Format:=wdTableFormatGrid8, Style:=191, _
connection:=sConn, _
SQLStatement:=sSQL
.Rows(1).HeadingFormat = True
End With
######################################

In 2003 the Connect to DSN prompt is displayed at the InsertDatabase
line even though the dsn has been specified in the connection string.

Has anyone else seen this and why would it happen in 2003 but not XP?

Also, I tried using DataSource:=dsnPPTS and remove dsn from the
connection string, but got the following error:

****Operation cannot be completed because of display or database
engine failures.****

Hm?

Thanks - any comments are welcome.

 

[Virginia Kirkendall]

Hi folks -- anyone else seen this problem? I am at a loss & really
could use some help - urgent. Thanks much.

 

[Andi Mayer]

Hi folks -- anyone else seen this problem? I am at a loss & really
could use some help - urgent. Thanks much.

check your references, I heard that DAO is reappering in the standard
references in front of ADO
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Virginia Kirkendall]

Hi Andi:

Checked the references. Neither ADO nor DAO were checked. I added
ADO, but the Data Source dialog still appears. Any other ideas?

 

[Andi Mayer]

Hi Andi:

Checked the references. Neither ADO nor DAO were checked. I added
ADO, but the Data Source dialog still appears. Any other ideas?

Sorry I don't use 2003.

I will change to 2003 after Sp3

If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

 

[Virginia Kirkendall]

Well, in case anyone is interested, I found a way around the issue.
It is not "solved" in my opinion since the very same ODBC DSN
connection string worked flawlessly in XP but produces a Select Data
Source dialog box in 200. I never found a way to use a system DSN
with the InsertDatabase Method. I may be wrong here, but I believe
ODBC is no longer supported like it was and MS wants us to use the OLE
DB or Office Database Sources now.

What I ended up having to do was create a office database source
(.odc) on the network so all users can access it. Based on
suggestions I found here, I recorded a macro that used the Insert
Database wizard in Word 2003 to whip up the code below. I then pasted
it into my existing fuction that grabs data from the SQL Server based
on a date entered by the user in input boxes:

#####################################################################
##removing any part of the InsertDatabase line broke the connection##
#####################################################################
sConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=Back;Data Source=SERVER-04;"

'Set up Quotes Requested Table
Set rQuote = ActiveDocument.Bookmarks("QuoteData").Range

With rQuote
.Collapse direction:=wdCollapseEnd
.InsertDatabase Format:=23, Style:=191, LinkToSource:=False, _
Connection:=sConn, SQLStatement:=sSQL, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
DataSource:= _
"M:\Project Files\Status
Reports\Templates\StatusReports.odc", _
From:=-1, _
To:=-1, _
IncludeFields:=True
.Rows(1).HeadingFormat = True
End With