Saturday, March 27, 2010

32bit or 64bit OS?

Ah, for most people, the answer would be a something like "Er, What?". Clearly it shouldn't matter, and I think that is important to keep in mind. Because I think it's getting to the point where this is probably the case.

In the Windows world, we more or less moved over to 32 bit with Windows 95. What is 32 bit anyway? Well, imagine 32 1's or 0's in a row. That's a representation of a number. It's 2 to the power of 32, or 2x2x2x2...x2 (32 times), which = 4 billion or so. Now, there are 8 bits in a byte, and, well, I'll let wiki explain the rest if you are interested. The practical implication of this is that you are limited to 4GB of memory. Which is a lot, right?

Well, no. Not really any more. And certainly not if you want to do anything serious, like a database or virtualization. Even consumer PC's these days, since the hungry-hungry-hippo called Vista was let loose, have come with 2GB ram as a reasonable medium standard. If you have even remotely paid any attention to spec changes over the years you will know that they double every now and then. So we are only one double away from hitting that 4GB limit.

There was a 64 bit version of windows XP. It was very much the bleeding edge. Almost nothing worked on it, and peripherals in particular were the pain point. Getting that printer or scanner, video card etc to work was a real gamble, and most times you got snake-eyes. Vista was actually better, as the 64-bit version was released at launch date, and flagged that hardware vendors had to start producing 64-bit drivers from now on or else. And they have. Of course, the 32/64 bit question was somewhat hidden by the overall horribleness that was Vista. Windows 7 is really just Vista done right, and to be honest, the latest patched version of Vista is actually not too bad. However, it's reputation is permanently tarnished and much like Windows ME will spend it's eternity in History as a dud, a lemon and a turkey.

Well, most sane people, and those cautious in businesses around the world, have largely been hanging on the XP platform waiting to jump over Vista. It has helped that since the Core2 the CPU's have more or less also been "good enough" to keep going for a few years. The GFC has not helped IT budgets much either, so this has also influenced things.

Anywhoo, at my place of work we recently replaced a bunch of XP machines. What did we get? Well, some intel i7 based Dell boxes, with Window 7 and 6GB of RAM. Yours with a 23" LCD and Office for around $1700 Australian - which is a pretty good deal in the current market. Yes, I know Office 2010 is just around the corner. Sometimes these buying opportunity windows open up just briefly and you go for it when you can, even if it's not 100% ideal. I also think of the permanently revolving doors you see at some hotels -- if you wait for it to stop you are waiting forever.

So, perhaps you are wondering, which did we get - the 32 or 64 bit version of Windows 7? Well, the clue should be the 6GB RAM. Are you paying attention? Stay with me here!

So, how have i found the 64 bit experience? On the whole, like the rest of Win7, it's almost flawless. Really, the water is fine, jump right on in. With new hardware, you know the PC itself is going to work. The trick then is any legacy software and hardware. In my environment the biggest bugbear was Access 95 databases we were still using. This was the final push for me to upgrade them all to 2000 format, and I'm glad I did. My previous post goes over this in tragic detail if you are interested in that saga.

The other problems I had were --
  1. Installing SQL 2000 client tools. The installer recognised the 64 bit environment and didn't like the idea of putting the 32 bit version on the CD there. Fortunately, I found that you can still install it by running an installer buried a bit on the CD. A google search turned up this solution in under 5 minutes. Yes, and caused by trying to continue to use old software note.
  2. Printing to our copier at work required downloading a new driver. Printing to an older, less popular and consumer grade printer also provided interesting problems, but I was even able to do this using the Vista driver.

Overall, I have (so far) not had a problem I have not been able to solve. This is as much a testament to Win7 as the 32/64 issue, but I think there has been significant evolution of the software over the years to prepare for this moment.

