insert DATABASE within mailmerge

[nate]

essentially I have a mail merge that produces headings and parameters for a
DATABASE query, so for every mailmerge heading there is a new query that
produces a table under that mailmerge heading.
Problem is that I want to use me.path in order to fill the path to the
database in the query vs. hard coding it. Below are some scratchy comments
about what I have tried so far.


when you do an insert -> DATABASE and use a mailmerge field inside of the
text that you use for the DATABASE connection. I need to use the me.path in
that as well but don't know how to replace that text.
I know I can access that DATABASE text via:
ActiveDocument.Fields(2).Code
For example if I make a button and in the click event
put a msgbox(ActiveDocument.Fields(2).Code)
it will show me what is being used in the DATABASE query.
But when I try to fill a string var with the correct code and stick that
string into the ActiveDocument.Fields(2).Code object via:
ActiveDocument.Fields(2).Code = tempstr
it errors. maybe because I am running it inside the document.open() func.
if not here then where do I run it? I can't do it manually via buttons etc.
this is all suppose to be an automated process.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bmF0ZQ==?=,

essentially I have a mail merge that produces headings and parameters for a
DATABASE query, so for every mailmerge heading there is a new query that
produces a table under that mailmerge heading.
Problem is that I want to use me.path in order to fill the path to the
database in the query vs. hard coding it. Below are some scratchy comments
about what I have tried so far.

when you do an insert -> DATABASE and use a mailmerge field inside of the
text that you use for the DATABASE connection. I need to use the me.path in
that as well but don't know how to replace that text.
I know I can access that DATABASE text via:
ActiveDocument.Fields(2).Code
For example if I make a button and in the click event
put a msgbox(ActiveDocument.Fields(2).Code)
it will show me what is being used in the DATABASE query.
But when I try to fill a string var with the correct code and stick that
string into the ActiveDocument.Fields(2).Code object via:
ActiveDocument.Fields(2).Code = tempstr
it errors. maybe because I am running it inside the document.open() func.
if not here then where do I run it? I can't do it manually via buttons etc.
this is all suppose to be an automated process.

There's a chance that the string you're trying to use isn't valid, from Word's
POV. Have you tried manually changing a database field's code and updating the
field?

What error are you getting?

Do you realize you'll lose the embedded Mergefield if you replace the .Code?

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

 

[nate]

basically I copied what was originally in the database string and then added
the chr(34) to replace the double quotes that needed to be in there.

No I don't realize that I will lose the embedded mailmerge. Reason is that
as long as the merge is called after the database connection is set it should
be the same as it worked originally.
ie. as long as the special characters exist in the database string being
replaced then it should look like a mail merge. BTW what are the special
characters?? chr(??)

Why would I manually change the code? That would just give me a new string
with the same problem... "Hard coded paths".

It's giving me a type mismatch error.
If I msgbox(tempstr) it looks like the text shown when doing
msgbox(ActiveDocument.Fields(2).Code)
with the exception that the special chars for the mailmerge might not be
correct.
But that shouldn't give a type mismatch.. should it?

here is the string

tempstr = "DATABASE \d " & Chr(34) & " " & Replace(Me.Path, "\", "\\") &
"\\accomplishments.mdb" & Chr(34) & "\c " & Chr(34) &
"Provider=Microsoft.Jet.OLEDB.4.0;Password=\" & Chr(34) & "\" & Chr(34) &
";User ID=Admin;Data Source=" & Replace(Me.Path, "\", "\\") &
"\\accomplishments.mdb;Mode=Read;Extended Properties=\" & Chr(34) & "\" &
Chr(34) & ";Jet OLEDB:System database=\" & Chr(34) & "\" & Chr(34) & ";Jet
OLEDB:Registry Path=\" & Chr(34) & "\" & Chr(34) & ";Jet OLEDBatabase
Password=\" & Chr(34) & "\" & Chr(34) & ";Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password=\" & Chr(34)
& "\" & Chr(34) & ";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Database=False;Jet OLEDBon't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair=False;Jet OLEDB:SFP=False "
tempstr = tempstr & Chr(34) & "\s " & Chr(34) & "SELECT `ProjectNumber`,
`AccountTitle` FROM `qryActivities` WHERE ((`Coop` = ' MERGEFIELD Abbrev '))"
& Chr(34) & " \*MERGEFORMAT"

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?bmF0ZQ==?=,

' MERGEFIELD Abbrev ')

This part. If you replace the .Code with another string,
the mergefield will be lost. And then the DATABASE field
can no longer function. You first need to be clear about
how you're going to handle this before you can even begin
to worry about any error message you're getting.

You CANNOT simply insert Chr$(#) into a string in order to
insert merge fields. It doesn't work that way. The field
code needs to be recreated.

You still haven't told me what code line is giving the type
mismmatch error.

Given that the entire purpose of this is to replace a file
path in the database field code? Your best bet is probably
to store this information in a document variable (see the
VARIABLES collection in the Word VBA) or custom document
property (as in File/Properties).

You code can then change the content of this, rather than
trying to manipulate the field code, directly.

In order to place the content of the Variable or Property
into the Database field, select the current path
information, between the "quotes" and delete it. In its
place insert a DocVariable or a DocProperty field code.
Note that in this case the path should have SINGLE, not
double back-slashes.

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