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 OLEDB
atabase 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 OLEDB
atabase 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=""mypas sword"";" & _
"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=mypassw ord;" & _
"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 wrote:
Peter, I ran debug with various 'watches' and can assure you that the
db2 code is definitely pointing at it (strCurrentFileName =
CurrentDb.Name produced the path to db2). I can also assure you that
the db1 code is pointing at db1. I think the reason that I have to
reboot follows on from trying to sign on the the database when the
pop-up tells me to do so. If I accept defeat gracefully the problem
does not occur.
When I have tried to sign in with my own user name the response is that
the workgroup file cannot be found. I am sure this happened to me
before, so I hope that powering the system down will clear whatever is
preventing the thing working.
As far as permissions go I have given myself 'administer' rights on
everything, including the database. The same goes for Admin - and I am
a member of the Admins group.
More on Monday - if anyone manages to get in to the office!
Murray
Peter Jamieson;394386 Wrote:
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