Connection problem with Access

[Murray Muspratt-Rouse]

I already have 1 .mdb working with mailmerge. With a 2nd one Word i
asking me to sign in authorise the connection to the .mdb. Here is th
Access VBA code, which some may find familiar - Doug Robbins and Pete
Jamieson helped me to get it working some months ago!

Sub OpenWordDoc(strDocName As String, strLetterDescription As String
strFormName As String)
Dim objApp As Object
Dim objMMMD As Object
Dim strCurrentFileName As String
On Error Resume Next
DoCmd.OpenQuery "Delete all rows from MergeTable"


'Load data to MergeTable with a query that collects the required dat
after update

If strFormName = "Volunteers" Then
If strLetterDescription = "REFERENCE REQUEST" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendVolunteerRefereedata")
Else
If strLetterDescription = "Volunteer Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateVolunteerRefereeChaserDate")
DoCmd.OpenQuery ("qryVolunteerRefereechaser")
Else
If strLetterDescription = "TRAINING DATES" Then
DoCmd.RunMacro ("Set up training dates data fo
merge")
Else
DoCmd.OpenQuery ("Volunteer letter data")
End If
End If
End If
End If
If strFormName = "Clients" Then
If strLetterDescription = "REFERENCE CLIENT" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeLetterDate")
DoCmd.OpenQuery ("qryAppendClientRefereedata")
Else
If strLetterDescription = "Client Referee chaser" Then
DoCmd.OpenQuery ("qryUpdateClientRefereeChaserDate")
DoCmd.OpenQuery ("qryClientRefereechaser")
Else
DoCmd.OpenQuery ("qryClient")
End If
End If
End If
If strFormName = "Befrienders" Then
DoCmd.OpenQuery ("qryBefriender")
End If
strCurrentFileName = CurrentDb.Name

Set objApp = CreateObject("Word.Application")
objApp.Visible = False
'objApp.Activate
'Set objMMMD = objApp.Documents.Open(FileName:=strDocName)

objApp.Documents.Open FileName:=strDocName
ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False _
, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:=""
_
WritePasswordTemplate:="", XMLTransform:=""

objApp.ActiveDocument.MailMerge.OpenDataSource Name:= _
strCurrentFileName, _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True
_
AddToRecentFiles:=False, PasswordDocument:=""
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:=""
Revert:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Use
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extende
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registr
Path="""";Jet OLEDBatabase Password="""";Jet OLE" _
, SQLStatement:="SELECT * FROM `mergetable`"
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess
With objApp.ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With


Dim intSplitName As Integer
Dim intLength As Integer
intLength = Len(strDocName)
intSplitName = InStrRev(strDocName, "\", , vbTextCompare)
strDocName = Right(strDocName, intLength - intSplitName)


objApp.Windows(strDocName).Activate
objApp.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges
'objMMMD.Close SaveChanges:=False
'Set objMMMD = Nothing


'objApp.Documents.Open strDocName


objApp.Visible = True
objApp.Activate

End Sub

I have a nasty feeling that the Admin password has been changed fro
blank - but this part of the code is still working in the other .mdb
Word was asking for the ODBC password. Can I change the code to use m
account name and password? Please ask for any more information yo
require.

Murra

 

[Peter Jamieson]

If the database has a password "thepassword", you have to specify it in
the connection string, like this (I think). I've assumed username
"theusername"

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= strCurrentFileName, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
"User ID=theusername;Data Source=strCurrentFileName;" & _
"Mode=Read;Jet OLEDBatabase Password=""thepassword"";", _
SQLStatement:="SELECT * FROM `mergetable`", _
SubType:=wdMergeSubTypeAccess

If the database has a workgroup security file, then you have to specify
the user name "theusername", password, and the location of that file
"wgpathname", e.g.

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:=strCurrentFileName, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";" & _
"User ID=theusername;Data Source=strCurrentFileName;" & _
"Mode=Read;Jet OLEDB:System database=""wgpathname"";", _
SQLStatement:="SELECT * FROM `mergetable`", _
SubType:=wdMergeSubTypeAccess

Notes:
a. this is off the top of my head.
b. I don't think you need any of the the other parameters for
OpenDataSource
c. I think at the moment you have "Date Source" when you should have
"Data Source". But maybe you do not need either
d. if you have pathnames for the Data Source and System database that
push the length of the Connection parameter over 255 characters, you may
have a problem
e. it's possible that in order to make this work, you'll have to put
the connection info. into a .odc file rather than supply it inline
f. bear in mind that doing this embeds securty info. in the Word file.
Unfortunately, even if you prompt for the password info. at runtime, the
connection string will probably still end up embedded in the word doc.,
at least if the user saves the file


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Peter, thank you once again for coming to my aid! The database in whic
the mailmerge function is still working I will call db1, the one wit
the problem db2. I have been seeing what I could run this morning an
have found the following. If I try to run the function in db2 it the
produces the same authorisation error in db1. To clear it I have t
reboot. One of the 'help' suggestions that comes up for the DD
reconnection failure is to restart before resorting to ODBC or DAO.
am unable to power down and restart because I am working remotely - th
PC is behind locked doors and I do not have the key! If we are not stil
frozen up on Monday I will hope to be able get someone else to restar
it for me.

I am getting a nasty feeling that what I really ought to do is create
new workgroup file, so as to be certain of knowing what the Admi
password is. Oddly enough, when I had logged on to db2 this morning an
opened the document into which I want to insert the necessary merge dat
in Word and tried to connect to db2 the error message I got said that
did not have the necessary permissions to use it.

To alter the connect statement as you suggest I need a Word 2003 VB
reference manual to ensure that I do not make things worse! I have jus
found that I am able to update the document in db1 - I suppose I shoul
not have been surprised at that!

Murray

 

[Peter Jamieson]

hello Murray,

I'm not completely sure what is going on here but...
a. if trying to access db2 is affecting db1, I wonder if you are using
the same database pathname in both the Name and COnnection string
parameters?
b. frankly, a Word 2003 VBA reference manual is unlikely to help because
- almost every time I have seen documentation for the
OpenDataSource method, it is wrong. That said, it is mostly "benignly"
wrong (i.e. it wouldn't matter if you followed the documentation), but
clearly written by someone who has followed earlier documentaiton and
not checked for themselves.
- /Word/ VBA reference material typically does not document the
connection strings, which are generally regarded as part of the database
documentation.

That said, I'll try and do some tests here to check the facts. It would
be useful to know for sure which type of database security you are
dealing with - "database password" or "workgroup security file".

You may or may not know that with the workgroup security file approach,
permissions can be relatively fine-grained, e.g. an individual may have
read/write permission for tablea, read only permission for tableb, and
no permissions for tablec.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Peter, I ran debug with various 'watches' and can assure you that th
db2 code is definitely pointing at it (strCurrentFileName
CurrentDb.Name produced the path to db2). I can also assure you tha
the db1 code is pointing at db1. I think the reason that I have t
reboot follows on from trying to sign on the the database when th
pop-up tells me to do so. If I accept defeat gracefully the proble
does not occur.

When I have tried to sign in with my own user name the response is tha
the workgroup file cannot be found. I am sure this happened to m
before, so I hope that powering the system down will clear whatever i
preventing the thing working.

As far as permissions go I have given myself 'administer' rights o
everything, including the database. The same goes for Admin - and I a
a member of the Admins group.

More on Monday - if anyone manages to get in to the office!

Murray

 

[Murray Muspratt-Rouse]

From what I have observed there should be no need for the VBA code t
reconnect with the database. It is running under MS Access, not unde
MS Word. Task Manager shows the current applications as 1. the mai
application form 2. the 2nd form 3. Microsoft Access [the 3rd form]
Word does not appear (because it has not been made visible yet). I hav
checked this theory by running a mail merge in db1 with debug and sa
Word appear only when the code made it visible.

If you set up a mail merge document from Word you have to connect t
the merge data source. Does Word then store the connection with th
document so that it can be made when the document is opened? That woul
explain some of the problems others have encountered when they hav
distributed the function to PCs running other versions of Access an
Word.

Murray

 

[Peter Jamieson]

Hi Murray,

Right, I checked various things and
a. in your current code, strCurrentFileName is in the Connection
parameter as a literal, not a variable, which will not work.
b. to use a secured database via OLE DB, you have to connect via a
..odc (Office data connection) file). AFAICS there is no rhyme or reason
to this - it's just the way that OpenDataSource and the so-called Office
Data Source Object that it uses works. If you don't use a .odc,
Word/ODSO just will not honour the security information in your
connection string.
c. any other problems you are having are /likely/ to be to do with
multi-user access open modes - i.e. if one connection has the database
open exclusively, or in a mode that denies other users the capabilty to
read the data.

There are two ways to use a .odc file. if you create a .odc with all the
correct connection info and specify the table to be linked to, you
should be able to open the data source like this:

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= "the pathname of the .odc file"

i.e. with no other parameters. Doiing it that way would mean you would
need one .odc for every different data source.

However, because Word, or ODSO does not make use of the full
capabilities of a .odc to define a query, if you need a more complex
query I believe you have to override the one in the .odc by providing
SQLStatement and/or SQLStatement1 as well.

becuase you can override the query, you can get away with having one
..odc for each database+user combination.

However, it is also possible to set up a completely empty .odc file
(create an empty notepad file, and rename it to empty.odc). You can then
put all the connection info., including security info., in the
connection parameter, and Word/ODSO will now honour it! Go figure!

The advantage of that is that you only need one .odc file, at least for
all .mdb connections. However, this approach is undocumented (I
discovered it by accident several years ago) and presumably unsupported.
It does, however, appear to work in Word 2002/2003/2007.

So, putting all that together, suppose you have an empty .odc called
c:\a\empty.odc and a database password "mypassword", you should be able
to use


objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= "c:\a\empty.odc", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=theusername;Data Source=" & strCurrentFileName & ";" & _
"Mode=Read;Jet OLEDBatabase Password=""mypassword"";", _
SQLStatement:="SELECT * FROM `mergetable`"

or even

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= "c:\a\empty.odc", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"User ID=theusername;Data Source=" & strCurrentFileName & ";" & _
"Mode=Read;Jet OLEDBatabase Password=mypassword;", _
SQLStatement:="SELECT * FROM `mergetable`"

For a workgroup security file called c:\a\security.mdw, user name of
myusername and password of mypassword, you need

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= "c:\a\empty.odc", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=""mypassword"";" & _
"User ID=theusername;Data Source=" & strCurrentFileName & ";" & _
"Mode=Read;Jet OLEDB:System database=""c:\a\security.mdw"";", _
SQLStatement:="SELECT * FROM `mergetable`"

or with srings that contain no spaces etc. you can probably use

objApp.ActiveDocument.MailMerge.OpenDataSource _
Name:= "c:\a\empty.odc", _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password=mypassword;" & _
"User ID=theusername;Data Source=" & strCurrentFileName & ";" & _
"Mode=Read;Jet OLEDB:System database=c:\a\security.mdw;", _
SQLStatement:="SELECT * FROM `mergetable`"

AFAICS you do not need any other parameters. Some of the others are
meaningless for an OpenDataSource; some are only applicable if your data
source is a Word document. Even "LinkToSource" does not apply to OLE DB
connections (I'm not sure it applies to any type of connection in recent
versiosn of Word, but it may still apply to MS Query connections).

As I say, I suspect any other problems will be related to simultaneous
access using different access modes. Nor am I saying that they will
necessarily be easy to fix.

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Hi Peter,

I really do appreciate all the time and trouble you have taken to
investigate my problem. There is one thing I do not understand - why
does exactly the same code work in db1 and not in db2 on the same PC?
When I started to add the mail merge function into db2 I copied the VBA
code module from db1 and then made one change to the VBA code - the
default query that runs to load MergeTable with the data to be merged
for "Volunteers".

a. Now I understand what you say! Because the connection string is
enclosed in quotes the whole lot is a literal.
b. I must have a go at producing a .odc file
c. As I am the only user of the db at the moment I do not think that
this applies.

Murray

 

[Peter Jamieson]

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

From what I have observed there should be no need for the VBA code to
reconnect with the database. It is running under MS Access, not under
MS Word. Task Manager shows the current applications as 1. the main
application form 2. the 2nd form 3. Microsoft Access [the 3rd form].
Word does not appear (because it has not been made visible yet). I have
checked this theory by running a mail merge in db1 with debug and saw
Word appear only when the code made it visible.

If you set up a mail merge document from Word you have to connect to
the merge data source. Does Word then store the connection with the
document so that it can be made when the document is opened? That would
explain some of the problems others have encountered when they have
distributed the function to PCs running other versions of Access and
Word.

Murray

hello Murray,

I'm not completely sure what is going on here but...
a. if trying to access db2 is affecting db1, I wonder if you are
using
the same database pathname in both the Name and COnnection string
parameters?
b. frankly, a Word 2003 VBA reference manual is unlikely to help
because
- almost every time I have seen documentation for the
OpenDataSource method, it is wrong. That said, it is mostly "benignly"

wrong (i.e. it wouldn't matter if you followed the documentation), but

clearly written by someone who has followed earlier documentaiton and
not checked for themselves.
- /Word/ VBA reference material typically does not document the
connection strings, which are generally regarded as part of the
database
documentation.

That said, I'll try and do some tests here to check the facts. It would

be useful to know for sure which type of database security you are
dealing with - "database password" or "workgroup security file".

You may or may not know that with the workgroup security file approach,

permissions can be relatively fine-grained, e.g. an individual may have

read/write permission for tablea, read only permission for tableb, and

no permissions for tablec.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Hi Peter!

As soon as someone appeared in the office this morning I powered dow
the PC - this made no difference, as you probably expected. I the
followed your instructions to use an empty .odc file and got th
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 ge
so far in the list of database fields is the Jet password! I obviousl
have a syntax problem - is the statement too long?. Here is what I hav
got: -

Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7826"";User ID=Murra
M-R;
Data Source=C:\Documents and Settings\All Users\Documents\Age Concer
Databases\clients2002.mdb;
Mode=Read;Jet OLEDB:System database=C:\Documents and Settings\Al
Users\Documents\Age Concern Databases\Secured.mdw;,
SQLStatement="SELECT * FROM 'MergeTable'", SQLStatement1:=""
SubType:=wdMergeSubTypeAccess

Help!

Murray

 

[Peter Jamieson]

Hi Murray,

as it stands, you need to terminate the connection string:

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password=""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]

Hi Peter,

I have followed your suggestion, but the system does not like it.
have deliberately started with a normal Word document and then used th
Word Mail Merge 'wizard'. When I get to 'Select recipients' and brows
for the .odc file I am then shown the contents of the record
Connection contained SQLStatement and the string contained in quote
contained SELECT * FROM [mergetable].

 

[Peter Jamieson]

If you try to connect /manually/ with an empty .odc file, Word cannot
connect because it expects all the necessary information to be in the
..odc and it (probably) will not ask to find out the rest of the information.

In other words, the empty .odc can only be used if you are connecting
from VBA and providing the necessary info. in there.

When I'm testing this kind of stuff I generally have a test VBA
subroutine that just contains

ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument

followed by the OpenDataSource call. This is simply because for some
connection types, the connection cannot succeed unless you have closed
any existing connection.

If you want to try using a "real" .odc, here's a sample taken from a
Word 2003 system (Word 2007 uses a slightly different .odc XML format
that has, finally, been documented, but will probably work with the
older ones. I am assuming you are doing a connection to
a database called c:\a\mydatabase.mdb,
with a workgroup security file called c:\a\mysecurity.mdw
using login mylogin,
password mypassword
and connecting to a table called mytable

The content after </head> is not really needed - it's used by Internet
Explorer when it tries to open a .odc. As usual, various things can be
removed from the connection string. To use this .odc from Word VBA, all
you should need is

Activedocument.Mailmerge.OpenDataSource _
Name:="the pathname of the .odc"

NB, AFAICR Word/ODS wrongly takes the name of the table to open from the
<meta name=Table content=mytable> line, which is supposed to be HTML
documentation, instead of the
<odc:CommandText>mytable</odc:CommandText> line.


<html>

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Table content=mytable>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<oescription>A short description...</oescription>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">

<odc:ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Password=mypassword;User
ID=mylogin;Data Source=c:\a\mydatabase.mdb;Mode=Share Deny None;Extended
Properties=&quot;&quot;;Jet OLEDB:System
database=c:\a\mysecurity.mdw;Jet OLEDB:Registry Path=&quot;&quot;;Jet
OLEDBatabase Password=&quot;&quot;;Jet OLEDB:Engine Type=5;Jet
OLEDBatabase Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password=&quot;&quot;;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</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>mytable</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>

</head>

<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0
style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%'
cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding:
3px; background-color: threedface'>
&nbsp;
</td>
<td id=tdTableDropdown style='padding: 3px; background-color:
threedface; vertical-align: top; padding-bottom: 3px'>

&nbsp;
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow
solid; font-family: Arial; font-size: 1pt; padding: 2px;
background-color: threedface'>

&nbsp;
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px;
border-top: 1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>


<script language='javascript'>

function init() {
var sName, sDescription;
var i, j;

try {
sName = unescape(location.href)

i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }

i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }

document.title = sName;
document.getElementById("tdName").innerText = sName;

sDescription = document.getElementById("docprops").innerHTML;

i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }

if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);

if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {

}
}
</script>

