Create an Apache Sub-Domain

Not long ago, I was looking information on how to create and configure an Apache sub-domain on my test server. I found a number of websites covering the subject, but even after editing my httpd.conf file to add the domain and sub-domain entries, the sub-domain would not work!? So I did more digging and testing and what I found was that the various tutorials and explanations all omitted one crucial element to the puzzle, none of them mentioned that you had to also add a subdomain entry to Windows’ host file! I assumed that since the domain already had an entry, Apache & Windows were smart enough to know that a sub-domain would use the same host entry as the domain, I was wrong! Apparently, Apache & Windows are not that smart, actually they appear to be blatantly stupid!

So the first thing you have to do is edit the httpd.conf file to add the required virtual host entries: 1 for the domain & 1 for the sub-domain


    ServerName www.domain.com
    DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/websites/www.domain.com"


    ServerName subdomain.domain.com
    DocumentRoot "C:/Program Files/Apache Software Foundation/Apache2.2/htdocs/websites/www.domain.com/subdomainFolderName"

But even once this is done, you then have to go and make an entry for both your domain and another for your sub-domain in the Windows Host file. The values below assume a local test server

127.0.0.1    www.domain.com
127.0.0.1    subdomain.domain.com

Once you make both of these changes, restart your Apache server, and everything should work. It did for me at least!

MS Access – VBA – Capitalize the First Letter of a String

Below is an example of a function which will capitalize the first letter of a given string. The second input variable allows you to specify to return the rest of the string as is, or lowercase it.

'---------------------------------------------------------------------------------------
' Procedure : UCase1stLtr
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Capitalize the first character of a given string
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sString           String to process
' bLowerCaseTheRest Whether or not the rest of the string should be in lower case or not
'
' Usage:
' ~~~~~~
' UCase1stLtr("hjkwhjkwhkjw12218hjksdjkhNJH", False) -> Hjkwhjkwhkjw12218hjksdjkhNJH
' UCase1stLtr("hjkwhjkwhkjw12218hjksdjkhNJH", True) -> Hjkwhjkwhkjw12218hjksdjkhnjh
' UCase1stLtr("hjkwhjkwhkjw12218hjksdjkhNJH") -> Hjkwhjkwhkjw12218hjksdjkhnjh
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Mar-02                 Initial Release
'---------------------------------------------------------------------------------------
Function UCase1stLtr(sString As String, Optional bLowerCaseTheRest As Boolean = True) As String
On Error GoTo Error_Handler

    Select Case Len(sString)
        Case 0
            UCase1stLtr = vbNullString
        Case 1
            UCase1stLtr = UCase(sString)
        Case Else
            UCase1stLtr = UCase(Left(sString, 1)) & IIf(bLowerCaseTheRest, LCase(Mid(sString, 2)), Mid(sString, 2))
    End Select

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & sModName & "/UCase1stLtr", True
    Resume Error_Handler_Exit
End Function

MS Access – Button Picture – doesn’t support this format … or the file size is too big

I came across the following post and wanted to see what the issue was since I routinely use Irfanview myself (it is a great, free no strings attached piece of software).

So I contacted the creator of Irfanview directly regarding the error, and he was able to pinpoint the exact nature of the problem. As it turns out, the ico files created by Irfanview are just fine. The issue is that MS Access would seem to expect a specific type of ico file (and doesn’t tell anyone this).

Irfanview creates a 24BPP (with no alpha layer) ico file while MS Access seems (based on my testing and returned information from Irfan) to expect a 32BPP with an alpha layer ico file.

So for anyone else ever seeing this this of error. An ico is not an ico in the world of MS Access. It is picky and requires a specific type of ico file. So just be sure of the type of file your are creating is a 32BPP with an alpha layer ico and you should have no problems using them as a picture for a button. Alternately, instead of having such issues using a ico file, use BMPs instead. I personally don’t like BMPs as they aren’t used in any other programming (always jpg, gif, png, ico) so I will stay with ico so I can reuse them easily where I please. It would be nice if MS would integrate common image files into the application, but that is out of my hands (if it were up to me many issues like this would have been resolve a long time ago!).

