bobpeers -> blog

If you are using the ADO object to connect to a database within an HTA in a networked environment then you will get a rather annoying message box warning about ActiveXObjects.

To prevent this in all my HTAs I start the script off with the following statements (this is in Javascript).

var objShell = new ActiveXObject("WScript.Shell")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\Zones\\1\\1406", 0, "REG_DWORD")
objShell.RegWrite("HKCU\\Software\\Microsoft\\Windows\\CurrentVersion\\Internet Settings\\ZoneMap\\Domains\\myServer\\file", 2, "REG_DWORD")
objShell=null

The effect of the first RegWrite is to allow ‘Access data sources across domains’ and then the second write adds my network location (myServer) to the trusted sites.

These can be manually changed from within Internet Explorer as they are the zones settings in Internet Options.

Setting these keys requires no admin rights but they may well get overwritten by Group Policy so it’s best just to include them at the start of the script.

Note that the first time the application is run you will still get the warning as it will have read the old values, but by the next run they will be set.

I recently developed an HTA Application written in Javascript that used ADO to retrieve and update data in an MS Access database.

One of the calls tried to open a stored query but failed with the error ‘Unkown function’. It turns out that the query contained the Nz VBA function to convert null values into some other, zero in my case.

This works fine when run from within MS Access but when called externally would fail due to the JET 4.0 database engines sandbox mode.

I could have changed the registry key to allow the function but that would only work on my computer so as a fix I replaced all the references to Nz with IIf statements, not an elegant solution but it works none the less.

After all the testing with creating bootable USB drives and trying many different distributions I guess the inevitable had to happen. At some point in my partitioning/formatting of the USB drive I’ve typed

/sbin/mkfs.vfat -F 32 /dev/sda1

instead of

/sbin/mkfs.vfat -F 32 /dev/sdb1

Only a small error, replacing sdb1 with sda1, but the result is that I reformatted my main Windows XP Professional NTFS partition to FAT32 and in the process lost Windows. I’m not bothered about data loss as I keep anything important in my Linux partition that’s also backed up to a NAS device. What I’m really bothered about is the prospect of reinstalling Windows. In my experience it goes something like this:

1. Boot using my Acer install disk (pre SP1 from 2003).
2. Wait 30 minutes for install to complete.
3. Reboot, listen to annoyingly loud music while setting my computer preferences for the first time.
4. Reboot again.
5. Spend 2 hours (minimum) customizing and uninstalling the crap put on my computer.
6. Go to Windows update and start the painful process of getting a pre SP1 XP Professional computer to SP2. This usually requires 50 to 100 updates along with maybe 3 reboots on the way. Allow at least 3 hours for this with a fast internet connection.

If all goes well the whole process will take an entire evening. Not forgetting that the fresh Windows install will overwrite my mbr so making my dual boot laptop into a single boot Windows only machine requiring me to boot into a live Linux distribution and then installing GRUB on the mbr again. Phew!

Compared to this exercise in time wasting, installing any new Linux distribution these days seems like a walk in the park. I’ll just have to be extra careful in future when formatting drives.

I was writing some VBA code in MS Access today and discovered some strange behaviour when inserting values into a table field when the data type of the field is set to byte (Byte variables are stored as single, unsigned, 8-bit (1-byte) numbers ranging in value from 0-255).

To recreate the problem simply create a new table with one field of type byte, this can be done by running the following SQL statement:

create table test (id byte)

Next run the following code:

insert into test values (1000)

If you look at the value actually inserted into the table it shows the value 232. There is no warning of invalid data type or out of range warnings, it just goes ahead and inserts the result of the VBA Mod function on 1000 mod 256. The Mod function is defined as ‘Divides two numbers and returns only the remainder’. If you try it with the value 513 it will insert the value 1.

It can be of no coincidence that a VBA byte can have a maximum value of 255. The most worrying thing here is that it converts my integer to a byte result and inserts the value without any warning. If you were trapping errors at this point to trying to catch someone inserting an integer or long value into the field then you’ll never know an error has occurred as no error is thrown.

Even more strangely in VBA code if you open a recordset on the table and add an integer using AddNew method then an error IS thrown, but if you execute the equivalent SQL statement using a command object NO error is thrown. The AddNew behaviour is shown below.

Dim rs As New ADODB.recordset
Dim cn As CurrentProject.Connection
With rs
	.Open "select id from test", cn, adOpenForwardOnly
	.AddNew
	.Fields("id")=1000	'throws an error
	.Update
	.Close
End With

The next code using the command object does not throw an error.

Dim cmd As New ADODB.Command
Dim cn As CurrentProject.Connection
With cmd
	.ActiveConnection = cn
	.CommandText="insert into test values (1000)"
	.Execute      'no error