</body>

</html>




Peter Jamieson

http://tips.pjmsn.me.uk

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].

 

[Murray Muspratt-Rouse]

Peter, I have solved the problem of setting up mail merge documents b
exporting MergeTable as an Excel spreadsheet. When in doubt - cheat!

I am now facing a new problem - how to point Jet OLEDB:System Databas
at the PC that acts as server for other PCs.

Murray

Hi Murray,

as it stands, you need to terminate the connection string:

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7826"";Use

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.u

 

[Peter Jamieson]

When in doubt - cheat!

Quite!

I am now facing a new problem - how to point Jet OLEDB:System Database
at the PC that acts as server for other PCs.

Not sure why you would need this for /Word/ if you have removed the need
to connect to Access data sources, but if it's so that users can
generally speaking get at Access databases, then I think you have to put
the path in the registry. Probably better to google that and ask in an
Access group if you don't find the necessary, although I can probably
dig it out for you if necessary.

Peter Jamieson

http://tips.pjmsn.me.uk

Peter, I have solved the problem of setting up mail merge documents by
exporting MergeTable as an Excel spreadsheet. When in doubt - cheat!

I am now facing a new problem - how to point Jet OLEDB:System Database
at the PC that acts as server for other PCs.

Murray

Hi Murray,

as it stands, you need to terminate the connection string:

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Password=""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]

