Why You Should Join AA (Access Anonymous)
I'm an Access hater too - so the idea of giving tips on it galls me just a bit. Recently, however, I had the opportunity to help a fellow with his Access driven Coldfusion site. This poor soul had moved his Coldfusion site from experthost.com to godaddy.com to save a few bucks. Saving money on hosting is almost never worth it in my opinion - but I won't go there for this post. After much heartache he got the Access DSN installed using the godaddy control panel. But, uh oh... the new Access driver is the one with Unicode support (at least that's what I surmise without talking to the perky but useless godaddy support). That means that lots of things are now broken.
Broken Item 1: Permissions
In his old site he had set up his access drivers to use a username and a password. All of his queries look like this:
SELECT someCol
FROM someTable
WHERE someThing = 'someOtherThing'
</cfquery>
Broken Item 2: Yes/No Field
His code is replete with queries that check Yes/No Columns. These columns are Access' equivalent of Boolean or bit columns. Most of his queries read like this:
SELECT someCol
FROM someTable
WHERE myYesNo = 1
</cfquery>
SELECT someCol
FROM someTable
WHERE myYesNo = #yesNoVariable#
</cfquery>
SELECT someCol
FROM someTable
WHERE myYesNo = #yesNoFormat(yesNoVariable)#
</cfquery>
In the second case there was no variable - just a static constant in the query. In this case we simply converted the constant to an appropriate "yes" or "no" as needed. Like this:
SELECT someCol
FROM someTable
WHERE myYesNo = YES
</cfquery>
Broken Item 3: The "National" Column
Finally we had a number of update and insert queries that were erroring out but we didn't know why. We finally determined that the problem was a particular Yes/No column titled "national". This column refused to recieve an update or an insert. It turns out that under the new driver, national is a keyword. So we had to change this:
UPDATE myTable
SET national = YES
WHERE myField = 'something'
</cfquery>
UPDATE myTable
SET [national] = YES
WHERE myField = 'something'
</cfquery>
My advice to you, if you use access, is to seek help. Remember that the first step to recovery is to admit you have a problem. Meanwhile, I hope these tips help you if you ever run into this type of issue. Perhaps some of my readers can add their favorite tips to this post and further enrich our knowledge.
1 Comments
-
I can bet the biggest problem with moving to the new version of the driver was the plain fact that your friend doesn't use cfqueryparam. I've must of updated the JDBC drivers and the MDAC drivers a thousand times on my boxes and never had this problem with an access database.
Sites to Watch
Aggregators
RSS
