Access 2007 Debugging Disabled?

So I was trying to debug a rather convoluted report-generating VBA module in Access 2007 (code which I did not write, but which I have inherited for pro-bono support). But no matter what I tried, Access would not stop for debugging breakpoints.

I did my normal Google searches to see if this was an issue, and I was finding nada on the subject, until I came across this lovely little post.

Turns out there’s a little setting in the Current Database options screen called “Use Special Access Keys.” The little help icon seems to indicate that this setting controls whether or not the special key combinations to bring up the database window, etc., will be enabled (you might disable this normally for end-users using an Access app, for instance). But what it fails to mention is that this setting also controls whether or not the debugger is active!

Check the box, close and re-open the database, and voila, breakpoints. Now I have to go back and remove all those message boxes that I was using when I couldn’t get debugging to work…

Little Bit of DOS Magic

Ok, so not really DOS, but rather the Windows XP interactive command console, whatever they’re calling it these days.  In any case, it comes in handy to perform tasks that a graphical interface just can’t handle efficiently.

I have a bunch of files categorized into multiple directories, like this:

2000 Aerials
— NJ
—- Burlington County
—— X00 to X03
—— X04 to X06
—- Camden County
—— etc.

I’d like to flatten the contents of all of the various subdirectories into one directory.  One ring to rule them all, one ring to find them, one ring to bring them all, and in the darkness bind them.

The for command is how to do it:

for /R . %f in (*.*) do copy /Y “%f” h:destination

The /R switch tells for to run this command (the for command) in each directory under the listed director, which in this case is . (the current directory).  In each of the directories, for builds a list of all files matching *.*, and runs the copy command (substituting the %f with the filename).  Thus, wherever the for command recursively finds a file (and at whatever depth), that file is copied directly to the destination.

Access 2003 combo box displaying blank entries!

A client’s database suddenly was showing blank entries in dropdown selection boxes (combo boxes), on forms, reports, and even the datasheet.  The data was there, and the combo boxes actually have items and attached values, just no display.  After futzing for a while and googling away, finally discovered that this is a “known issue” with Microsoft Office 2003 Service Pack 3.  One more reason to be shy about service packs, especially the kind that automatically update.  Surprise!

The solution was to remove the “Format” property for the fields in the combo box.  An alterative, from Microsoft, is to use the SQL query for the combo box to append a blank string to the string value (thus ignoring the Access field Format property).

KB945280 — Combo box controls and list box controls display no value or incorrect values in Access 2003 after you install Office 2003 Service Pack 3

Microsoft also issued a hot-fix for Access issues post SP3, which I haven’t tried yet, but it’s supposed to fix this problem.

KB945674 — Description of the Access 2003 post-Service Pack 3 hotfix package: December 18, 2007

C’est la vie.

Force Firefox 3 to Refresh a Dynamic Page

Firefox has for a long time kept form variables in memory when the user clicked reload, or navigated away and then clicked the back button.  Filling hidden form fields with old data from the cache is a really bad idea if you ask me, and I needed to force a refresh.

Usually, I’d use the meta tags to tell the browser not to cache the page, but Firefox 3 seems to ignore them entirely, and will continue to cache and load from the cache.  Wonderful.

Solution for now is to set the following HTTP headers (seen here in ColdFusion code):

<cfheader name="Cache-Control" 
    value="max-age=0, no-cache, no-store, must-revalidate">
<cfheader name="Pragma" 
<cfheader name="Expires" 
    value="Wed, 11 Jan 1984 05:00:00 GMT">

NB: The order of the headers seems to be important!  You can find a good discussion and test results on MozillaZine’s forums.

Enable Remote Desktop Remotely

This one is well discussed across the web, but I can never find what I’m looking for when I need it.  So this one is for my reference, and if it helps anybody else — all the better.

Enable remote desktop in the remote registry.

  1. Run Regedit
  2. Select File | Connect Network Registry
  3. Enter the name of the remote computer and select Check Names
  4. Go to hklmsystemcurrentcontrolsetcontrolterminal serverFdenyTSConnection
  5. Change the FdenyTSConnection to 0

Steps above found at tech-recipes.
There is also a utility at IntelliAdmin that will do these steps for you.

Make a hole in the firewall on the remote machine.

  1. Get PSTools from Microsoft. We need the psexec utility.
  2. At a command prompt, run the following: psexec remotecomputer cmd

    netsh firewall set portopening protocol=TCP port=3389 name=TS mode=ENABLE profile=DOMAIN

Summary information from Misha Shneerson.
Another option (disabling the firewall entirely) at TechRepublic.

Converting UTM to Latitude and Longitude in ColdFusion (CFC)

In the National Register location data, everything is plotted with UTM coordinates (zone, easting, and northing).  I want to use this location information in work I’m doing on PAB, which at the moment requires me to convert everything to latitude and longitude.

I could do this in ArcMap, and reproject the whole dataset.  But I want to write an automated import script for NR data, so I had to find code I could use in ColdFusion separate from traditional GIS software.

Found paydirt in Chuck Taylor’s GIS toolbox.  He has a javascript version of the conversion routines, so all I needed to do was migrate that to a ColdFusion component (CFC).  Having freely received, I’ll freely give — here’s the CFC for converting UTM to latitude and longitude.  No restrictions attached.

Image Tile Caching

Update (Oct 2, 2009): I’ve written a new post with links to newer versions of this script. Make sure you use the code there, not here.

For the map mosaic viewer I’m working on, the images are generated by ER Mapper’s Image Web Server from the source JPEG 2000 and ECW files. It works great, but can be a little slow, as it has to load very large images (equivalent of 100GB uncompressed) and pull tiles out. Because the viewer always uses the same tiles at each zoom level (defined by a lat/lon bounding box), it made sense to cache the images.

A real simple PHP file seemed to do the trick:

Continue reading

Mosaics of Old Maps

One of the really cool projects that I get to work on is building indexes and access systems for the various maps that become part of the Greater Philadelphia GeoHistory Network. (In case you don’t want to read the rest, just check out the beginnings of something I’m working on. View the map, then click “LUM 1942.”)

Many of the maps that were scanned so far are from old atlases, ranging from 1858 through the early 1900s. Then there are two sets of land use survey maps that were produced by the Works Progress Administration in 1942 and 1962. (Wikipedia says that the WPA was shut down by Congress in 1943, so I’m not sure where the 1962 set came from, but that’s what the title sheet says…)

Anyway, I’ve been having fun with the 1942 set of the Land Use Maps. The maps are arranged in a grid of 100 plates that covers Philadelphia, and are pretty straightforward to navigate. But in this modern day and age where Google offers a seamless coverage of the whole earth, who wants to flip through individual plates?

Enter the mosaic.

Continue reading