Access97 Security in Forms and Reports

One of my clients just showed me a giant loophole in their Access application, which I wrote. (blush!) When he right-clicks on a report in preview mode, he can export the report to a Word document. Why is this scary? Well, the report happens to be an invoice and in Word you can edit the invoice, something you aren't supposed to be able to do!
How do you disable the right-click facility in a report preview? It's connected to the report.ShortcutMenuBar property. Set this property to a menu macro, and it appears instead of the default one.
In forms it's even easier: set the form.ShortcutMenu property to False, and the right-click doesn't work. This is particularly important for login forms where you don't want the user to be able to change to Design View. Of course in a .mde file that doesn't work anyway, but let's rather be safe than sorry.

Mustang gets an Echo

I am trying an experiment, and I'd like your input. After all, it's a socal web (or something). I have created a web site that demonstrates how a database can be maintained on a web site. So far, very boring, unless you're a database developer.
But wait, there's more! Now I've added a comments section to the bottom of every page. You can leave comments, just like this blog, but they show up directly on the page. Please try it out and leave a comment at www.mustang.co.za. If the commenting system works well, I'll add it to some other web sites I run. The system is explained below, in case you are a webmaster somewhere. Once you sign up and pay $12 you get a simple code snippet to include on the page:
<div class="js-kit-comments" permalink=""></div><script src="http://js-kit.com/for/mustang.co.za/comments.js"></script>That's it. Let the comments begin!
Update Sunday 20 Sept: I didn't realise that the "Echo Live" service is still in a public beta, and that if I disable anonymous commenting then the wheels fall off. Apart from that it's working fine, and I'm generally quite impressed with the passion and dedication of the guys as JS-Kit..

Introducing Echo from JS-Kit on Vimeo.

Microsoft Access and the Ten-Year-Old Currency Bug

Today I wasted hours of valuable time fighting with a bug that is in Microsoft Access 97 and can also be found in Microsoft Office Access 2007. Does it really take more than 10 years to fix a bug like this, or is Microsoft just deaf? You decide.
This is how you get the bug to work:
1. Change your regional and language settings to "English (South Africa)" so that your currency fields use the "R" symbol.
2. Create a table with some fields in it. Include two currency fields, Field2 and Field3.
3. Populate the table with a few rows of data
4. Create a report, and make sure that the format of Field2 and Field3 is set to "Currency".
5. To be extra sure, include the following code in the Report Open event:
Report!Field3.format = "Currency"
6. Preview the form. Close the database.
7. Change your regional and language settings to "Zimbabwe", so your currency is now "Z$".
8. Open the database, create a form, and put the currency fields on the form.
9. Make sure both currency controls have their format set to "Currency"
10. To make extra sure, put the following code in the form open event:
form!Field3.format = "Currency"
11. Put a button on the form to preview the report.
12. Set the database to open the form on opening the database.
13. Preview the form.
14. Close the database, and change the regional and language settings to "English (Ireland)"
Theoretically, all the controls with the Currency format should show their values in Euros, the currency for Ireland. Wrong.
As you can see from the screen shots shown here, Access converts the format from "Currency" to "R #,##0.00;R-#,##0.00" for the fields that were edited when the settings were South Africa, and "Z$#,##0.00;(Z$#,##0.00)" for the Zimbabwe settings. Only if you use code to force the control to retain the "Currency" format, does the formatting work correctly. That's why Field3 displays correctly but Field2 does not.
Why does it do it? Because Access is written by programmers in Redmond, USA, who seem to think that Canada and Mexico are on other continents, and don't realise that Redmond is not the centre of the universe and that most of the planet does not format its dates as mm/dd/yyyy. But that's another bug.
Now you can change your currency settings back to what you normally use, and shake your head in disbelief. This bug works in all version of Access from 97 to 2007. It was reported in January 2004 applying to "Access 2 and above" by Allen Browne. I guess it might be fixed in a decade or so.
Update Friday 27 March: I tried doing a loop through all the report controls, but you can only do that in design view, not in print preview, so I used the Tag property and stored the names of all the Currency controls in a tag item. Then I use the OnOpen function to set the format of the explicitly listed controls. I use a similar technique for forms. This bug (or is it an undocumented feature?) has cost me 2 days worth of programming time. Thanks for nothing, Microsoft! Both of these bugs are not documented anywhere on the Microsoft site or in the Access documentation that I could find.
Update Saturday 28 March: Setting the currency fields in the table to have a format of "Currency" doesn't work either. It seems that anything done at design time is likely to fail. What's worse is that Access lies about the format setting. It will display the format property as "Currency" when it isn't stored that way. You can expose this lie only by changing to a different region, and then inspecting the format property again. By then it's too late because the app is already installed in another country on a customer's machine, and they are yelling at you on the phone. Access 2007 also has a format called "Euros" just to add to the confusion.
The code I use in the OnOpen event is basically this