Hi Peter!

So you are giving me another learning opportunity - Javascript. I'
afraid I do not follow what you say about naming the table on th
odc:CommandTextmytable/odc:CommandText line. I was wondering where th
SQL statement would go in. Is this the place?

As I have told you in another reply post I have solved the problem o
linking Word documents to the data source by exporting the table tha
is loaded with the mail merge data as a spreadsheet and using that t
insert the merge fields. When the VBA code executes Word inserts dat
from the Access table - it obviously has the same name!

Thank you again for all your help

Murray

 

[Peter Jamieson]

So you are giving me another learning opportunity - Javascript.

I hope not! The javascript in .odc files is completely standard and
isn't used by Word.

FWIW I posted my reply before you posted your "export to Excel"
solution, which I guess renders the whole .odc thing obsolete.

However, if you do need to know the answer to this...

I'm
afraid I do not follow what you say about naming the table on the
odc:CommandTextmytable/odc:CommandText line. I was wondering where the
SQL statement would go in. Is this the place?

I think some of my angle brackets have disappeared, which makes things a
bit harder.

I'll try to recap:
a. .odc files are used by a number of Office programs, including Word,
Excel, possibly Publisher and possibly Infopath.
b. .odc files let you define
- a type of .odc ("you want to connect to a database and be
presented with a list of available tables", or "you want to connect to a
specific table"),
- a connection string,
- a type of query string ("the query is a table name" or "the query
is a SQL statement")
c. as far as I know, Excel interprets what you put in a .odc file
"correctly" - if you specify a SQL query, Excel will execute that query,
etc. But Word does not. (FWIW it was always a bit difficult to tell what
"correct" was supposed to mean because the .odc file format was never
officially documented. AFAIK .odc has now been documented, but only the
2007 version)
d. For one thing, I don't think Word will honour anything other than a
"table name" in the .odc file. In other words, you can't put some
complex piece of SQL in there and expect Word to execute it when it
opens the .odc
e. However, Word does not even get the table name from the place it is
supposed to get it from, i.e. that <odc:CommandText> element. AFAICR
Word actually gets it from a meta tag (which is supposed to be a piece
of documentation, not an "instruction" that looks like:

<meta name=Table content=mytable>

f. If you only need to specify a table name (i.e. you want all the
rows and all the columns in that table), then you can specify the name
in the .odc, name the .odc in the Name parameter of Word's
OpenDataSource call, and that's it.
g. If however, you need to specify any kind of filtering or any SQL
that is anything more than "SELECT * FROM [tablename]", you have to
specify that SQL in the SQLStatement (and if it's a long piece of SQL,
in SQLStatement1) parameter(s) in the OpenDataSource method call.

So just for fun, let's recap:
a. Microsoft has a number of different connection methods to let you
get data from Access, none of which is a completely reliable method
b. In some cases, Microsoft forces you to use a .odc (or perhaps a
..udl, but let's not go there) even though all the necessary connection
information could be in the Connection parameter of the OpenDataSource call
c. Office 2003 .odc is undocumented (unless that has changed recently)
d. Word gets the table name it needs to connect to from the wrong
place in .odc
e. You can't use .odc facilities for specifying a query. You have to
do it in OpenDataSource.
f. Whatever you do, Word ends up embedding your security information
in every Word document that is saved after connecting to a secured
Access data source
g. If you get the slightest thing wrong, Word wil fail.

Easy to explain, eh?

OK, I could probably do better. But it's just a pity that Word's
super-duper modern way of doing "mailmerge" is something you can only
do if you can program in .NET, understand how content controls work,
etc. etc. In other words, something that ordinary users expected to be
able to do "out of the box" has been turned into something only
so-called I.T. professionals can do. I find that set of design and
technological approaches very difficult to respect.


Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Peter, I should have posted a reply to my own post that you have kindl
answered - I needed to change the connection statement in the Acces
VBA for reconnecting Word to Access, so as to point at the workgrou
.mdw file on the server PC. I used the Network Places address for it
With that I have been able to copy the Access front end to both PC
local to the server PC and to remote ones.

Murray

 

[Peter Jamieson]

Hi Murray,

I take it that means you're sorted, at least for now?

Peter Jamieson

http://tips.pjmsn.me.uk

 

[Murray Muspratt-Rouse]

Hi Peter,

Yes, you may! I have put any attempt to understand .odcs on the bac
burner...

Thanks again