A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

opendatasource - avoid password prompt



 
 
Thread Tools Display Modes
  #1  
Old May 14th 09, 08:28 PM posted to microsoft.public.word.mailmerge.fields
darren via OfficeKB.com
external usenet poster
 
Posts: 6
Default opendatasource - avoid password prompt

Hi

Pulling my hair out. I have an ACC2003 mde which has basic password
protection (not mdw). It opens a merge document (Word2003) and sets
datasource using opendatasource. However, it prompts the users for a password.
I have tried to include the password but the prompt still appears. All help
appreciated.

strConnection = "DSN=MS Access Database;DBQ=" & CurrentProject.FullName & "
;FIL=MS Access'UID=Admin;PWD=test;"

' Set the mail merge data source
objWord.MailMerge.OpenDataSource _
Name:=CurrentProject.FullName, _
LinkToSource:=True, _
Connection:=strConnection, _
ReadOnly:=True, _
SQLStatement:="SELECT * FROM [" & strTbl & "]"

Thanks

Darren

--
Darren

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...merge/200905/1

Ads
  #2  
Old May 14th 09, 09:58 PM posted to microsoft.public.word.mailmerge.fields
darren via OfficeKB.com
external usenet poster
 
Posts: 6
Default opendatasource - avoid password prompt

Think I've sussed it. I tried setting it manually then looking at
connectstring but still did not work. However, when I tried doing exactly the
same thing but recording it as a macro I got a different result.

Solution:

Name:="", _

--
Darren

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...merge/200905/1

  #3  
Old July 7th 09, 11:53 PM posted to microsoft.public.word.mailmerge.fields
Sudha
external usenet poster
 
Posts: 1
Default opendatasource - avoid password prompt

Hi

So unless one uses macros you can't mailmerge using a password protected mdb?

"darren via OfficeKB.com" wrote:

Think I've sussed it. I tried setting it manually then looking at
connectstring but still did not work. However, when I tried doing exactly the
same thing but recording it as a macro I got a different result.

Solution:

Name:="", _

--
Darren

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...merge/200905/1


  #4  
Old July 8th 09, 10:22 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default opendatasource - avoid password prompt

So unless one uses macros you can't mailmerge using a password
protected mdb?


There are ways to do it, but I wouldn't claim they will always work, and
they tend either to be complicated for the user or compromise security
by storing a password either in the Word document, a plain text file, or
both.

There are at least five ways you could conceivably connect to an Access
data source from Windows Word:
a. via DDE (requires Access on the system doing the merge, and may not
work well in Office 2007, especially with .accdb databases)
b. ODBC
c. OLE DB
d. by exporting data first, and connecting to that
e. by connecting indirectly, e.g. via a linked table in a second
Access database

If you're using Word 2000 or earlier, you can't do (c), but I'm assuming
Word XP or later and right now I'm working with Word 2003. Let's focus
on (a) and (c).

If you open the database and select the DDE method (which would require
you to check Word Tools-Options-General-Confirm conversions on open).
Then, if the database is already open in Access, you should see the list
of tables/queries and be able to open the data source without further
prompts. If the database is not open, Access will try to open it and
prompt for a password. The user will probably not see this prompt as it
typically appears behind the Word Window, so they have to know to look
for it.

If you save/close/re-open the Word document, you should see the same
behaviour except you may see an additional "SQL" prompt at the beginning.