'
'// Fix up CURRENCY formatting
'
Dim strField as string, strTag as string, n as long
    strTag = Nz(form.Section(acDetail).Tag, "")
    If Len(strTag) > 2 Then
        strTag = strTag & ","
        n = InStr(1, strTag, ",")
        While n > 0
            strField = Mid$(strTag, 1, n - 1)
            strTag = Mid$(strTag, n + 1)
            form(strField).Format = "Currency"
            n = InStr(1, strTag, ",")
        Wend
    End If

This code relies on the tag in the form detail having a list of fields, separated by commas, that require correct currency formatting. Similar code works in the OnOpen event of a report.
One final thought: I don't hate Microsoft, or Access. I have used Access 97 almost every day since I bought it in 1998 and installed it on Windows 98. Access is awesome, in spite of these annoyances. As a programmer I am in awe of all the things it does. Why they have to spoil it with undocumented nonsense that persists for 10 years is incomprehensible to me. I guess geeks aren't perfect either.
Update Sunday 29th March: Wayne Phillips sent me another elegant solution. I haven't tried it yet but it looks promising. I am al;so dismayed to find that the same bug exists in Excel, so I guess it's never going to be fixed, or documented.
Update Wednesday: Here is a comprehensive list of similar Access "gotchas" (i.e. Momentary Lapses of Reason on the part of Microsoft). I cannot believe the arrogance of these people. And as for the Dollars vs Lira argument, I can't believe that ANY competent programmer would have a "Currency" format that was not hard wired to $ or Lira if the possibility of confusion could arise. Microsoft themselves have now introduced a "Euros" format for precisely this problem, but have left the old "Currency" behaviour permanently broken. PEBCAK indeed: the only debate is whose chair and keyboard? I say Michael Kaplan's chair and keyboard, not mine. And when are they going to WTFM to document it correctly?

Access97 SR-2 install on Windows Vista: a workaround

Microsoft Access97 works fine on Vista, once you can get it to install. My original install CD is the SR-1 version, and I was alarmed to discover that the SR-2 patch doesn't work on Vista, because Vista's new security model prvents the patch from updating the program files. How do I install the Access 97 SR-2 patch? Fortunately there is a workaround.
I used my old testing laptop and created a WinXP install, and then installed Access97, the ODE Tools, the print relationships wizard, and the service packs for both ODE and SR-2. Then I backed up the files onto an external drive. The folder I used for Access97 was c:\Program Files\Office97 so that these files don't get mixed up with newer versions of office.
Next, I created an install script using Inno Setup, based on the log file created by the SR-2 patch program. After creating and running the setup program, my copy of Access97 reports that it is indeed version SR-2, as shown in the screen shot above.
If you want to use this patch, then you must have done the following:
Delete the HATTEN.TTF (Haettenschweiler) font, and then install Access97 SR-1. I have not tested the install with a pre-SR1 version. Run Access97 SR-1 as administrator (just right click on the shortcut and select "Run as administrator") to ensure that Access has correctly set up all its registry entries.
Only once you have completed these steps is it okay to download and run my Access97SR2Setup.exe file (25MB). Alternatively, you can download the Access97sr2.zip file which contains a copy of the Inno Setup script file. Don't be confused: you can't use this script to actually install Access97 without an original Access97 or Office97 CD, because the script only updates existing files. If you want to install the Acces97 Runtime tester, then follow the link. The runtime version only allows you to run Access programs, not edit Access databases. Also, my installer does not patch Word, Excel, etc.
Update: after installing the patch I have stopped getting a weird compile error when creating MDE files. I guess it was fixed by one of the SR-2 updates or the ODE service pack fix.

Vista Disables Help and SendKeys

