Access newer Microsoft Access databases from VB6

I had to update a legacy program today that was written in VB6 and imports data from Access (jet) databases, the company that make the Access applications have upgraded to Access 2010 from the 2003 format that they were using so the import program immediately failed.

Luckily, Microsoft still support this scenario with the aid a new download http://www.microsoft.com/en-us/download/details.aspx?id=23734 that replaces the DAO library. Once installed, you can remove the reference to DAO 3.6 (or earlier) and add a reference to "Microsoft Office 12 access database engine object library". This new library appears to be backwards compatible with the old .MDB formats as well.

GNOME 3 on two monitors

I've been using the GNOME 3 desktop in Linux for a while now, since they made it available in Ubuntu 11.10 and I've now upgraded both my work and home machines to 12.04 LTS. Alas when I got to work, I plugged in my external monitor, ran the NVidia configuration and all was well until I switched to a new desktop and found the windows from the first desktop on the second monitor were still visible.

It appears the default behavior in GNOME 3 is only enable switching desktops on the primary monitor, anyway it didn't take a lot of googling especially as Greg Cordt's blog was the first result http://gregcor.com/2011/05/07/fix-dual-monitors-in-gnome-3-aka-my-workspaces-are-broken/ Thanks Greg.

Tortoise Right-Click Menu

Just a little one I found, on the right-click in Explorer you sometimes get a "SVN Get Lock" and sometimes you don't (If you don't then it's available in the Tortoise sub-menu anyway). I thought that this was a little inconsistent at first but since playing with the properties on the server I see that the menu option appears if the "svn:needs-lock" property is set for that file on the server.

BTW, setting the lock property of multiple files is rather difficult in Tortoise, well, not difficult, just laborious as it only allows you to do it one at a time. You can do it to a whole load by using a Linux machine, install SVN on it ("sudo apt-get install svn" for Ubuntu/Mint/Debian based distros) then you can use the command line:

$ svn propset svn:needs-lock 1 *.asp

The above sets it for all asp files, use a different spec for that, remember to commit your changes after though using the "svn commit" command.

Insert into ADO (something) values (gotcha)

Yep, another gotcha. This time revolving around a SQL Identity column, when inserting a row into a table, you might want to know what SQL Server has populated the identity column with, in SQL this is done by using a built in function called "scope_identity()". I use this to get around another gotcha in that using the build in global variable "@@Identity" is flakey at best as if the table has a trigger that inserts into another table with an identity column then @@Identity will contain the value fro mthe other table.

Using VB6 and ADO to insert into a table, you can open a recordset on the following SQL:

set nocount on
insert into MyTable (GroupCode, TypeCode, Description, SomeID)
values ('TBTEST', 'Blah', 'UnitTest 39408.8', Null)
set nocount off
select scope_identity()

The one row and column that is returned is the value of scope_identity(), which is the value we want.

Now opening this recordset presents problems as unless you specify a forward-only cursor type, the ADO engine will read back and forth in the recordset, not normally a problem for normal recordsets, if a little wasteful (re-reading the recordset many times when you only asked it to read it once) but on this recordset that executes stuff as well it is a real problem.

If you open up the recordset, and it re-reads the first record, the insert statement is executed again, this would result in a duplicate record being inserted into the table or an error occurring if a unique index/constraint has been violated.

The simple solution is to use a forward-only cursor when opening the recordset, e.g.

rst.Open strSQL, Me.Connection, adOpenForwardOnly, adLockReadOnly

This will ensure that the insert statement at the begining of the SQL is only excuted the once.

A couple of VB6 gotchas

If, like me, you're lucky enough to be maintaining legacy software written in ancient development platforms then you're probably used to workarounds rather than waiting for a product to be fixed.

I came across a couple of particularly scary ones in the VB6 environment concerning the loading and saving of files, I say scary because what it actually loads and saves might not be what you'd expect.

Firstly the loading, once a project is loaded, it seems to have a mindset that it is the only program that could possibly be altering any files so that when you close and reload a module for instance, it doesn't actually read the code from the disk but appears to use a cached version of the file. This is great for speed but bad news if like me, you use third party programs that could alter the source files independent of the VB6 environment.

An example would be Tortoise SVN, where if you have different developers working on a project and you're not using an SCC plug-in then getting the latest version of a source file may be futile if the VB6 environment doesn't actually read it and uses its cached version instead, then you will be working with an out of date source file that after saving, will not incorporate the latest amendments from the others in your team.

Workaround: Short of always using a SCC plug-in, which given what's in the marketplace currently, I'd rather not, it'll be a case of closing VB6 down and re-opening it.

Secondly, on saving. In just about every other language or development platform that I have developed in, when you compile the application to an executable, it automatically saves all the source files that you are working on. VB6 is different, being more of a tokeniser than a real compiler, again it's using what it has cached in RAM to convert to an executable rather than in older 3GLs, a separate compiler program would have been called to parse the source file(s) and hence the need to save them automatically when you compile.

So now I have a DLL that is compiled and managed to check in a source file that doesn't, brilliant.

Workaround: Hit the save button.

The only problem with these workarounds is that you have to remember to do them.

Single parent childless families

I was updating a DLL that takes XML from a web page and extracts the data from it to update a database. The web control had been upgraded from a simple data grid to a tree grid.

The DLL only handled data grids so I wrote a new routine to handle the whole tree grid, which would recursively process the child nodes.

In the original routine, I put a check for any tree like behavior within the XML and kick it out to the calling process with a simple message - "Tree", the calling process would see this message and call my new routine to process the XML in a different way.

Little did I realise that my check in the first routine had a slight bug in it, it kicked normal data grids back as trees as well but the whole process still worked, this is because the routine that handles trees with all the child nodes also handles straight lists as well.

A straight list is but a tree without the child nodes, so no problem then. I didn't leave it there of course, I tidied up the code and made it look like I meant to do that in the first place. :-)

A Light hearted one

I was compressing a large file in 7Zip when I decided to cancel it, it prompted me to make sure.

So, I presume that [Yes] would cancel it, [No] would not and ahem, [Cancel], what do you do? Cancel the compression or Cancel the request to cancel?