Hopefully this will help someone out.

2008 Nissan Altima 2.5S Review

Well, it was finally time to retire my 15 year old Dodge Breeze. An exceptional car!

My wife and I after sitting in numerous cars and considering various factors, we choose to purchase a 2008 Nissan Altima 2.5S. Don’t get me wrong, when I first set out looking, I wanted to purchase a North American vehicle. We hit all the local dealers, and it was by fluke that my father found this vehicle in Hawkesbury.

Here are my thoughts on the car after a couple of months of driving.

Pros:

Comfortable
Nice, easy to read dashboard
Adjustable steering column
Huge amount of space; both in the car and in the trunk
Smooth and comfortable ride
Lots of power under the hood, if and when you need it

Cons:

General
- Trunk hinges go into the trunk space, and crush anything in their path! I am surprised that the design is so poor. My 15 year old Breeze, didn’t even present this flaw. You’d think in 15 years Nissan could have learnt a thing or two from their own design and their competitors design.
- Gas consumption – I thought purchasing a much newer car, I would see some savings on gas. I was wrong. Very disappointing to see that automotive manufacturer have sat on their butts for the past decade, or two, instead of innovating their products. Also, what they have done to give you the impression of good consumption is to give a very big gas tank, so the onboard computer tells you you have a big range, but wait until you have to fill it up. My last fill-up cost me 103$!
- Brake and Gas pedals are too close. If you are like me, a decent size man, then you will have an issue in the winter wearing snow boots and using the pedals, as your boot will touch both pedals at the same time since there isn’t enough clearance between the two.
- Maintenance perspective – I was disappointed to discover that I cannot put this car up on my ramps to do my own oil changes, etc. because the nose is low and long. So you are forced to deal with a mechanic for even the most basic oil change.
Heating/Ventilation
- Heat – we find that, in the winter, the car doesn’t heat up very quickly.
- Heat – If you set the vent to take in cold air, it gets heated. There is no way to get just outside air flow, it is always heated.
Radio
- After being used to the Dodge radio, I do not like the one in the Altima as much. The sound is fine, but I do not like having to cycle through preset A-B-C instead of being able to set multiple channels to the same button. This is a very minor issue though and a personal one at that. I should also mention the radio console is well positioned and very intuitive to use.
Horn
- The horn is very difficult to activate on the steering wheel! Ridiculously so. Basically it is like not having one. Horrible design and obviously never tested by the designers.
- Dinky horn. No truly I’d expect this on a small smart car or a clown car at the circus, but not on a nice, big car like a Nissan Altima. One doesn’t use a horn often, but when you need it, you want it to get everyone’s attention. I don’t think one does the job.
Remote Control
- ~~Remote control – battery life is very short (couple of months)~~
- Remote control – because the buttons are not recessed, gets triggered when in your pockets, just by sitting down or bending over.
- Remote control – the setup baffles me. For instance, if the car is running, but in park, you cannot use the remote to open the trunk!?

I know I have compiled quite a list of Cons, but don’t get me wrong, it is a nice car. I’m just a little surprise that certain items were never addressed in car well developed car. This is not a new model, the Altima has been around for quite some time.

One very nice thing, is the dealership! I dealt with Rendez-Vous Nissan in Hawkesbury. They where all so very nice! And they provide you with a courtesy car, free of charge, and will even come and pickup your car and leave you with a courtesy car, free of charge, when you need maintenance done! Let me tell you my experience with Chrysler was nowhere near anything like this!!! Forget a free courtesy car, heck forget a courtesy car altogether. If you wanted one, go across the street and rent one!

So the Altima may have problems (what car doesn’t), the company makes up for them. It is this attention to their clients that most distinguishes them and continues to put them ahead of the North American cars (dealerships), IMHO. Until North American automotive manufacturer put customer service/customer satisfaction at the top of their list, they will continue to see foreign automotive manufacturer a part of the market from them. My experience with Chrysler dealerships will forever make me avoid them altogether, even though the vehicle I had from them was excellent! You have to feel like more than a cash cow, and Nissan make you feel wanted, liked, and not just a wallet. The personnel were kind, offered coffee, newspaper,… Just nice people. Don’t get me wrong, they want business just like any other business, I just didn’t feel like it was being crammed down my throat as it felt like at numerous other deals we went to when searching for our car.

