Ah... ignore my other mail, which may appear above or below this one -
it's still in the ether as I type.
My problem/solution was this, and maybe it will help the original
poster:
BACKGROUND:
When you start a mailmerge from Access, you don't actually pass the
data to Word. What happens is that Word tries to make a link to the
table/query in Access so that it can suck up the data for itself. Word
links the MainDocmument to the SourceDocument.
To do this, Word looks for an instance of "MS Access"*. This is the
default name that Windows uses for the Access program. If Word finds
an instance running, it will use it. If not, it creates a new one.
If Word finds an existing instance, with your database open, then it
can access the data, because that file is already open and therefore
security has been passed. The mail merge continues without a problem.
PROBLEM:
If Word has to open a new instance of your database (ie. it cannot
find a running instance of Access), then it gets stuck at the security
prompt because it is trying to open the database file without
reference to the Workgroup file/security settings. You can replicate
this by trying to open a secured db by clicking direct on the db file
in Explorer - you can only open it via the shortcut which references
the WorkGroup file.
So, why isn't Word using the instance of Access that is open and
running the code?
SOLUTION:
Because Word is searching for the string "MS Access"* IN THE TITLE
BAR. If you have changed the Application Title in Options->StartUp, or
created the AppTitle property (same thing, but done via VBA), then
Word will not find it.
What to do:
At the start of my mail merge routine, I added these lines:
On Error Resume Next
CurrentDb.Properties.Delete ("AppTitle")
CurrentDb.Properties.Refresh
The 'On Error' ensures that if the Delete command fails (because
AppTitle hasn't been created, then the code continues. You may want to
put a nother On Error line after the above to redirect errors to your
usual error handler.
The Delete command removes the AppTitle property, resetting the Title
bar value to the default - which Word is searching for.
The Refresh command ensures that the Properties Collection is fully
aware of this change. Without this, Access still thinks it's got your
custom title, and Word won't find it. I didn't have this line, and
that was my problem.
At the end of your mail merge routine, insert these lines:
Set prp = CurrentDb.CreateProperty("AppTitle", dbText, "My
Application")
CurrentDb.Properties.Append prp
Application.RefreshTitleBar
"My Application" is your custom text. Note that you need to first
Create the property, then Append it to the properties collection.
RefreshTitleBar ensures the title bar is... er... refreshed... and
your user won't even see it flicker.
CONCLUSION:
Long-winded solution for 6 lines of simple code, but hopefully I've
explained the problem clearly for others. The Properties collection is
a bit different from other Collections in that most of the StartUp
options need to be Created as properties in ordr to use them. They
don't inherently exist, despite being in the StartUp dialog box.
ALTERNATIVE SOLUTION:
Export your data to a .txt, .xls or .csv file, and have Word use that.
Word doesn't even need to use Access then. Have a look at the solution
presented here:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html
* Could be "Microsoft Access". Doesn't matter, the point is, it is the
default name the system uses for Access.