I have been having fun with my new laptop and Windows Vista. Actually it doesn't suck too badly, except that the HP LaserJet 1020 support in Vista is badly broken, but I have written about it before. HP lost out on a laptop sale because of this problem, and will probably lose out on our next printer purchase as well.
Today's WOW revelation (remember "The WOW Starts Now" slogan?) is that the trusted VB "Sendkeys" function has been disabled in Vista, breaking a gazillion VB6 programs, as well as some key (ahem) parts of Miami/Mustang. Fortunately I found a library written by Karl E Peterson that required only minor tweaking to get it to work in Access 97.
In the process I discovered that the Access97 help function doesn't work in Vista until you install a help file program. That in turn required me to validate my copy of Windows again, which doesn't work properly in Firefox, so I had to go to the URL using IE7. What a mission!
The dreaded UAC (User Annoyance Control) is a little more manageable if you use the Norton UAC program from Norton Labs.
Update: Any Access97 developer who needs SendKeys and who does not have the time and/or energy to modify Karl Peterson's code, can email me for a modified copy: vmusic at spamcop dot net.

There is only one AccessOpener

I'm sure they meant well, but earlier today someone posted a copy of my Access Opener freeware program on a software catalogue site without my knowledge or permission.
It seems that he/she isn't the first offender. Also CNET's downloads.com site, ZDNet, and the TechRepublic site have done the same, again without my permission. I don't mind if people link to my page, but I can't be expected to run around the internet trying to get sites to update their servers to the latest version as well. Please stop it!

Update 5 years later: Don't Steal My Freeware! Some people just won't learn.

Taking Stock of Stocktaking

As an Access database programmer I have often had to figure out solutions to tricky problems, but keeping track of stock has never been one of them, until now.
It seems like such a trivial thing: count up all the stock items, and then track all the sales and deliveries for a given period, and then predict what should be there. Simple!? No. It turns out to be a lot more tricky than that.
Say we do the stock count on Monday. Does one include the Monday sales or not? What about deliveries? Were they received before, during or after the stocktake was done? Some companies close their doors during stocktaking. What if this isn't possible?
Then there is the question of partial quantities. If items come in packs of a dozen, what do you do about open packs? Is this regarded as part of the production stock or not? So many questions are raised but not always answered. It's not as simple as it looks, that's for sure!

SMS Free Software Updated

There's nothing like customer problems to show up missing features or weaknesses in my SMSQ software. A driver was interfering with the GSM modem on one system, so I added "Monitoring Messages" that get sent out on a predefined basis, like every hour during the day. They were so punctual that I could almost set my watch using them, and they helped isolate the problem.
Then one of my largest customers had the SIM card for their GSM modem suspended by Autopage, their cell phone company. This effectively blocked the sending of 2000 appointment reminder messages the first day, and when it was repeated the second day 992 messages were blocked before we picked it up. Needless to say Autopage is a really popular supplier with this client right now.
Catching up the backlog of messages caused me to add a "start time" and "end time" to the package, to prevent messages being sent at rude times, like the early hours of the morning. I also added the ability to read the entire message that was being sent or received, which helps to ensure that the system is working correctly. SMSQ is freeware, and uses an Access database to store the messages.

Free SMS Software Gets Better

There's nothing quite like watching a program already in use to improve it further. Three weeks ago I released the first version of my free SMSQ program, which allows a database program to send and receive SMS messages.
The program has been in constant use for over 2 weeks already, and numerous minor improvements have been made. We are now on version 1.0030, and there is a support utility called SMSsend that allows you to generate messages from a batch file or shortcut. For example, you can send the administrator an SMS whenever the server reboots, or you can use the Windows Task Scheduler to get the SMS program to run file maintenance on the weekend.
All of these features arose out of a genuine need when running the system in a live production environment. Today I installed the software on an old Windows 98 clunker, and discovered that the MDAC files were missing or out of date. I also added some other minor cosmetic changes. There's nothing like a real world problem to make software better.

AccessOpener 1.20 now supports Access 2007