Mr Lube / Monsieur Lub Review

I recently was desperate for an oil change, didn't want to wait in the Canadian Tire waiting room for hours, and couldn't perform my own since my car cannot go on standard ramp?! Anyways, I decided to try a local Mr. Lube.

I rolled in without an appointment and was taken right away.
The technicians were very curtious and seemed methodical. They performed several operations that even my dealership does not normally (lubing the door for instance without even asking).
All was going exactly as advertized, until the technician checking my air filter, pulled out my air filter in a manner that it could not be reinserted (it was deformed and no way to put it back from where it came); I'm not saying that it was not due for a change. The technician told me, not that I had any choice at that point, that had to be replaced. Obviously! So he went and pull one from inventory, installed it in literally 1 minute.
And I was done.

Then I got my bill.
$46.99 for the Oil change, checkup (fuilds, battery, …)
$89.99 for the air filter!

Not knowing any better, I paid. I figured maybe it was a specialty item from the manufacturer.

As soon as I got home I decided to check up the part, here are a few links I pulled (sadly over the years many links no longer work):

~~http://www.ebay.com/itm/LUBER-FINER-CAF1848P-Cabin-Air-Filter-/260858277533 ($8.04US)~~
~~http://www.autopartlookup.com/partdescription.asp?m=LBF&p=CAF1848P~~
Nissan Altima Cabin Filter ($7.98-$15.70)
~~http://www.amazon.com/TYC-800107P-Nissan-Altima-Replacement/dp/B002E5BW74/ref=pd_sxp_grid_i_0_0 ($13.05)~~
http://www.autopartswarehouse.com/mmp/nissan~altima~acc_cabin_filter~parts.html ($8.56)
~~http://www.nextag.com/nissan-altima-cabin-air-filter/shop-html ($4.93-$22.95)~~
http://www.buy.com/prod/2007-2008-nissan-altima-cabin-air-filter/217600565.html ($17.49)

As you can see, the air filter can be purchased for anywhere between $5 and $30 (on the very high end of things), with an average price of about $12-15. A hell of a long way from the $90 charged by Mr. Lube. That makes a markup of anywhere from 200% to 1700%, on the average it would be a markup of about 650%.

All of this to say, you can be sure that Mr. Lube lost a new customer today.

You make your own judgement call on whether or not you wish to do business with Mr. Lube. For me the decision is clear!

Update 2012-Feb-29

Well, I was contacted by a representative from Mr Lubes head office in BC about my complaint. Basically, I was informed that the price also included an installation fee! Come on really! So according to Mr Lube, a 7.95$ filter costs 82$ to install. Now since the installation took no more than 5 minutes to install, that translates into a rate of 984 $/hr. I'd also ask, why aren't parts and labour separate on the invoice, like any ordinary mechanic's shop? With cheap explanations, should I say excuses, like that it quickly becomes evident the type of business Mr Lube truly is. Hey, I have nothing against making a profit, but 82$ to install an air filter is simply ripping-off your client! Nothing more, nothing less! They appear, in my experience, to be more interested in excuses, than customer satisfaction. Their intervention did nothing but to reinforce my initial impression of their business!

Update 2012-Mar-10

CBC's Marketplace had a segment on their April 6th, 2012 show regarding Mr. Lube which brought to light their dubious cabine air filter prices, etc… See:

http://www.cbc.ca/marketplace/2012/busted/statements.html
~~http://www.cbc.ca/marketplace/includes/2012/episodes/busted/08_mrlube_statement.pdf~~

You may also want to check out my review when I decided to go back again to Mr Lube to see if my first experience was atypical or not.

VBScript – Rename the Extension of all the Files in the Current Directory

Here is another odd problem I had to tackle and which VBScripting was up to the task of easily handling.

