[Murray Muspratt-Rouse]

I already have 1 .mdb working with mailmerge. With a 2nd one Word is asking me to sign in authorise the connection to the .mdb. Here is the Access VBA code, which some may find familiar - Doug Robbins and Peter 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 data 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 for 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="""";Us er ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry 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 from 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 my account name and password? Please ask for any more information you require.

Murray

[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 wrote:
I already have 1 .mdb working with mailmerge. With a 2nd one Word is
asking me to sign in authorise the connection to the .mdb. Here is the
Access VBA code, which some may find familiar - Doug Robbins and Peter
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 data
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 for
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="""";Us er
ID=Admin;Date Source=strCurrentFileName;Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
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 from
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 my
account name and password? Please ask for any more information you
require.

Murray

[Murray Muspratt-Rouse]

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

[quote=Peter Jamieson;394254]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

[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 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 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, 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

[quote=Peter Jamieson;394386]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

[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=""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

[Murray Muspratt-Rouse]

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

[quote=Peter Jamieson;394386]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

[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

Murray Muspratt-Rouse wrote:
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

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