Microsoft Access 2007 has been available for some time, but I only recently downloaded a trial version to experiment with. As a result I can now announce version 1.20 of AccessOpener, the utility that allows you to open an Access file with the same version of Access as it was originally created, assuming you have it installed on your PC.
The biggest problem with getting the new version to work was Access 2007's insistence that it is the only Access program on the PC, which wasn't true. I kept getting the screen shown below:
It took several attempts to eventually figure out why Access 2007 was doing this, and the new version of AccessOpener seems to work correctly with Access 2007. I have not been able to test it with Access 2003 or Access 2000, but it still works properly with Access 2002 and Access 97 on Windows XP. I suspect there will be trouble with Windows Vista, but haven't been able to confirm this yet.
I have also improved the "train" function, which should make setup a bit more simple. The installation program is also a bit more intuitive. Please report any bugs here or write to me directly.

Access Opener download page

SMS software for databases

Two of my biggest customers send SMS messages to their customers on a regular basis. These messages are generated by an Access database program. One of these is a medical application where patients are reminded of their appointment bookings. Since this is done for numerous clinics around the country, it involves hundreds of message every day.
Until now we have relied on two applications called Trebuchet and EconoSMS, neither of which are sold or supported any more. This has left my clients in a vulnerable position, since a hardware failure on either PCs could leave them high and dry. I tried experimenting with a package called "SmartCell" from Indisoft, but several attempts to contact their support line failed. I'm glad I didn't buy the product since it either isn't supported any more or the company doesn't reply to their emails. Not good.
Then I found a better package for US$124 from Logiccode Software. It's an ActiveX DLL that can communicate with a standard GSM modem, and it has enabled me to create a fully-functional Visual Basic application called SMSQ in a week, thanks to some well-written VB6 demo code, and my experience with other SMS applications.
The most difficult part was writing the database code using ADO, since I'm used to DAO syntax that works in Access97. The new application will work with Access 97, 2000, 2002 or 2003 file formats. I am currently rewriting my Miami libraries to support this application. Miami now supports field encryption, bulk emailing, bulk SMS, mousewheel support, simple barcodes and PDF printing. It also has a runtime installer package for Access 97.

Zippy gets cleverer

Zippy Backup Assist is a utility that I use regularly to save development work into zip files, where the zip file name includes the date. It also gets used to repair and compact Access databases before making the backup, to keep the zip files small and the Access files manageable. Now I have added another feature: a second parameter that is passed to the zip.bat batch file when Zippy quits. This second parameter contains another date, usually one day behind the first one, but it can be any number of days. The new version includes an Inno Setup-generated setup file, and can optionally install the PkZipC software as well. Update: I have made some more improvements to make a backup copy of any mdb file name ending with the word "data", and the "pause" time is less CPU-intensive. It also has a MSG= keyword, and the PAUSE= function works a bit better. Get version 1.0081.

AccessOpener 1.11 gets a boost

If you're an Access programmer you need this free utility. I use it all the time, and it saves me a lot of trouble. It's only when it isn't there that I miss it the most, like when my laptop had to be reinstalled, or when I'm working on a client's machine.
Recently I learnt a lot more about the Windows Prefetch system, and encountered the DiskTrix SystemBoosterXP product, which got me thinking: why not use AccessOpener to read the msaccess.exe file from the disk and place it into cache memory while the computer is booting up? Once it's there it will speed up the time it takes to load Access and open a file.
So I added a /LOAD option to work with the /SET option at startup, and it's all packaged and working, thanks to Inno Setup.

Access97 Tab Control may cause your application to crash

I love Access97. It's probably the best version of Access that Microsoft ever released, bugs and all. It works on Windows 98, NT, Windows 2000, and WinXP; and it runs fast.
One of the things I like about it is the Tabbed Control facility for forms design. I use it a lot, allowing me to put lots of fields on a form, but not having to display all of them at the same time.
There is one small hiccup: if the form gets "too complex", then woe betide anyone who tries to recompile a form that was previously compiled. Access97 just doesn't like it, and crashes.
Fortunately, there is a solution: don't compile the form twice. If you edit a form with a tabbed control, quit from Access once you're done. Then do a "decompile" with the shift key held down, and then a repair and compact for good measure.
This process is made easy with my AccessOpener utility, and seems to be a reliable means of fixing the problem. When I create an MDE version of the application, I always do a decompile, followed by a repair and compact, and then open the MDB file with the shift key held down. Then it is safe to make the MDE, knowing that it is only the MDE process that will be doing the compile.