Reply
 
Thread Tools Display Modes
  #1   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default Connection problem with Access

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
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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




  #3   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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

Last edited by Murray Muspratt-Rouse : February 7th 09 at 11:15 AM
  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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





  #5   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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


  #6   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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
  #7   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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





  #8   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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

[quote=Peter Jamieson;394481]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
  #9   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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





  #10   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Hi Peter!

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

Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7 826"";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;,
SQLStatement="SELECT * FROM 'MergeTable'", SQLStatement1:="", SubType:=wdMergeSubTypeAccess

Help!

Murray

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

Last edited by Murray Muspratt-Rouse : February 9th 09 at 02:00 PM


  #11   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

Hi Murray,

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

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""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 wrote:
Hi Peter!

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

Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7 826"";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;,
SQLStatement="SELECT * FROM 'MergeTable'", SQLStatement1:="",
SubType:=wdMergeSubTypeAccess

Help!

Murray

Peter Jamieson;394515 Wrote:
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





  #12   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""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;"
SQLStatement="SELECT * FROM [mergetable]"

I have tried all day to find a document that sets out how to format the statements to go in the .odc file. What I have is Office Professional Edition 2003. Can you help me further?

Going by the time of your reply are you on the West Coast?

Murray

Quote:
Originally Posted by Peter Jamieson View Post
Hi Murray,

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

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""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 wrote:
Hi Peter!

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

Provider=Microsoft.Jet.OLEDB.4.0;Password=""pixie7 826"";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;,
SQLStatement="SELECT * FROM 'MergeTable'", SQLStatement1:="",
SubType:=wdMergeSubTypeAccess

Help!

Murray

Peter Jamieson;394515 Wrote:
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




  #13   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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:CommandTextmytable/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=docpropsoocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40"
oescriptionA short description.../oescription
/oocumentProperties
/xmlxml id=msodcodc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40"
odc:Connection odc:Type="OLEDB"

odc:ConnectionStringProvider=Microsoft.Jet.OLEDB .4.0;Password=mypassword;User
ID=mylogin;Data Source=c:\a\mydatabase.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System
database=c:\a\mysecurity.mdw;Jet OLEDB:Registry Path="";Jet
OLEDBatabase Password="";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="";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:CommandTypeTable/odc:CommandType
odc:CommandTextmytable/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'
 
/td
td id=tdTableDropdown style='padding: 3px; background-color:
threedface; vertical-align: top; padding-bottom: 3px'

 
/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'

 
/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

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

  #14   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

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

[quote=Peter Jamieson;394769]Hi Murray,

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

Connection:="Provider=Microsoft.Jet.OLEDB.4.0;Pass word=""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
  #15   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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

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

Peter Jamieson;394769 Wrote:
Hi Murray,

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

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







  #16   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Hi Peter!

So you are giving me another learning opportunity - Javascript. 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?

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

Thank you again for all your help

Murray

[quote=Peter Jamieson;394960]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:CommandTextmytable/odc:CommandText line.
  #17   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

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 wrote:
Hi Peter!

So you are giving me another learning opportunity - Javascript.



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?

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

Thank you again for all your help

Murray

Peter Jamieson;394960 Wrote:
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:CommandTextmytable/odc:CommandText line.





  #18   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Peter, I should have posted a reply to my own post that you have kindly answered - I needed to change the connection statement in the Access VBA for reconnecting Word to Access, so as to point at the workgroup .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 PCs local to the server PC and to remote ones.

Murray

[quote=Peter Jamieson;395061] 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
  #19   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Connection problem with Access

Hi Murray,

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

Peter Jamieson

http://tips.pjmsn.me.uk

Murray Muspratt-Rouse wrote:
Peter, I should have posted a reply to my own post that you have kindly
answered - I needed to change the connection statement in the Access
VBA for reconnecting Word to Access, so as to point at the workgroup
.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 PCs
local to the server PC and to remote ones.

Murray

Peter Jamieson;395061 Wrote:
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





  #20   Report Post  
Murray Muspratt-Rouse Murray Muspratt-Rouse is offline
Member
 
Location: Mill Hill, London, England
Posts: 44
Default

Hi Peter,

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

Thanks again

Murray
[quote=Peter Jamieson;395224]Hi Murray,

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

Peter Jamieson
Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word could not re-establish a DDE Connection to Microsoft Access Len Mailmerge 16 October 1st 09 11:16 AM
DDE connection w/ Access & Word 2007 mariacle Mailmerge 8 January 14th 09 07:38 PM
Word could not reestalish a DDE connection to ACCESS DB capri Mailmerge 7 May 9th 07 07:19 PM
mailmerge from access database using DDE connection? Mike Mailmerge 3 January 11th 06 10:00 PM
Data Source Connection Problem Marty Mailmerge 4 March 26th 05 12:20 PM


All times are GMT +1. The time now is 01:20 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"