So I discovered a few things with Microsoft Access recently. It’s not my database of choice, but it’s very easy in my current work environment to use it effectively – and effectiveness is always the key. So we have been discussing more and better ways to implement security. One of the ways to do this is to change how the ID for a record is created. Access provides a way to use GUIDs for the AutoNumber column of your choice, but they’re very cumbersome – especially if you’re going to be passing them around in a secure app environment as a URL string. I know, URL Strings aren’t the best, but they’re fast. That references back to the same thing as effectiveness in my book! Well I didn’t like the way they looked so I changed back to a Number format and started getting very odd results for the AutoNumber. Very long integers as well as negatives and they were no longer sequential. That’ll teach me not to click on the “New Values” option of my AutoNumber fields. There’s an option there to change them to “Random” from “Increment”. This will help by providing a minor bit of security where users before might have been able to change the URL ID and change what record they were looking at.

The next problem this created for me was in the way I normally had been grabbing the newly created records in my Access tables. I guess it’s pretty standard for most people to do something along these lines when creating and inserting values into your access tables:

1
2
3
4
5
6
7
8
9
10

<cfquery name="test1" datasource="source1">
     INSERT INTO Table (datacol)
     VALUES ("datapiece");
</cfquery>
 
<cfquery name="getID" datasource="source1">
    SELECT MAX(ID) as MaxID FROM Table
</cfquery>
 
<cfset myID = getID.MaxID />

Well this didn’t work anymore with the Random seed for the AutoNumber. The max was no longer the last one inserted. It led to quite a bit of hair pulling on my part – and that’s an accomplishment when you’re bald. So I had heard from a co-worker that @@Identity did in fact work in Access. I hadn’t spent much time looking into it – being as I had a solution that worked (Max(id)) and I prefer to work in SQL Server or MySQL. But now I was presented with a need to figure it out. A few minutes of Googling led me to this article by Charlie Arehart from 2002(!) I was a little surprised to see it had been working for this long. So now I do my access inserts like this:

1
2
3
4
5
6
7
8
9
10
11
12

<cftransaction>
      <cfquery name="test1" datasource="source1">
             INSERT INTO Table (datacol)
             VALUES ("datapiece");
      </cfquery>
 
      <cfquery name="getID" datasource="source1">
             SELECT @@IDENTITY as MaxID;
      </cfquery>	
 
      <cfset myID = getID.MaxID />
</cftransaction>

And everything works out just fine. The keys are the CFTransaction tag and using two separate CFQuery tags. Now to go revamp a whole section of code to use this little tidbit!