[Graham Mayor]

I have cross posted to the mailmerge forum in case Peter Jamieson has not
been watching and has some ideas

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I will leave it up to the user -- high maintenance type. She
and I are wondering why the data changes but guess we will never know
why, there seems to be no logic to what numbers change. I will be
discussing this with the network group also, if they can set up
appropriate permissions, think our first try will be to remove the
password. They are also having an issue with the database locking
with the error 3734 (Database has been placed in a state....) when
using mail merge, have you seen this and do you have any thoughts on
this? I was wondering if the ODBC connection might also do this.
They did not have these troubles in A97 so are blaming the recent
conversion.

"Graham Mayor" wrote:

If I follow your example, I now see what you see - but I am not
sure if that takes us any farther forward. I guess it's a case of
which workaround the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in
these numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04,
and $1811.75.These are actual numbers from a governmental db. Now
put a database level password on it. When connecting to this db
from Word, it apparently can not use oledb, even when the option to
confirm conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in
your document with the salary field. I actually put it in twice,
once no formatting and once with the currency formatting: \
#$#,###.00. And you should see the error in the formatted only
figures with all but the last two numbers which convert OK. Let me
know what you see. If I use DDE, all is OK, but the user will have
to do this each time they use mail merge with a new letter. Also,
if I set the Access db to number and let only Word do the
formatting, it appears OK. However, the user would like it stored
in currency for reports, etc. I think we may end up taking off the
password and using network file security. This will revert back to
oledb and is also OK. It seems to be when Word ues ODBC that we
have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other
than a Currency field with currency formatting forced on it
through the field's properties. That is most likely the source
of your problem. The field Data Type should be set to Currency
with no change to the defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the
field code for currency formatting it will appear as 927.2. In
most cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no
calculations, only straight merging form one table. This is
Word2003 and Access 2003. Thoughts?

[Peter Jamieson]

Roughly speaking,
a. there have always been problems with the Currency field type in Access.
I think it's because the underlying representation is not float or double
and that the libary routines that Microsoft uses in Word and possibly
elsewhere do not deal with these numbers correctly, but have never tried to
get to the bottom of it. There is or was a KB article about some aspects of
this but I'm not in a position to look for it right now. The only ways I
know to work around these problems are (1) change the data type of the
database field) and possibly (2) use a query, probably with an explicit
conversion function such as cstr() to return the same value in a different
format. If you need to do calculations on these numbers, do it in the query.
If the query is sufficiently short, you can put it in a Word VBA
OpenDataSource call - otherwise it needs to be in an Access query.
b. to make an OLEDB connection to an Access database with a password you
have to create a suitable .odc file and use that as your data source. The
problem with this is that the password has to be embedded in the .odc, or
the user is presented with the scary data link dialog box and has to know
how to enter the password. if they do that and save the .doc, the password
is embedded in a .doc and is probably rather easily discovered.

A sample .odc as created by the Office data link editor to open a table
called mytable in a database called c:\mydbs\mdb.mdb with a password mydbpw
looks 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=mytable
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=mydbpw;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

-----------------------------------------

You can chop a lot of that out (I would guess you can reduce the connection
string to

odc:ConnectionStringProvider=Microsoft.Jet.OLEDB .4.0;Password="";User
ID=Admin;Data Source=C:\mydbs\mydb.mdb;Mode=Share Deny None;Jet
OLEDBatabase Password=mydbpw;/odc:ConnectionString

)

If you leave the Jet OLEDBatabase Password=mydbpw; bit out but open the
database with the .odc the user should see the data link dialog. all they
actually have to do is go to the All tab and fill in the correct value for
the Jet OLEDbatabase Password, then press OK. However, this does not
appear to work if you specify the .mdb dirctly as the data source.