End With

It seems to me that running an SQL statement from either a query window or through the ADODB command object uses some different method whereas the AddNew method bypasses this thereby raising the correct error.

Of course the lesson here is to check your data before trying to insert it into the database rather than catching errors caused by invalid data.

After my recent problems with networking in Vista I’ve come across even more serious issues.

Last weekend we visited my girlfriends brothers who bought a new computer last year but have never been able to get it connected to the internet. It’s common in many flats in Denmark that the building has it’s own internet connection that is then shared amongst the flats; basically creating a LAN in the building. It’s fast, cheap and if it fails then it’s not your problem to fix.

I was trying to get a connection (they have Vista Home Premium) but all it kept saying was that the LAN connection could not be identified. I tried every possible fix but nothing would get me a valid IP address. Her brother told me that they had been warned that the buildings LAN didn’t work with Vista and they should get a router to connect through so they had bough one just in case.

Sure enough as soon as I connected the router everything worked without a hitch, they router was assigned an IP address and the router gave out an IP address to the computer.

The sheer fact they they were forced to spend money on a router that should be totally unnecessary amazes me. It basically means that everyone in their building buying a new computer will have to also buy a router, unbelievable!

This exercise in futility also made me think more about carrying around a bootable USB with some Linux distribution installed, or maybe a Linux Live CD to be used for testing and diagnostic tasks. One of the problems I had was that I couldn’t easily tell if the problem lay with Vista or maybe there was no internet connection at all. If I could have booted into Linux I could have easily diagnosed the problem and been sure it was with Vista from the start.

Type these words into Google and you quickly see there’s plenty of problems with networking and Vista. My own particular problem drives me crazy as it seems there’s no fix apart from manually resetting the network adapter every time it drops the connection.

It doesn’t happen straight away, but after some time (often after the computer has been woken from sleep) the network icon says I only have ‘Local connection’, not local and internet. Even this isn’t strictly true since I cannot connect to my router, I actually have no connection at all.

If I right click the icon and select ‘Diagnose and repair’ to open the fancy ‘Vista Network Diagnostics And Troubleshooting tool’ it suggests this.

After resetting the network adapter everything works fine again every time. This sort of bug is very irritating, even more so for my girlfriend who is not so technical ;-).

I can hardly believe Vista shipped with so many network issues that still haven’t been resolved. Hopefully the soon to be available Service Pack 1 will fix this but I’m not holding my breath.

It seems that the well known system requirements for Vista have caused some confusion, even amongst IT professionals.

The other day at work I was sending a couple of 2-3 year old HP desktop PCs in to IT support to have Windows XP Professional installed along with Office 2003. These are not cutting edge machines but they have 3GHz Pentium 4′s and 512MB of RAM, more than enough for a business PC running office applications.

Their response was that they didn’t think they would run Windows XP without 1GB of RAM. My 4 year old laptop with a 1.3GHz processor and 512MB of RAM runs XP and office just fine and the last time I checked the requirements for Windows XP it said 128MB of RAM was required.

I know that new computers all have at least 1GB of RAM but the idea that you need this to do anything is just not true (at least if not using Vista). It seems that people have heard so much about how you need X MB of RAM or a dual core processor that the average processing power of a PC is far greater than most people require.

I often see adverts from high street computer stores describing quite high-spec machines as ‘good for basic text editing, email and browsing the internet’ when that’s the sort of thing that can be done on a mobile phone these days.

It’s one of the (many) reasons I use Linux, it does all I require and much more without making me feel like I need to upgrade my laptop every 6 months.

I recently bought a new computer for my girlfriend, it’s a HP dc5750 and has Vista Business installed and, strangely I think, also has the 60 day trial for Office 2007. Why they insist on installing this trail version on a business machine is quite beyond me as surely most businesses will already have a license to Office 2003, for example, and just use that.

So I uninstalled the trial version and installed Office 2003. Unfortunately this leaves Business Contact Manager (BCM) in place with plugins for Outlook, Excel etc. so each time I started any Office 2003 application I would get a dialog informing me that this version was not compatible with BCM.

After a short search I found the fix without needing to uninstall BCM.

Open the registry editor (click start and type regedit).

Navigate to the following keys where HKLM stands for HKEY_LOCAL_MACHINE:

HKLM\SOFTWARE\Microsoft\Office\Outlook\Addins\Microsoft. BusinessSolutions.eCRM.OutlookAddIn.Connect3

Change the DWORD LoadBehavior from 3 to 1

For other Office applications goto:

HKLM\SOFTWARE\Microsoft\Office\***\Addins\BcmHistoryAddIn.BcmConnect

Once again change the LoadBehavior from 3 to 1 where *** is the application name.