For (c), if you try to open the database, you will probably see the
"Datalink dialog" box which prompts for the database name, login,
password etc. This dialog box has 4 tabs. To open the data source from
here, you need to:
a. Select the Provider tab and ensure that the Microsoft Jet 4.0 OLE
DB Provider is selected (it probably will be). for Access 2007
databases, it would have to be the ACE 12.0 provider.
b. Select the Connection tab and ensure that the database pathname is
correct, and if necessary, the User name. Leave the password blank
(/this/ password is for databases that use workgroup security) and leave
"Blank password" checked. But ensure you check "Allow Saving Password".
c. Select the All tab and double-click the Jet OLEDB: Database
Password item (it's probably about 6 items down in the list). Enter the
database password in the dialog box.
d. Click OK. You should see the list of available queries and tables.
Select the one you want.
e. Click OK. You now see that datalink dialog box again. Check Allow
Saving Passowrd (again) on the Connection tab, and re-enter the database
password in the All tab. Click OK. You should now be connected.
f. if you save/close/re-open that Word document, you will probably see
the datalink dialog box again. You will probably be able to check "Allow
Saving Password" again, re-enter the passord in the All tab, and the
data source will re-open. In other words, the security information is
apparently not being saved.

Obviously,it would be quite a trek for a user to get through that. But
it does work. AFAIK the reason why you get the datalink dialog prompt
twice is because the first time, Word is simply retrieving the list of
tables and queries. The second time, it has already discarded the
security info. but needs to open the source you chose.

An alternative to this is to put all the relevant information in a .odc
file. You can create a proper .odc file by starting with the New
Source... button in Select Data Source dialog, then choosing
Other/Advanced, and making the same selections as you did in the above
procedure. At a certain point, you can select whether you want the .odc
to open a specific table/query, or whether you want to leave that
selection till later. In that dialog box, ensure you check "allow saving
password in file". Then give the .odc a suitable file name and save it.

Word will then prompt for that .odc. When you select it, either the
tale/query you specified should open, or you sshould be prompted for a
table/query, depending on what you selected. However, if you now save,
close and re-open the resulting mailmerge main document, Word will try
to re-open the table/query you selected - it does not look at the .odc
and re-prompt for a table/query.

Or you can create the necessary bits of the .odc using, e.g., Notepad.
e.g. like this one, which opens the database
c:\mydbs\mydb.mdb with password "mypw" and prompts for a table/query.
(Actually, you can cut this down further because a lot of the stuff in
the ConnectionString is not essential)

html

head
meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8"
meta name=ProgId content=ODC.Database
meta name=SourceType content=OLEDB
xml id=docprops/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="";User
ID=Admin;Data Source=c:\mydbs\mydb.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password=mypw;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:Connection
/odc:OfficeDataConnection
/xml
/head
/html

To specify a table/query, let's say it's called "thetablename" you need
something more like this:

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=thetablename
xml id=docpropsoocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40"
oescriptionThe table I selected/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="";User
ID=Admin;Data Source=c:\mydbs\mydb.mdb;Mode=Share Deny None;Extended
Properties="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDBatabase Password=mypw;Jet
OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;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:CommandTextthetablename/odc:CommandText
/odc:Connection
/odc:OfficeDataConnection
/xml
/head
/html

The thing to notice about this is that in theory, the table name should
be specified in this line:

odc:CommandTextthetablename/odc:CommandText


However, because Word interprets the .odc incorrectly, it uses the value
in this line:

meta name=Table content=thetablename


Peter Jamieson

http://tips.pjmsn.me.uk

Sudha wrote:
Hi

So unless one uses macros you can't mailmerge using a password protected mdb?

"darren via OfficeKB.com" wrote:

Think I've sussed it. I tried setting it manually then looking at
connectstring but still did not work. However, when I tried doing exactly the
same thing but recording it as a macro I got a different result.

Solution:

Name:="", _

--
Darren

Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...merge/200905/1


  #5  
Old August 3rd 22, 01:09 PM
Kakashi-1 Kakashi-1 is offline
Junior Member
 
First recorded activity by WordBanter: Apr 2022
Posts: 11
Default

In general, I think that you need to be very careful about data such as passwords, as well as customer data, when it comes to business. If you cannot ensure the cybersecurity of your company on your own, then it is better to contact Data centre services . These services are provided by specialists from wisetek, which I have repeatedly contacted in the past.
  #6  
Old Yesterday, 02:52 PM
groverborme groverborme is offline
Junior Member
 
First recorded activity by WordBanter: Aug 2022
Posts: 5
Default

Interesting post, thank you.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to have MS Word Prompt for ODBC or connection login and password [email protected] Mailmerge 2 September 17th 21 07:18 AM
Default prompt for password Kokomojo Microsoft Word Help 2 September 7th 06 04:44 PM
Prompt for document properties Save opion fails to prompt R.Evans Microsoft Word Help 6 August 18th 06 12:17 AM
bug with OpenDataSource Michel Mailmerge 0 February 3rd 05 04:21 PM
bug with OpenDataSource Michel Mailmerge 0 February 3rd 05 02:40 PM


All times are GMT +1. The time now is 12:23 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.