I had the need of changing the extension of all the files in a folder from *.wdseml to *.eml. If you only have 1 or two files, a manual solution is feasible, but in this instance I needed to change the extension on thousands of files, so an automated solution was needed. Below is the script that I put together for the job. It could easily be modified to prompt the user to make it more versatile…

'*******************************************************************************
'Author:	Daniel Pineault / CARDA Consultants Inc.
'Purpose:	This script will, without any prompts to the end-user, rename
'		all file extension specified to whatever new file extension is 
'		specified of all the files meeting the criteria in the same
'		folder as the vbscript
'Revision:	2012-01-31   Initial Release
'*******************************************************************************

sVBSPath = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) _
		     - (Len(WScript.ScriptName) + 1)))
with createobject("wscript.shell")
   .currentdirectory = sVBSPath
   .run "%comspec% /c ren *.wdseml *.eml", 0, true
end with

VBScript – Determine Current Directory Path

Have you ever needed to merely determine the full path of the location where a vbscript is located? I t really isn’t that hard, once you know how to do it! Below is one method to determine directory path of the executing vbscript.

sVBSPath = Left(WScript.ScriptFullName,(Len(WScript.ScriptFullName) _
       - (Len(WScript.ScriptName) + 1)))
msgbox sVBSPath

VBA – Directory/Folder Name Validation

I had a need to create folders from Access, but needed a means to first validate that the folders names were acceptable as Windows does not allow certain characters and has certain basic rules (refer to the 2 links commented out in the function below for all the details). As such, I created the following simple function which I supply the folder name to and it returns True/False whether the string is acceptable or not. It really wasn’t very difficult and this is the perfect situation in which to utilize the power of regular expression to validate the folder name with!

'---------------------------------------------------------------------------------------
' Procedure : IsInvalidFolderName
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Validates whether the string passed is an acceptable folder name
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFolderName name of the folder you're wanting to create
'
' Usage:
' ~~~~~~
' IsValidFolderName("MsAccess Databases") will return True
' IsValidFolderName("MsAccess :: Databases") will return False
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2012-Feb-01                 Initial Release
'---------------------------------------------------------------------------------------
Function IsValidFolderName(ByVal sFolderName As String) As Boolean
'http://msdn.microsoft.com/en-us/library/windows/desktop/aa365247(v=vs.85).aspx#file_and_directory_names
'http://msdn.microsoft.com/en-us/library/ie/ms974570.aspx
    On Error GoTo Error_Handler
    Dim oRegEx          As Object

    'Check to see if any illegal characters have been used
    Set oRegEx = CreateObject("vbscript.regexp")
    oRegEx.Pattern = "[<>:""/\\\|\?\*]"
    IsValidFolderName = Not oRegEx.test(sFolderName)
    'Ensure the folder name does end with a . or a blank space
    If Right(sFolderName, 1) = "." Then IsValidFolderName = False
    If Right(sFolderName, 1) = " " Then IsValidFolderName = False

Error_Handler_Exit:
    On Error Resume Next
    Set oRegEx = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & vbCrLf & _
           "Error Source: IsInvalidFolderName" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Enjoy!

MS Access 2007-2010 Ribbon Development

A while back, ok several years ago, I took a look at the new (at the time it was new) ribbon. I was interested in porting my Custom Toolbars into the new and improved format. At the time I spent hours trying to digg up information on the subject.

To put things bluntly, MS messed up good on the implementation!

This is not to say that the ribbon is not a powerful tool and all the rest of that.

That said, it has been implemented in what I consider an incomplete form. MS provides no tool, to develop custom ribbons, no built-in callback routines…, no access to the ribbon built-in images, and so many more flaws I don’t even know where to start! It is a mess.

Then they still support old custom toolbars, but you have no way to edit, delete or work with them directly. You have to revert back to using 2003 or prior, or develop your VBA own code to interact with them.

The whole situation smells bad.

So what is one to do. First off, you have to realize, that the entire Office suite Ribbon is fully customizable and to work with it you can use a Custom UI Editor, EXCEPT for MS Access. That’s right, as usual, MS Access is in a category of its’ own. What a surprise!

