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
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 |
#7
|
|||
|
|||
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 |
#8
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 |
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 |