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.
No comments:
Post a Comment