Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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 10:15 AM |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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 01:00 PM |
#11
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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:
|
#13
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word could not re-establish a DDE Connection to Microsoft Access | Mailmerge | |||
DDE connection w/ Access & Word 2007 | Mailmerge | |||
Word could not reestalish a DDE connection to ACCESS DB | Mailmerge | |||
mailmerge from access database using DDE connection? | Mailmerge | |||
Data Source Connection Problem | Mailmerge |