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

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

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

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