Firstly, get ready to do a lot of reading, before you are even ready to tackle creating a ribbon. Here are a few places to start:

www.accessribbon.de
http://www.andypope.info/vba/ribboneditor.htm
http://msdn.microsoft.com/en-us/library/aa338202(v=office.12).aspx
http://msdn.microsoft.com/en-us/library/bb187398.aspx
http://www.rondebruin.nl/ribbon.htm

For MS Access, try http://www.ribboncreator.de/en/ The ribbon creator is a graphical interface to create the required XML and VBA code. Basically, what MicroSoft forgot to include in its’ own software!!! At the very least it will speed up development and you can tweak what it generates.

All in all, the ribbon, because of the way MS has chosen to implement a half baked technology, is a miserable failure (from the developer’s perspective)! Actually, even from the end-user’s perspective also. Where I used to be able to load 6 toolbars containing 50 icons (or more) where I had all the commands I needed at my finger tips, I now have 1 ribbon containing 10-20 commands. You end up spending your time flipping back and forth, all day, between tabs! It is never ending and tedious after a while. This is MS’ idea of empowering the end-user? Come on! But hey, it looks cool dude! This is the best that the best minds at MS could come up with; seriously!!!

That said, I believe MS has other plans for the ribbon, probably a way to introduce the end-user to the new layout, flow to be expected in future releases of their OS or other programs. Let use not forget the fact that MS is playing a major game of catchup with MAC.

MS Access – Splitting and Deploying Your Database – Part 2

In my previous post entitled Splitting and Deploying Your Database, I elaborated on the critical importance of splitting your database before deploying it to your end-users. That said, many people post questions regarding securing their applications from their end-users to minimize their ability to mess around with the database, mainly fool around directly with the tables. In this post I will elaborate on a few possible step you can take as a developer to best secure your database from what I qualify of ‘dangerous’ users.

There are a few things that you can do to try and harden your database against your users. In 2 instances, you have the ability to take some steps using programming to secure your app. In the 3rd, you can deploy your application in a secured method. Let’s examine each.

Hide Object Pane & Disable SHIFT Bypass

As a developer, you can do your best to make it as difficult as possible for any user to gain access to any of your database objects (tables, queries, forms, reports, …) to pose a threat in the first place. To do this you need to:

Create an autoexec macro the uses RunCode to execute a VBA procedure at startup
In your startup procedure add in the following code

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End If

This code will hide the Access object browser (the pane that lists all the database objects: tables, queries, forms, reports, …). If they can’t see them, then they can’t mess around with them!

Since any knowledgeable user knows that they can bypass any autoexec macros by holding the SHIFT key at startup, we need to disable the shift bypass capacity. Now there is no point in rehashing this subject, so please refer to: http://access.mvps.org/access/general/gen0040.htm

Convert to MDE/ACCDE

One more step you can take is to convert your database (mdb/accdb) to an (mde/accde) format. By doing so you lockout the user’s access to the all the VBA code. Furthermore, this creates a compiled version of the database which mean it should be optimized for use. Win, win. Just remember you cannot do development on an (mde/accde), so keep your original file (prior to conversion) for further development.

Deploy Your Database Using Runtime

The last thing you can do to restrict your users and limit their ability to run amuck in your database is to deploy your application using MS Access’s runtime version. Instead of giving your user the full blown MS Access application, only install the free runtime version. Unless, your user needs to do development in the database, there is no need for them to have the full version of MS Access. By doing this, your user will not be able to edit any of the database objects! They will only have access to whatever you have developed and given them access to. Also, note that when deploying using the runtime version ensure you put in place error handling throughout all of your VBA procedures, otherwise when an error is raised, the application will blatantly crash. You can freely download and install the runtime version of MS Access directly from Microsoft’s website at:

MS Access Runtime 2007 -> http://www.microsoft.com/download/en/details.aspx?id=4438
MS Access Runtime 2010 -> http://www.microsoft.com/download/en/details.aspx?id=10910