DDE Access to Word using mde

[Beth]

I would like to give my clients mde files instead of the open source mdb.
However, I need to export reports(mailmerge) to Word and this doesn't seem to
work with DDE connection. It gives error message "....mde is locked for
editing by another user. Click 'Notify' to open a read-only copy of the
document...." Clicking notify doesn't work either. it hangs and I have to
kill Word throught the task manager.

connecting with ODBC works great! However, ODBC doesn't handle memos and
dates.

By the way there are no other users(except the current opening of the file)
and this has nothing to do with opening shared or exclusive in the options
menu.

DDE works with mdb but not with mde.

Any suggestions?

Thank you,
Beth

 

[Peter Jamieson]

Here, in the simple case where you have a Word 2003 document and you connect
to an Access 2002/3 .mde using DDE, the connection is fine. Assuming you are
actually launching a mailmerge from Access,
a. how exactly are you doing that?
b. can you try the simpler case and see if that works?
c. which versions of Word and Access are you using? (It sounds like 2000
(which I can't test right now), but if it's 2002/2003 have you tried the OLE
DB connection method as well?)

Peter Jamieson

 

[Beth]

I have all three(2000,2002,2003) versions of Access and Word.

I am opening Word from an Access procedure as follows:
Set WordDoc = GetObject(WordFileSpec, "Word.Document")

strConnect = "TABLE " & merge_qry
strSql = ""
If (WordDoc.Application.Version > 9#) Then
strSql = "SELECT * FROM [" & merge_qry & "]"
End If

WordDoc.MailMerge.OpenDataSource _
Name:=Datafile, _
ReadOnly:=True, LinkToSource:=True, _
Connection:=strConnect, _
SQLStatement:=strSql

where Datafile is the database name and merge_qry is the table name.

What I found is this:

With Access running as mdb, this works for all combinations.
With Access running as mde:
It works fine with any version of Access and Word 2002.
It works fine with any version of Access and Word 2003 but I currently have
some problem with my installation. I keep getting the old "Encountered a
problem and has to close" message for the 2nd instance of Access that Word
opens. but Word opens just fine.
It does not work with any version of Access and Word 2000. I get the message
originally noted.

What is the OLE DB method that you mentioned? Does it handle memos?

Thanks,
Beth

 

[Peter Jamieson]

What is the OLE DB method that you mentioned? Does it handle memos?

OLE DB is "OLE Database", i.e. "Object Linking and Embedding - Database",
and was Microsoft's successor to ODBC, sort of.

OLE DB is the method that Word 2002/2003 use by default - in fact, given the
code you are using, I am pretty sure that when your Access code opens the
word document in Word 2002/2003, Word will be using OLE DB rather than DDE
to open the data source. Word will ignore what you put in the connect string
and use the name and the SQLStatement to get the data. If you really wanted
to get Word 2002/2003 to use DDE, you would have to add a parameter to the
call, i.e.

Subtype:=wdMergeSubtypeWord2000

However, Word 2000 doesn't know about this parameter so you have to consider
having two versions of your OpenDataSource call.

So either the OLE DB connection is handling your dates and memos just fine
(it should, but issues with memos do pop up from time to time), or I'm wrong
and DDE is being used.

However, you cannot use OLE DB as a mailmerge data source connection method
in Word 2000 - the OLE DB code just isn't there. ODBC should get your dates
OK, but you would need to use switches in the Word fields to format them
correctly. But again, you may have discovered otherwise. Further, what ODBC
does with Access memo fields depends on which version of the Jet ODBC driver
you are using - you may have seen the article about this at

http://support.microsoft.com/kb/242072

If the 50000 characters mentioned in there is enough, and you are in a
position to install the SP where required, that could be a way forward.

It does not work with any version of Access and Word 2000. I get the
message
originally noted.

Did you try the "opening Word manually and connecting manually to a .mde as
I suggested? If not, I still think it would be worth establishing whether
there is simply a problem with WOrd 2000 and .mde. I will try to have a look
at it myself, but I'm in the middle of sorting out my virtual machines and
may not be able to do that for a while.

Peter Jamieson

I have all three(2000,2002,2003) versions of Access and Word.

I am opening Word from an Access procedure as follows:
Set WordDoc = GetObject(WordFileSpec, "Word.Document")

strConnect = "TABLE " & merge_qry
strSql = ""
If (WordDoc.Application.Version > 9#) Then
strSql = "SELECT * FROM [" & merge_qry & "]"
End If

WordDoc.MailMerge.OpenDataSource _
Name:=Datafile, _
ReadOnly:=True, LinkToSource:=True, _
Connection:=strConnect, _
SQLStatement:=strSql

where Datafile is the database name and merge_qry is the table name.

What I found is this:

With Access running as mdb, this works for all combinations.
With Access running as mde:
It works fine with any version of Access and Word 2002.
It works fine with any version of Access and Word 2003 but I currently
have
some problem with my installation. I keep getting the old "Encountered a
problem and has to close" message for the 2nd instance of Access that Word
opens. but Word opens just fine.
It does not work with any version of Access and Word 2000. I get the
message
originally noted.

What is the OLE DB method that you mentioned? Does it handle memos?

Thanks,
Beth

Here, in the simple case where you have a Word 2003 document and you
connect
to an Access 2002/3 .mde using DDE, the connection is fine. Assuming you
are
actually launching a mailmerge from Access,
a. how exactly are you doing that?
b. can you try the simpler case and see if that works?
c. which versions of Word and Access are you using? (It sounds like 2000
(which I can't test right now), but if it's 2002/2003 have you tried the
OLE
DB connection method as well?)

Peter Jamieson