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></xml><xml id=msodc><odc:OfficeDataConnection
xmlns
dc="urn:schemas-microsoft-com
ffice
dc"
xmlns="
http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=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 OLEDB
atabase Password=mydbpw;Jet
OLEDB:Engine Type=5;Jet OLEDB
atabase 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 OLEDB
on't Copy Locale on
Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>mytable</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:ConnectionString>Provider=Microsoft.Jet.OLEDB.4.0;Password="";User
ID=Admin;Data Source=C:\mydbs\mydb.mdb;Mode=Share Deny None;Jet
OLEDB
atabase Password=mydbpw;</odc:ConnectionString>
)
If you leave the Jet OLEDB
atabase 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 OLEDb
atabase 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 OLEDB
atabase 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
