Hi Peter,
I have followed your suggestion, but the system does not like it. I have deliberately started with a normal Word document and then used the Word Mail Merge 'wizard'. When I get to 'Select recipients' and browse for the .odc file I am then shown the contents of the record - Connection contained SQLStatement and the string contained in quotes contained SELECT * FROM [mergetable].
.
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""pixie7826"";User ID=Murray M-R;Data Source=C:\Documents and Settings\All Users\Documents\Age Concern Databases\clients2002.mdb;Mode=Read;Jet OLEDB: System Database=C:\Documents and Settings\All Users\Documents\Age Concern Databases\Secured.mdw;"
SQLStatement="SELECT * FROM [mergetable]"
I have tried all day to find a document that sets out how to format the statements to go in the .odc file. What I have is Office Professional Edition 2003. Can you help me further?
Going by the time of your reply are you on the West Coast?
Murray
Quote:
Originally Posted by Peter Jamieson
Hi Murray,
as it stands, you need to terminate the connection string:
Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""pixie7826"";User
ID=Murray
M-R;
Data Source=C:\Documents and Settings\All Users\Documents\Age Concern
Databases\clients2002.mdb;
Mode=Read;Jet OLEDB:System database=C:\Documents and Settings\All
Users\Documents\Age Concern Databases\Secured.mdw;"
It's possible that you may need to quotes various things such as the
User ID and paths (two sets of double quotes at either end, like the
Password) but I don't think so.
The quotes in your SELECT probably need to be backquotes, like this:
SQLStatement:="SELECT * FROM `mergetable`"
rather than straight quotes, like this
SQLStatement="SELECT * FROM 'MergeTable'"
I tend to use [ ] instead to make this clearer:
SQLStatement:="SELECT * FROM [mergetable]"
You don't need SQLStatement1 or subtype, but neither of them should
cause any problems as they stand.
Peter Jamieson
http://tips.pjmsn.me.uk
Murray Muspratt-Rouse wrote:
Hi Peter!
As soon as someone appeared in the office this morning I powered down
the PC - this made no difference, as you probably expected. I then
followed your instructions to use an empty .odc file and got the
connection to work. My next problem is producing a .odc file for Word,
so as to be able to get the merge fields into the document. All I get
so far in the list of database fields is the Jet password! I obviously
have a syntax problem - is the statement too long?. Here is what I have
got: -
Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7 826"";User ID=Murray
M-R;
Data Source=C:\Documents and Settings\All Users\Documents\Age Concern
Databases\clients2002.mdb;
Mode=Read;Jet OLEDB:System database=C:\Documents and Settings\All
Users\Documents\Age Concern Databases\Secured.mdw;,
SQLStatement="SELECT * FROM 'MergeTable'", SQLStatement1:="",
SubType:=wdMergeSubTypeAccess
Help!
Murray
Peter Jamieson;394515 Wrote:
If you don't need the Word object to connect, remove the relevant code
as it's probably a distraction.
However, I'm not sure you can manually connect Word to an Access data
source that has a securit database, unless the Admin username and a
blank password will give you access to the table you need. I think
you'd
probably have to do it the way I suggested, at lleast for an OLE DB
connection. For a DDE connection, if the database is already open,
you're OK.
Even with an OLE DB connection, once you have set it up
programmatically, Word should retain the necessary connection
information. Whether it always uses it when you reopen the document is
a
different matter.
Peter Jamieson
http://tips.pjmsn.me.uk
|