You may be wondering - will we have to move to a 128bit OS in a few years time? Well, there is that whole "never say never" thing (which is clearly self-contradicting, but that's not what I mean). Remember that each extra bit doubles the number it represents. So a 64 bit number isn't double a 32 bit number, it's 4 billion or so larger. That's a LOT of memory (16 billion GB). Like, probably as much as has been manufactured in the history of mankind, or not even. In one PC. Doesn't seem all that likely, does it? So, I think this is probably safe to say the this is the last of these kinds of changes we will have to make in our lifetimes. I've lived through 8, 16, 32 and now 64 bit OS's. Change is pain, but to be honest, this is the least painful of these transitions I have experienced.

Sunday, March 14, 2010

Upgrading from Access 95 to Access 2000 (or greater)

This blog entry will only interest a relatively few people on the planet. If you don't use Microsoft Access 95, then I suggest you stop now. This will not make much sense nor be particularly riveting.

I have used Access 95 at work for about the last decade or so. The main piece of software the company uses was written in it, before I started working there and I have carried on from there. On the whole it is a good piece of software, and I think it speaks well that it can be used for so long. It even has some advantages over more recent versions, primarily the ability to have a single server copy of the front end that everyone uses which can be edited in real-time. It might seem like an afterthought, but it has excellent help that is actually helpful. Contrast this to .NET or Excel help which is generally useless. I think perhaps Access 97 was better than 95, and it shared many of the positives and had some features I like such as intellisense (where it completes the word in code, and also gives you parameters for functions etc). Anyway, it's a moot point as we were stuck with 95.

However, it is also not without it's flaws. The most notable being the limit to a machine with 1GB RAM (usable, which is a point worth noting). It also is quite slow sometimes and has some interesting compatibility issues with SQL server data types and locks. For example, it shows boolean values as 0 or -1, but does not actually work in a query if you compare it to -1, but does if you compare to <>0. It fails to delete data in linked ODBC tables reasonably often, I'm not sure why, but at least it let's you know it didn't work. Mind you, we are using the now-old SQL 2000, so that might have something to do with it.

There are some patches that are mandatory for any Access 95 install, by the way, including a patch to the Visual Basic for Applications (VBA) VBA232.dll. The right version is just over 1MB, the wrong one is just under.

Anyway, the deal breaker for us is the RAM issue. 1GB seemed like a massive amount of memory back in good old 1995 when this software was "new", and I believe it still is a lot, but todays machines have and need this and more. Vista was a massive memory hog, which is why at work we stuck with XP, but Windows 7 is a lot more reasonable. Recently we have upgraded a number of desktops at work and they have come with 6GB of RAM and Win 7 Pro 64 bit. This blows poor old Access 95's mind, and it perversely gives the error that that it is out of memory when you try and launch it.

Now, recently I discovered it IS possible to run Access 95 on a machine with over 1GB of RAM. Well, sort of. What you do is limit the machine to only use 1GB (actually, just under), no matter how much RAM is installed. It's certainly easier than physically removing the SIM cards, and it means that you don't have to have the exact SIM configurations installed. To do this you use (in Run type) MSCONFIG and then go to the BOOT.INI, and look at the Advanced Options. In there you can set the max RAM to 1023 MB. This is one MB under 1GB, which as we all know is 1024 and not the dubious marketing 1000 MB. A reboot later and Access 95 will indeed work. The downside of this technique is that the rest of your OS will probably suck badly with this limit. There is a possible relief valve you can use to eek out just a little more performance, if you are determined to go this way, and that is to then increase your virtual memory. This is done in the My Computer (right click), Properties, Advanced, Performance, Virtual Memory. If you set the minimum to 2048, max to 4096 (ie 2-4GB of VM) then things will go.. ok.. probably. When I tried the above to the new 6GB machines, they did indeed work ok. Not as quickly as with the full 6GB, but not too badly. I tried multitasking with 5 or 6 applications (eg Outlook, Word, Excel, Paint, IE, etc) and it all ran. But despite getting this working (and it was somewhat of a revelation to me that this was even possible, I was resigned to the fact that it wasn't), I felt this was a "second best" solution. Really, the time has come to move on from Access 95.

Over the years I have tried to "bulk" upgrade from Access 95. That is, to create a blank database and import the objects from the Access 95 database. I have tried it to 97 and 2000 without success. I can't recall now what exactly went wrong, but after importing a number of objects the whole thing just died - mostly with errors about the code being corrupt or something.

However, on Friday I was able, for the first time, to do this. The machine that did this miracle was my recently rebuilt (due to a hard drive failure) XP box, which has just under 1GB of RAM, and Office 2003 installed, patched to the latest of course. The database being imported into was actually in the older Access 2000 format. The trick was to make sure that the Access 95 database I was importing from could compile first (don't even think about importing it if it doesn't). I imported objects also in a fairly specific order, which may or may not matter. First make sure all the linked tables are there, and any references (I have Excel reference because there is some code that manipulates spreadsheets). I imported the code modules and macros first. Then the queries. Then the forms/reports. Why in this order? Well, these things can rely on each other - and it works best if the thing that is being relied upon is already present. It also allows you to try compiling the database between each step to make sure it all still works.

In our case I need to import three databases into one existing one. The issue of good naming conventions of objects becomes apparent. I have a policy of naming forms, for example, with the prefix "Form A01 - ", showing that it is a form in the database "A", and its number is 01. After the dash is some description of the form. If there is a query used in that form, then the query is called "Form A01 -" too. Reports are "Report A01 - ". Where possible queries are saved and named, so recordsources of forms and objects (eg lists and combo boxes) get saved as named queries. It makes for a lot of queries, but it means they are all visible outside of the form editing. Anyway, one advantage of this system is that when 3 databases are combined the objects are all uniquely named. If they are not, the first duplicate gets a "1" added to the end of it's name, or "2" or "3", depending on how many duplicates there are already. For this reason alone it is also a good convention never to END a named object with a number. The duplicates may or may not be the same of course (think : code fork), which will need to be checked, and if there are hundreds of these it may become an insurmountable problem. Either that or work our where each object is used and rename it, which is a big job again. This needs to be sorted out before the first object is even created, so it's either a problem or it isn't at this stage of the game.

So, was the process perfect? Well, no, not quite. I have only tested certain functions, and most things seem to be working correctly, but there are some oddities. For example, some forms have up to 6 copies of the "file" menu, which is definitely abnormal. These are custom menus, and usually there is just one. I'm not sure what that means at this stage.

[Edit - I worked out what this was caused by. The forms with multiple menus all had subforms, and these subforms had menus set. In access 95, only the parents menus were used but in later version all forms menus were drawn. The fix was therefore simple - remove the menus on the subforms. They were not supposed to be there anyway, it was just from sloppy form copying.]

I seem to recall that there might be issues with the DAO versus ADO view of life - that is, how recordsets are handled in code. I need to check into that pronto as a lot of the important code actually uses this. A re-ordering of references might be all I need to ensure it does what I want, and certainly it has compiled the code, so that's encouraging.

What more is there to say?

Oh, I know. I have had to do some slightly tricky things to emulate the development I had with Access 95. I'm used to editing the server copy and everyone uses that. The problem is that Access 2000 and onward seems to delight in corrupting any multi-user front end. You have to really have a local copy for each user. So how to do this? The solution I came up with is to have a local copy of the front end. When it launches it checks a version number in a table versus a version number which is a constant on the main menu form. If these are different it warns the user that there is an upgrade available. They then press an upgrade button on the form which launches a batch file the quits the app. The batch file pauses for a second (waits for the quit) then copies the server version to the local directory, and then re-launches the database. It all works quite well, apart from the issue that I have different versions of Excel installed around the place (2000, 2003, 2007) and this breaks the references. This is something that needs to be fixed each time - the first time it tried to run code it fails, and drops into debug. Stop the code and deselect the missing reference, and select the right one. Compile and off you go. I have thought of perhaps having three different versions of the master, one for each version of excel, but I can't be assed doing this as I don't have enough users to justify the hassle. If I had hundreds of users I would. Mind you, if I had hundreds of users I don't think I'd be using Access. We do also have a web-based front end, but it's oh-so-much-quicker to develop stuff in Access.

I will be slightly sad to see Access 95 go. It's been a solid workhorse. From 2000 format, I suspect it will be relatively easy to go to 2003 or 2007, but these things have a way of making a liar out of me, so take that with a grain of salt. I'll post here if/when we cross that bridge.