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 wrote:
Peter, thank you once again for coming to my aid! The database in which
the mailmerge function is still working I will call db1, the one with
the problem db2. I have been seeing what I could run this morning and
have found the following. If I try to run the function in db2 it then
produces the same authorisation error in db1. To clear it I have to
reboot. One of the 'help' suggestions that comes up for the DDE
reconnection failure is to restart before resorting to ODBC or DAO. I
am unable to power down and restart because I am working remotely - the
PC is behind locked doors and I do not have the key! If we are not still
frozen up on Monday I will hope to be able get someone else to restart
it for me.
I am getting a nasty feeling that what I really ought to do is create a
new workgroup file, so as to be certain of knowing what the Admin
password is. Oddly enough, when I had logged on to db2 this morning and
opened the document into which I want to insert the necessary merge data
in Word and tried to connect to db2 the error message I got said that I
did not have the necessary permissions to use it.
To alter the connect statement as you suggest I need a Word 2003 VBA
reference manual to ensure that I do not make things worse! I have just
found that I am able to update the document in db1 - I suppose I should
not have been surprised at that!
Murray
Peter Jamieson;394254 Wrote:
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 OLEDB
atabase 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