If you have problems with putting a password in plain text in a .odc (I can
think of circumstances in which you wouldn't, but not many) then
a. you are probably better off using Windows directory security, i.e.
relying on Windows user security
b. you could try the following:
- make the connection via the .odc route as suggested.
- save and close the .doc
- edit the .odc in Notepad and remove te Jet OLEDBatabase Passowrd
entry
- save the .odc
- reopen the .doc. Here, it seems to remember the previous settings. But
I wouldn't want to rely on it. Although the password may be buried in the
binary .doc file format, it only takes somweone to save it as .rtf or .html
to see the password, or someone to dig around a bit using VBA, and so on.

NB, you have to use a .odc or .udl to open OLE DB data source in Word if you
want to modify anything in the standard connection string other than the
stuff that Word puts in itself. You have to use a .odc rather than a .udl
(as far as I know) if you want to specify a particular table to connect to.

Peter Jamieson


"Graham Mayor" wrote in message
...
I have cross posted to the mailmerge forum in case Peter Jamieson has not
been watching and has some ideas

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I will leave it up to the user -- high maintenance type. She
and I are wondering why the data changes but guess we will never know
why, there seems to be no logic to what numbers change. I will be
discussing this with the network group also, if they can set up
appropriate permissions, think our first try will be to remove the
password. They are also having an issue with the database locking
with the error 3734 (Database has been placed in a state....) when
using mail merge, have you seen this and do you have any thoughts on
this? I was wondering if the ODBC connection might also do this.
They did not have these troubles in A97 so are blaming the recent
conversion.

"Graham Mayor" wrote:

If I follow your example, I now see what you see - but I am not
sure if that takes us any farther forward. I guess it's a case of
which workaround the user will be happiest with?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Yes, I have done more testing now and narrowing the problem down.
Create a new database. In a table, e.g employees, create a field, I
named it salary. Make it currency data type and format. Put in
these numbers:$1983.90, $927.20, $781.60, $2233.50, and $1355.04,
and $1811.75.These are actual numbers from a governmental db. Now
put a database level password on it. When connecting to this db
from Word, it apparently can not use oledb, even when the option to
confirm conversions on open is NOT selected. I guess because of the
password. It appears to default in this instance to use ODBC. Either
by choosing ODBC when the option to confirm conversion is selected,
or by Word defaulting to this connection type, use mail merge in
your document with the salary field. I actually put it in twice,
once no formatting and once with the currency formatting: \
#$#,###.00. And you should see the error in the formatted only
figures with all but the last two numbers which convert OK. Let me
know what you see. If I use DDE, all is OK, but the user will have
to do this each time they use mail merge with a new letter. Also,
if I set the Access db to number and let only Word do the
formatting, it appears OK. However, the user would like it stored
in currency for reports, etc. I think we may end up taking off the
password and using network file security. This will revert back to
oledb and is also OK. It seems to be when Word ues ODBC that we
have the problem.

Karen
"Graham Mayor" wrote:

I have tried and failed to reproduce this. Is there any more that
you can tell us about the Access file and how the relevant data is
created and stored?

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org


KYaeger wrote:
Also, WHY does simple formating change the values??? I am talking
about random changing like e.g. 2258.80 becoming $2266.00 in the
word document. I might be abel to understand simple rounding but
not this type of change. This is VERY important because the client
uses mail merge for intent to hire letters and the salaries are
going in wrong about 10% of the time.

"CyberTaz" wrote:

If I interpret your statement correctly:

"If I remove the field code for currency formatting..."

it sounds like the salary is being stored in something other
than a Currency field with currency formatting forced on it
through the field's properties. That is most likely the source
of your problem. The field Data Type should be set to Currency
with no change to the defult formatting for the Data Type.
--
HTH |:)
Bob Jones
[MVP] Office:Mac


"KYaeger" wrote in message
...
I have a word letter with merged fields from Access. One field
containing salary sometimes is not what is stored in the Access
table. e.g. employees
salary in employees table is $927.20. In the merged document it
is listed as
$929.00-even rounded it would not be that. If I remove the
field code for currency formatting it will appear as 927.2. In
most cases the merged field
is correct, but occassionally this problem will come up. I have
tried re-entering it in the table, but still wrong in the
document. We should be
able to rely on correct information as there are no
calculations, only straight merging form one table. This is
Word2003 and Access 2003. Thoughts?