run-time error '5922' word was unable to open data source

[Mary]

When I created a Macro to insert data from Access XP to
Word XP, it works. However, when I modify it to where
the user enters a value, and based on that value, will
insert data into Word I get this error:

run-time error '5922' word was unable to open data source

What is the problem? Below is the code:

Private Sub Document_Open()


Dim strDate As String



strDate = InputBox("enter date")

Selection.Range.InsertDatabase Format:=0, Style:=0,
LinkToSource:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us
er ID=Admin;Data
Source=H:\DeMiliaM\PRC\PRCTest.mdb;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Jet OLEDBatabase
Password="""";Jet OLEDB:Engine Type=5;Jet OLEDBa" _
, SQLStatement:= _
"SELECT `ID`, `Name`, `Age`, `Date` FROM `test`
WHERE ((`Date` >= #'strDate'#))" _
& "", PasswordDocument:="",
PasswordTemplate:="", WritePasswordDocument _
:="", WritePasswordTemplate:="", DataSource:= _
"H:\DeMiliaM\PRC\PRCTest.mdb", From:=-1, To:=-1,
IncludeFields:=True



End Sub

 

[Cindy M -WordMVP-]

Hi Mary,

When I created a Macro to insert data from Access XP to
Word XP, it works. However, when I modify it to where
the user enters a value, and based on that value, will
insert data into Word I get this error:

run-time error '5922' word was unable to open data source

Try using the Fields.Add method instead of InsertDatabase -
that's never worked reliably, but addin the DATABASE field
directly usually does.

Word is very finicky about the SQL syntax used when
connecting data in the field. Use the "Insert Database"
command from the DATA toolbar to insert the same data, with
a link. Be sure to set a Query Option for the Date field,
so you get the entire SQL syntax you need. You can then
copy the field's content into the Text argument of the
Fields.Add method.

Note, also, that Word 2002 has real problems with updating
links to databases connected using OLEDB. This could also
be the problem you're experiencing. If it continues, try
using an ODBC connection, instead. (Information on how to
do that is in the Mail Merge FAQ on my website.)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail